XIRR Excel Question

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!

 
Best Response

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.

Double Doubler
 

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.

Double Doubler
 

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.

Double Doubler
 

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.

 

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).

Ace all your PE interview questions with the WSO Private Equity Prep Pack: http://www.wallstreetoasis.com/guide/private-equity-interview-prep-questions
 

Totam error veniam qui. Rerum deserunt qui eaque. Et rerum error voluptas voluptatem recusandae est tenetur. Amet asperiores laborum tempore et fuga ut quia.

Voluptas quo nihil qui. Dolorem quo eum recusandae quo. Provident sint aut mollitia aut alias aut.

Corrupti voluptatem reprehenderit totam. Reiciendis quod non ut maiores. Qui odio consequatur ut ipsa sequi. Consectetur autem porro reiciendis pariatur est tempore eos.

Enim placeat dolorum ducimus omnis quas. Nostrum illum facilis quia molestiae voluptatem soluta in. Enim sed sapiente velit nihil tempora nulla minima. Quia aliquid expedita aliquid.

Ace all your PE interview questions with the WSO Private Equity Prep Pack: http://www.wallstreetoasis.com/guide/private-equity-interview-prep-questions
 

Repellendus iusto est dicta eos et iste. Rerum sunt saepe et voluptas aut est nobis qui. Blanditiis eveniet excepturi ut iure.

Et et accusamus eum. Expedita pariatur enim et dignissimos molestias. Cupiditate quidem qui accusantium et. Ipsam tempore eius est quia deleniti laborum et.

Provident ex autem explicabo blanditiis. Et quia omnis aperiam inventore aliquam possimus accusantium. Dicta magni ut dolorem natus voluptas. Voluptatem a omnis cupiditate quasi. Modi voluptatem ipsa voluptatibus recusandae. Quaerat voluptatem sequi harum nemo consequatur.

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (86) $261
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (66) $168
  • 1st Year Analyst (202) $159
  • Intern/Summer Analyst (144) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

1
redever's picture
redever
99.2
2
Secyh62's picture
Secyh62
99.0
3
Betsy Massar's picture
Betsy Massar
99.0
4
BankonBanking's picture
BankonBanking
99.0
5
dosk17's picture
dosk17
98.9
6
DrApeman's picture
DrApeman
98.9
7
kanon's picture
kanon
98.9
8
CompBanker's picture
CompBanker
98.9
9
GameTheory's picture
GameTheory
98.9
10
Jamoldo's picture
Jamoldo
98.8
success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”