Using a Future Value Cash Flow Table to Analyze a Safe Plan

Analyze the following scenario by using Excel to calculate a Future Value cash flow

table to provide answers to the questions posed. In the PDF containing your

answers, be sure to paste in copies of the relevant Excel tables you used to

determine your answers.

Lets assume you want to generate the required $83,159.79 per year in retirement

by switching over your retirementsavingsinto a very safe 4% interest yield account

and that you only withdraw the interest to live on (i.e. you never deplete the

balance of the account and can thus be secure no matter how long you live and

also can leave it to your children, etc.). You would need to amass a final account

value by the time you retire and transition it to the 4% yielding safe account of

$83,159.79/0.04= $2,078,994.75 to generate that much interest to live on for the

rest of your life. While you are working, lets assume you use a moderate to

aggressive risk investment account that can generate 7% interest per year on your

savings.

A. If you startsaving the same amount each and every year when you start your

job by saving each year and making your first deposit on your 23rd birthday

(i.e. you save up during the year from age 22 to age 23 and place your deposit

in the bank at the beginning of each yearstarting at age 23), how much must

you save each year to provide the needed income for retirement? Again,

assume you work the year you are 65 and retire right at the end of that year,

so you do not make a yearly deposit into your retirement account at the end

of that year (i.e. the start of your age 66 year) and instead use any money

you saved the year your are turning 65 to make your retirement transition.

So your last retirement deposit will come at the beginning of your age 65

year from money you save the previous year and will only have one year to

accrue interest before you have to convert your entire account over to the safe 4% yield interest account.

Please explain the answer briefly with all values mentioned in excel.

Future Value Your answer in your Excel table should look something Perde Ben of a Frame for Each Cash Flow! Aconditionat 23 24 25 25 27 2 25 30 31 22 33 34 35 30 37 3 30 30 31 42 43 44 45 45 47 41 50 51 32 53 54 55 50 SB 59 61 52 63 6 65 $2,028,995.25 8. Do the same analysis as in Part A, but complete it using a Present Value Analysis instead. Using this analysis, how much should you save each wear? Be sure to show your cash flow table in your