This .xls spreadsheet was designed to make it easy to calculate total and average annual ROI for rental property investments. All the assumptions exist on a single tab for easier use and sensitivity analysis on key variables.
$45.00 USD
Update: The maximum analysis period was extended from 10 years to 30 years.
All return metrics and the DCF Analysis are broken up between a leveraged scenario and unleveraged scenario. Unleveraged just means no debt financing or debt service included in the cash flow.
Assumptions:
- Purchase Price
- Percentage of Purchase Price Financed
- Renovations
- % of Renovations Financed
- Hold Period (in years up to 30 years)
- Exit cap rate and selling fees
- Monthly rent and rent increase
- Monthly expenses and expense increase
Based on those inputs, a monthly 10-year pro forma will populate that drives down to net operating income and cash flow levered and unlevered.
Output Return Metrics:
- Levered Total Cash-on-Cash return
- Periodic Cash-on-Cash return
- Average Annualized Cash-on-Cash return
- Unlevered Total Return-on-Investment
- Periodic Return-on-Investment
- Average Annualized Return-on-Investment
- NPV (levered/unlevered) based on defined discount rates
- Visualizations for cash flow
There is a separate tab for annual cash flows and on that tab there are per period and cumulative ROI (Return-on-Investment) and CoC (cash-on-cash) calculations.
Different Between ROI and CoC Return for Rental Properties
Note that ROI is different than CoC in that ROI only looks at unleveraged situations where you compare the cash returned against the total investment before any debt is used. CoC simply looks at the total cash returned against total cash invested after any debt. This is why there is no such thing as a leveraged ROI. It is a leveraged CoC return.
I did not include any complex logic in here such as refinance logic assumptions and cash flow waterfalls since it is supposed to be a simple way to analyze an investment in a single rental property. I can do custom work to add whatever you like though. Just email me (jason@smarthelping.com)
If you wanted to use this spreadsheet in Google Sheets instead of Excel, simply upload it. The visuals might come through a bit odd, but it is easily adjusted. All the formulas and logic will work exactly the same.
You may also be interested in:
- Multifamily Investment Analysis (up to 4 properties/refi logic/CF waterfall with IRR hurdles)
- Short-term Rental Scaling Financial Model
