Question

Bilbo Baggins wants to save money to meet three objectives. First, he would like to be able to retire 30 years from now with retirement income of $28,000 per month for 20 years, with the first payment received 30 years and 1 month from now. Second, he would like to purchase a cabin in Rivendell in 15 years at an estimated cost of $717,000. Third, after he passes on at the end of the 20 years of withdrawals, he would like to leave an inheritance of $650,000 to his nephew Frodo. He can afford to save $1,900 per month for the next 15 years.

  

If he can earn a 12 percent EAR before he retires and a 10 percent EAR after he retires, how much will he have to save each month in Years 16 through 30?

Answer 1

=(28000/(pr/12)*(1-1/(1+pr/12)^(12*rp))+650000/(1+pr/12)^(12*rp)+717000*(1+br/12)^(12*15)-1900/(br/12)*((1+br/12)^(12*15)-1)*(1+br/12)^(12*15))*(br/12)/((1+br/12)^(12*15)-1)

pr is post retirement EAR
br is before retirement EAR
rp is retirement period

=(28000/((1+10%)^(1/12)-1)*(1-1/(1+(1+10%)^(1/12)-1)^(12*20))+650000/(1+(1+10%)^(1/12)-1)^(12*20)+717000*(1+(1+12%)^(1/12)-1)^(12*15)-1900/((1+12%)^(1/12)-1)*((1+(1+12%)^(1/12)-1)^(12*15)-1)*(1+(1+12%)^(1/12)-1)^(12*15))*((1+12%)^(1/12)-1)/((1+(1+12%)^(1/12)-1)^(12*15)-1)

OR

=-PMT((1+12%)^(1/12)-1,12*15,0,PV((1+10%)^(1/12)-1,12*20,-28000,-650000)+FV((1+12%)^(1/12)-1,12*30,0,PV((1+12%)^(1/12)-1,12*15,-1900,717000)))
=4470.22