Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
XIRR
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? -- W |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
XIRR
On Apr 4, 2:04*pm, "W" wrote:
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? If XIRR does return a #NUM error, in such a case you may wish to provide a value for GUESS that is in close proximity to the actual rate of return #NUM error signifies that Excel has exhausted all 20 iterations and failed to find the IRR or it may be that there is an intermediate calculation where the differential of the XNPV function is ZERO Is there any third party software the implements an XIRR that is more powerful and can take more realistic inputs? -- W Although this may not solve your concern with stock data calculations for XIRR yet you may want to have a look at TADXL add-in for finance. This add-in provides new financial functions for Excel and extends the existing financial functions. Excel's financial functions or most of them assume interest is compounded discretely on a periodic basis. Whereas in finance and banking interest is usually compounded continuously. Here TADXL extends the 5 TVM functions of FV, PV, NPER, PMT & RATE to permit selection of either discrete or continuous compounding. NPV function of Excel calculates net present value of an annuity with end of period payments and you have to mutate it to find net present value of annuity with start of period payments. Then there are a good number of financial functions that aren't there in Excel, for example, you can't calculate payback period (undiscounted or discounted), profitability index, benefits to costs ratio, net future value. Here too, TADXL has new functions that make it possible to perform capital budgeting techniques I listed in the last para. And then building on them it provides the corresponding functions for irregular cash flows where as Excel only has two such functions namely XIRR and XNPV |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XIRR | Excel Programming | |||
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) |