Question

Bilbo Baggins wants to save money to meet three objectives. First, he would like to be able to retire 30 years Income of $28,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 $320,000. Third, after he passes on at the end of the 15 years of withdrawals, he would like to leave an Inheritance of $600,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 10 percent EAR before he retires and a 8 percent EAR after he retires, how much will he have to save each month in years 11 through 30? $3,665.46 $3,738.77 $4,114.20 O $4,301.22 O $3,592.15

Answer 1

First, we calculate the nominal annual interest rates, given the EAR.

EAR = (1 + (r / 12))¹² - 1 , where r = nominal annual interest rate.

8% EAR

EAR = (1 + (r / 12))¹² - 1

8% = (1 + (r / 12))¹² - 1

r = ((1 + 0.08)^1/12 - 1) * 12

r = 9.57%

10% EAR

EAR = (1 + (r / 12))¹² - 1

10% = (1 + (r / 12))¹² - 1

r = ((1 + 0.10)^1/12 - 1) * 12

r = 7.72%

Next, we calculate the amount of money required 30 years from now to fund the two goals of retirement income, and leaving inheritance to Frodo.

The amount of money required 30 years from now is calculated using PV function in Excel :

rate = 7.72% / 12 (nominal rate of return during retirement. Monthly rate = annual rate / 12)

nper = 15 * 12 (total number of monthly payments during retirement = number of years in retirement * 12 = 15 * 12)

pmt = -28000 (Monthly payment required during retirement. This is entered with a negative sign because it is a payment from the retirement fund)

fv = 600000 (Inheritance amount required to be left at the end of retirement. This is entered with a negative sign because it is a payment from the retirement fund)

PV is calculated to be $3,169,117.44

| Al : х v f =PV (D3/12,15*12,-28000-600000) 1 A B с $3,169,117,44 | | EAR 10.00% 8.00% D Nominal Rate 9.57% 1.72%

Now, we calculate the amount in the retirement fund 10 years from now, before the cabin in Rivendell is purchased. The amount in the retirement fund 10 years from now is calculated using FV function in Excel :

rate = 9.57%/12 (nominal rate of return before retirement. Monthly rate = annual rate / 12)

nper = 10 * 12 (total number of monthly savings upto 10 years = 10 * 12)

pmt = -2000 (Monthly saving amount. This is entered with a negative sign because it is a payment into the retirement fund)

FV is calculated to be $399,727.71

| A2 X B - A $3,169,117.44 $399,727.71 1 2 for EFV(D2/12, 10*12,-2000) E F Nominal Rate 9.57% 7.72% EAR 10.00% 8.00%

Out of this accumulated amount of $399,727.71, an amount of $320,000 is used to purchase the cabin in Rivendell. Amount remaining after cabin purchase = $399,727.71 - $320,000 = $79,727.71.

Lastly, we calculate the monthly saving required during Years 11 to 30 to accumulate the required amount in 30 years from now. The monthly saving required during Years 11 to 30 is calculated using PMT function in Excel :

rate = 9.57%/12 (nominal rate of return before retirement. Monthly rate = annual rate / 12)

nper = 20 * 12 (total number of monthly savings from Year 11 to Year 30 = 20 * 12)

pv = -79727.71 (Amount remaining after cabin purchase. This is entered with a negative sign because it is like a payment into the retirement fund)

fv = 3169117.44 (The amount of money required 30 years from now)

PMT is calculated to be $3,665.46

X foc =PMT(D2/12,20*12,-A4,A1) А В D 1 2 $3,169,117.44 $399,727.71 320,000.00 $79,727.71 ($3,665.46) EAR 10.00% 8.00% Nominal Rate 9.57% 7.72% 3

A 1 =PV(D3/12,15*12,-28000,-600000) 2 =FV(D2/12, 10*12,-2000) 3 320000 4 =A2-A3 5 =PMT(D2/12,20*12,-A4,A1) EAR 0.1 0.08 Nominal Rate =((1+C2)^(1/12)-1)*12 =((1+03)^(1/12)-1)*12

The monthly saving required during Years 11 to 30 is $3,665.46