Formula for Microsoft Excel®'s FV function?

1. Sep 30, 2006

MAtkins

Formula for Microsoft Excel®'s FV function??

Hi:

I'm trying to find a formula that will return the same results as Microsoft Excel®'s FV function.
I've found several Future Value functions on the Internet but none return the same answer as Excel® (or even close).
I noticed also that Excel®'s FV function requires both a Present Value and a Payment. I can't find any FV formulas that require both.

Any help would be greatly appreciated.

2. Oct 28, 2006

jfielding

I am currently investigating the same function. I am working with the formula:
FV(n) = PV(1 + r/n)Yn (Yn should be in super-script)

or in excel, using excels FV variables:
FV = pv*POWER(1+rate,nper)

This formula agrees with the excel FV when the "pmt" is 0. Also note that "type" is irrelevant when "pmt" is 0. I'm working on finding formula including pmt and will post if you reply.

I have another issue with the formula. It does not appear that it allows for compound interest other than for an annually. i.e. The formula works fine for a 3 year period compounding annaully, but what if I want to calculate monthly or quarterly compounding?

3. Oct 28, 2006

uart

This is often taught in high-school here as a application of geomentric series.

The derivation goes like this.

Using the notation :
r = 1 + interest_rate_per_term_as_decimal
p = present value
a = payment per term
eot1 denotes the FV at end of term 1 etc.

eot1: rp + a
eot2: r(rp + a) + a = r^2p + ra + a
eot3: r(r^2p + ra + a) + a = r^3p + r^2a + ra + a
...
eotn: r^np + (r^(n-1) + r^(n-2) + ... 1)a = p r^n + a (r^n - 1)/(r-1)

That is,
FV = p r^n + a (r^n - 1)/(r-1).
This is precisely what exel computes for the case of payments made at the end of each term (payment type = 0). It's easy enough to repeat the calculations as above for the case of payments made at the beginning of each term.

4. Nov 4, 2006

jfielding

hmmmm.... Thanks for the high-school math lesson, but I don't think that was the point of the question. There are some standard equations in the accounting and/or finance arena, and I think MAtkins was just trying to relate them to the FV function of Excel… or at least that’s what I was trying to do. (Maybe this is the wrong forum for this question)

The std formulas are related to Compound Interest (Future Value):
Investment without payments
FVn = P(1 + r/n)Yn

Investment with payments starting at end of 1st period
FVn = P(1 + r/n)Yn + c[((1 + r/n)Yn ) - 1) / r/n]

Investment with payments starting at beginning of 1st period
FVn = P(1 + r/n)Yn + c[((1 + r/n)Yn + 1 - (1 + r/n) ) / r/n]

Anyway, I have a spreadsheet that relates the above std formula’s to Excel’s FV function if anyone’s interested. My guess is MAtkins figured it out and moved on.

5. Sep 14, 2009

aleks512

Re: Formula for Microsoft Excel®'s FV function??

Isn't this what you're looking for?
http://support.microsoft.com/kb/214005#appliesto [Broken]

Last edited by a moderator: May 4, 2017
6. Jan 21, 2010