I’m always helping clients do cash flow analysis for their properties. Usually I just work up a spreadsheet at the time of the analysis. I finally decided to spend an afternoon and put together a spreadsheet that I could reuse over and over.
This spreadsheet is done as a workbook with 4 pages. Here is a description of the various worksheets and how to fill them out.
The Property Cash Flow Analysis Worksheet
It shows the results of the analysis. The one editable area of the worksheet is the Property Address section, where the user can input the address of the property they are analyzing.
Along with the Property Address the Income section, which shows the gross yearly income, the vacancy allowance, and the resulting effective gross income. The gross income and vacancy allowance are pulled from the “Income” worksheet.
The next section shows the Expenses, pulled from the “Expenses” worksheet.
These numbers are used to calculate the Net Operating Income. This number is used to calculate a range of property values based on various Cap rates.
The next section of this sheet is the Debt Service section which pulls the total annual debt service from the “Loans and Debt” worksheet.
The last section is the Property Value section. As mentioned earlier, this section provides a range of estimated property values based on the Annual Net Income and various Cap Rates.
The Income Worksheet
The Income Analysis worksheet provides sections for entering monthly income for up to ten units (if there is only 1 unit, like a single family home, only enter a value in one of the cells). You can also enter in estimated monthly incomes from extra sources like Laundry, storage units, etc.
The last value the user enters is the estimated annual vacancy rate.
From this information, total monthly income, total annual income, and effective annual income are calculated. The total annual income, annual vacancy allowance dollar amount, and effective gross income are automatically transferred to the Property Cash Flow Analysis worksheet.
The Expenses Worksheet
The expenses worksheet has an area for entering Annual Expenses, like taxes and insurance so you don’t have to figure out the monthly amounts ahead of time.
The next area is for monthly expenses. There are categories for most expenses you will need, plus a few that are less common. You should be able to put in actual numbers, based on history, for things like utilities, and estimated/budget numbers for things like advertising. The main thing to remember is to include all your expenses.
The worksheet will calculate the monthly amounts for your annual expenses and the total monthly and annual expenses. The Total Annual Expenses is transferred to the Property Cash Flow Analysis worksheet automatically.
The Loans and Debt Worksheet
The Loans and Debt worksheet provides debt service info. It allows for up to 3 fully amortized mortgages and 3 interest only loans, or more importantly.. combinations of them. It does not cover partially amortized loans with balloon payments.
What to use the spreadsheet for
The main use for the spreadsheet is to figure out if a prospective investment property will ‘cash flow’ (Income Less Expenses is greater than Debt Service). In most cases, investors want positive cash flow. For some properties, they may be willing to put up with a small negative cash flow before taxes.
The spreadsheet can also be used for comparing property values. All other things being equal (which admittedly they seldom are) the property with the best cap rate for a given price is the better investment.
Download the spreadsheet from here.
If you don’t have Excel 2007 or Excel 2010, try out Office Web Apps for free </a