Is there a simpe formula for future value with increasing payments? (Excel help)

Factual Questions
1

I’m trying to determine the future value of periodic payments. Every formula that I’ve found requires a fixed payment but since the payment will be based on my salary, I want to increase my payment by 3.5% annually since I plan on getting annual raises.

For example, suppose my bi-weekly salary is $1923 and I want to contribute 1%. What will the future value be in 20 years if I get a 3.5% yearly raise and an 8% interest rate?

Can this be done with a simple formula in Excel?

No this isn’t homework. I’m 44 and college was a long time ago.

2

You can work out a formula, but these sorts of problems are generally easier to work out from first principles.

The key observation here is that in any given year, your payments are fixed, so you can use the fixed payments formula for each year’s deposits. Then you just need to discount each value (use the PV function) and add them up.

3

You probably have to manually calculate it using the standard formula for a growing annuity:

Present value of a growing annuity (where g is the annual growth rate)

Here’s a link to using Excel for financial modeling:

And here’s a link to Excel add-ins that will do financial calculations:
http://www.ozgrid.com/Services/excelfunctions1.htm

4

One short cut: if your salary increases by 3.5% each year and if the underlying time-value of money is 8%, then you can merge the two by taking 1.08/1.035 = (approx) 1.0435 and so use 4.35% as the interest rate. Then your standard Excel program should work fine.

5

Here’s the solution from first principles I was talking about earlier:

I’m assuming that the first deposit is at the end of the first biweekly period in the first year. There are 26 biweekly periods in a year, so the effective biweekly interest rate is 1.08[sup]1/26[/sup] - 1, or approximately .002964. The present value of all the deposits is, in Excel notation, PV(0.002964;26;-19.23;0), which is equal to $480.52.

The present value of your geometric progression is just the present value of $480.52 invested at the beginning of every year using the interest rate Dex mentioned. In Excel notation, this is PV(1.08/1.035-1;20;-A1;0) (assuming that the value of the previous PV calculation is stored in cell A1). This is roughly equal to $6,333.76.

The future value is just $6,333.76 * 1.08[sup]20[/sup], or $29,521.37. Not bad for a $20 investment every two weeks.

6

Thanks for the help guys. My plan is to put a trivial amount of money into a Roth biweekly so that when I retire in 18 years I can take a heckuva vacation and not be irked about paying taxes on my withdrawal.