The Scenario Analysis tool is a great way to quickly assess the impact of variations in key assumptions on the expected profitability of your rental property investment (also called sensitivity analysis or scenario analysis).
Our spreadsheet offers scenarios pre-built around 5 assumptions: Purchase Price, Gross Scheduled Income, Interest Rate, Down Payment and Rule of Thumb (as an estimate of operating expenses). Profitability impact is assessed based on 4 Financial Ratios: Internal Rate of Return (%), Cap Rate (%), Cash on Cash Return ($) and Cash Flow ($) in year 1.
With this tool, it’s easy to assess how your IRR, cap rate, CoC and cash flow metrics are impacted by a change in your purchase price or a lower than expected rental income. Note that when such analysis is performed, the spreadsheet will assume all other parameters remain constant.
To run the analysis, you need to input values for each of the 2 parameters displayed in the table (x and y axes):
Once your parameters have been set, you can run the analysis by pressing F9 on your keyboard. This will automatically populate and / or refresh all tables.
Important: Values of the parameters must be plugged and cannot be linked directly to another sheet of the workbook (like the Dashboard). Doing so would create circularities which would prevent Excel from running the analysis properly.
The exemple below shows how decreasing or increasing you gross scheduled income by $5,000 impacts your IRR, Cap Rate, CoC and cash flow. We set the mid-range at $100,000, which is our expected gross scheduled income but you can set that parameter to any value. Each of the below tables also shows the impact of a $100,000 variation in purchase price (with a mid-value of $5,000,000) on profitability.
How to read the results:
Terms & Conditions
© 2021 RealEstateSpreadsheets.com – All rights reserved.