Web.acd.ccac.edu



CCAC SOUTH: SET-105 - Technical Computing Fall 2019

Assignment #2 Retirement Calculations Revised: 8/21/2019

Student Name: Date:

THIS ASSIGNMENT IS STILL UNDER REVISION.

This assignment will determine the retirement parameters for a young engineering student (named Samantha).

1. Samantha is 25 years old and wants to retire early when she is 60 years old and plans to live until the age of 90. Her current annual Income is $65,000. Since she has a job with a good firm, she expects annual raises of 3% over the duration of her career. Her employer will match (100%) the first 3% of her retirement contributions.

2. Since Samantha is still young, she estimates that her 401K retirement funds will grow an average of 10% each year until her retirement if she puts 90% in stock funds and 10% in bond funds. Samantha assumes the average rate of inflation will be 2.5%.

3. Use MS-Excel to calculate and record all of your work for this assignment by recreating Tables 1, 2, and 3 Use links between the tables so that each specific piece of data (cell) originates only in one place (cell).

4. Samantha’s current annual budget is outlined below in Table 1. Calculate the total monthly and the total annual budget represented in Table 1 and then add it to Table 2.

5. Samantha has decided she needs to achieve $60,000 income (in today’s dollars) from the returns of her retirement savings when she is 60 years old. I.e. $60,000 in today’s dollars will be worth much less when she retires in 2050 at age 60. Use a search engine to find out how much $60k in today’s dollars will be worth in 2050 (reduced amount) and how much will be needed in 35 years to match the purchasing power of $65,000 (required amount) – enter it into Table#2. Assume inflation will be 2.5%. A good website to try would be: calculators/inflationjan08.htm

6. How much money will Samantha have in her retirement account (after 35 years) if she saves 9% of her annual salary ($65,000), she gets 3% raises each year, her employer matches the first 3% of her contributions, and her money will earn 8% each year?

A common assumption is that a retiree can spend 4% of the amount in his/her retirement account each year in order to account for inflation and to make sure that he/she will not run out of funds. Calculate 4% of the total retirement account after 35 years and enter it into Table#2.

Try this: calcxml.c35om/do/pay07

7. A common assumption is that your annual income in retirement could be lower (80% of your current income) because you will be spending less. This site will calculate how much you should save each year in order to achieve 80% of your current income ($65,000):

How does this amount (to save) compare with the calculations in the previous step?

8. Assume Samantha was born on June 15, 1994, she is not 25, her current annual salary is $65,000, and she plans to retire in 35 years on June 15, 2054. How much annual Social Security (in today’s dollars) will Samantha get in 35 years once she reaches age 60? How will this affect her retirement plan?

How much annual Social Security (in inflated/future dollars) will Samantha get in 35 years once she reaches age 60?

9. If Samantha starts her career at $65,000 and achieves a 3% raise each year, her salary after 35 years should be about $177,000. How much annual Social Security (in today’s dollars) will Samantha get once she reaches age 65 if her highest salary before retirement is $177,000? How will this affect her retirement plan?

10. If Samantha starts her career at $65,000 and achieves a 3% raise each year, her salary after 35 years should be about $177,000. How much annual Social Security (in today’s dollars) will Samantha get once she reaches age 65 if her highest salary before retirement is $177,000? Assume she was born on 6/15/1994. How will this affect her retirement plan?

11. Use the previous calculations and data to complete Table#3. Table #3 should pull the data from Tables #1 and 2 whenever possible (rather than hard-coding the values).

12. Let’s try a different approach. Use the following calculator to calculate Samantha’s retirement plan assuming the following:

Her age today: 25

Her age at retirement: 60

Expected Life expectancy: 90

Desired annual income: $60,000

Inflation Rate: 2.5%

Current monthly savings: $700 (10 of her salary plus 3% employer contribution)

Stop contributions/savings at age: 60

Annual return on investments: 10%

Tax Rate during retirement: 25%

Post-retirement income (SS): $1700 from age 60 to age 90

Provide a printout as part of this assignment. Did you try this with your own data?

13. Show any graphs that you encountered that help explain her retirement results. Embed them into your Excel file (insert picture from file).

14. Turn in the electronic copy of your Excel file along with a printout. Save the file as: Assignment2 – your name.xls

Optional Credit: Vanguard () is a low fee mutual fund company that offers 401K retirement plans. Which fund should Samantha select if she wants 80% in stocks (equities) and 20% in bonds? (Note: There are many acceptable answers)

Percent risk that she will run out of money?

Table 1 – Budget

|Expense |Amount |Frequency |

|Medical Insurance |500 |Monthly |

|Medical Out-of-Pocket/Deductible |2000 |Annual |

|Dental |480 |Annual |

|Prescriptions |25 |Monthly |

|Gas |74 |Monthly |

|Electric |100 |Monthly |

|Water |65 |Monthly |

|Sewage |35 |Monthly |

|Cable & Internet |120 |Monthly |

|Cell Phone |50 |Monthly |

|Auto#1 Purchase & Maintenance |267 |Monthly |

| | | |

|Gasoline |120 |Monthly |

|Auto Insurance |540 |Annual |

|Driver License |35 |Annual |

|Food |300 |Monthly |

| | | |

|Housing Maintenance |400 |Annual |

|County Taxes |700 |Annual |

|School taxes |3500 |Annual |

|Local Township Taxes (West Mifflin) |400 |Annual |

|Home Insurance |400 |Annual |

|  |  | |

|Planned Discretionary Expenses: |1000 |Annual |

| | | |

|Monthly Basic Necessities: | | |

|Annual Basic Necessities: | | |

| | | |

|TOTAL ANNUAL EXPENSES: | |

Table 2 – Calculations and Data

| | |

|Item#4: Total Monthly Expenses: | |

|Item#4: Total Annual Expenses: | |

|Item#5: In 2015, an annual $60,000 in today’s dollars will be worth | |

|(reduced amount): | |

|Item#5: In 2015, this is the required amount needed to match the purchasing| |

|power of $60,000 in today’s dollars: | |

|Item#6a: How much will be in Samantha’s retirement account after 35 years: | |

|Item#6b: Amount to save each year until retirement: | |

|Item#6c: Annual amount to spend each year in retirement assuming a 4% | |

|withdraw rate: | |

|Item#7a: How much to save each year in order to achieve 80% of your current| |

|salary: | |

|Item#7b: How does the amount in the previous line (Item#7) compare to the | |

|results from Item#6? | |

|Item#8a: How much annual Social Security (today’s dollars) should Samantha | |

|expect to receive? | |

|Item#8b: How much annual Social Security (inflated/future dollars) should | |

|Samantha expect to receive? | |

| | |

Table 3 – Conclusions

| |FUTURE VERSUS TODAYS DOLLARS ??? |Monthly |Annual |

|1 |How much will Samantha get from Social Security when when she retires? | | |

|2 |How much will be in Samantha’s retirement account when she retires? | | |

|3 |Total Income at Retirement (Line 1 + 2): | | |

|4 |Spending Budget at retirement: | | |

|5 |Excess or Deficit (Line 3 – 4): | | |

|6 |Will Samantha be able to retire comfortably as desired? | |

|7 |Your Suggestions and/or Comments to Samantha: | |

CCAC SOUTH: SET-105 - Technical Computing Fall 2019

Assignment #2 Retirement Calculations Revised: 8/21/2019

Student Name:

|Objective |Status of Objectives |Comments |

|Budget Table entered | | |

|Use of linked cells to achieve less hard-coded values | | |

|Table 2 completed – expense summary | | |

|Value of $60K in the year 2030 | | |

|Savings required to retire | | |

|Table 3, Section 1 – Basic calculations | | |

|Table 3, Section 2 – Adjust for Social Security | | |

|Table 3, Section 2 – Adjust for employer calculations | | |

|Graphs Shown on Paper? (qty=2) | | |

|Graphs embedded into Excel (qty=2) | | |

|Amount Social Security identified | | |

|Stock Funds identified | | |

|Electronic copy on S-Drive | | |

|Paper copy submitted | | |

|Summary Page provided Y/N? | | |

|Second Worksheet used? | | |

|Linked cells from another worksheet? | | |

|Colors or Lines used? | | |

|Decimals set to 0 or 2 | | |

|Formatting: Centering, Bold, etc. | | |

|Meaningful filename? | | |

|Additional Excel commands used? | | |

|Optionals: | | |

| | | |

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download