Formula for Microsoft Excel®'s FV function?

  • Thread starter MAtkins
  • Start date
1
0

Main Question or Discussion Point

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.
 

Answers and Replies

2
0
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?
 
uart
Science Advisor
2,776
9
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.
 
2
0
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.
 
1
0
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:

Related Threads for: Formula for Microsoft Excel®'s FV function?

  • Last Post
Replies
9
Views
5K
  • Last Post
Replies
1
Views
4K
Replies
17
Views
5K
  • Last Post
Replies
3
Views
4K
  • Last Post
Replies
1
Views
3K
  • Last Post
Replies
2
Views
2K
Replies
3
Views
2K
  • Last Post
Replies
6
Views
2K
Top