Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
XIRR Requiring Sale Price 0
I am noticing some strange behaviors in the XIRR function in Excel 2003.
First, if an investment is wiped out, and I have a sale price of $0 on the final date, XIRR gives a #NUM error. I have to fudge past this by making the sale price $0.01. Why does XIRR forbid $0 for the sale price? Second, if the Sale date is not about eight months in the future, XIRR also gives a #NUM error. When modeling a stock investment, the purchase and sale might be within a three week period. So having to fudge the sale date to eight months in the future makes the model artificial. Any way around these two issues? -- Will |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
XIRR Requiring Sale Price 0
On Nov 3, 1:40*am, "Will" wrote:
Why does XIRR forbid $0 for the sale price? Generally, it doesn't. If the last cash flows are zero, they simply have no effect on the XIRR computation. First, if an investment is wiped out, and I have a sale price of $0 on the final date, XIRR gives a #NUM error. I presume you are saying that you have just two cash flows: an investment (negative number), and a sales price (zero, in this case). Look at the XIRR help page; it shows the formula for XIRR. In your case, XIRR tries to find an IRR rate that satisfies this equation (simplified): 0 = CF0/(1+r)^t1 + CF1/(1+r)^t2 Since, in your case, CF1 is zero, this simplifies to: 0 = CF0/(1+r)^t1 Normally there is no "r" that satisfies that equation. Exceptions: (a) using binary computer arithmetic, there might be some infinitessimal "r" that will cause the rounded result to be zero; and (b) if CF0 is zero, there is an infinite number of solutions. So XIRR correctly returns #NUM to indicate that it cannot find a solution within its internal limitations. Second, if the Sale date is not about eight months in the future, XIRR also gives a #NUM error. Generally, there is no such limitation. However, XIRR will return #NUM if it cannot find a solution within its internal limitations. If you are sure there is a reasonable solution, you might need to help XIRR by specifying the "guess" parameter. That is unfortunate because I do not know of any way, in general, to detemine a good "guess". ----- original posting ------ On Nov 3, 1:40 am, "Will" wrote: I am noticing some strange behaviors in the XIRR function in Excel 2003. First, if an investment is wiped out, and I have a sale price of $0 on the final date, XIRR gives a #NUM error. * I have to fudge past this by making the sale price $0.01. * Why does XIRR forbid $0 for the sale price? Second, if the Sale date is not about eight months in the future, XIRR also gives a #NUM error. * *When modeling a stock investment, the purchase and sale might be within a three week period. * *So having to fudge the sale date to eight months in the future makes the model artificial. Any way around these two issues? -- Will |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
XIRR Requiring Sale Price 0
Errata (nitpick) ....
On Nov 3, 10:41*am, I wrote: 0 = CF0/(1+r)^t1 Normally there is no "r" that satisfies that equation. *Exceptions: (a) using binary computer arithmetic, there might be some infinitessimal "r" that will cause the rounded result to be zero Actually, an infinitely large "r"; and even that is not quite right. What I mean is: the divisor (1+r)^t1 would be infinitely large. The size of "r" would be limited, depending on "t1", so that the divisor expression does not overflow. For example (probably not the only one), if CF0 is the smallest possible number in Excel (=2^-1022), and the divisor is the largest possible number (=(2^1023 - 2^(1023-53))*2), the result is exactly 0. This is due to the behavior of binary computer arithmetic, not Excel per se. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting a sale price and rounding up | Excel Worksheet Functions | |||
Can I automaticly figure "cost" and sale price in Excel? | Excel Worksheet Functions | |||
If function with date; if sale is Oct place sale $ amount here | Excel Worksheet Functions | |||
formula for cost as percentage of sale price | New Users to Excel | |||
skus in columm a for each sale, qty. of skus sold, each sale in co | Excel Worksheet Functions |