Question

Please if you could do each problem, and show steps for excel! Thank you!

You will graduate in a few years and start working and it’s never too early to start planning for your retirement and other financial events. Let’s fast forward to the beginning of your career. Here’s some assumptions to help you get started. Your starting annual salary will be $60,000. You plan to work for 43 years before retiring. You expect your salary to grow at an annual average rate of 4% after year 1 on the job. When you retire you want a 22-year retirement annuity that begins 43 years from today with an equal annual payment equal to 80% of your final working year salary. Assume the first retirement annuity payment would occur immediately upon retirement 43 years from today. You realize your purchasing power will decrease over time during retirement. Assume any retirement fund savings will earn an 7.8% compounded annually before and after retirement Answer the following questions to help finalize your retirement planning.

1. What is your expected final year working salary?

2. What is your desired annual retirement income?

3. How much will you need at retirement 43 years after the beginning of your career to fund your desired retirement annuity?

4. How much will you have to deposit annually in the form of an equal annual end of the year deposit over 43 years to fund your desired retirement annuity?

5. Re-work questions 3 and 4 assuming you would like to leave your grandkids $200,000 when you die 22 years after retirement.

6. Now, forget the grandkids and imagine you get $25,000 in graduation gifts from your family that you deposit into your retirement savings account at the beginning of your work career. We-work the previous question (#5) with this new initial deposit. How large does your annual deposit over your working career need to be in addition to the initial $25,000?

7. Looking at your answers in the last two questions, the annual amount, while doable, might be a bit of a financial stretch for you early in your career. Let’s redo the scenario in the last question where you have the $25,000 initial deposit but will deposit $5000 at the end of year for the first 10 years and then another equal annual amount at the end of each year for the remaining working years. How large does this second annual deposit need to be to meet your retirement savings goal from question #3?

Answer 1

Basic data Given:

a) starting annual salary = $ 60,000

b) Working years before retirement = 43 years (i.e.N)

c) expect salary to grow at an annual average rate of 4% after year 1   (i.e. g)

Answer:

1. What is your expected final year working salary?

$60,000 will be expected to grow @ 4 % per annum fro the Next 43 years hence, expected final year salary will be

= First salary x (1+g)^N

⁼ $60,000 x (1.04)⁴³

= $60,000 x 5.40

= $ 3,24,000

2. What is your desired annual retirement income?

Desired annual Retirement income = 80% of your final working year salary

= 80% of $ 3,24,000

= $ 2,59,200

3. How much will you need at retirement 43 years after the beginning of your career to fund your desired retirement annuity?

in oreder to find out the amount needed in account at the time of retirement we have to find the present value(at the beggining of 43rd year ) of annual payments.

Year	Annual Payment	PVIFA @ 7.8%	Present value of Payments
1st Payment (at the beggining of 43rd year)	$2,59,200	1.00	$2,59,200
Remaining 21 payments (44 th year to 65 th year)	$2,59,200	10.1726*	$26,36,726
			$28,95,926

Note: PVIFA = [1−(1+r)^-n​ ] / r

= [ 1-(1+7.8%)^-21] / 7.8%

=10.1726

4. How much will you have to deposit annually in the form of an equal annual end of the year deposit over 43 years to fund your desired retirement annuity?

Future value of an Annuity = P [ (1+r)ⁿ-1] / r

Where,Future value of an Annuity = $28,95,926

P = Yearly Annuity Payment

r = rate of return = 7.8%

n = no of years = 43 years

Future value of an Annuity = P [ (1+r)ⁿ-1] / r

$28,95,926 = P [ (1+7.8%)⁴³-1] /7.8%

$28,95,926 = P *311.15

Yearly Annuity Payment (P) = $28,95,926 / 311.15

= $ 9,307 per annum

5. Re-work questions 3 and 4 assuming you would like to leave your grandkids $200,000 when you die 22 years after retirement.

Re-work questions 3

Present value (at the time of retirement) of $200,000 required at the end of 22 years after retirement

= $2,00,000 x 1/(1+r)ⁿ

= $2,00,000 x 1/(1.078)²²

= $2,00,000 x 1/5.2193

= $38,319.27

Hence we have to increase the account balance by $38,319.27 at the end of retirement so that after 22 years there will be a balance of $2,00,000.

therefore , Total amount required @ end of 43 years = $28,95,926 + $38,319.27 = $29,34,245.27

Re-work questions 4 : How much will you have to deposit annually in the form of an equal annual end of the year deposit over 43 years to fund your desired retirement annuity?

Future value of an Annuity = P [ (1+r)ⁿ-1] / r

Where,Future value of an Annuity = $29,34,245.27

P = Yearly Annuity Payment

r = rate of return = 7.8%

n = no of years = 43 years

Future value of an Annuity = P [ (1+r)ⁿ-1] / r

$29,34,245.27= P [ (1+7.8%)⁴³-1] /7.8%

$29,34,245.27 = P *311.15

Yearly Annuity Payment (P) =$29,34,245.27 / 311.15

= $ 9,430.32 per annum