Forum Discussion

sakamoji's avatar
sakamoji
Copper Contributor
Dec 17, 2024

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_tarler's avatar
      m_tarler
      Bronze Contributor

      As I think Hans is saying, the difference is if you have negative Rate or negative Terms:

       

      • sakamoji's avatar
        sakamoji
        Copper 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.

Resources