XIRR Err:502

2 replies [Last post]
henders254
Offline
Last seen: 2 weeks 4 days ago
Title: ★★
Joined: 20 Dec 2012
Posts: 13

I am getting Err:502 while calculating XIRR. Here’s my record:

A B
1/08/11 -$9,995
14/10/11 $223
27/04/12 $231
12/10/12 $239
20/12/12 -$19,976
2/01/13 -$9,283
26/04/13 $851

=XIRR

Result: Err:502

Thanks.

Libreoffice version 4.1.3.2 on OS X Mavericks

oweng
oweng's picture
Offline
Last seen: 18 hours 4 min ago
Title: ==Moderator==
Joined: 26 Jan 2012
Posts: 2663
first two amounts seem key

I am not aware of how this financial function is supposed to receive its inputs, but reading the help page would seem to indicate that it expects the first two values to have a certain relationship i.e., value 1 = invested / borrowed amount, and value 2 = first return / payment. A quick test shows that the date against these two amounts is also influential, which I guess is to be expected. The size of the first return / payment needs to be a certain percentage of the original investment for the function to work i.e., for six months ~70.0%, for seven months ~66.8% and decreasing from there.

Details of the financial formula can be found on the (Wikipedia page). A quick test to determine NPV would seem to indicate that the Calc function does work, although it is very picky about the first pair of values and their dates. I think in your case the negative amounts being greater than the positive amounts means there effectively is never going to be a rate of return.

FinancialEngineer
Offline
Last seen: 1 year 16 weeks ago
Title:
Joined: 19 May 2013
Posts: 2
Point of discontinuity at the XIRR in the underlying equation

Hello

I have been playing around with your schedule of cash flows and dates for the past five hours, and came across some interesting findings.

Firstly, none of the spreadsheet programs that I used in my experiment including Excel, LibreOffice Calc, OpenOffice Calc, Gnumeric, and IBM Symphony spreadsheets produce any results for these cash flows.

The XIRR functions in all the above mentioned spreadsheet programs reports an ERROR. So I had to look deeper into why the XIRR functions in these spreadsheet programs report error no matter what GUESS rate one uses.

It would seem that the underlying XNPV equation for your cash flows have a point of discontinuity at the interest rate that happens to be one of the XIRR values. The function does have a limit at this point which happens to be -1 or in terms of interest rates a value of -100%.

So how did I find this XIRR value you may ask? The answer is that I used the tadXIRR function found in tadLOcalc add-in for LibreOffice Calc. There are over 90 financial functions in this add-in that are based on a popular Excel add-in called tadXL found here at http://tadxl.com

Now to be honest, the tadXIRR function in tadLOcalc itself reports an ERROR for finding the XIRR for these cash flows using any guess rate. This is obvious as explained earlier in the examination of these cash flows that a point of discontinuity exists at -1

To make tadXIRR find the IRR, I had to make use of an extra argument found in this tadXIRR function called COMPOUNDING. This value is used to specify the compounding frequency of interest which may be any value such as 1 for annual, 6/12 for semi-annual, 3/12 for quarterly, 1/12 for monthly, 1/52 for weekly or 1/365 for daily and so on.

Now using daily, weekly and monthly compounding of interest, I found three different XIRR values. As these three values indicate a nominal interest rate I then used tadEFFECT function to get the annual effective yield that happens to be the XIRR value for these cash flows and as you can see from the list of values at the bottom that this XIRR value is very close to -100% and comes out to be -99.99738752%

And finally using tadXNPV function one could check that the net present value of these cash flows is almost ZERO as expected at the XIRR as the discount rate.

The XNPV functions in Excel, LibreOffice Calc, OpenOffice Calc, Gnumeric, and IBM Symphony spreadsheets report an error for these cash flow and are unable to find the net present value using the XIRR value of -99.99738752%

##### A ############ B
1 ### 8/1/2011 ##### ($9,995)
2 ### 10/14/2011 ### $223 
3 ### 4/27/2012 #### $231 
4 ### 10/12/2012 ### $239 
5 ### 12/20/2012 ### ($19,976)
6 ### 1/2/2013 ##### ($9,283)
7 ### 4/26/2013 #### $851 

=tadXIRR(B1:B7 ; A1:A7 ; -10 ; 1/365)
-1040.15387%

=tadEFFECT(-1040.15387% ; 1/365)
-99.99738752%

=tadXIRR(B1:B7 ; A1:A7 ; -10 ; 1/52)
-955.07758%

=tadEFFECT(-955.07758% ; 1/52)
-99.99738752% 

=tadXIRR(B1:B7 ; A1:A7 ; -10 ; 1/12)
-701.95448%

=tadEFFECT(-701.95448% ; 1/12)
-99.99738752% 

=tadXNPV(-99.99738752% ; B1:B7 ; A1:A7)
$(0.03)

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.