Excel Exercise You are planning for your retirement. You estimate that you will

Question

Excel Exercise

You are planning for your retirement. You estimate that you will have $600,000 at the time of your retirement. Your goal is to have a $0 balance at the end of 20 years (at which point you plan on becoming a burden to society and your family). You estimate your expenses at $50,000/Year. You estimate interest on your savings at 5%/year. You estimate inflation at 3%/year. Assume interest and expense payments are made at the beginning of the year.

Objectives:

Determine if $600,000 is sufficient

If not, determine what you will need

Provide all related spreadsheet models

Provide a short recommendation

State any other assumptions you feel you need to make for this exercise

Explanation / Answer

Now you need $50,000 per year for 20 years.

You earn an interest of 5% and with an infation of 3%, your annual rreal return would be only 2%

Now we calculate the present value in excel using the PV() function as in =PV(rate,nper,pmt,fv,type) in MS excel

The rate = 0.05 -0.03 =0.02 or 2%

nper = 20 years = 20

pmt = 50,000

fv =0 and

type = 1 (Since payments are to be made at the beginning of the year)

So the Present value will be = PV(0.02,20,50000,0,1) = $833,923.10

So you require $833,923.10 for retirement and not $600,000 that you determined.

Recommendation: You need to save more for retirement that what you determined since there is a shortfall of $233,923.10

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.