Forum Discussion
sakamoji
Dec 17, 2024Copper Contributor
FV (Future Value) conundrum!
Future Value (FV) Conundrum!
Example Starting Amount $100,000, Term 3 and Interest Rate 3%.
Standard formula is FV=PV*(1+rate)^Term
To agree with first web calculator I tried,(for inflation), I turned the term to a NEGATIVE.
Answer using Standard Formula with NEGATIVE exponent, answer is $91,514.17
Using Excel Function =FV(rate,nper,pmt,[pv],[type]) with NEGATIVE NPER, answer is $91,514.17, which is in agreement with standard formula result above.
Answer from WEB site BankofCanada calculator AND Buyupside calculator all in agreement, all say $91,514.17
BUT......
Web site calculator dot net calculates it different as $91,267.30!!
Web site vertex42 agrees with $91,267,30
Excel array with agrees with $91,267,30!!
Year Principal After Inflation
1 $100,000.00 $97,000.00 ←*0.97
2 $97,000.00 $94,090.00 ←*0.97
3 $94,090.00 $91,267.30 ←*0.97
So which one is correct? Difference of two hundred forty six and eighty seven cents!
What am I doing wrong?
I see that the excel array yields $91,267.30, That is irrefutable. Agree?
The standard formula FV=PV*(1+Rate)^Term also yields $91,267.30 if Rate is expressed as NEGATIVE.
So the suggestion that I go with a NEGATIVE interest rate in the EXCEL function has merit.
What perplexed me was out of the 1/2 dozen or so on line calculators I used, half went one way and the other went the other way ($91,514.17). I just wanted to run this by the Excel community. I cannot dispute the Excel array, so I am going with the negative INTEREST rate although it still bothers me that some allegedly reputable on line calculators disagree with each other! Ah! Well! Trust no one! 🤣Except Excel!!
Thnaks for your input. Appreciated.
Instead of using a negative nper, use a negative rate.
Using a negative nper would be equivalent to using PV instead of FV: how much should I deposit now to be able to withdraw $100,000.00 after 3 terms at 3%.
- m_tarlerBronze Contributor
As I think Hans is saying, the difference is if you have negative Rate or negative Terms:
- sakamojiCopper Contributor
I see that the excel array yields $91,267.30, That is irrefutable. Agree?
The standard formula FV=PV*(1+Rate)^Term also yields $91,267.30 if Rate is expressed as NEGATIVE.
So the suggestion that I go with a NEGATIVE interest rate in the EXCEL function has merit.
What perplexed me was out of the 1/2 dozen or so on line calculators I used, half went one way and the other went the other way ($91,514.17). I just wanted to run this by the Excel community. I cannot dispute the Excel array, so I am going with the negative INTEREST rate although it still bothers me that some allegedly reputable on line calculators disagree with each other! Ah! Well! Trust no one! 🤣Except Excel!!
Thnaks for your input. Appreciated.