First time here? Check out the FAQ!

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) |

Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.