I'm trying to find the IRR based on a DCF model I built for IBM. However, I keep on getting the #NUM returned. I'm hoping if anyone can see if there's anything wrong with my set up.
Item #1: EV (purchase price): $210,915,106.81 Item #2: CF 2017: $10,084,617.63 Item #3: CF 2018: $9,636,329.03 Item #4: CF 2019: $9,249,581.72 Item #5: CF 2020: $8,736,297.45 Item #6: CF 2021: $8,328,116.57
the formula I have in excel is "=XIRR(Item #1 to #6, CF 2017 to 2021).
I think I'm missing the terminal cf, but how do I find that, and how would I add it to the formula?
Iceshift, others might do it differently, but here is how I set mine up whenever my firm values a potential acquisition target (I always use the =IRR formula):
CF1=purchase price (this must be a negative as it's a cash outflow) CF2=Year 1 EBITDA CF3=Year 2 EBITDA CF4=Year 3 EBITDA CF5=Year 4 EBITDA CF6=Year 5 EBITDA CF6=Terminal value (we use the Gordon Growth formula)
Your mistake is most likely that you forgot to set the purchase price as a negative.
EDIT: I just opened up Excel and confirmed that if your purchase price is not set as negative, it does indeed give you a #NUM! error.
Sil Thank you for your detailed response. Attached is the way I have set it up. It shows both FCFF and EBITDA.
I have a few questions:
I understand that IRR is the rate of return that sets the NPV to $0.
FCFF IRR = 1% and EBITDA IRR = 6.02%
Sorry if these questions are very basic - this is my first week working these concepts.
Terminal value is an assumption. Can use perpetual growth or just simply an EBITDA multiple method (start by setting it equal to the multiple of your purchase price).
If using XIRR, the dates need to be actual dates like 12/31/2021, not just 2021. The purchase price needs to be negative at first. To add the terminal value just add another column and make the date the same as the 2021 date.
In aut eos eos. Esse qui dolores distinctio ea ut rem.
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...