Label Each Question:

1. Create a spreadsheet to determine retirement savings using the following criteria. Annual Compensation: $60,000 (monthly $5,000) 401K (4038) Contribution Percent 6% Company match 50% up to 6% (e. g. 3% 50% x 6%) Tax Rate 25% Annual Investment Return Rate 8% In your spreadsheet, each of these elds should be changeable. For example, if change the base pay to $48,000, your spreadsheet will calculate new retirement amounts. The intent of this exercise is also for you to learn how to do what if analysis for any of the key inputs on retirement savings. Please include your expected 401K (403B) balance at 10 year intervals as follows: 10 years, 20 years, 30 years, 40 years. Most of you will be 32, 42, 52 and 62 (ready for retirement) for these intervals. Create your le to allow for What if analysis for the following variables: 1. annual salary 2. investment rate 3. contribution percent 4. company match percent In your spreadsheet, highlight these elds in yellow indicating they can be modied to arrive at new retirement account balances. illlutll bUlllpCdelUll; LDUU,UUU (monthly $5,000) 401K (4038) Contribution Percent 6% Company match 50% up to 6% (e. g. 3% 50% x 6%) Tax Rate 25% Annual Investment Return Rate 8% In your spreadsheet, each of these elds should be changeable. For example, if change the base pay to $48,000, your spreadsheet will calculate new retirement amounts. The intent of this exercise is also for you to learn how to do what if analysis for any of the key inputs on retirement savings. Please include your expected 401K (403B) balance at 10 year intervals as follows: 10 years, 20 years, 30 years, 40 years. Most of you will be 32, 42, 52 and 62 (ready for retirement) for these intervals. Create your le to allow for what if analysis for the following variables: 1. annual salary 2. investment rate 3. contribution percent 4. company match percent In your spreadsheet, highlight these fields in yellow indicating they can be modied to arrive at new retirement account balances. 2. Since 401K (403B) contributions are taken out pretax, I would like for you to calculate the difference in monthly take home pay under two scenarios: a. Without 401K (403B) contributions b. With 401K (403B) contributions (hint: this number will be lower, but will be offset by your savings net of taxes) The intent of this calculation is to determine how much your take home (paycheck) will be reduced if you enroll in a 401K (403B). 401K Before What is a 401K/4OSB? - Account that allows for you to save pre-tax (income tax) for retirement - Money deducted from check pre-tax (range 3 to 6%) Company/organization matches funds (e.g. 50% up to 6%) - Invest in accounts you control Vesting- time you need to stay at company/organization * Your money.... - Different than pension- guaranteed income post retirement Impact of 401K on Paycheck Without With 401K 401K Deduction Deduction Annual Salary 5 40,000 5 40,000 Monthly Salary 5 3,333 5 3333 401K 5.00% 167 Taxable Income $ 3,333 5 3,166 Taxes 25.00% 833 792 $ 42Tax Savings Other Deductions @ @ _Take Home Pay 5 1.292 S 1, 225 S 125Monthly Cost Total Annualized Savings/Contribution Take Home Pay 51.2\"! 5112; 5 125Monthly Cost J Total Annualized Savings/Contribution Monthly Contribution S 167* Company Match 50% 84 Monthly Savings Combined S 251 Months 12 .5. Annual Savings 1.0.12 m Taxes Your Contribution $ 2,004 5 1,503 501 Time Value of Money Annuity - Annuity- constant payments- $3,012 - Future value of annuity - =FV(Annual Interst Rate/12,Years*12,Monthly Savings Combined)*-1 Example: \"Annual Salary: $40,000 \"401K Contribution: 5% ~/ Company match: 50% up to 5% ~/ Years: 10 \"Rate of return: 8% annual V'Amount after 10 years: $45,919 401K(403B) Spreadsheet Learning Objectives: 1. Familiarize students with the 401K(403B) retirement plan 2. Encourage students to enroll in 401K (403B) 3. Determine monthly cost of enrolling in 401K (403B) retirement plan 4. Apply future value concepts to determine amount of retirement savings 5. Discuss the intent of the 401K (403B)retirement account 6. Exposure to spreadsheet configuration for what if analysis