FV function as an algebraic expression

[gavin]

Can anyone tell me the underlying equation used by the FV function expressed
as an algebraic equation? I am creating a flash tool that will do the
calculation online, but need to know what the equation is so that it can be
programmed in.



The FV function uses the following syntax and arguments
FV(rate,nper,pmt,pv,type)
where:
Rate is the interest rate per period.

Nper is the total number of payment periods in an annuity.

Pmt is the payment made each period; it cannot change over the life of the
annuity. If pmt is omitted, you must include the pv argument.

Pv is the present value, or the lump-sum amount that a series of future
payments is worth right now. If pv is omitted, it is assumed to be 0 (zero),
and you must include the pmt argument.

Type is the number 0 or 1 and indicates when payments are due. If type is
omitted, it is assumed to be 0.

 

[Bernie Deitrick]

gavin,

It is simply exponential growth:

FV = P(1+r)^periods

where r is the rate of return, expressed as a decimal (5% = 0.05)

Note that return and period basis need to be consistent - 5% per year, then periods is in # of
years.

You can always do the period basis internally

FV = P(1+r/n)^(Y*n)

where n is the number of sub periods per time basis - usually, 12 months per year.....

HTH,
Bernie
MS Excel MVP

 

[gavin]

Hi Bert

Thanks for your help on this.

The formula works for making monthly payments P over a set number of
periods. It doesn't seem to allow for an initial lump sum payment at the
beginning of the period.

The FV dialoge box in excel allows for the input of a Pv value. I believe
the initial lump sum , of say £250, would go in here.

If you know how Excel inputs the initial lump sum into the equation would be
a great help.

Cheers

Gavin

 

[Bernie Deitrick]

Gavin,

Actually, the formula that I gave is for an initial value P, without additional payments.

If you want to include periodic payments, you need to use a summation, where each payment is
basically calculated separately:

FV = Sum for i = 0 to NP-1 of Payment for that period * (1+rate per period)^(NP-i)

(I cannot include the greek letter captial sigma.....)

Payment 0 would be the initial lump sum - all other payments would be the periodic payment. So, for
a four year investiment with monthly payments, you would need to loop through and do 48
calculations.

HTH,
Bernie
MS Excel MVP

 

[gavin]

Hey Bernie

Have tested this formula in Excel, and its does not give the same result as
when you input into the FV dialogue. They are close, but not quite the same.
It must be something in the way Excel does the calculation.

Does Microsoft publish the the formulas they have used when they programmed
Excel?

Cheers

Gavin