Is there an easy way to do a sensitivity analysis using variables that are not linear (i.e. the variable input changes throughout the hold period)?
For example, say rent growth is something like Yr1=0% // Yr2=1% // Yr3=1.5% // Yr4=3.5% // etc. Is there any way to sensitize my IRR to rent growth?
I know how to run a sensitivity if my rent grow was constant at say 3%, but most of the time I never use a linear growth rate.
Thanks for any help. Also if you know of any resources that address this type of sensitivity, that would be greatly appreciated.
Ah, you're diving into the more intricate aspects of financial modeling, aren't you? Sensitizing IRR with variables that are non-linear, like your example of staggered rent growth rates, is indeed a more complex task compared to linear variables. However, it's not only possible but also quite insightful for understanding the nuances of your investment's performance under various scenarios.
Based on the most helpful WSO content, here's how you might approach this:
Use of Advanced Excel Features: For non-linear variables, you can leverage Excel's capabilities such as Data Tables, Scenario Manager, or even more advanced features like Solver. These tools allow you to run multiple scenarios with different inputs and see their impact on your IRR. For your specific example of rent growth, you would manually adjust the growth rates for each year in your model and use a data table to see how these changes impact the IRR.
Custom Sensitivity Analysis: Since your growth rates change annually, you'll need to create a custom sensitivity analysis. This involves setting up your model so that you can easily adjust the growth rates for each year and observe the impact on the IRR. You might create a separate section in your Excel model where you input the different growth rates for each year, and then link these inputs to your main model calculations.
Financial Modeling Software: There are more sophisticated financial modeling software options out there that might offer more streamlined ways to handle non-linear sensitivities. These tools often come with built-in functions for running complex sensitivity analyses and might be worth exploring if you're frequently dealing with non-linear variables.
Learning Resources: For resources, the WSO DCF Modeling Course provides a solid foundation in sensitivity analysis, including how to set up your model for various types of sensitivities. While it might not cover your specific non-linear scenario out-of-the-box, the principles of sensitivity analysis it teaches can be adapted to your needs.
Remember, the key to successfully sensitizing non-linear variables like rent growth is in how you structure your model. It needs to be flexible enough to easily adjust for different scenarios, yet robust enough to accurately capture the impact of those changes on your IRR. It might take some time to set up initially, but the insights you gain from this analysis can be incredibly valuable for making informed investment decisions.
Sources: Trying to understand the concept of IRR, DCF Modeling Course ~ Pre-training text.pdf, Q&A: Non-Target School to Portfolio Manager at a Top Hedge Fund – 6 Years Out of Undergrad, Sensitivities......, Return sensitivities change too little - potential errors?
If the inflation rate follows a mathematical formula (e.g. y=a+bx or y=a+(1+b)^x, where Y is the cumulated inflation ratio and X is the year) you can sensitize the variable b.
Otherwise you can make a sort of scenario analysis like this
https://i.imgur.com/McOL27a.png
Hope this was helpful
This is a great response
Yes. You tie the growth rates for all 4 years to one variable in a table. Set the formulas within the actual model to look up and say OK, if "variable" = 0, then my growth rate in yr 1 is x, yr 2 is y, etc. Then you can manually plug a different set of growth rates for "variable" = 1, "variable" = 2, etc. etc.
Then when you go to run a sensitivity it all is boiled down to the one input.
Libero mollitia sit exercitationem cum aut doloremque. Totam fuga deleniti voluptate sit qui laboriosam et optio. Nisi iste et necessitatibus est nihil pariatur. Quibusdam maiores voluptatibus sed quibusdam vitae sunt.
Quas nisi dolorum dignissimos nam quas. Soluta et error voluptas voluptatem ut dolorum. Sit quia enim corporis dicta adipisci.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...