Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
XIRR
"W" wrote in message news:...
The XIRR function seems to want an array of values where the first element is always negative, and the rest are positive. So I model a stock purchase and sale as the first number being negative, and the sale being positive. But what about if you have a short sale of stock, where you receive funds before you buy and pay funds? That makes the first cell of the value array positive. XIRR freaks out and gives a #NUM! result. Why can't XIRR deal with a positive followed by negatives? It is weird to have to distort every financial transaction to make it look like something XIRR can work with. Is there any third party software the implements an XIRR that is more powerful and can take more realistic inputs? Correction on this, what happens is I get a negative XIRR. If I have a short sale like: 1/27/2012 2/3/2012 $52 -$50 XIRR tells me it is an annualized return of -87.1% (NEGATIVE return, even though it is net positive cash) If I reverse this: 1/27/2012 2/3/2012 -$50 $52 XIRR tells me it is 673% annualized return. But at least it is positive. Why does XIRR have problems dealing with positive cash up front followed by negative cash flows? -- W |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
XIRR
Have you looked at alternatives in the List of Financial Functions in Excel help?
Possibly?... MIRR -- Jim Cone Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware (Calculate Payments XL add-in: amount, interest rate, payment, term - in the free folder) "W" wrote in message ... "W" wrote in message news:... The XIRR function seems to want an array of values where the first element is always negative, and the rest are positive. So I model a stock purchase and sale as the first number being negative, and the sale being positive. But what about if you have a short sale of stock, where you receive funds before you buy and pay funds? That makes the first cell of the value array positive. XIRR freaks out and gives a #NUM! result. Why can't XIRR deal with a positive followed by negatives? It is weird to have to distort every financial transaction to make it look like something XIRR can work with. Is there any third party software the implements an XIRR that is more powerful and can take more realistic inputs? Correction on this, what happens is I get a negative XIRR. If I have a short sale like: 1/27/2012 2/3/2012 $52 -$50 XIRR tells me it is an annualized return of -87.1% (NEGATIVE return, even though it is net positive cash) If I reverse this: 1/27/2012 2/3/2012 -$50 $52 XIRR tells me it is 673% annualized return. But at least it is positive. Why does XIRR have problems dealing with positive cash up front followed by negative cash flows? -- W |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
XIRR
On Apr 4, 3:34*pm, "W" wrote:
"W" wrote in message news:... Correction on this, what happens is I get a negative XIRR. * If Ihavea short sale like: 1/27/2012 * * * * * * * *2/3/2012 $52 * * * * * * * * * * * * * *-$50 XIRR tells me it is an annualized return of -87.1% (NEGATIVE return, even though it is net positive cash) If I reverse this: 1/27/2012 * * * * * * * *2/3/2012 -$50 * * * * * * * * * * * * * *$52 XIRR tells me it is 673% annualized return. * But at least it is positive. Why does XIRRhaveproblems dealing with positive cash up front followed by negative cash flows? -- W XIRR function in Excel works as it suppose to, XIRR calculates internal rate of return for irregular cash flows. To calculate IRR, it uses net present value equation set to Zero. This also holds true for calculation of internal rate of return using IRR Excel function It doesn't have to be a net present value equation that is set to zero to find IRR yet since most professors at business schools and most text books define internal rate of return where NPV is zero thus most software programs make use of NPV equation whilst finding IRR The other equations that may be used for calculation of IRR include net future value equation set to zero, profitability index equation set to 1, or TVM equation set to 0 in case of uniform series of cash flows More about the usage of these 4 equations can be found here http://finance.thinkanddone.com/irr.html Now back to your sample cash flows, XIRR is producing the correct internal rates of return at which both sample projects have an NPV of ZERO See the detailed solution for XIRR Calculation and subsequent NPV calculation for your sample date below this line CF DATE T 52 1/27/2012 0 -50 2/3/2012 7/365 IRR calculation 52 - 50(1+i)^(-7/365) = 0 - 50(1+i)^(-7/365) = -52 (1+i)^(-7/365) = 52/50 1/(1+i)^(7/365) = 52/50 (1+i)^(7/365) = 50/52 (1+i)^(0.0192) = 0.961538462 1+i = 0.961538462^(1/0.0192) 1+i = 0.12936983441424 i = 0.12936983441424 - 1 i = 0.12936983441424 - 1 i = -0.87063016558576 i = -87.06% NPV Calculation NPV = 52 - 50(1+i)^(-7/365) NPV = 52 - 50(1-0.87063016558576)^(-7/365) NPV = 52 - 50(0.12936983441424)^(-7/365) NPV = 52 - 50/(0.12936983441424)^(7/365) NPV = 52 - 50/(0.12936983441424)^(0.0192) NPV = 52 - 50/0.961538461538462 NPV = 52 - 52 NPV = 0 CF DATE T -50 1/27/2012 0 52 2/3/2012 7/365 IRR calculation -50 + 52(1+i)^(-7/365) = 0 52(1+i)^(-7/365) = 50 (1+i)^(-7/365) = 50/52 1/(1+i)^(7/365) = 50/52 (1+i)^(7/365) = 52/50 (1+i)^(0.0192) = 1.04 1+i = 1.04^(1/0.0192) 1+i = 7.729777228 i = 7.729777228 - 1 i = 6.729777228 i = 672.98% NPV Calculation NPV = -50 + 52(1+i)^(-7/365) NPV = -50 - 52(1+6.729777228)^(-7/365) NPV = -50 - 52(7.729777228)^(-7/365) NPV = -50 - 52/(7.729777228)^(7/365) NPV = -50 - 52/1.04 NPV = -50 + 50 NPV = 0 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
XIRR
"W" wrote:
what about if you have a short sale of stock, where you receive funds before you buy and pay funds? [....] Why can't XIRR deal with a positive followed by negatives? It can. You are misinterpreting the reason why XIRR returns a negative IRR in this case. (By the way, I will use the terms XIRR and IRR interchangably. XIRR is simply Excel's way of calculating the IRR when cash flows occur on irregular dates.) "W" wrote: If I have a short sale like: 1/27/2012 2/3/2012 $52 -$50 XIRR tells me it is an annualized return of -87.1% (NEGATIVE return, even though it is net positive cash) And that is correct mathematically for the NPV formula that Excel uses to calculate the (X)IRR. The problem is that that definition of IRR is not appropriate for a short sale. See the explanation below. But first.... "W" wrote: If I reverse this: 1/27/2012 2/3/2012 -$50 $52 XIRR tells me it is 673% annualized return. But at least it is positive. GIGO. That is not the "reverse" of the cash flows that model the short sale. You had it right the first time. ----- Returning to the original question.... The problem is: when IRR is computed using the NPV formula, the assumption is that we have a profitable cash flow, ergo a positive IRR, when the sum of the later cash flows is __greater__ than (minus) the initial cash flow. That is a necessary mathematical requirement because, with a positive IRR, the magnitudes of the later cash flows are reduced ("discounted"). If the sum of the later cash flows were already less than the (minus) initial cash flow, the sum of the discounted cash flows would be even smaller than the (minus) initial cash flow. Ergo, the NPV could not be made zero with a positive IRR. But the point is: that assumption does not apply to short sales. For a short sale to be profitable, the "sum of the later cash flows" (the later purchase price less transaction costs) must be __less__ than (minus) the initial sale proceeds (less transaction and margin costs). So for short sales, the annualized IRR is (1 + (s-p)/s)^(365/days) - 1, where "s" is the initial net sales proceeds, "p" is the later net purchase proceeds, and "days" is the hold time. For your example, that would be about 615.56% annualized IRR. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
XIRR
PS.... I wrote:
So for short sales, the annualized IRR is (1 + (s-p)/s)^(365/days) - 1, where "s" is the initial net sales proceeds, "p" is the later net purchase proceeds, and "days" is the hold time. For your example, that would be about 615.56% annualized IRR. In case it is not obvious from the description, "s" and "p" are both positive typically. That is, they are __not__ signed cash flows. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
XIRR
PPS.... I wrote:
So for short sales, the annualized IRR is (1 + (s-p)/s)^(365/days) - 1, where "s" is the initial net sales proceeds, "p" is the later net purchase proceeds, and "days" is the hold time. For your example, that would be about 615.56% annualized IRR. Needless to say, the __annualized__ IRR is misleading, IMHO. I don't know what conventional practice is for short sellers. But IMHO, for hold times of less than 1 year, I would use the simple rate, namely (s-p)/p formatted as Percentage. Granted, that does not take "time value" into account. But I think any attempt to do so is misleading. Consider your example, but with a hold time of 14 days. The annualized IRR would be about 167.50%. Do you really __feel__ (subjectively) that the "rate of return" is more than 3.5 times better when the hold time is only 7 days? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XIRR | Excel Discussion (Misc queries) | |||
XIRR | Excel Worksheet Functions | |||
tir... is the same as xirr?? | Excel Discussion (Misc queries) | |||
To XIRR or Not To XIRR | Excel Worksheet Functions | |||
Xirr? | Excel Discussion (Misc queries) |