Download our Rental Cashflow Analysis Worksheet
I often help clients do cash flow analyses for their properties. Sometimes I work up the analysis and a custom spreadsheet just for my client’s project. I also use tools and spreadsheets developed by the CCIM organization. For clients looking for a simple tool they can use as an initial step in analyzing a property, I provide them with this simple Excel workbook.
You can download the spreadsheet from our website and send us an email if you would like help. Here is a download link:
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 minus 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 highest cap rate for a given price is the better investment.
The Property Cash Flow Analysis Workbook
The workbook is made up of 6 worksheets:
- Instructions – provides instructions for how to use the spreadsheet
- Property description – (Optional) used to enter the property address and description
- Income – Rental and other income
- Expenses – Annual and monthly expenses
- Loans and debt – loans and debt servicing
- Cash flow -the resulting cashflow and property value analysis
This worksheet provides instructions for using the workbook. The worksheets are password protected to prevent modifying the formulas. This should not prevent you from using the workbook. You can update all the columns you need.
Property description worksheet
This worksheet provides a place to describe the property. There is a place for the property address, a place for a description of the property, and a places to describe each type of rental unit.
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). If you have multiple units that have the same rent, you can enter the total monthly rent for all the units in one entry. If you have other income from the property, such as from laundry rooms, vending machines, or extra storage, enter that information in the appropriate rows. Finally, enter in your estimated vacancy allowance (the percentage of time each unit will be vacant over the course of a year, on average). 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.
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.