SOLVE ON EXCEL; SHOW FORMULAS

You are X (you will input your age) years old, college grad with a full time job in your career, thinking about saving for retirement.

You go to a financial planning site to get some ideas.

The site asks you the following questions:

1. At what age do you think you would like to retire?
2. How much money do you feel you would need per year to live on during the first year of retirement? (consider housing, medical expenses, travel, etc. many financial experts suggest 80% to 100 % of your income when you retire)
3. How is your health, what age would you look to have retirement benefits for?
4. How comfortable are you with risk? Would you consider yourself a risky investor you are fine with the ups and downs of the stock market? (use an investment rate from 10% to 14%) Moderate risk would like some growth from the stock market but would like safer investments such as bonds as well? (use an investment rate between 5% and 9%) Or low risk investor mostly bonds, CDs money markets? (use an investment rate between 2.5% and 5%)

Fill in answers:

Age now: 25

Age at retirement: 45 (difference between age at retirement and age now must be greater than 15 years)

Age want to receive retirement income until (suggest between 80 and 100): 90

Investment rate: 14%

Inflation rate: 2% (average rate over last 50 years = 3.5%, choose a rate between 1% and 6%)

Current years salary: 20000

Amount you have already saved for retirement: 1000 (must be greater than $100)

Amount you want to leave to heirs: 1000000 (must be greater than $1000)

SOLVE ON EXCEL AND SHOW FORMULAS PLEASE

1. Calculate the amount of money you will need the first year of retirement: To do this you will calculation the number of years until retirement: Age at retirement age now. Next assume you will progress in your career and make 3% more per year then the rate of inflation you chose: so now grow your current years salary by inflation plus 3% for the number of years until retirement. We will assume here that you want to continue with 100% of that amount, so that is the amount you need for the first year of retirement.
2. Calculation the amount you will need the day you retire if you want the amount you calculated in part 1 paid out annually from the day you retire until the last date you want to receive retirement income and you want to leave nothing to your heirs. Assume the rate of return you will receive during this period is 2% less than the investment rate you chose above (people generally invest a little bit more conservatively during retirement).
3. Now do the same calculation as part 2, but include the amount you decided to leave to your heirs above.
4. Using your answer to part 3, find the equal annual amount you would need to save every year from the age you input as now until the date you retire to reach the amount in answer 3, use the investment rate you chose above.
5. Now lets assume you are 10 years into saving for your retirement, so 10 years older than what you chose as your age now and assume you earned 2% less than expected on your investments and you ended up putting away only half as much as you assumed above. Now calculate the equal annual amount you would need to save every year to still save to the amount in part 3, assuming you can now earn the assumed investment rate.