Boy, this is all so confusing," said Jason as he stared at the papers on his desk.

$"$

If only I had

taken the advice of my finance instructor, I would not be in such a predicament today." Jason

Welch, aged

$2$

$7$

$,$

$$graduated five years ago with a degree in food marketing and is currently

employed as a middle

$-$

level manager for a fairly successful grocery chain. His current annual

salary of $

$7$

$5$

$,$

$0$

$0$

$0$

$$has increased at an average rate of

$5$

$\%$

$$per year and is projected to increase

at least at that rate for the foreseeable future.

The firm has a voluntary retirement savings program in place, whereby employees are allowed

to contribute up to

$1$

$1$

$\%$

$$of their gross annual salary

$($

up to a maximum of $

$1$

$2$

$,$

$0$

$0$

$0$

$$per year

$)$

and the company matches every dollar that the employee contributes. Unfortunately, like many

other young people who start out in their first "real" job, Jason has not yet taken advantage of

the retirement savings program. He opted instead to buy a fancy car, rent an expensive

apartment, and consume most of his income.

However, with wedding plans on the horizon, Jason has finally come to the realization that he

had better start putting away some money for the future. His fianc

$$

$,$

$$Jillian$,$ of course, had a lot

to do with giving him this reality check. Jillian reminded Jason that besides retirement, there

were various other large expenses that would be forthcoming and that it would be wise for him

to design a comprehensive savings plan, keeping in mind the various cost estimates and

timelines involved.

Questions:

$1$

$.$

$$What was Jason's starting salary? How much could he have contributed to the voluntary

savings plan in his first year of employment?

$2$

$.$

$$Had Jason taken advantage of the company's voluntary retirement plan up to the

maximum every year for the past five years, how much money would he currently have

accumulated in his retirement account, assuming a nominal rate of return of

$7$

$\%$

$?$

$$How

much more would his investment value have been worth had he opted for a higher risk

alternative

$($

i

$.$

e

$.$

$,$

$1$

$0$

$0$

$\%$

$$in common stocks

$)$

$,$

$$which was expected to yield an average

compound rate of return of

$1$

$2$

$\%$

$($

A

$.$

P

$.$

R

$)$

$?$

$3$

$.$

$$If Jason starts his retirement savings plan from January of next year by contributing the

maximum allowable amount into the firm's voluntary retirement savings program and

continuing each year up until his retirement, how much money will he have

accumulated for retirement, assuming he retires at age

$6$

$5$

$?$

$$Assume that the rate of

return on the account is

$7$

$\%$

$$per year, compounded monthly and that the maximum

allowable contribution does not change. Calculate the value of his retirement portfolio

under, both, a monthly and an annual savings plan.

$4$

$.$

$$How much would Jason have to save each month, starting from the end of the next

month, in order to accumulate enough money for his wedding expenses, assuming that

his investment fund is expected to yield a rate of return of

$7$

$\%$

$$per year?

FINA

$3$

$7$

$2$

$4$

$$Excel Project

$-$

$3$

$$Due Date:

$4$

$/$

$1$

$9$

$/$

$2$

$0$

$2$

$4$

$5$

$.$

$$If Jason starts saving immediately for the

$2$

$0$

$\%$

$$down payment on his house, how much

additional money will he have to save each month? Assume an investment rate of return

of

$7$

$\%$

$$per year.

$6$

$.$

$$If Jason wants to have a million dollars

$($

in terms of today's dollars

$)$

$$when he retires at

age

$6$

$5$

$,$

$$how much should he save in equal monthly deposits from the end of the next

month? Ignore the cost of the wedding and the down payment on the house. Assume

his savings earn a rate of

$7$

$\%$

$$per year

$($

A

$.$

P

$.$

R

$.$

$)$

$.$

$7$

$.$

$$If Jason saves up the million dollars

$($

in terms of today

$$

s dollars

$)$

$$by the time of his

retirement at age

$6$

$5$

$,$

$$how much can he withdraw each month

$($

beginning one month

after his retirement

$)$

$$in equal dollar amounts, if he figures he will live to the age of

$8$

$5$

$?$

Assume that his investment fund yields a nominal rate of return of

$7$

$\%$

$$per year.

$8$

$.$

$$After preparing a detailed budget, Jason estimates that the maximum he will be able to

save for retirement is $

$5$

$0$

$0$

$$per month, for the first five years. After that he is confident

that he will be able to increase the monthly saving to $

$7$

$5$

$0$

$$per month until retirement.

If the account provides a nominal annual return of

$7$

$\%$

$,$

$$how much money will Jason be

able to withdraw per month during his retirement phase?

$9$

$.$

$$What is the lesson to be learned from this case? Explain using suitable calculations.

JUST NEED EXCEL FORMULAS AND ANSWERS FOR QUESTIONS

$5$

$-$

$9$