Question

You would like to have enough money saved to receive $200,000 per year after retirement so that you and your family can lead a good life for 30 years (from age 65 to 95). You will make your first withdraw of $200,000 at the end of year when you are 65. If you will be 35 years old when you graduate and plan on making savings contributions at the end of your first year out of school, how much would you need to save in your post-MBA retirement fund to achieve this goal? Assume an interest rate is 8%. Complete in Excel using the Goalseek function. Please show steps and formulas.

Answer 1

The amount of withdrwal required in retirement = 200,000 per year

Number of years = 30

Interest rate = 8%

Present value for the 30 years in retirement at the beginning of retirement = PV(rate,nper,pmt) in excel = PV(0.08,30,200000) = 2,251,556.67

Now this is the corpus that you have to save by the age of 65 for retirement living

So now, we treat this as the future value and calculate the annual savings(PMT) required for 30 years from 35 years to 65 years.

So this can be calclated using excel function PMT() as in =PMT(rate,nper,pv,fv) = PMT(0.08,30,0,2251556.67) = 19,875.47

You need to save $2,251,556.67 in your post-MBA retirement fund to achieve this goal

You will need to save annually $ 19,875.47 during the 30 years between 35 and 65 to fund this corpus