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

June 2, 2005 3:06 PM Subscribe

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

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?

**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?

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

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

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

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

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

posted by stuart_s at 6:58 PM on June 2, 2005

*fv*=

*pv*(1+

*r*)

^{n}+

*pmt*((1+

*r*)

^{n}- (1+

*q*)

^{n}) / (

*r*-

*q*)

except if

*r*=

*q*, in which case

*fv*=

*pv*(1+

*r*)

^{n}+

*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]

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

posted by wtfwjd? at 12:29 AM on June 3, 2005

*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

posted by wtfwjd? at 12:30 AM on June 3, 2005

*q*in your nicely laid out explanation?posted by wtfwjd? at 12:30 AM on June 3, 2005

This thread is closed to new comments.

I failed math.

... so did Einstein...though....

posted by odinsdream at 3:16 PM on June 2, 2005