XIRR vs. Calculating IRR from MoM
I read that to get the implied IRR from your MoM multiple, you use the following formula:
IRR = (MoM) ^ (1/(# of periods) -1
I tried testing the above by comparing with what I Was getting for IRR if I used the XIRR function directly and I am getting vastly different results.
Here is a simple example to illustrate:
Assume a cash outflow in year (0) of -100, and then positive CFs of 25 for the remaining 6 years. Your distribution is something like this:
Dec-31-2010: -100
Dec-31-2011: 25
Dec-31-2012: 25
Dec-31-2013: 25
Dec-31-2014: 25
Dec-31-2015: 25
Dec-31-2016: 25
Above translates to a MoM of 1.50x, which results in an IRR of ~7% if I use the formula I listed above. HOWEVER, when I calculate the IRR using the XIRR fuction in excel, it gives me ~13%
Can you pls tell me what am I missing here?? I would have thought the numbers under both formula would be somewhat different, but not by AS MUCH
How is this a hard question ?!?!
moic doesnt factor in timing of distributions - it assumes all are at end
Your dates probably have weird formatting and XIRR isnt reading them up correctly. I’ve had that before.
There is nothing wrong with my date formatting. I have listed my example above. If you can, throw it into a spreadsheet and tell me if you get different results?
One poster has mentioned this but the difference is due to the timing of cash flows - if this isn't obvious / intuitive, do spend some time studying this as it's extremely important for any career in IB / PE. Generally, if I receive my cash back sooner, my IRR is higher, even is the MoM is identical. A 2x MoM returns a higher IRR if cash is received in 2 years rather than 5.
Your case is a good example of why we should expect very different IRRs between each method (as something like an extreme case of where the CASH IN is distributed evenly over a number of years, versus the other extreme of all CASH IN being received at a specific point in time).
I understand that and in an ideal world I would use IRR function. But here I can't which is why I was relying on the shorthand approach.
For context, I am trying to evaluate whether the bid we are offering would be acceptable to the sponsor (seller) and I only have how much cash this sponsor injected initially, so I wanted to evaluate what would have been the resulting IRR to this sponsor based on our offered purchase price.
Got it - I'm for sure guilty of using a shorthand but for a live deal you would want to use something accurate. Your estimate is going to be very rough however you cut it (i.e. if you truly only have the original CASH IN then that's a good start but think about whether they did any refinancing, how they financed any bolt-ons, etc.)
There's no reason you should be using anything other than XIRR for IRR calcs. If you're modelling annually and assuming semi-annual distributions, have your dates for XIRR calc purposes end Sep 30th. If the business / revenues are remotely seasonal, you should be modeling quarterly or monthly anyway.
I understand that and in an ideal world I would use IRR function. But here I can't which is why I was relying on the shorthand approach.
For context, I am trying to evaluate whether the bid we are offering would be acceptable to the sponsor (seller) and I only have how much cash this sponsor injected initially, so I wanted to evaluate what would have been the resulting IRR to this sponsor based on our offered purchase price.
Check date formatting
Consequatur omnis sed qui porro omnis quaerat nemo. Fugit tempore dolores illo voluptas qui. Consequatur qui quasi atque a maiores omnis. Doloremque impedit et tenetur ipsam similique impedit.
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...