I have a project with a group portion and an individual portion. My question is how on earth do I start this, determine the figures in question, etc?!?! Here are the details of the project:
Your younger brother, Tom, just received information from his company on his benefits. He tells you that he is not interested in reading the retirement information because he has another 30 years of working before he can even consider retiring. You take a look at the information and see that his company offers a great 401(k) plan that matches contributions up to 6% and has been offering an average return of 12% annually. At this time Tom is making $30,000 per year and receives an average raise of 3% per year.
Individually: Prepare a spreadsheet for Tom that shows how much money he could expect to have in his retirement account in 30 years given the following scenarios:
He takes advantage of the 401(k) plan offered by his employer by putting 6% in the plan for the next 30 years. (Assume that the plan will continue to earn 12% annually.)
He waits 10 years to begin putting 6% of his salary in the plan.
Each of you are to prepare your own spreadsheet for this task. The spreadsheet should clearly indicate each of the two scenarios required in the assignment. For the first scenario assume Tom puts his 6% into the fund at the END of each year with one ANNUAL payment. Also assume that Tom gets his annual salary increase each year AFTER the first year (so no raise until 1/1/09 for example). Monthly payments to the fund are not acceptable for this assignment. Use the same basic assumptions for scenario two, only he waits ten years to begin. You will need to use time value of money calculations for these deposits, and this would be a regular annuity when you go to use the tables in the text. You are not required to use the tables (you could use a financial calculator for example). Make sure your work is clearly labeled and explained.
To start this project and determine the figures in question, follow these steps:
1. Open a spreadsheet program like Microsoft Excel or Google Sheets.
2. Create a new spreadsheet and label it with a clear title, such as "Tom's Retirement Savings Projections".
3. Set up the structure of the spreadsheet by creating column headers for the following categories: Year, Salary, Contribution, Employer Match, Total Contribution, Investment Return, Ending Balance.
4. Start filling in the spreadsheet with the necessary data. Based on the information given in the project, Tom's initial salary is $30,000 per year and he receives a 3% raise annually. You can calculate each year's salary by multiplying the previous year's salary by 1.03 (1 + 3%). Fill in the Salary column accordingly.
5. Determine the contribution made by Tom each year. Since he is taking advantage of the 401(k) plan by putting 6% of his salary into the plan, you can calculate the yearly contribution by multiplying the Salary by 0.06 (6%). Fill in the Contribution column accordingly.
6. Calculate the employer match contribution. According to the project details, Tom's employer matches his contributions up to 6%. So, for each year, calculate the employer match by multiplying the Contribution by 0.06. Fill in the Employer Match column accordingly.
7. Calculate the total contribution made by adding Tom's contribution and the employer match. Fill in the Total Contribution column accordingly.
8. Calculate the investment return for each year. According to the project, the retirement plan has been offering an average return of 12% annually. You can calculate the investment return by multiplying the previous year's Ending Balance by 1.12 (1 + 12%). Fill in the Investment Return column accordingly.
9. Calculate the Ending Balance for each year. Add the total contribution and investment return for each year to get the ending balance. Fill in the Ending Balance column accordingly.
10. Repeat steps 4-9 for the second scenario where Tom waits 10 years before making contributions to the retirement plan.
11. Format the spreadsheet to make it visually appealing and easy to read. You can add borders, apply color to cells, and format numbers as currency if desired.
12. Add explanations and labels to make your work clear and understandable. This can be done through comments, text boxes, or simply adding comments in the spreadsheet cells.
By following these steps, you should be able to prepare a spreadsheet that shows how much money Tom could expect to have in his retirement account in 30 years for both scenarios. Remember to use the time value of money calculations for the deposits and make sure your work is clearly labeled and explained.