Compound Savings Plus Pay Increase

[Guest]

I need a formula to calculate compound savings based on salary defferal that
will compound based on a variable (montly,quarterly,annually) as well as
based on variable rate of return, employer match % and salary increase%.

So basically I want to take an monthly salary $6000, figure out a monthly
deferral based on salary (say 2% or $120 ) plus Employer match %(say 2% or
$120 as well) for a total monthly investment of $240 compounded monthly at
7% for year 1.

Then have it increase based on an annual increase rate (say 3% or
$180/month) and repeat for a set number of years (like 20).

Ideally I'd have a list of variables I could enter in to customize this report

I'd like to be able to see a 10 year report for the year end value.

 

[Bernie Deitrick]

I would set up a table, with 7 columns (A through G), one for each of the following, entered in row
2:

Date
Monthly Salary $
Monthly Deferral Amount $
Employer Match $
Monthly Investment $
Total Investment $
Monthly Earnings $

For each month, I would have one row, so the table would end up being 240 rows long to handle the
twenty years that you want.

I would use row 1 for my assumption constants:
Annual increase in salary (3%) in cell B1
Deferral amount (2%) in C1
Employer match (2%) in D1
Annual percantage rate return (7%) in F1
Initial account value (could be zero) in G1

Then in cell A3, enter the first date - let's say 9/1/06. In cell A4, enter 10/1/06. (US date
format) Then format both cells for dates, select both cells, and drag the fill handle down for 240
rows.

In cell B3, enter the starting salary (6,000)
In cell B4, enter the formula

=IF(MONTH(A4)=1,B3*(1+$B$1),B3)

(IF pay raises are given in a month other than January, change the =1 to reflect the different
month)
And then copy B4 down to match your dates column.
In cell C3: =B3*$C$1
In cell D3: =B3*$D$1
In cell E3: =C3+D3
In cell F3: =G3*$F$1/12
In cell G3: =G1+E3
In cell G4: =G3+E4+F3
Copy cells C3:F3 down to match, and cell G4 down to match, and you're done.
This assumes that interest is paid to the account at each month's end.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Bernie,

Thanks for the advice, and taking the time to answer this post. I have
tried to replicate, but the formula is giving me an error for some reason
that you asked me to put into B4. I'm pasting it in here:
=IF(MONTH(A4)=1,B3*(1+$B$1),B3)

I tried to copy and paste it right from your post as well. Am I missing
something?

 

[Bernie Deitrick]

Does A4 have an actual date, or a string that looks like a date?

Try

==IF(MONTH(DATEVALUE(A4))=1,B3*(1+$B$1),B3)

HTH,
Bernie
MS Excel MVP

 

[Guest]

Thanks Bernie. The problem was for some reason my excel expected |, not a
',' for these formulas. Once I put them in it worked good. Thanks! It
looks great.