XIRR with expected negative rate of return gives Err:502

English
, ,
#1

Sometimes when I get a negative rate of return with the XIRR function I get a Err:502. I did some testing today. I have my spreadsheet entries the opposite of examples I see. I thought that might be the problem. The latest entry at the top instead of bottom as typically shown. First I did a simple example in each order

=XIRR(B6:B8,A6:A8,0.1)

05/22/13 28384.68 39.35%

01/27/14 7999.99

01/28/14 -43667.92

=XIRR(E6:E8,D6:D8,0.1)

01/28/14 -43667.92 39.35%

01/27/14 7999.99

05/22/13 28384.68

Both seem to work correctly. It doesn’t seem to matter the order of the entries.
Then I changed the ending total so the result would be negative.

05/22/13 28384.68 Err:502

01/27/14 7999.99

01/28/14 -23667.92

01/28/14 -23667.92 -57.78%

01/27/14 7999.99

05/22/13 28384.68

Now the order makes a difference! But it is the opposite of what I expected. As my entries that give Err:502 are in the order given in most examples.

Here is an actual failure I get

=XIRR(E13:E14,B13:B14,0.4) Err:502

01/28/2014 ($4,811.29)

01/23/2014 23.464 $213.09 $4,999.94

What am I doing wrong that I get an Err:502 instead of showing a negative rate of return? I have tried negative numbers in the guestamation without help.

#3

Let us see your XIRR calculation in terms of solving for the rate in the underlying XNPV equation.

We are solving for the interest rate in the NPV equation that will result in a value of zero for the function f(i).

The XNPV equation is almost the same as the NPV equation the slight difference in XNPV is the use of actual time period that is used in discounting each cash flow.

You have three cash flows thus the XNPV equation that we are trying to solve looks like this

a / (1+i)^(t0-t0/365) + b / (1+i)^(t1-t0/365) + c / (1+i)^(t2-t0/365) = 0

a (1+i)^-(t0-t0/365) + b (1+i)^-(t1-t0/365) + c (1+i)^-(t2-t0/365) = 0

a + b (1+i)^-(t1-t0/365) + c (1+i)^-(t2-t0/365) = 0

here a = 28384.68, b=7999.99, and c=-43667.92

28384.68 + 7999.99 (1+i)^-(250/365) -43667.92(1+i)^-(251/365) = 0

If you look closely at the last two time periods (250/365) and (251/365) then there is a very small difference between the two and its almost minute

The XIRR function in spreadsheet programs would use iterative methods to solve the XNPV equation using the three cash flows discounting them each at their respective time period.

But as I said since the time period for b and c are almost the same only differing by 1/365 of a time period then we are able to solve for this rate without using the XIRR function.

5/22/2013 ### 28384.68 ### 0

1/27/2014 ### 7999.99 ### -250/365

1/28/2014 ### -43667.92 ### -251/365

If we add up the last two cash flows as they are so close to each other on time scale then we are left with only two cash flows as follows

5/22/2013 ### 28384.68 ### 0

1/28/2014 ### -35667.93 ### -251/365

Here the first cash flow occurs at present thus it is the PV - present value

and the second cash flow occurs at the end of time scale thus it is the FV - future value

And we can now easily solve for the interest rate using the following equation

PV + FV (1+i)^-t = 0

FV (1+i)^-t = -PV

(1+i)^-t = -PV/FV

(1+i)^t = FV/-PV

1+i = (FV/-PV)^1/t

i = [ (FV/-PV)^1/t ] - 1

i = [ (-35667.93/-28384.68)^(365/251) ] - 1

i = [ 1.256590879^1.454183267 ] - 1

i = 1.393946527 - 1

i = 0.393946527

i = 39.395%

We can confirm the interest rate by finding XIRR of the original three cash flows

XIRR(B1:B3,A1:A3) = 39.35%

As you can the value we calculated by hand 39.395% is almost the same as the one found by XIRR 39.35%

The same goes for the second data set where the last cash flow c = -23667.92

5/22/2013 ### 28384.68 ### 0

1/27/2014 ### 7999.99 ### -250/365

1/28/2014 ### -23667.92 ### -251/365

5/22/2013 ### 28384.68 ### 0

1/28/2014 ### -15667.93 ### -251/365

i = [ (-15667.93/-28384.68)^(365/251) ] - 1

i = [ 0.551985437^1.454183267 ] - 1

i = 0.421420311 - 1

i = -0.578579689

i = -57.858%

Now if use the original three cash flows and their dates in XIRR we get

XIRR(B1:B3,A1:A3) = -57.78%

Once more the value we found by hand -57.858% is almost the same as the one found by XIRR -57.78%

So far so good, but then you changed the order of the cash flow thus making the last cash flow as the first cash flow and vica versa

The XIRR function as stated earlier is programmed to solve for the interest rate in the underlying XNPV equation that was earlier given as

a + b (1+i)^-(t1-t0/365) + c (1+i)^-(t2-t0/365) = 0

But when you changed the order, the time periods that were calculated internally by the XIRR function are now different that those we had when the cash flows were in sequence

This change in timings of the cash flows has now altered the underlying XNPV equation into an XNFV equation thus we are now solving for interest rate in the net future value equation. The internal rate of return however stays the same nevertheless.

Here is how the XNFV equation looks like that the XIRR function is solving to find the interest rate.

a (1+i)^(t2-t0/365) + b (1+i)^(t1-t0/365) + c = 0

c + b (1+i)^(1/365) + a (1+i)^(251/365) = 0

For the first data set a=28384.68, b=7999.99, c=-43667.92

1/28/2014 ### -43667.92 ### 0

1/27/2014 ### 7999.99 ### 1/365

5/22/2013 ### 28384.68 ### 251/365

But if you now look closely the difference between timing of c and b is almost the same where c is compounded at t=0 and b is compounded at t=1/365. These two cash flows are only one day apart from each other.

Thus we can reduce these two cash flows in to one by adding them up that would result in the following two cash flows

1/28/2014 ### -35667.93 ### 0

5/22/2013 ### 28384.68 ### 251/365

Here 28384.68 is the present value and -35667.93 is the future value and we have to solve for the interest rate using the future value equation as follows.

PV(1+i)^t + FV = 0

PV(1+i)^t = -FV

(1+i)^t = -FV/PV

1+i = (-FV/PV)^(1/t)

i = [(-FV/PV)^(1/t)] - 1

i = [ (–35667.93/28384.68)^(365/251) ] - 1

i = [ (35667.93/28384.68)^(365/251) ] - 1

i = [ 1.256590879^1.454183267 ] - 1

i = 1.393946527 - 1

i = 0.393946527

i = 39.395%

As you can see this rate that we just found using the future value equation is the same rate we previously calculated using the present value equation.

Now if we ask the XIRR function to find the interest rate, at least Excel is reporting a #NUM! error as it is not able to find this rate

XIRR(B1:B3,A1:A3) = #NUM!

And we can do the same for the second data set to solve for the interest rate in the underlying future value equation

1/28/2014 ### -23667.92 ### 0

1/27/2014 ### 7999.99 ### 1/365

5/22/2013 ### 28384.68 ### 251/365

Once again the first and the second cash flows are only 1 day apart thus we reduce these two cash flows into one by adding them up to get the following

1/28/2014 ### -15667.93 ### 0

5/22/2013 ### 28384.68 ### 251/365

Now that we have only two cash flows, we may solve for the interest rate in the future value equation as follows

i = [ (–15667.93/28384.68)^(365/251) ] - 1

i = [ (15667.93/28384.68)^(365/251) ] - 1

i = [ 0.551985437^1.454183267 ] - 1

i = 0.421420311 - 1

i = -0.578579689

i = -57.858%

Once again the rate we just found is identical to the interest rate we previously calculated using present value equation.

But when I tried using Excel XIRR function with the three cash flows and their dates it gives me the #NUM! error.

XIRR(B1:B3,A1:A3) = #NUM!

Once we had reduced our cash flows to only two we could have also used the RATE function to find the XIRR for our two sets of cash flows. But here it may be that the RATE function will not be able to solve for the interest rate as it used iterative techniques to solve for the interest rate in the TVM equation given below and in our case the PMT is 0 and the other values for PV, FV, NPER are known and we are solving for the RATE

PV + PMT (1+RATE*type) ( 1 - (1+RATE)^-NPER ) / RATE + FV (1+RATE)^-NPER

For the first data set

NPER = 251/365

PMT = 0

PV = 28384.68

FV = -35667.9

RATE = ?

=RATE(251/365,0,28384.68,-35667.9)

39.39%

For the second data set

NPER = 251/365

PMT = 0

PV = 28384.68

FV = -15667.93

RATE = ?

=RATE(251/365,0,28384.68,-15667.93)

-57.86%

I noticed that OP listed a third set of cash flows at the bottom of his or her post that showed only two dates and two cash flows. The XIRR for these two cash flows may be found following the instructions I have provided in solving for the internal rate of return in my presentation.

Edit

I looked at the actual set of numbers for which you are getting an error

01/28/2014 ($4,811.29)

** 01/23/2014 23.464 $213.09 $4,999.94 **

If you tried to use each of the three cash flows 23.464, 213.09 and 4999.94 then XIRR will only be able to find the rate when 4999.94 is the cash flow for the date 01/23/2014

For the other two cash flow of 23.464 or 213.09 the return is so large that it is even out of the limits for the data type that hold the XIRR values

i = [(-FV/PV)^(1/t)] - 1

FV = -4811.29

PV = 4999.94

i = [ (–4811.29/4999.94)^(365/5) ] - 1

i = [ (4811.29/4999.94)^(73) ] - 1

i = [ 0.96 ^73 ] - 1

i = 0.06 - 1

i = -0.94

i = -94%

FV = -4811.29

PV = 213.09

i = [ (–4811.29/213.09)^(365/5) ] - 1

i = [ (4811.29/213.09)^(73) ] - 1

i = [ 22.58^73 ] - 1

i = [ 660,675,148,281,935,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000.000 ] - 1

i = 66067514828193500000000000000000000000000000000000000000000000000000000000000000000000000000000000000%

FV = -4811.29

PV = 23.464

i = [ (–4811.29/23.464)^(365/5) ] - 1

i = [ (4811.29/23.464)^(73) ] - 1

i = [ 205.05^73 ] - 1

i = [ 5,830,991,233,029,370,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000.000 ] - 1

i = 583099123302937000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000%

#2

Your combination of dates and amounts are not good examples for this function. I have explained my understanding when the same question was raised here and thankfully had an expert expand upon what I provided. From the help page for this function:

XIRR(Values; Dates; Guess)

Values and Dates refer to a series of payments and the series of associated date values. The first pair of dates defines the start of the payment plan. All other date values must be later, but need not be in any order. The series of values must contain at least one negative and one positive value (receipts and deposits).

Guess (optional) is a guess that can be input for the internal rate of return. The default is 10%.

The example then provided is that of an initial investment (large negative Value) and ensuing returns from that investment (smaller positive Values). This is a highly specific function that will require fine tuning of the Guess value if a re-investment (further large negative Value) occurs later in the date sequence, as the eventual break-even point is being pushed out.

#4

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.