I’m trying to use the XIRR function for a series of uneven cash inflows/outflows and it doesn’t seem to be giving me the correct result. The sum of all the cash flows is a negative number and yet the XIRR function returns a value of 4 million percent… Is there anything special I have to watch out for or that I may be doing wrong? An IRR of 4 million percent on net negative cash flows seems wrong to me. Thanks
IRR does not work for uneven cash flows.
XIRR is supposed to work for uneven cash flows. But the result I’m getting doesn’t make any sense.
I use xirr all the time and it works fine but you need to be sure you’re indexing each cash flow to a date, and the dates need to be in excel’s date format. Aside from that suggestion it’s tough to know exactly what’s going wrong without seeing the spreadsheet.
Like Ian said, it’s hard to know with XIRR without seeing the sheet. Here’s a sample from a model I happen to have open at the moment: IRR 0.020557389 row 1: Year 0 1 2 3 4 5 Row 2: Year number 4/16/2009 4/16/2010 4/16/2011 4/15/2012 4/15/2013 4/15/2014 Row 3: Net flow (10506) 1521 1006 776 588 1076 formula: =XIRR(D3:AA3,D2:AA2) Since XIRR is in the pack, it won’t tell you what your doing wrong.
XIRR expects at least one positive cash flow and one negative cash flow; otherwise, XIRR returns the #NUM! error value. If any number in dates is not a valid date, XIRR returns the #VALUE! error value. If any number in dates precedes the starting date, XIRR returns the #NUM! error value. If values and dates contain a different number of values, XIRR returns the #NUM! error value.
It should be set up properly as all my dates have been input as the =Date function and I have both positive and negative cash flows. Using eire1130’s example and the XIRR function I get -0.218848 instead. Hmm not sure.
I get -0.218928658 with his numbers, so that’s close to yours kcin.
Ok so then does it make sense that a series of uneven quarterly cash flows over a period of 8 years with a sum that is negative equates to an XIRR of 4.5 million percent?? I don’t see how that makes sense.
my cash flows go out twenty three years for that IRR as displayed, it was merely for formatting. Going out to year 5, -21% is right. I tend not to use the date function unless I must. I prefer to use edate when adding dates. Are you certain your formatting is right for the cash flows?
I’ve tried copy and paste special, values and it still is messed up. Seems to work for other calculations just not this stream of cash flows. Maybe I’m going about trying to find the answer in the wrong way. I’m trying to see what the performance of a business since inception has been assuming it is sold for “X” sometime in the near future. So I’m taking free cash flows on a quarterly basis and adding any acquisitions or dispositions that were made and then having one lump sum payout at the end. Does this seem like the right way to finding the answer? Any help would be great, thanks.
If I understand your problem right, I wouldn’t use the IRR algorithm. IRR is only useful for comparing two mutually exclusive projects. Also, IRR is iterative, so it will assume that all reinvestments are done at the same rate of return. This might not be a good assumption. If this is a VC / PE situation where you invest a certain amount at time 0, and maybe again at time 4 and 8 and expect a big payout at time 10, you may be able to use IRR. IRR works best when you trying to measure efficiency. So if one project comes out at 15% IRR and the other 16.5%, you know immediately which one to go with.
I’m more or less just trying to measure past performance of a business from inception to date, and then assume it sells for a lump sum at a certain point in the future. It’s easy to see whether or not the total inflows/outflows are positive or negative, but is there a way to measure this on a percentage basis?
I find XIRR to be problematic if the figure returned is negative. For instance if you push the date of the last postive cash flow further into the future then the XIRR will increase (become less negative) which is the opposite of what should happen.
The other method you can try with uneven cash flows is to change your time periods to make them short enough that your cash flows are no longer uneven. For instance if your cash flows are once quarterly, but unevenly spaced (such as Feb, June, July, then Dec) you can shorten your time periods to monthly instead of quarterly and use zero for the months with no cash flows. Then calculate a monthly IRR and compound it to produce annual. You can shorten it to weekly or daily too if necessary.
XIRR goes crazy if total cashflows are negative.
Sounds like you have some divergence going on there. Have you tried using a few different values for the “guess” field to see if you can get some convergence? Obviously this assumes you haven’t made the usual simple mistakes of mixing up %ages and nominal and other things that could give you a GIGO problem.
I have not time to read all the posts, but if you are still having problems, try adding , -0.00000001 to the formula. For example: =XIRR(D3:AA3,D2:AA2,-0.00000001) I had problems in the past with negative cashflows and adding that value at the end made it work. Check the Excel menu for this function and will lead you to add a small value… Hope this helps. Alex