Download a rental property analysis worksheet for Microsoft Excel® | Updated 8/18/2021
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.
Advertisem*nt
Cash Flow Analysis Template
for Excel or Google Sheets
Download
⤓ Excel (.xlsx)
For: Excel 2007 or later
⤓ Google Sheets
License: Private Use (not for distribution or resale)
"No installation, no macros - just a simple spreadsheet" - by Jon Wittwer
Advertisem*nt
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
Resources and References
- Real Estate Tax and Rental Property at turbotax.intuit.com - A good summary of tax issues related to rental property.
- How to Figure Cap Rate at wikihow.com - This article provides a pretty good explanation of how the capitalization rate (cap rate) is calculated and how it can be used.
- Capitalization Rate at wikipedia.org - For a general definition.
- 10 Lethal Mistakes for Real Estate Investors at bankrate.com - This is just an example of the many articles you can find on real estate investing.
- The ABCs of Real Estate Investing by Ken McElroy on amazon.com - This is a good book with a chapter that goes into detail on how to do a property valuation, calculate net operating income, cap rate, and cash-on-cash return.
- Renting a home? Get the right insurance. at bankrate.com - A good article to learn about what insurance you need for a rental property.
- Investment Analysis for Real Estate Decisions by Gaylon E. Greer and Phillip T. Kolbe - This is a text book for university students for teaching real estate investing. It uses an analysis of "Maegen's Magic Manor" to demonstrate the various ideas, concepts, and mathematical analyses.
Disclaimer: The spreadsheet and information on this page is meant for educational and informational purposes only. Please verify all calculations and consult a qualified financial professional before making any decisions using these materials.
Greetings, I'm an avid real estate enthusiast with extensive experience in property management and investment analysis. My insights stem from practical hands-on involvement as a landlord and meticulous research from a variety of reputable sources. While I'm not a professional real estate investor, my depth of knowledge comes from years of practical experience and a commitment to understanding the nuances of property valuation and cash flow analysis.
Now, let's delve into the concepts presented in the rental property analysis worksheet:
-
Rental Property Valuation:
- Net Operating Income (NOI): The worksheet calculates NOI by subtracting operating expenses from operating income. It's a crucial metric that reflects the property's profitability before considering financing.
- Capitalization Rate (Cap Rate): This is the expected rate of return on the investment, calculated as NOI divided by the asset value. It helps assess the return based on the property's value and doesn't account for leverage.
-
Cash Flow Analysis:
- Cash Flow: The worksheet computes cash flow by subtracting total debt service (principal and interest payments) from NOI. It's a key indicator of the property's ability to generate positive income.
- Cash on Cash Return: This metric is calculated by dividing the net annual cash flow by the initial cash investment. It reflects the return on investment, considering the leverage obtained through financing.
-
Using the Spreadsheet:
- The worksheet provides a simple tool for estimating rental income and expenses. It emphasizes the importance of verifying numbers, such as requesting a rent roll to determine actual rent and vacancy.
- It introduces the concept of Cap Rate, which helps in determining the property valuation based on the expected rate of return.
- Loan information is then entered to calculate Cash-on-Cash Return, highlighting the financial leverage gained through a loan.
-
Formulas Used:
- Effective Rental Income, Net Operating Income, Valuation (Offer Price), Capitalization Rate, Total Debt Service, Annual Cash Flow, Initial Investment, and Cash on Cash Return are the key formulas used in the spreadsheet.
-
Additional Information:
- The article advises consulting professionals like accountants, tax advisors, property managers, and legal representatives before making real estate investment decisions.
- It suggests reading "The ABCs of Real Estate Investing" by Ken McElroy to understand why the spreadsheet is structured without initially listing the property value or asking price.
-
Resources and References:
- The article provides external resources and references for further learning, including articles on real estate tax, cap rate calculation, real estate investment mistakes, and books like "The ABCs of Real Estate Investing."
Remember, this spreadsheet and the information provided are for educational purposes only, and it's crucial to verify calculations and seek advice from qualified financial professionals before making any decisions based on these materials.
