[Solved] Future Value (FV) comes as negative

Discuss the spreadsheet application
Post Reply
henders254
Posts: 4
Joined: Fri Dec 21, 2012 12:06 pm

[Solved] Future Value (FV) comes as negative

Post by henders254 »

I am trying to calculate future value but the number comes as negative.

Formula I am using: FV(rate; numperiods; payment; presentvalue; type)

=FV(6.64%; 10; 0; 0.22; 0 )

where my rate of return is 6.64%, period is 10 years and present value is $0.22 - all these values come from other fields in the spreadsheet.

The output I am getting is -$0.41

But if I plug in the same input in http://www.investopedia.com/calculator/fvcal.aspx I get the correct value which is $0.42

I noticed that if I hand-edit the FV and put -0.22, I get output $0.42 but, unfortunately, I can't hand-edit this as it should come from another field which field which has the value: $0.22.

Could anyone please help me fix this issue?

Thanks.
Last edited by Hagar Delest on Sat Dec 22, 2012 11:45 am, edited 1 time in total.
Reason: tagged [Solved].
Openoffice 3.4.1 on OS X Mountain Lion
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Future Value (FV) comes as negative

Post by eremmel »

According to Office Help, there is an example
Example
What is the value at the end of an investment if the interest rate is 4% and the payment period is two years, with a periodic payment of 750 currency units. The investment has a present value of 2,500 currency units.
=FV(4%;2;750;2500) = -4234.00 currency units. The value at the end of the investment is 4234.00 currency units.
That is in-line with your observation. You might prefix the function with an minus.
Last edited by eremmel on Fri Dec 21, 2012 3:40 pm, edited 1 time in total.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Keith Wild
Posts: 56
Joined: Wed Aug 12, 2009 1:14 pm
Location: London, UK

Re: Future Value (FV) comes as negative

Post by Keith Wild »

The example in Office Help is obviously bonkers.

If the interest rate is in cell A1 and PV in cell B1 and the term in C1you could just use the formula (1+A1)^C1*B1, maybe with $ signs if required.
e.g. (1.0664)^10*0.21 = 0.42
AOO 4.1.10
macOS Big Sur version 11.6.2
crusader
Volunteer
Posts: 537
Joined: Sun Jan 20, 2008 5:06 am

Re: Future Value (FV) comes as negative

Post by crusader »

Include *-1 (multiply by minus one) in your formula: FV(rate; numperiods; payment; (presentvalue*-1); type).
In your example: =FV(6.64%; 10; 0; (0.22*-1); 0)

This approach does make one assumption: the "presentvalue" (whatever its source) is always positive.

Hope this helps...
LibreOffice 26.2.x on Linux Mint Cinnamon 22.3
In a world without walls, who needs Window$
A candle loses nothing by lighting another candle
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Future Value (FV) comes as negative

Post by eremmel »

Also Excel returns a negative answer. It just might have to do with the financial definition of an 'investment'. I can image that it costs you money. I got this from a Wiki:
Investment has different meanings in finance and economics.

In economics, investment is related to saving and deferring consumption. Investment is involved in many areas of the economy, such as business management and finance whether for households, firms, or governments.

In finance, investment is putting money into something with the expectation of gain, usually over a longer term. This may or may not be backed by research and analysis. Most or all forms of investment involve some form of risk, such as investment in equities, property, and even fixed interest securities which are subject, inter alia, to inflation risk.
The FV function is classified as a financial function and not an economic function.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
henders254
Posts: 4
Joined: Fri Dec 21, 2012 12:06 pm

Re: Future Value (FV) comes as negative

Post by henders254 »

Keith Wild wrote:The example in Office Help is obviously bonkers.

If the interest rate is in cell A1 and PV in cell B1 and the term in C1you could just use the formula (1+A1)^C1*B1, maybe with $ signs if required.
e.g. (1.0664)^10*0.21 = 0.42
Thanks, this is exactly what I needed. Solves my problem.
Openoffice 3.4.1 on OS X Mountain Lion
FinancialEngineer
Posts: 12
Joined: Sat May 04, 2013 10:22 am
Location: Unknown

Re: Future Value (FV) comes as negative

Post by FinancialEngineer »

eremmel wrote:Also Excel returns a negative answer. It just might have to do with the financial definition of an 'investment'. I can image that it costs you money. I got this from a Wiki:
Investment has different meanings in finance and economics.

In economics, investment is related to saving and deferring consumption. Investment is involved in many areas of the economy, such as business management and finance whether for households, firms, or governments.

In finance, investment is putting money into something with the expectation of gain, usually over a longer term. This may or may not be backed by research and analysis. Most or all forms of investment involve some form of risk, such as investment in equities, property, and even fixed interest securities which are subject, inter alia, to inflation risk.
The FV function is classified as a financial function and not an economic function.
The 5 TVM functions (RATE, NPER, PMT, FV, and PV) in spreadsheet programs (Excel, OpenOffice Calc, LibreOffice Calc, Symphony Calc, GNumeric) and in financial calculators (TI BA II plus, HP 10bII plus, Casio Fc 100) are programmed using one of the following two time value of money equations.

PV (1+RATE)^NPER + PMT * (1+RATE * type) [ { (1+RATE)^NPER } - 1 ] / RATE + FV = 0

OR

FV (1+RATE)^-NPER + PMT * (1+RATE * type) [ 1 - { (1+RATE)^-NPER } ] / RATE + PV = 0


The first one of these two equations is defined as sum of compounded initial investment, compounded periodic payment and the non-compounded terminal value.

The second one of these two equations is defined as sum of discounted terminal investment, discounted periodic payment and the non-discounted initial investment.

We can define the five functions from these two equations to solve for the 5 different TVM variables

f(RATE)
f(NPER)
f(PMT)
f(PV)
f(FV)

All these functions can be solved for to find NPER, PMT, PV and FV yet we are unable to solve for f(RATE). As it is evident from the two equations that variable for RATE is not separable from the other four variables in the time value of money equations.

This leads us to make use of iterative methods in locating the interest rate at which the function f(RATE) yields a value of ZERO. Discussing iterative methods here is beyond the scope of this discussion and one can find many examples on the web for various numerical methods one can adapt in finding the RATE.

Now as we are setting the sum to zero in each of the two TVM equations, this requires that at least one of the three values for PMT, PV or FV must be negative and at least one of these three value must be positive. Without this enforcement the 2 two time value of money equations will not yield the desired results for the PMT, PV and FV.

Thus we are asked to make use of conventions from accounting when it comes to financial transactions that any money amount being paid out (DEBIT) should be used with a negation. And any money amount being paid in (CREDIT) should be used as positive.

So if you have an initial investment where you invested $100,000 that means an outgoing cash flow for PV should therefore be -100000 and at the maturity date the incoming amount for FV will be positive.

But then those who program these spreadsheet programs (Excel, OO Calc, LO Calc, Symphony Calc, and Gnumeric), and the financial calculators (TI BA II plus, HP 10bII plus, Casio Fc 100) simply take other peoples ideas without much thought as to what is it they are programming or what else lies beyond the trivial TVM equations. Not many people tend to think on their own as someone in a New York newspaper once wrote "Thinking is difficult" and we tend to not bother putting pressure on own nerves.

What I am getting at is that time value of money is much more complex than the obvious that has been programmed into spreadsheet functions and pocket calculators. For examples, annuity payments (PMT) may not be in constant amount and it may grow or shrink by a rate or it may increase or decrease by a constant amount. To make thing a bit more interesting, the annuity payments may never end and go on forever *young*. But then growth may not coincide with periodic payment and there are gaps between payments and growth. Or the payments may not commence at all at present and are deferred into the future.

So you can either accept what is spoon fed to you or you can leave the nest at age 18 and start to think on your own. I have done the latter yet no one bother to offer me a job since 1993 as I am a college drop out thanks largely to those who run the State of New York (Vampire Statesmen they are called).
OpenOffice 3.4.1 (en-US) , Win XP SP2
k9gardner
Posts: 1
Joined: Mon Mar 30, 2015 6:58 pm

Re: [Solved] Future Value (FV) comes as negative

Post by k9gardner »

For future generations who don't want to have to read through this whole topic, the entire answer is contained in this brief summary/section of FinancialEngineer's post (with my comments):
[By accepted-use definition]: PV + FV = 0.

[This is because] we are asked to make use of conventions from accounting when it comes to financial transactions that any money amount being paid out (DEBIT) should be used with a negation. And any money amount being paid in (CREDIT) should be used as positive.

So if you have an initial investment where you invested $100,000, that means an outgoing cash flow for PV [should be entered as] -100000 and at the maturity date the incoming amount for FV will be positive.
OpenOffice 4.1.1 on MacOS 10.10.2
Post Reply