Description
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):
- Mid-Value: represents the central value of that parameter in the table (for instance: central row for purchase price or down payment / central column for gross scheduled income or Interest rate)
- Interval: defines the increment / reduction in value from one row (or column) to another for that particular parameter
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.
Exemple
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:
- Assuming a purchase price of $5,000,000 and a gross scheduled income of $100,000 (both being our mid-range values), we can expect an investment IRR of 18.6%, a cap rate of 9.8%, a cash on cash return of 7.2% and $6,641 in monthly cash flow during the first year (as displayed in the center cell of each table). Should the gross scheduled income decrease by $5,000, projected IRR would decrease to 16.9%, Cap rate to 9.2%, CoC to 4.4% and monthly cash flow to $4,099.
- Conversely, an increase of gross scheduled income to $105,000 (+$5,000) associated with an increase to the purchase price to $5,100,000 (+$100,000) would lead to a project IRR of 20.0%, a cap rate of 10.3%, a CoC of 9.2% and a monthly cash flow of $8,632
