Label Each Questions:

401K(403B) Spreadsheet

The purpose is to quantify the potential total retirement savings at post-graduation intervals for students.

1. Create spreadsheet to determine retirement savings using the following criteria.

Annual Compensation: $60,000 (monthly $5,000)

401K (403B) Contribution Percent 6%

Company match50% up to 6% (e.g. 3%- 50% x 6%)

Tax Rate 25%

Annual Investment Return Rate 8%

In spreadsheet, each of these fields 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 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 file to allow for what if analysis for the following variables:

1. annual salary

2. investment rate

3. contribution percent

4. company match percent

In spreadsheet, highlight these fields in yellow indicating they can be modified to arrive at new retirement account balances.

2. Since 401K (403B) contributions are taken out pretax, calculate the difference in monthly take home pay under two scenarios:a. Without 401K (403B) contributionsb. With 401K (403B) contributions (hint: this number will be lower, but will be offset by savings net of taxes)

The intent of this calculation is to determine how much take home (paycheck) will be reduced if enroll in a 401K (403B). What is a 401K/403B?

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		$ 40,000	$ 40,000
Monthly Salary		$ 3,333	$ 3,333
401K	5.00%		167
Taxable Income		$ 3,333	$ 3,166
Taxes	25.00%	833	792	$ 42	Tax Savings
Other Deductions		600	600
Take Home Pay		$ 1,900	$ 1,775	$ 125	Monthly Cost

Total Annualized Savings/Contribution

Monthly Contribution			$ 167	*
Company Match	50%		84
Monthly Savings Combined		$ 251
Months			12
Annual Savings			$3,012
				Take home	Taxes
Your Contribution			$ 2,004	$ 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

Amount after 10 years: $45,919