Wonky XIRR Return

D

Drymr

New Member
Joined
Feb 20, 2017
Messages
9
Hi there,

I have a long XIRR calculation that keeps returning 0.00% even though the negatives are significantly outweighed by the positives (-2,607,471 and +4,661,616). I tried pasting the calculation below but it just came out as text and isn't formatted correctly. I think there may be something wrong with a setting in excel because this doesn't make sense to me. I've checked and iterative calculation is checked. Can anyone suggest what the problem may be?

[TABLE="class: cms_table, width: 7849"]
<tbody>[TR]
[TD="align: right"]Aug-17[/TD]
[TD="align: right"]Sep-17[/TD]
[TD="align: right"]Oct-17[/TD]
[TD="align: right"]Nov-17[/TD]
[TD="align: right"]Dec-17[/TD]
[TD="align: right"]Jan-18[/TD]
[TD="align: right"]Feb-18[/TD]
[TD="align: right"]Mar-18[/TD]
[TD="align: right"]Apr-18[/TD]
[TD="align: right"]May-18[/TD]
[TD="align: right"]Jun-18[/TD]
[TD="align: right"]Jul-18[/TD]
[TD="align: right"]Aug-18[/TD]
[TD="align: right"]Sep-18[/TD]
[TD="align: right"]Oct-18[/TD]
[TD="align: right"]Nov-18[/TD]
[TD="align: right"]Dec-18[/TD]
[TD="align: right"]Jan-19[/TD]
[TD="align: right"]Feb-19[/TD]
[TD="align: right"]Mar-19[/TD]
[TD="align: right"]Apr-19[/TD]
[TD="align: right"]May-19[/TD]
[TD="align: right"]Jun-19[/TD]
[TD="align: right"]Jul-19[/TD]
[TD="align: right"]Aug-19[/TD]
[TD="align: right"]Sep-19[/TD]
[TD="align: right"]Oct-19[/TD]
[TD="align: right"]Nov-19[/TD]
[TD="align: right"]Dec-19[/TD]
[TD="align: right"]Jan-20[/TD]
[TD="align: right"]Feb-20[/TD]
[TD="align: right"]Mar-20[/TD]
[TD="align: right"]Apr-20[/TD]
[TD="align: right"]May-20[/TD]
[TD="align: right"]Jun-20[/TD]
[TD="align: right"]Jul-20[/TD]
[TD="align: right"]Aug-20[/TD]
[TD="align: right"]Sep-20[/TD]
[TD="align: right"]Oct-20[/TD]
[TD="align: right"]Nov-20[/TD]
[TD="align: right"]Dec-20[/TD]
[TD="align: right"]Jan-21[/TD]
[TD="align: right"]Feb-21[/TD]
[TD="align: right"]Mar-21[/TD]
[TD="align: right"]Apr-21[/TD]
[TD="align: right"]May-21[/TD]
[TD="align: right"]Jun-21[/TD]
[TD="align: right"]Jul-21[/TD]
[TD="align: right"]Aug-21[/TD]
[TD="align: right"]Sep-21[/TD]
[TD="align: right"]Oct-21[/TD]
[TD="align: right"]Nov-21[/TD]
[TD="align: right"]Dec-21[/TD]
[TD="align: right"]Jan-22[/TD]
[TD="align: right"]Feb-22[/TD]
[TD="align: right"]Mar-22[/TD]
[TD="align: right"]Apr-22[/TD]
[TD="align: right"]May-22[/TD]
[TD="align: right"]Jun-22[/TD]
[TD="align: right"]Jul-22[/TD]
[TD="align: right"]Aug-22[/TD]
[TD="align: right"]Sep-22[/TD]
[TD="align: right"]Oct-22[/TD]
[TD="align: right"]Nov-22[/TD]
[TD="align: right"]Dec-22[/TD]
[TD="align: right"]Jan-23[/TD]
[TD="align: right"]Feb-23[/TD]
[TD="align: right"]Mar-23[/TD]
[TD="align: right"]Apr-23[/TD]
[TD="align: right"]May-23[/TD]
[TD="align: right"]Jun-23[/TD]
[TD="align: right"]Jul-23[/TD]
[TD="align: right"]Aug-23[/TD]
[TD="align: right"]Sep-23[/TD]
[TD="align: right"]Oct-23[/TD]
[TD="align: right"]Nov-23[/TD]
[TD="align: right"]Dec-23[/TD]
[TD="align: right"]Jan-24[/TD]
[TD="align: right"]Feb-24[/TD]
[TD="align: right"]Mar-24[/TD]
[TD="align: right"]Apr-24[/TD]
[TD="align: right"]May-24[/TD]
[TD="align: right"]Jun-24[/TD]
[TD="align: right"]Jul-24[/TD]
[TD="align: right"]Aug-24[/TD]
[TD="align: right"]Sep-24[/TD]
[TD="align: right"]Oct-24[/TD]
[TD="align: right"]Nov-24[/TD]
[TD="align: right"]Dec-24[/TD]
[TD="align: right"]Jan-25[/TD]
[TD="align: right"]Feb-25[/TD]
[TD="align: right"]Mar-25[/TD]
[TD="align: right"]Apr-25[/TD]
[TD="align: right"]May-25[/TD]
[TD="align: right"]Jun-25[/TD]
[TD="align: right"]Jul-25[/TD]
[TD="align: right"]Aug-25[/TD]
[TD="align: right"]Sep-25[/TD]
[TD="align: right"]Oct-25[/TD]
[TD="align: right"]Nov-25[/TD]
[TD="align: right"]Dec-25[/TD]
[TD="align: right"]Jan-26[/TD]
[TD="align: right"]Feb-26[/TD]
[TD="align: right"]Mar-26[/TD]
[TD="align: right"]Apr-26[/TD]
[TD="align: right"]May-26[/TD]
[TD="align: right"]Jun-26[/TD]
[TD="align: right"]Jul-26[/TD]
[TD="align: right"]Aug-26[/TD]
[TD="align: right"]Sep-26[/TD]
[TD="align: right"]Oct-26[/TD]
[TD="align: right"]Nov-26[/TD]
[TD="align: right"]Dec-26[/TD]
[TD="align: right"]Jan-27[/TD]
[TD="align: right"]Feb-27[/TD]
[TD="align: right"]Mar-27[/TD]
[TD="align: right"]Apr-27[/TD]
[TD="align: right"]May-27[/TD]
[TD="align: right"]Jun-27[/TD]
[TD="align: right"]Jul-27[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-1,325,289[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]25,490[/TD]
[TD="align: right"]10,646[/TD]
[TD="align: right"]-58,022[/TD]
[TD="align: right"]-25,610[/TD]
[TD="align: right"]-25,587[/TD]
[TD="align: right"]-25,613[/TD]
[TD="align: right"]-25,610[/TD]
[TD="align: right"]-25,587[/TD]
[TD="align: right"]-25,610[/TD]
[TD="align: right"]-25,587[/TD]
[TD="align: right"]-25,613[/TD]
[TD="align: right"]-25,604[/TD]
[TD="align: right"]-25,534[/TD]
[TD="align: right"]-25,610[/TD]
[TD="align: right"]-33,587[/TD]
[TD="align: right"]-33,610[/TD]
[TD="align: right"]-41,927[/TD]
[TD="align: right"]-41,953[/TD]
[TD="align: right"]-41,950[/TD]
[TD="align: right"]-36,927[/TD]
[TD="align: right"]-63,950[/TD]
[TD="align: right"]-47,723[/TD]
[TD="align: right"]-47,750[/TD]
[TD="align: right"]-47,744[/TD]
[TD="align: right"]-47,697[/TD]
[TD="align: right"]-47,747[/TD]
[TD="align: right"]-47,723[/TD]
[TD="align: right"]-47,747[/TD]
[TD="align: right"]-47,723[/TD]
[TD="align: right"]-47,750[/TD]
[TD="align: right"]-48,673[/TD]
[TD="align: right"]-170,411[/TD]
[TD="align: right"]86,102[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4,539,379[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]IRR[/TD]
[TD="align: right"]0.00%[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
I think you'll find the zero result is actually approximately 2.98E-09. It's a little quirk of XIRR that it returns this value if it can't calculate a result.

You haven't posted your formula, but my guess is that you're starting with the August 2017 zero value? You'll need to start with the non-zero December 2017 value, in which case XIRR should return approximately 15.04% p.a., which looks correct to me.

You're probably aware of the limitations of IRR, i.e. if your cashflows flip between positive and negative there may be multiple solutions?
 
Upvote 0
I'll add my two-cents here to help out. I suspect different versions of Excel interpret IRR with multiple inflection points in different ways. I put the entire dataset into IRR and the function reported 1.1757093%.

Incidentally, XIRR reported 15.0417155% when the dataset begins at Dec , 2017.
 
Upvote 0
StephenCrump said:
I think you'll find the zero result is actually approximately 2.98E-09. It's a little quirk of XIRR that it returns this value if it can't calculate a result.

You haven't posted your formula, but my guess is that you're starting with the August 2017 zero value? You'll need to start with the non-zero December 2017 value, in which case XIRR should return approximately 15.04% p.a., which looks correct to me.

You're probably aware of the limitations of IRR, i.e. if your cashflows flip between positive and negative there may be multiple solutions?
Click to expand...

Thanks for replying. Too bad XIRR isn't able to start with zeros. Oh well.
 
Upvote 0
Drymr said:
Thanks for replying. Too bad XIRR isn't able to start with zeros. Oh well.
Click to expand...

Assuming your header Aug-17 is in cell A1, you could use:

=XIRR(INDEX(A2:DP2,MATCH(TRUE,A$2:DP$2<>0,)):DP2,INDEX(A1:DP1,MATCH(TRUE,A$2:DP$2<>0,)):DP1) array-entered

which should return 15.04% p.a.
 
Upvote 0
DRSteele said:
I put the entire dataset into IRR and the function reported 1.1757093%.
Click to expand...

.... per period (month) = (1+1.1757%)^12-1 = 15.06% p.a., i.e. close to XIRR result, but not exactly matching given the IRR assumption that all months are of equal length.
 
Upvote 0
Stephen, yes I did calulate all that. Thanks tons.
 
Upvote 0
StephenCrump said:
(1+1.1757%)^12-1 = 15.06% p.a., i.e. close to XIRR result, but not exactly matching given the IRR assumption that all months are of equal length.
Click to expand...

That doesn't make it wrong. In fact, that might be the right thing to do. The IRR is used for two typical purposes: for comparing financial options; and for calculating the imputed interest rate. For the former, the exact percentage usually is not the determining factor. For the latter, some jurisdictions require that monthly payments be treated as occurring equally spaced; in fact, in the US, the APR is 12*IRR(...), not the compounded rate.

I, too, had thought of suggesting the use of Excel IRR, if only because it seems to be more stable than Excel XIRR.

"More stable", but not without its foibles. I believe that Excel IRR, like Excel XIRR, uses an approximation of the derivative of the NPV instead of the exact derivative, which is computable. I suspect it seems to have less of adverse impact on Excel IRR because the discount rate is compounded by integers instead of non-integers.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
AVERAGEIF TROUBLES
Replies
3
Views
79
AhoyNC
A
G
  • Question Question
Copy some specific columns whose header is merge
2 3
Replies
26
Views
672
Joe4
Joe4
nburaq
  • Solved
Conditional Formatting last n rows as data entered
Replies
6
Views
362
Fluff
Fluff
J
  • Question Question
adding cells based on criteria across several columns
Replies
5
Views
999
Cubist
Cubist
S
  • Solved
lookup return from range of dates
Replies
4
Views
512
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,827
Messages
6,181,198
Members
453,022
Latest member
RobertV1609

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top