Rental Property Analysis Excel Template
This article tutorial will teach you how to use our free rental property analysis spreadsheet. Many real estate investors dabble in apartment investing and rentals (single-family homes up to fourplexes) and have asked for a functional template to handle residential properties.
I suggest you download the template now to follow along throughout the tutorial.
Download our Free Tactica RES™ Residential Rental Property Analysis Spreadsheet
A robust analysis spreadsheet will allow you to analyze a 1-4 unit property in just 15-20 minutes. Enter your details below to get access.
Rental Property Cash Flow Spreadsheet Contents
The simple template emphasizes cash flow as the primary investment metric and will serve as a quick cash flow analysis spreadsheet that will project stabilized cash flow and annual yield. It's meant to provide an operational snapshot where you can alter the purchase price, capital expenditure (CAPEX) assumptions, financing terms, revenue, and expense assumptions and see how it affects the cash-on-cash return.
Like all Tactica financial templates, cells with brown text are your responsibility.
This first section is where you will enter the primary property information. It should be reasonably self-explanatory.
The most crucial assumption is the pricing you are willing to pay. If you are looking at MLS listings, this is where you would plug in the realtor's asking price. It is highlighted in blue, so it stands out.
You will then enter all your debt assumptions.
Type: Bank, Conventional, and VA would all suffice. This assumption doesn't affect any numbers; it is for informational purposes only.
LTV/LTC: You can choose either an LTV (loan-to-value) or an LTC (loan-to-cost) option. Your election will significantly impact how much equity you must bring if the property requires considerable rehab. The CAPEX section is the next section we will cover. LTC financing will cover a portion of the capital budget, while LTV financing will not.
Interest Rate/Amortization/Loan Cost: You would enter whatever mortgage terms you discuss with a financial institution. If you want to factor in closing costs, you could do that in the loan cost cell. Currently, I have the loan cost set as 3% of the total loan amount. If there were other closing costs you want to account for, you could adjust this cell to 4% or 5%. The gross loan cost is calculated right below.
Loan Amount: The loan amount will be calculated differently depending on if you choose LTV or LTC in cell "8."
Loan Amount with LTV = Purchase Price * LTV %
Loan Amount with LTC = (Purchase Price + CAPEX) * LTC %
Monthly Payment: The mortgage payment (principal and interest) will be calculated automatically.
Monthly PITI: Monthly principal, interest, property tax, and insurance will all be calculated here. I will show you shortly where you make insurance and property tax assumptions in this financial template.
Total Equity Required: This is what you will need to bring to the closing table in the form of a down payment.
Equity Requirement = Purchase Price + Capex + Loan Cost - Loan Amount
The CAPEX section is where you can log all the major capital expenditures. Commonly, this is where you'll account for significant deferred capital. Things like:
Roof replacements
New siding
Major interior renovations
Landscaping
Add-ons/Additions
Structural enhancements
If no capital projects are planned, it may be wise to factor in a working capital budget, which could mainly be a reserve for something that comes up after taking over ownership.
The operations section is where you will input all of your monthly assumptions. You'll notice you can input rental income for up to four units. If you don't need all four, you can type in $0, as I did in this duplex scenario. The monthly rent and all other monthly assumptions are annualized to the right.
You should get a good idea of potential market rents by looking at similar properties on Zillow, Craigslist, or apartments.com. Don't forget to research if there are opportunities to collect ancillary income for items like quarter laundry, parking, and storage. You will also need to put in a vacancy rate. You can see that I am using 5%.
For operating expenses, you may be able to get historical utility bills from the seller. You'll be able to get an insurance estimate before offering. Administrative costs include rental licenses, permits, and other miscellaneous expenses. Thanks to free aggregator sites, most investors I know don't need to pay anything for marketing.
Repairs are the easiest expenses to understate. Old houses will typically be more labor-intensive. If you are not handy, expect to pay a premium to fix things, as you must contract everything out. If you plan to utilize a property manager, they should be able to help you fine-tune your expense budget. They will also inform you of their property management fee (usually as a percentage of total revenue). In my example, I am self-managing.
Replacement reserves are a good idea and provide a natural safety net for your proforma underwriting.
The net operating income (NOI) and the cash flow after debt service are summarized. All monthly cash flows are annualized.
The only expense line you don't touch in this section is for property taxes flowing in from a different part.
Whenever you are investing in any real estate, it behooves you to do your due diligence and review the county website to find:
Assessor's market value of the property
The annual property tax payment for the current year
Once you enter that information, the template will calculate the monthly taxes payable and the tax rate ( taxes payable / property assessment).
The final assumption you must make is a reassessment as a percentage of the sales price. If you pay more than the assessor's property value, do you expect them to adjust the assessed value once they review the sale? If so, 100% would be a conservative estimate for the reassessment percentage. When I purchased my duplex in 2015, the county assessment was more than what I paid for the property in just two years!
This reassessment percentage will determine your future property taxes payable. The template will multiply today's tax rate (1.34%) by the projected upcoming assessment.
Future Taxes Payable = Reassessment % * Price * Tax Rate
$3,223 = 100% * $240,000 * 1.34%
The monthly property taxes payable then flow into the operations.
Hopefully, The final section will make you comfortable enough to make an investment decision. Firstly, the total cost basis of the rental property investment is summarized. I've never believed in using cap rates for small rentals (I hesitate to rely on them on large apartment buildings), so the first metric in focus is a Yield on Cost (YOC). This calculation is:
YOC = NOI / Total Cost Basis
The YOC doesn't account for any leverage; it shows you how your annual returns would look with an all-cash purchase (including paying cash for renovations).
The next metric is the Cash-on-Cash Return. This calculation is:
Cash-on-Cash = Cash Flow / Total Cash Invested
It's essential to verify that:
Cash-On-Cash > YOC
If true, leverage is accretive and will boost your annual yield. Utilizing debt will yield more lucrative returns than just purchasing the project in cash.
Finally, the template will summarize:
Gross Rent Multiplier (GRM)
Debt Service Coverage Ratio (DSCR)
Expense Ratio
This tool is a great template to use if you are looking at a one-off turnkey rental property. For veteran investors, there are some shortcomings regarding the following business plans.
Residential Investment
Multifamily Turnkey/Value-Add
Multifamily Redevelopment
Multifamily Development
Residential Investment (Turnkey or “Fix”)
This free residential template lacks multiple analysis features that are presented in the paid Residential Analysis Model:
Investment overview dashboard
Appreciation assumption
Residual sale assumption
IRRs and equity multiple for multiple holding periods
Rehab module (repair timing, draws, interest expense)
“Fix & Flip” vs. “Fix & Hold” comparison
Multifamily Turnkey/Value-Add
This template isn’t sufficient for multifamily properties with renovation opportunities.
Multifamily Redevelopment
Once the property is stabilized, this template won’t be suitable for apartment repositioning efforts that require bridge financing and a refinance. The paid Redevelopment Model is perfect for messy rebranding efforts.
Multifamily Development
This template isn’t adequate for ground-up construction to adaptive reuse. We offer site visitors:
Miscellaneous Analysis Features
This template lacks creative ways to underwrite capital expenditures, sensitivity analysis, and an investment overview dashboard highlighting all significant investment facets.
If you're interested in the inner-working of this tool to maximize its utility with advanced Excel knowledge, we recommend visiting these articles:
We plan to publish more posts about the commonly used Excel functions and the major analysis components within the Tactica workbooks.
Summarizing Rental Property Cash Flow Spreadsheet
This concludes the rental property investment analysis spreadsheet tutorial. This tool should be incredibly helpful in vetting a potential rental acquisition opportunity quickly and accurately. As long as you have an annual yield target you’d like to achieve, the template will promptly verify if your goals are attainable at various pricing thresholds.