M
Michael Marshall
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 nonsensicalinterest 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 anExcel 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
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 nonsensicalinterest 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 anExcel 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