Question

Your older brother turned 35 today, and he is planning to save $20,000 per year for retirement, with the first deposit to be made one year from today. He will invest in a mutual fund that's expected to provide a return of 7.5% per year. He plans to retire 30 years from today, when he turns 65, and he expects to live for 25 years after retirement, to age 90. Under these assumptions, how much can he spend each year after he retires? His first withdrawal will be made at the end of his first retirement year.

Answer 1

Here , Given data for calculation are :

Annual Investment (PMT) = $ 20,000

Annual return (r) = 7.5%

Total installment (nper) = 30-1= 29 . ( as the first deposit to be made one year from today at beginning )

Future value of deposited fund , by use of FV(rate,nper,pmt,pv,type) function in excel

FV (7.5%,29,-20000,0,1) = $ 2,047,988.05

**spend each year after he retires, by use of PMT(rate,nper,pv,fv,type) function in excel

Annual withdrawal at end of year = PMT(7.5%,25,-2,047,988.05,0,0) = $ 183,726.38