Ask Your Question
0

XIRR with expected negative rate of return gives Err:502 [closed]

asked 2014-01-29 19:02:39 +0200

benchrest gravatar image

updated 2015-09-12 03:38:16 +0200

Alex Kemp gravatar image

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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-19 11:50:24.820341

2 Answers

Sort by » oldest newest most voted
1

answered 2014-02-24 14:59:17 +0200

updated 2014-02-24 16:28:33 +0200

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

edit flag offensive delete link more
0

answered 2014-01-30 05:11:09 +0200

oweng gravatar image

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.

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2014-01-29 19:02:39 +0200

Seen: 8,737 times

Last updated: Feb 24 '14