Sign in or start a free trial to avail of this feature.
16. Investment Calculations Part 2
In this lesson we calculate the IRR and Money-On-Money multiple achieved by the investors in TrackerTime.
To explore more Kubicle data literacy subjects, please refer to our full library.
Lesson Goal (00:04)
The goal of this lesson is to calculate the investment returns for each of the investors in TrackerTime.
Calculating Total Returns (00:23)
After calculating the exit value of each party’s stake in TrackerTime, our objective is to calculate the returns to each party. For example, the total returns to Ventura are represented by the exit value of Ventura’s stake. In our model, we have a switch that lets users decide to use the EV / Revenue multiple or the EV / EBITDA multiple. As a result, our total returns cell uses an IF function that returns the appropriate exit value based on the selected value of the switch. We use a similar IF statement for all the return calculations in this lesson.
Calculating the Money-on-Money Multiple (01:09)
The money-on-money multiple is calculated by dividing the exit value by the initial investment. As these have opposite signs, we should add a minus sign to the calculation to ensure the result is positive.
Calculating the Internal Rate of Return (01:51)
To calculate the Internal Rate of Return, we use the IRR function on all the cashflows from the investment. When using the IRR function, we need to make sure to add zero value cashflows for each year between the initial investment and the exit value. If we don’t do this, the IRR returned by this function will be too large.
Analyzing the Returns (04:18)
After calculating returns, we can obtain many insights from them. For example, the Internal Rate of Return is dependent on the length of time between the initial investment and the exit. In our model, Ventura has a higher IRR than SeedCo even though SeedCo invested less money in TrackerTime. This is because SeedCo invested earlier, and tied their money up for longer.
By contrast, the money-on-money multiple is only dependent on the amount of money invested and received. In our model, SeedCo has a much higher money-on-money multiple, because they invested a smaller amount of money. As a result, SeedCo comfortably achieves their money-on-money target, while Ventura only achieves their target in certain circumstances.
In the previous lesson, we calculated the amount of cash that SeedCo and Ventura would receive in both scenarios of a revenue multiple and an EBITDA multiple on exit. Now we want to calculate the IRR and money-on-money multiple for each of these scenarios. Let's skip up to the top and begin this work. As you may have remembered, I have a switch that allows me to decide which multiple is being used. And when I'm calculating the total returns for Ventura, I'm going to use this switch. And I'll write equals if this cell is equal to yes. Then I'll return the EBITDA evaluation number.
Which is here.
Otherwise, I'll return the cell above.
For the money-on-money multiple, we'll do a similar if function. That checks if the cell is equal to yes. And if it is, it will do a money-on-multiple calculation on the EBITDA evaluation figure. And I'll scroll down take the Ventura EBITDA evaluation figure, and divide by I26. And if we want to use the revenue multiple, we'll take the revenue exit value and the same investment amount.
To calculate IRR, I'll use the same if function.
So again, checking that it's equal to yes.
And if it is, we'll write IRR and scroll down and select the cashflows.
Here we are. And if it's the revenue multiple, we'll write another IRR function.
And in my formula, I'll just make sure the brackets are correct.
There we go.
As you can see, this IRR seems very big. In actual fact, it's too big. We're investing 20 million, and we're receiving 85 million in five years time. That doesn't result in a 300 percent return. And the error that we have relates to the way our numbers are structured in our returns analysis.
It's trying to capture all the cashflows throughout the life of the investment. And here, we're actually only showing it two. 20 and 85.9. And this is where the 300 percent is coming from. The IRR function is assuming that there's only two cashflows in consecutive years. 20 and 85.9. It's not allowing for the zeros that happen in between.
And I'll also accomplish this for SeedCo.
And we can copy this.
With this in place, our IRR function should now work correctly. So I'll scroll up to the top and as you can see, we now have the IRR function of 44 percent. Which makes a lot more sense.
If I now flick the switch, I should get an even higher IRR.
And I do.
Off camera, I'm going to fix the formatting on these numbers, and add in the numbers for SeedCo and Mary Hernandez as well. Once these calculations are complete, we can perform some interesting analysis. Firstly, we can see that Ventura actually has a higher internal rate of return than SeedCo. Even though SeedCo only invested a small amount of money. And the reason for this is simple. SeedCo has it's money locked away for an additional four years. And as a consequence, this reduces SeedCo's IRR. On the other hand, SeedCo has a much higher money-on-money multiple. 35.8X versus 5.8X. SeedCo has a target money-on-money multiple of 20 times. In both scenarios, Seedco's target money-on-money multiple return is definitely met. However for Ventura, the story is not the same. It's looking for five times its money, and it gets that. Under the revenue multiple. However for the EBITDA multiple, it's a bit below five.
In both cases, Mary ends up a multimillionaire. Earning between 884 million and 653 million. However, if Mary had decided to not take the five million in back in 2016, how much would she have made? Well actually, not that big a difference. In the grand scheme of things.
As in other financial models, certain variables such as the exit multiple will have a big bearing on the final returns that investors receive. As an exercise, try and create some sensitivity tables underneath this model. For some of the more important variables that you have found in our calculations. In the after file below this video, I'll give you an example of at least two of these sensitivity tables.