Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compound Savings Plus Pay Increase
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compound Savings Plus Pay Increase
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 "BigIdeazLittleSkill" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compound Savings Plus Pay Increase
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 he =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" wrote: 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 "BigIdeazLittleSkill" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compound Savings Plus Pay Increase
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 "BigIdeazLittleSkill" wrote in message ... 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 he =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" wrote: 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 "BigIdeazLittleSkill" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compound Savings Plus Pay Increase
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. "Bernie Deitrick" wrote: 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 "BigIdeazLittleSkill" wrote in message ... 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 he =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" wrote: 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 "BigIdeazLittleSkill" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I increase a column of numbers by a percentage-same cell | Excel Discussion (Misc queries) | |||
Implied Compound Interest? | Excel Worksheet Functions | |||
Linea increase | Excel Discussion (Misc queries) | |||
Amount of Increase of Wages | Excel Worksheet Functions | |||
Compound Rate of Return | Excel Worksheet Functions |