Reference no: EM133074622

Learning Objective:

Activity:

Step 1:  Calculate the future value of the money you will need.How old are you now?

Calculate the time value of money

Using the time value of money (TVM) principles develop a retirement calculator using the following steps. Submit an Excel file showing your calculations and answers to the questions below. 

1. At what age do you want to retire?
2. Assume you will live until the age of 90.
3. In today's dollars, how much will you need each year for retirement?  Choose based upon a realistic amount such as $50,000, $75,000, or even $100,000 per year.
4. Calculate the future value of the answer in part D assuming a 3% inflation rate.  You may also use a published current inflation rate.   For example; if I am 35 years old, I want to retire when I am 60 and want $50,000 a year in today's dollars.

Step 2:  Calculate the present value of the money you will need for retirement.

1. Choose two rates of return you would like to investigate.  Chose a lower conservative number such as the current 3 year CD rate and chose a higher more aggressive number such as rate of stock return.  You can choose whatever you want but justify your answer.
2. Include assumptions for your future pension (give info on how calculated such as SURS website, etc.)  Subtract future pension flow from the Future Value needed in the first year of retirement.
3. Include assumptions for Social Security.  Do NOT just say there will be no Social Security.  Get into the Social Security website and figure what your SS would be at retirement. Subtract future Social Security flow from the Future Value needed in the first year of retirement.
4. Include your spouse in your calculations.  Suggest doing columns in your spreadsheet for both since you will each have different pensions, Social Security, etc.
5. After you have set up the income flows from pensions and Social Security,  you will have the amount of money you still need per year.  If you are VERY fortunate, you will have enough pension and Social Security to cover your annual income/expense needs.
6. But most of you will need to save up a nest egg to use as an income flow over the years you retire.

Step 3:  Calculate the annual amount you need to save up

1. Take the number you calculated in Step 2, Part G  above and divide it by 4%.  4% is the published percentage that most investment advisors agree can be taken out of savings (401K, SEP, IRA, personal savings, etc.) per year.  This is the amount you will need to save to achieve your retirement goals.
2. Use the Present Value of Money to figure how much you need to save yearly using this total amount in Step 3; Part A.  Use the two different rates of return: 3 year CD rate and a stock rate of return.  Consider what stock rate of return you want to use.  Use the number of years you have until retirement.
3. Subtract any money you currently have saved up right now for retirement.  Include 401Ks, IRAs, or any other money earmarked for retirement.
4. This will be a number of $ you need to save yearly to reach your retirement goals.

Questions:

Are you surprised by the number?  Do you think you will be ready for retirement?

write paragraph answer. Show me your use of financial analysis.  Use facts!

• Does this project include Social Security?
• Does it include pensions?
• Include descriptions of techniques you will use to save for retirement such as increasing your savings rates when you receive promotions and raises.
• Include techniques such as increasing savings after children are out of the house, or out of college, etc. write paragraph answer

For this project, set up TVM formulas in Excel  If you need help, then please contact me.

Requirements:

Create own retirement calculator in excel.  Do not use any other retirement calculator on the internet, you will not get any credit.