Real Estate Cash Flow and Sensitivity Analysis

In March, I posted about five common real estate expenses and how they affect your rental property’s net income. Recently, I have started to research purchasing multi-family real estate in Philadelphia, and needed a tool where I could easily shift data around. I came up with a worksheet that allows me to input income and expenses, calculate a capitalization (cap) rate and required equity, and calculate a sensitivity analysis for permanent financing. The proceeding are screen shots of my spreadsheet and an explanation of the sections. As usual, all data is for information purposes only.

Income & Expenses (P&L):

This section is basic and includes all income streams and all expense streams for the property. Many of the properties that I have been researching contain four-units, but lines for rental income can be added or subtracted as desired. Operating expenses are from the aforementioned blog post and can also be added or subtracted as desired. There are a few formulas which calculate the vacancy factor, maintenance, and management fees; which are noted in the description.

Capitalization Rate & Equity Injection:

With all of my workbooks, I use a separate worksheet to keep all of my repetitive data concentrated. This takes slightly longer to setup, but makes changing the data much easier. In the “Cap Rate” line, the percentage pulls from a range of capitalization rates selectable by a drop down list defined in the other worksheet. For this type of real estate, I estimate a range of 8.00% to 10.00%, increasing at increments of 0.50%. The valuation; in this case $557,830, calculates by dividing net income with the capitalization rate. Equity injection is entered directly to this worksheet, takes the defined percentage of the valuation, and is used to calculate the required loan amount.

Sensitivity Analysis:

This section has four slots for a permanent financing sensitivity analysis, and allows you to test changes in interest rate, amortization length, or both. Similar to the capitalization rate above, the interest rate and amortization length on the right-hand side pull from another worksheet via a drop-down box. Interest rates range from 6.75% to 7.75% in 0.25% intervals, and amortization lengths range from 10 years to 30 years in 5 year increments. The text box on the left-hand side updates automatically from this data so they don’t have to be changed manually. The middle columns calculate the monthly and annual debt service from the loan amount above, and the interest rate and amortization length from this section. The DSCR section at the bottom calculates by dividing net income with the annual debt service for each scenario.

Putting it Together:

This is how the cash flow looks with all three sections together. As always, feel free to contact me with any questions you may have.

Technorati Tags: Amortization, Cap Rate, Cash Flow, Debt Service, DSCR, Financing, Real Estate, Sensitivity Analysis

Blogging and the Web: Follow Up

In my blog post from yesterday, I mentioned how I use RSS feeds to efficiently browse web content. Today, I found this video which does an excellent job of explaining the concept in plain english. Enjoy!

EDIT: The link I originally posted stopped working, but found it on Viddler.

Technorati Tags: howto, RSS, Viddler

Blogging and the Web: How it’s Helped Me and can Help You.

When I started this blog, it was because I had a lot of ideas and I wanted to organize them. For a few months I had been keeping my thoughts offline in a journal and realized that if I published them, I would have an opportunity to help others and not just myself. The secondary benefits of this website have been to search engine optimize (SEO) myself as well as create a living, interactive resume. This is especially important to me because I was switching positions and have to ensure my online “brand” is professional. Appreciation goes out to Alex Hillman; a friend of mine from college, who helped me find hosting and setup my blog.

With the necessary infrastructure in place, it was time to fill the site with content. I chose to focus primarily on real estate because that is where my experience is concentrated. I started with basic ideas and have begun moving towards ideas that go into further depth. While updates have slowed slightly, it is due to a position change and updates will resume shortly. My new position will allow me to continue talking about real estate, and enhance my knowledge of commercial and industrial (C&I) lending.

Immediately I noticed the importance of Technorati and it’s tagging system, which is used to continuously index blogs. Within a days of posts, a search for my name in Google came up with this blog and my “about me” or resume page. I have also noticed that once my blog was filled with content, it was being accessed by a number of customers across the country. With my primary and secondary goals on track, I started to look into the numerous web applications available to help make my life more efficient.

Emurse is an online resume tool that allows you to create and store your resume online. A great feature of the site is that your resume is easily viewed with an option to download in a number of formats including rich text, pdf, and Microsoft Word format. Emurse also has a great profile section, which can be viewed from your resume page.

Twitter is a a mini blogging tool that asks one question: What are you doing? The service allows you to follow users or be followed. Posts are short; 140 characters to be exact, and can be sent or received through the web, standalone programs like Twitterific, text message, or the AIM instant messaging client. The service has been used in a number of manners including posting articles, documenting trips / events, and letting your friends in the area know where you are.

Netvibes was introduced to me this weekend by my friend Brad Levinson, and offers its users the ability to cater the web to their needs through RSS feeds. Content pages are organized by tabs and the pages are customizable from one to four columns. Here are a few examples of how I have my Finance and Philadelphia tabs setup, just click on the thumbnails.

It is important to remember that none of this is possible without those that create all of this content. A few weeks ago I had the opportunity to get together with a coworking group that Alex started called Independents Hall. The premise of these communities is to allow the independent community a physical location to work, brainstorm, and create discussion. It was an excellent chance for me to sit, listen, and learn for a few hours about what is developed, being developed, and etc. I encourage anyone who would like to learn more to contact Alex directly.

Technorati Tags: Alex Hillman, Brad Levinson, coworking, emurse, Independents Hall, Netvibes, Twitter

Sample of a Construction Cash Flow

Last week I discussed my Four Tips to Calculate an Interest Reserve. Realizing the importance of providing a visual example, I have provided a sample construction cash flow that I created in Microsoft Excel. The example is for a six-building construction project with staggered stages of development, and is populated with simulated data. If required, a larger version of the image is also available.

• The Monthly Draw section shows how much money is required for the project on a monthly basis. In January and February 2007, the customer is developing the work site, and the cost has been spread over all six buildings. In the proceeding four months, the construction will begin, and those costs are assigned to each individual building. The total of monthly draws sums up the total cost on a monthly basis.
• The Total Loan Outstanding section demonstrates how much of the construction loan has been borrowed on an accrual basis. The accrued construction line keeps a running total of construction costs after every month. This is calculated as the sum of all previous months, plus any new costs in the current month. The accrued construction costs are added to any non-construction costs (soft costs, acquisition costs, etc) to create the total loan outstanding. Interest is calculated on a monthly basis by multiplying the total loan outstanding and the projected interest rate; which in this case is 9.0%. It is important to note two things about the interest rate; it should be expressed as a decimal, and must be divided by 12 because it is being calculated on a monthly basis. In this example, an annual interest rate of 9.0% would be expressed as (0.09/12).
• The Rental Income section is used for any income generated by the property on a monthly basis while it is under construction. The section allows for gross rental income, any expenses that may be expected, and net rental income. Net income would be applied towards the monthly interest expense of the property, which reduces the required interest reserve. In this example, the property won’t be generating any income so it has been left blank.
• The Interest section further breaks out interest by expense category. In this example it is the acquisition, soft, and closing costs; which would be paid out immediately, and construction. Total interest should equal the earlier interest calculation.
• The Cash Flow Cushion / (Shortfall) is the difference between monthly interest and any income generated by the property.
• The Interest Reserve section keeps a running total of how much has been used, based on all of the aforementioned sections.

If interested further, please download the full construction cash flow.

Technorati Tags: Cash Flow, Construction, Interest Reserve

Traditional Cash Flow versus UCA Cash Flow

One of the benefits of completing a formal credit training through a third party, is the exposure to different analysis procedures. My formal credit training was provided by the Risk Management Association (RMA) Commercial Lending School; and although it was short in duration compared to traditional institutional programs,
made an excellent accentuation to my real-life banking experience. I consider the course on the Uniform Credit Analysis (UCA) Cash Flow method invaluable because it is a debt service analysis tool created by bankers for bankers.

Traditional Cash Flow:

The traditional cash flow method relies solely on the income statement for calculating a company’s ability to repay its debts. The cash flow is created using a “bottom-up” approach beginning with net income and adding certain non-cash and cash expenses back to the cash flow. Expenses which are traditionally credited in the cash flow are amortization and depreciation
(non-cash), and interest and taxes (cash) creating what is known as earnings before Interest, Taxes, Depreciation, and Amortization (EBITDA). Debt service through the traditional method is calculated by summarizing the company’s prior-year current portion of long term debt (CPLTD), current year interest expense, and the projected annual debt service from new debt facilities. EBITDA is divided by total debt service to create a debt
service coverage ratio (DSCR) which demonstrates the company’s ability to repay it’s debt. For example, a DSCR of 1.25x states based on the preceding, the company has enough cash flow to cover it’s debt payments by 125%.

UCA Cash Flow:

The UCA cash flow includes a mixture of balance sheet and income statement data which portrays actual changes to cash on hand from a year-to-year basis; which provides a multi-dimensional view into ability to service debt. Balance sheet items included which are not covered by the traditional cash flow include; but are not limited to, actual cash collected from sales, fluctuations in inventory levels, and changes to accounts payable. The cash flow gives an understanding of why cash increases
or decreases between any two periods. The UCA cash flow is split into two main categories; cash flow generated from operations after all core operating expenses have been removed, and the ensuing increases or decreases to cash. While the former is intricate to the UCA cash flow, for analysis purposes is highlighted as net cash after operations (NCAO). This is the equivalent starting point of net income when compared to the traditional cash flow.

The UCA cash flow utilizes a “top-down” approach beginning at NCAO and continuously reconciling how much cash a company has on-hand after a number of activities.  The cash flow can be broken into three sections; debt amortization, capital expenditures, and financing activities.

• Cash after debt amortization (CADA) highlights how much cash a company has on hand after paying its interest payments, shareholder dividends, and current year CPLTD. If CADA results in a negative figure, it demonstrates that a compay cannot pay it’s debt through it’s core operations.
• Capital expenditures, or fixed assets, is removed from the cash flow and demonstrates how much cash remains or financing required. A financing requirement does not automatically equate to a problem because many companies utilize debt to finance equipment upgrades, expansions, and etcetera. However, it is important to understand the difference between extraordinary expenditures and those required from a year to year basis.
• Financing activities demonstrate cash generated or reduced when borrowing or repaying loans. Traditional methods of borrowing funds include short term borrowings through a line of credit, long term borrowings through term facilities, and borrowing money from shareholders; and support working capital and capital expenditures. It is important to note that a -0- in this section doesn’t mean that no funds are outstanding, but that the loan amounts have not changed in a given period.

Ending cash is calculated by reconciling the cash surplus or deficit to beginning cash, and demonstrates funds available to service any new debt. The DSCR is calculated by dividing ending cash by the projected annual debt service from new debt facilities, providing an outlook into the company’s ability to repay. Similar to the traditional cash flow, a DSCR of 1.25x states based on the preceding, the company has enough cash flow to cover it’s debt payments by 125%.

The charts provided are from my final Commercial Lending School case study where  the company is purchasing its office and manufacturing facility from a third party, The charts highlight how the two methods can lead to conflicting results based on the exact same data.If you would like more details about this example, please view my writing sample.

Technorati Tags: Commercial Lending, RMA, Financial Statements, Traditional Cash Flow, UCA Cash Flow

Four Tips To Calculate an Interest Reserve

In integral part of any construction financing package is to create a reserve of funds to pay the monthly interest. This is possible because construction financing traditionally requires only interest payments to allow you to focus on completing your project. Interest reserves are common in construction lending but calculating the amount can be complex; especially with larger projects. If the reserve is too small it will
run out before the project is complete, and if it is too large you will over pay on loan fees.

• Budget your project: There are a number of expenses that you will face including your acquisition cost (if you don’t own the property), construction costs, and soft or other costs. Construction costs primarily consist of your materials and labor, while soft costs include insurance, taxes, legal and professional fees, and marketing. Remember that setbacks with any project can occur so adding a small contingency to you budget to account for these issues is a good idea.
• Plan your timetable: Once you have budgeted, plan out the construction time frame in bi-weekly or monthly increments. This will give you an understanding of how much of your construction loan will be outstanding at every period, which is important for calculating your reserve. Also, it will most likely be required by the financial institution you are doing business with. It is important to keep in mind that delays may occur in construction so make sure to plan for them.
• Know your interest rate: Construction loans are based off of either the Wall Street Journal Prime Rate (Prime) or the London Interbank Offered Rate (LIBOR) rate index. The former is tied to the federal funds rate, which may change at irregular intervals while the latter is quoted at intervals of either three,
  six, or twelve months. Talk with your financial institution about which rate index they will use, and how many basis points they are charging.
• Putting it all together: Your monthly interest expense is calculated when you multiply the outstanding loan balance at month’s end by your quoted interest rate, and your estimated interest reserve is the sum of those monthly interest payments.. It is important to note that you must divide the interest rate by 12, to accurately reflect monthly payments.

Technorati Tags: Interest Reserve, LIBOR, Prime, Construction Loan