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 $24,000 per month for 15 years, with the first payment received 30 years and 1 month from now. Second, he would like to purchase a cabin in Rivendell in 10 years at an estimated cost of $355,000. Third, after he passes on at the end of the 15 years of withdrawals, he would like to leave an inheritance of $550,000 to his nephew Frodo. He can afford to save $2,000 per month for the next 10 years. Required: 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 11 through 30?

$1,750.41

$2,559.26

$1,681.77

$2,208.11

$1,716.09

Answer 1

Solution: Step 1: We know that Bilbo Baggins want to purchase a cabin in Rivendell in 10 years at an estimated cost of $355,000. He can save $2,000 per month for next 10 years @ 12% EAR. We will thus want to know the Future Value of this monthly ordinary annuity by using the Future Value function in excel: =FV(Rate,nper,-PMT,,0) here rate= 12%/12=1% monthly; nper = 10*12=120 months and PMT = 2000 thus computing these values in the above formula we will get = $460,077.38 after 10 years. Thus our Second Goal is accomplished by this saving and we have got a surplus of $460,077.38- $355,000 = $105,077.38

Step 2: We now want to know the amount that Bilbo Baggins should have in his retirement fund at the end of Year 30. We know that he wants to have retirement income of $24000 per month for 15 years payment starting at the end of each month. We would be applying Present Value function of excel to know the value of the amount needed at the time of retirement to fund this monthly ordinary annuity. The formula for Present Value in Excel = PV(rate,nper,-pmt,,0) here rate = 10%/12 = 0.83% monthly; nper= 15*12 = 180 -1 month = 179 months and PMT = 24000; computing these values in the formula we will get = $22,27,990.02. This is the value which should be there in his retirement fund at the end of Year 30.

Step 3: The question also mentions that Bilbo wants to leave an inheritance of $ 550,000 for nephew after 15 years from his retirement. We would like to know the current value of this inheritance too. Present Value function of the excel will come to our rescue again. The formula for Present Value in Excel = PV(rate,nper,,-FV,0) here rate =10%; nper =15 years; and FV = 550000. By computing these values in the formula we will be getting $1,31,665.63 this is the present value of the inheritance goal of Bilbo.

Step 4: We will be adding the Present Value of ordinary monthly annuity and Present Value of the inheritance amount = $22,27,990.02 + $1,31,665.63= $23,59,655.65, this is the amount required in the retirement account of Bilbo. We know from Step 1 that Goal 2 has been accomplished and after that a surplus amount is left i.e. $105,077.38. Thus we need $2359655.65 - $105077.38 = $2254578.27 to fulfill the retirement goals of Bilbo. From Year 11 through 30 i.e. 20 years or 20*12=240 months each month he should save an amount which we will calculate using the PMT function in Excel. The formula is =PMT(rate,nper,,-FV,0) here rate = 12% /12 = 1%; nper = 20*12 = 240 months and FV= 2254578.27 by computing these values in the formula we would be getting $2,279.07 per month savings through Year 11 to Year 30.

Note: The exact answer from the options is not been deduced but the process is rationally correct don't know why the difference.

An additional observation also computed that the surplus of $105077.38 deduced in Step 1 will not sit idle from Year 11 to Year 30 and will earn 12% EAR. Thus by using the Future Value function in excel = FV(rate,nper,,-PV,0) here rate= 12%, nper =20, PV = 105077.38 by computing these values in the formula we would be getting $10,13,607.19 at Year 30 end. This will leave the balance i.e. $2359655.65 - $1013607.19 = $1346048.45 to be funded by the monthly investment from Year 11 to Year 30. We will calculate using the PMT function in Excel. The formula is =PMT(rate,nper,,-FV,0) here rate = 12% /12 = 1%; nper = 20*12 = 240 months and FV= 1346048.45 by computing these values in the formula we would be getting $1,360.67 per month savings through Year 11 to Year 30.

Note: Again this thought process also doesn't give us the answers mentioned in the option. The workings according to rationale is correct.