Formula for Microsoft Excel®'s FV function?

In summary, we are discussing the formula for Microsoft Excel®'s FV function and how to find a formula that returns the same results. We have discovered that the FV function requires both a present value and a payment, and have found a formula that agrees with the FV function when the payment is 0. There is also a discussion on how to incorporate compound interest into the formula for different compounding periods. Additionally, there is a mention of standard equations in the accounting and finance realm that relate to compound interest and a suggestion of a website for further assistance.
  • #1
MAtkins
1
0
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.
 
Mathematics news on Phys.org
  • #2
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
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
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


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

1. What is the FV function in Microsoft Excel®?

The FV (Future Value) function in Microsoft Excel® is a financial function that calculates the future value of an investment based on a series of constant payments and a constant interest rate. It is commonly used to forecast the value of an investment over time.

2. How do I use the FV function in Microsoft Excel®?

To use the FV function, you will need to provide the following inputs: rate (interest rate per period), nper (number of periods), pmt (constant payment per period), [pv] (optional - present value), [type] (optional - timing of payments, 0 or 1). The syntax for the FV function is =FV(rate, nper, pmt, [pv], [type]).

3. What is the difference between the FV function and the PV function in Microsoft Excel®?

The FV (Future Value) function calculates the future value of an investment, while the PV (Present Value) function calculates the present value of an investment. The main difference is that the FV function assumes payments are made at the end of each period, while the PV function assumes payments are made at the beginning of each period.

4. Can I use the FV function to calculate the future value of a loan?

Yes, the FV function can be used to calculate the future value of a loan. In this case, the rate input should be the loan's interest rate, the nper input should be the number of loan payments, and the pmt input should be the loan payment amount.

5. What type of data does the FV function return?

The FV function returns a numeric value, which represents the future value of an investment or loan.

Similar threads

  • General Math
Replies
3
Views
2K
  • General Math
Replies
31
Views
1K
  • General Math
Replies
2
Views
1K
Replies
4
Views
283
  • Computing and Technology
Replies
13
Views
927
Replies
7
Views
953
  • Precalculus Mathematics Homework Help
Replies
8
Views
2K
  • General Math
Replies
8
Views
1K
Replies
9
Views
2K
Back
Top