So, I consider myself pretty decent at Excel, and I cannot figure out what's going on here for the life of me, and neither can my two associates... I am using an XIRR function which is working fine, except for when it should be returning a negative IRR it's giving me a #NUM error. Here are the dates and cash flows I am using, but it seems to be giving me an error any time the return is negative:
Dates: 7/15/16, 10/7/16, 6/30/17, 6/30/18, 6/30/19, 6/30/20 Values: -1500000, 843104, 916910, 261708, 261708, -669740
Let me know if anyone can tell wtf I am doing wrong. Thanks!
Why would it be negative? The sum of all the cash flows is positive... If I don't use a guess, I get .399622.
Your cash-on-cash return is 1.076x
Sorry sorry, the first number is -2,000,000. -1,500,00 works, I adjusted it and then forgot I changed it before writing this post. Working too fast.
Yeah, I am stuck now too. Damn!
I even tried doing a goal seek to return the IRR using some different methods, but nothing worked. Have you tried on a calculator? I've had better experience with those over Excel in the past on some certain issues.
I also have .399622. Try stripping out the guess parameter and just keeping the default.
Sorry sorry, the first number is -2,000,000. -1,500,00 works, I adjusted it and then forgot I changed it before writing this post. Working too fast.
Hey Billy,
The IRR value you're looking for doesn't exist.
You're asking the XIRR function to return a value that doesn't exist.
0 = CF1 / (1+k)^t1 + CF2 / (1+k)^t2 + CF3 / (1+k)^t3 + etc.
No matter what you make your discount rate (k), you'll never actually make the right side of the formula reach 0. In other words, your function lives in the 3rd and 4th quadrant and will never cross the x-axis.
In any case, the cashflows on this investment are terrible. Four years post investment, you'll have lost $386k, or 20% on your initial investment.
Haha thanks Double Doubler I see that they are terrible, running a number of different downside scenarios. I see what you're saying and think the last huge negative cash flow at the end skews the IRR calc.
Spot on.
SBed because I was curious too. On the 39% with the $1.5mm upfront, doesn't that assume reinvestment of cash inflows? It obviously sounded too high to me as well with that cash return, but I think it's calculated as a CAGR, correct?
I don't think it's due to revinvestment I think it's due to the quick repayment of invested capital with more than 100% of your cash being returned in year 1. Could be wrong though. I'll dig in a bit more and see if I can figure it out.
And I wouldn't trust the XIRR of 0.3996225 generated when your first cashflow is $-1.5M.
Without spending time on the situation, there's no way you're generating 39% return if the sum of your cashflows only returns a +7.6% on your initial investment.
Basically, you shouldn't use XIRR when your cashflows change signs too often. There are a ton of forum threads on the subject, I invite you to read them and then create your own bespoke formulas as required.
Just went through and did the manual calc for the -1.5mm starting cash flow and it is in fact 39%. I believe it's due to the large cash flows within the first 6 months of investment that more than make you your money back. Obviously the larger cash flows up front hold more weight than the smaller cash flows towards the end.
Ran the same calc and tried to goal seek it to figure out the IRR and as mentioned its not possible.
Good on you for doing the calc manually. I trust that 39% is correct, but it still strikes me as odd. The problem is that the IRR and XIRR formulas are excessively volatile for short series of cashflows because of its reinvestment assumptions.
My recommendation:
My colleagues and I far prefer using the MIRR formula for exactly situations like this (this way you can set the reinvestment rate at your firm's cost of equity) and it stabilizes the IRR of the project you're trying to evaluate. Two big disadvantages to using MIRR though: 1. There is no "XMIRR" formula (if you're really serious, you could code the function in VBA, but few people are that motivated) 2. You'll have to explain what MIRR is to everyone
Assuming your cashflows are annual (incorrect I know but bear with me), and you set your finance rate and reinvestment rate to 0%, MIRR will churn out a 1.03% IRR for the project. If you set your finance rate to 6% and reinvestment rate to 12%, MIRR will churn out a 5.29% IRR.
This exercise is a good way to stress test the investment. Give it a try and let me know what you think.
IRR is 8.81%...
Not sure how you got this
Didn't see the dates. The IRR is approximately 39.96% (assuming today as the valuation date).
XIRR can't have the last # be a negative nor can the first # be positive. Just add a date and put in zero.
I thought there was some similar rule, too, but if you add a date (7/1/2020) and use $0 or even just $1, it still gives #NUM.
It makes no sense to calculate IRR in this scenario (negative cash flows at the very beginning and at the very end), and that's probably why the XIRR function is not working (the existence of "one and only one solution" is not guaranteed). [EDIT: As a matter of fact, with these cash flows you'd have positive NPV between -13% and 39%, and negative NPV outside this range, i.e. there are 2 IRRs that would be equally "correct".]
I would analyze this opportunity by presenting the CoC multiple (1.076x) and the NPV at your company's WACC, if we're talking about a portfolio company's potential project.
In the context of Private Equity (only based on your title), if this is a potential investment, with initial outflow = price, positive cash flows = dividends, and final outflow = cash injection needed (e.g. to meet the obligation to dismantle a plant at the end of the useful life), I would check when the capital call is needed. In most PEs it will not be possible after the initial 5 years, and therefore such capital call should be made together with the investment. If that's the case, you could probably discount the final cash outflow amount by the 4-year risk free rate (assuming you will put this cash straight away in a risk-free investment), and add it to the initial investment (7/15/16). You can then proceed and calculate IRR, having no sign changes.
Using 0.9% as 4-yr Risk Free rate you'd get 6.3% IRR. This should be similar to the point of view of your LPs, IMHO.
XIRR funjction download? (Originally Posted: 11/02/2009)
Does anybody have a link for the XIRR add-in on Excel 2003?
I don't have it on my comp, and have not been able to find it online.
Thanks.
It's really built into Excel - it's part of the Analysis ToolPak (Tools-Addins). Load ATP and ATP - VBA. if you don't have it, have your IT people fix your office (or, do a detect and repair on office if on a personal machine)
Also if you cant get it to load (for whatever reason), you could do it by hand by using a Riemann Sum(keep in mind that Excel iterates r to power of di - d1/365) & cross check it with the IR rate that you use for XNPV (but I guess if you already have that then you would already know XIRR).
I went to Tools -> Add-in -> VBA, and got this message:
"The feature you are trying to use is on a network resource that is unavailable."
Any further ideas?
Minus aut eum eveniet animi. Omnis reiciendis eos et minus. Nam qui deserunt et assumenda quia quaerat aliquid. Culpa modi alias non iusto.
Et laborum a illum omnis ut velit et. Dolorem nulla aut rem atque qui. Et possimus velit dolor ut explicabo est dolorum. Fugit et vel dolorum inventore. Unde quod delectus illum excepturi error a.
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...
Distinctio velit soluta enim accusamus. Fugit odit similique sequi saepe reiciendis placeat. Tenetur unde distinctio tempore vel eum vitae.
Repellat aspernatur rerum nulla. Excepturi aliquam hic adipisci dolorem quaerat consequatur sed. Laboriosam deleniti et inventore itaque beatae quod. Possimus voluptas ut consequatur quam. Eaque recusandae omnis deserunt aut vitae fugit non.
Sit reiciendis animi iusto. Blanditiis expedita possimus et error fugiat sed. Dolorem ipsum sunt et beatae. Odit praesentium eum porro velit et molestiae. Quasi quia aut ducimus et ut.
Doloremque omnis maiores tempore et hic praesentium perferendis. Ab molestiae et doloribus inventore blanditiis nihil. Ullam voluptatum a sed sit ipsa perspiciatis. Magnam pariatur saepe atque reiciendis. Fuga maxime non eum qui atque.