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

Posted on April 25th, 2007 | By: bootstrap economist | Filed under Banking

Leave a Reply

You must be logged in to post a comment.