Forum Discussion
FV (Future Value) conundrum!
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%.
As I think Hans is saying, the difference is if you have negative Rate or negative Terms:
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.
I see that the excel array yields $91,267.30, That is irrefutable. Agree?
Not necessarily. This is a serious case of "garbage in, garbage out" (GIGO). You present a formula and a set of parameters, without ever articulating the problem that you want to solve.
I cannot dispute the Excel array [....] Ah! Well! Trust no one! Except Excel!
All you demonstrated is that FV(3%, -3, 0, -100000) and FV(-3%, 3, 0, -100000) have different results.
You also demonstrated that the series C1*(1 - 3%) has the same result as FV(-3%, 3, 0, -100000), when C1=100000 and the formula is copied into C2:C4. Certainly, that is to be expected.
You still have no reason to accept one or the other use of the FV function as a solution to your problem (unstated). Or that the FV function should be used at all!
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
I have not been able to duplicate your contradicting results. But then again, you do not properly identify the calculators (complete URLs) and exactly how you used them.
-----
So, what problem might you want to solve?
Since you mention "future value" and "inflation", I can think 3 possibilities.
.1 If something costs $100,000 today, what will it cost in 3 years, assuming 3% inflation?
The answer is =FV(3%, 3, 0, -100000), which returns $109,272.70.
Presumably, that is not the problem you are trying to solve.
.2 What is the "buying power" of $100,000 in 3 years, assuming 3% inflation?
IOW, if we put $100,000 into a shoebox today and open it in 3 years, what can we expect to purchase in today's dollars, assuming 3% inflation?
And that is the same as asking: what amount in today's dollars will cost $100,000 in 3 years, assuming 3% inflation?
Yes, we could write =FV(3%, -3, 0, -100000).
But it is more natural to write =PV(3%, 3, 0, -100000).
In both cases, the result is $91,514.17.
.3 If something costs $100,000 today, what did it cost 3 years ago, assuming 3% inflation?
The answer is the same as #2: =PV(3%, 3, 0, -100000).
.4 So, what does FV(-3%, 3, 0, -100000) calculate, if it is not the answer to #2 and #3?
That would be the answer to: if we invest $100,000 today, what is the value in 3 years, assuming an annual loss of 3%? And that does result in $91,267.30.
But an annual loss of 3% is not the same as a "loss" (reduction) in buying power due to 3% inflation, as demonstrated by #2 above.
