Ask Your Question

Financial_Engineer's profile - activity

2014-02-28 02:13:44 +0200 received badge  Teacher (source)
2014-02-24 16:25:58 +0200 received badge  Editor (source)
2014-02-24 14:59:17 +0200 answered a question XIRR with expected negative rate of return gives Err:502

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)