Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Formula for Microsoft Excel®'s FV function?

  1. Sep 30, 2006 #1
    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. jcsd
  3. Oct 28, 2006 #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?
     
  4. Oct 28, 2006 #3

    uart

    User Avatar
    Science Advisor

    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.
     
  5. Nov 4, 2006 #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.
     
  6. Sep 14, 2009 #5
  7. Jan 21, 2010 #6
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook

Have something to add?



Similar Discussions: Formula for Microsoft Excel®'s FV function?
Loading...