13. Find the Drivers of IRR
Some of our model inputs will have a bigger impact on our IRR than others. Find out which inputs are the most important using some simple sensitivity analysis.
Finding the input drivers of IRR
- Sensitivity analyses are a great way to find the variables that most affect IRR
1 Extend the sensitivity analyses inputs between optimistic and pessimistic values
2 Identify the inputs that have the biggest impact on our baseline IRR
3 Examine what will determine the final value of these inputs
SHIFT + →: Select next cell
ALT + A , W, T: Create data table
In the previous lesson we saw that our projected IRR for this project was 9.8%, well below our clients’ target of 15%.
We now have to find which variables have the biggest impact on our returns, so Joe can evaluate our results in a little more detail.
Already he believes we are being too conservative on the interest rate and the gross rent multiple, and has asked for these to be changed to 5% and 20 times.
This is a common occurrence when building valuation models.
An investor will often be happy with a conservative set of inputs until he sees a low result.
The inputs are then often changed to reach a desired higher result.
To be fair, this is Joe’s investment, so let’s decrease the interest rate to 5% and increase the gross rent multiple to 20.
As you can see, both of these changes have caused a jump in IRR, particularly the gross rent multiple, which clearly has a big impact on investment returns.
In addition to these changes, Joe has given us a range of values for four inputs which he’d like to see in the model.
Three of these inputs relates to the mortgage he will need from the bank.
To complete this task in Excel I’m going to run some sensitivity analysis on each input. And off camera I’ll set up the desired cell formatting.
Let’s start with the holding period.
I’ve entered the desired inputs along the top in this row and in the selected cell I need to link to my output value, which will be the internal rate of return.
I’ll now select these cells in an array and then Alt + A W T for a data table.
The row input cell will be the assumed holding period and then I’ll press OK.
And as you can see, from the results we get a higher IRR, the shorter the holding period.
This is for two reasons.
In later years our rental increases are quite small.
The returns won’t grow hugely as a result.
In addition, if we hold the asset for longer, the large cash pile we receive from selling the asset takes longer to materialize.
Due to the time value of money this reduces our rate of return.
I’ll now complete the same analysis for interest rate.
So again, I’ll select the internal rate of return cell, select my array, Alt + A W T to create a table.
This time the row input cell will be the interest rate value, and as you would expect, a lower interest rate gives us a higher IRR.
Off camera I’ll complete the same sensitivity analysis for loan term and loan amount.
From these results, we see that the holding period, the interest rate on the loan, and especially the loan amount have the biggest impact on our potential returns.
The loan term interestingly doesn’t have that big an impact.
Joe is immediately intrigued by this analysis and realizes that reaching his target rate of return will largely be determined by the mortgage he receives from the bank and how quickly he can flip the property at a reasonably high sale price.
With these insights he can now make a much more informed decision on whether to proceed with the investment.