Rental Property Cash Flow Analysis
This spreadsheet is for people who are thinking about purchasing rental property for the purpose of cash flow and leverage. It is a fairly basic worksheet for doing a rental property valuation, including calculation of net operating income, capitalization rate, cash flow, and cash on cash return.
This worksheet is not going to teach you how to be a good real estate investor. It is just a simple tool to help you put into practice some techniques for property valuation and cash flow analysis. Disclaimer: I am not a professional real estate investor. I created this spreadsheet based on experience as a landlord and from various references.
Cash Flow Analysis Template
for Excel
Description
The calculations for doing a rental property valuation and cash flow analysis are not very complex. This Excel spreadsheet makes things even more simple by providing a convenient way to calculate and compare results.
Edit the cells with the light blue background. Always double-check calculations because you don't want to make an important financial decision only to find out later that you had accidentally overwritten or messed up one of the formulas.
For example, if you add more rows to the operating expenses, double check the formula used to total the expenses to make sure it is summing all of the expenses.
The numbers included in the spreadsheet or in the screenshot above are theoretical examples only and are provided only to help show you how to enter data. Some basic instructions for doing the analysis are included below, but you should also consult your team (accountant, tax advisor, property manager, legal rep, etc.) before making real estate investment decisions.
You can find other spreadsheets that provide a more thorough investment analysis (such as 10-year cash flow projections). This one was designed for people who are still learning the basics of rental property investing for cash flow.
Update 10/26/2018 - Fixed error associated with cells D50-D51 in scenario B. Formulas are now the same as cells C50-51.
How to Use this Spreadsheet
Step 1: Estimate Rental Income and Expenses
You may be able to get some information from a real estate sales brochure or proforma, but you should also verify all numbers. For example, you could request a rent roll to determine actual rent and vacancy in the past year.
The expenses will depend on many things, including the type of property, age, location, condition and whether you are using a property management firm or trying to handle it all yourself. You can usually find out the exact real estate taxes by looking online. Your property manager may be able to help you come up with estimates on other expenses. Like any investment, it is extremely important that you do proper research before purchasing real estate.
Step 2: Enter a Cap Rate to Calculate the Property Valuation
With an accurate picture of what rent you can charge and the operating expenses, you can now enter your desired capitalization rate (or the cap rate you can reasonably expect for your location) to determine the property valuation, or the initial offer price.
What is Cap Rate?
The capitalization rate is your expected rate of return on your investment, calculated as Net Operating Income divided by the Asset Value. It has to do with whether the income minus expenses provides a decent return based on the value of the property, and does not take into account leverage (money you may have borrowed). If you pay cash for the property or fully pay off the loan, this is the return you'd be expecting. If it's under 3%, you should ask yourself if it might be easier to invest in a CD. If it's over 10%, you are receiving excellent income compared to the value of the property.
Next, enter the actual purchase price. The loan information is based on the actual purchase price.
You will notice that in this worksheet, we didn't start off by listing the property value or asking price. Read the book "The ABCs of Real Estate Investing" by Ken McElroy if you'd like to understand why I set up the spreadsheet the way I did. (hint: the valuation does not depend on the asking price)
Step 3: Enter Loan Information to Calculate Cash-on-Cash Return
The financial leverage you get from a loan is one of the main purposes of investing in rental property. The cash-on-cash return is the key metric calculated by this worksheet. It is the net annual "cash flow" divided by your initial "cash" investment (thus "cash on cash"). The cap rate percentage is the same regardless of whether you have a loan or own the property outright. The cash-on-cash return is where you see the effect of leveraging the bank's money.
The spreadsheet assumes the loan is a fixed rate loan. Enter your down payment, fees, and interest rate to calculate the initial investment and total debt service.
Note that the net cash flow and the cash on cash return are both pre-tax calculations. Even though there may be additional tax benefits such as depreciation and deduction of interest payments, these are not part of the cap rate, cash flow, or cash on cash return calculations.
Summary of the Formulas Used
Effective Rental Income = Rental Income - Vacancy and Credit Losses
Net Operating Income = Operating Income - Operating Expenses
Valuation (Offer Price) = Net Operating Income / Desired Cap Rate
Capitalization Rate = Net Operating Income / Purchase Price
Note: Capitalization rate may be based on the current property value instead of the purchase price.
Total Debt Service = Principal Payment + Interest Payment
Annual Cash Flow = Net Operating Income - Total Debt Service
Initial Investment = Down Payment + Acquisition Costs and Loan Fees
Cash on Cash Return = Annual Cash Flow / Initial Cash Investment
