Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel TVM functions of RATE, NPER, PMT, PV, and FV fail to produceresults that are known about Geometric Series
The TVM equation that is used in calculating the 5 time value of money Excel functions is a geometric series up to N terms. But when I tried to test known results for present value and future value of series of periodic payments at an exceptional interest rate at which the following results are expected but Excel failed to produce such results
When the terms of Geometric Series are an even number then the following holds true for present value and future value of an annuity at an interest rate of -200% Example data uses an annuity in amount of $100 for 101 and 100 years discounted at -200% Present value of a series of payments in amount of $100 for 100 years at -200% interest rate is $0 Future value of a series of payments in amount of $100 for 100 years at -200% interest rate is $0 Present value of a series of payments in amount of $100 for 101 years at -200% interest rate is $-100 Future value of a series of payments in amount of $100 for 101 years at -200% interest rate is $100 But Excel is unable to find this present value and future value at -200% interest rate Now if we ask Excel to find the interest rate when the other values for NPER, PMT, PV and FV are known Excel gives garbage results such as nonsensical interest rate of -100% when the actual rate should be -200% What Gives I tried the same calculations using same TVM functions that are found in an Excel add-in called tadXL and it reported correct interest rate of -200% for all different options and it also produced correct results for NPER, PMT, PV and FV when an interest rate of -200% was entered to find the respective values |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
geometric mean of a series of negative asset returns | Excel Discussion (Misc queries) | |||
Solve for interest rate of a security when pv, fv, nper are know? | Excel Discussion (Misc queries) | |||
how to calculate Rate when PMT, Nper & PV is known | Excel Worksheet Functions | |||
Formula & functions intermittently fail to calculate | Excel Worksheet Functions | |||
User-defined functions created in Excel 2000 fail in Excel 2003 | Excel Discussion (Misc queries) |