Excel Wizards: How do I get the Future Value if the payments are growing by a fixed %?

June 2, 2005 3:06 PM

Dear Excel Gods, please tell me there is an easy way to do this. I am so stuck! I am using FV (Future Value) to figure out the return of an investment. But, as it says in the excel help, "[FV] Returns the future value of an investment based on periodic, constant payments and a constant interest rate." The payment I am getting is not constant, but rather itself grows by a constant interest rate each period. So what equation or formula do I need for that?

To clarify, regular old FV says, 'Fred pays you $500 bucks a year for ten years and you throw it in a bank account that earns 10% per year. So the FV is $7968 at the end of the ten years." The situation I need to calculate is this: 'Fred pays me $500 this year, but each year he owes me 5% more-- so next year he owes me $525, and the year after $551.25 (525*1.05) on so on. Every year when Fred pays me I put it in the bank and get 10% interest. What is the FV at the end of the 10 years?" I know how to solve this by setting up a table and having a line for each year and carrying over theresults. But I need a formula that I can put in one cell for it. Anyone know how to do this or know what formula works like this?

posted by wtfwjd? to computers & internet (10 comments total)

Maybe something like described here? It seems that the exponential thing is the key.

I failed math.

... so did Einstein...though....
posted by odinsdream at 3:16 PM on June 2, 2005

odinsdream: yeah, in theory I could do it that way, but that requires setting up a table (see how they use the fill down in the example which builds the table.) I have to get this from just a few cells, and preferably a single cell formula (like the FV formula.) For reasons that would take way too long to explain (but to do with the VBA macros already built) I need a solution that doesn't rely on a table. Too bad, because the way suggested in the link you posted is simple and obvious. But thanks for trying :-)
posted by wtfwjd? at 3:32 PM on June 2, 2005

Ah. If you're already using VB macros for something else, perhaps you'd be willing/able to code up what you want and make a User-Defined Function (UDF).

With a UDF, you could still have it in a single cell, while all the work is done in a macro whenever the formula is auto-calculated.

You'd end up with a cell that just had something like this in it:
"=CalculateNonConstantFV(BasePayment, Percentage, Duration)"

...or, however you ended up writing your function. Though, I'd be willing to bet someone else on the internet has already written a UDF for exactly what you're looking to do.
posted by odinsdream at 4:06 PM on June 2, 2005

Also, mrexcel has a nice online community message board for this kind of stuff, if you haven't come across it already.
posted by odinsdream at 4:42 PM on June 2, 2005

Yes, you could probably sit down and in a few minutes or hours depending upon how long you are out of college derive a single formula for this, but why? That is the beauty of a spreadsheet. You can do this without a single formula and pull the result back into a single cell. Five minutes of spreadsheet inelegance versus most likely more than an hour of formulaic elegance. This is a no brainer. If I am paying you I know what I want you to do, just document it so I can understand your math later.
posted by caddis at 6:32 PM on June 2, 2005

I think you're looking for the second formula on this page. That gives you the present value. Multiply by (1+rate)^(#of periods) to get the future value.
posted by stuart_s at 6:58 PM on June 2, 2005

fv = pv (1+r)ⁿ + pmt ((1+r)ⁿ - (1+q)ⁿ) / (r-q)

except if r=q, in which case

fv = pv (1+r)ⁿ + n pmt (1+r)^n-1

fv future value
pv present value
r interest rate per period (10% in the first example)
q rate of increase in payment (5% in the first example)
n number of periods
posted by blue mustard at 7:37 PM on June 2, 2005 [1 favorite]

Oops, forgot a variable:

pmt first period payment
posted by blue mustard at 7:40 PM on June 2, 2005

blue mustard: the answer is perfect!! This saves me so much hassle, thanks! Can I ask you [or any math star who might read this] one follow up question since I am a little (read: extremely) lame when it comes to math.... If I know the fv in your equation and I know the bank's interest rate, what is the formula to figure out what Fred's % increase is each year (assuming it is a fixed rate for the duration of the number of periods.) In other words, what is the formula if I know everything but q in your nicely laid out explanation?
posted by wtfwjd? at 12:29 AM on June 3, 2005

blue mustard: the answer is perfect!! This saves me so much hassle, thanks! Can I ask you [or any math star who might read this) one follow up question since I am a little (read: extremely) lame when it comes to math.... If I know the fv in your equation and I know the bank's interest rate, what is the formula to figure out what Fred's % increase is each year (assuming it is a fixed rate for the duration of the number of periods.) In other words, what is the formula if I know everything but q in your nicely laid out explanation?
posted by wtfwjd? at 12:30 AM on June 3, 2005