Question

At the age of 30, Morgan decided to create a financial plan to retire in 25 years. He had the following retirement objectives:

■ Home purchase objective: Own a home worth at least $1,200,000 with no mortgage.

■ Vacation objective: Have an amount of $45,000 for a European tour.

■ Monthly allowance objective: Receive a month-beginning allowance of $2000 for 20 years after retirement.

He created the following financial plans to achieve the above retirement objectives. Answer the questions related to each plan.

Plan to achieve his home purchase objective

Assuming that the value of a property in a Toronto suburb would double over 25 years, Morgan would purchase a house worth $600,000 by making a down-payment of $30,000 and obtaining a mortgage for the balance amount from a local bank at an interest rate of 4% compounded semi-annually for 25 years.

a. If the interest rate is constant over the 25-year period, calculate the month-end payments for the mortgage. What would be his total investment in the house over the term?

Plan to achieve his vacation objective

Deposit $150 at the end of every month for ten years into a savings account that earns 3% compounded monthly. At the end of the ten years, transfer the accumulated money into an investment fund that earns 6% compounded quarterly, and allow the money to grow in this fund until retirement.

b. How long (in years and months) would it take to accumulate the required amount of $45,000 to pay for his vacation?

c. To ensure that the amount accumulates to only $45,000 at the time of retirement, by how much should he change his monthly deposit?

Plan to achieve his monthly allowance objective

He will save $500 at the beginning of every month until retirement in an RRSP that has an interest rate of 5.4% compounded monthly.  

d. What would be the accumulated value of the RRSP at the time of retirement?

e. For Morgan to be able to withdraw $2000 from the RRSP at the beginning of every month during his planned 20-year retirement period, what does the nominal interest rate, compounded monthly, need to change to, assuming the interest rate during the 25-year savings period remains unchanged at 5.4% compounded monthly?

Answer 1

Part (a)

Payment frequency is monthly. Hence, a period here is one month.

Hence, wee need interest rate per month. Let's say it'r, then following equation should be satisfied.

(1 + r)¹² = (1 + 4% / 2)² = 1.0404

Hence, Rate = r = 1.0404^1/12 - 1 = 0.3306%

Nper = nos. of months in 25 years = 12 x 25 = 300

Loan amount = 600,000 - 30,000 = $ 570,000

Hence, monthly payment = PMT (Rate, Period, PV, FV) = PMT (0.3306%, 300, - 570000, 0) = $  2,998.36

his total investment in the house over the term = Down payment + PMT x n = 30,000 + 2,998.36 x 300 = $  929,506.95

Part (b)

Annuity = $ 150 per month; i = interest rate per month = 3% / 12 = 0.25%; Time period = n = number of months in 10 years = 12 x 10 = 120

Hence, FV at the end of 10 years = FV₁₀ = A / i x [(1 + i)ⁿ - 1] = 150 / 0.25% x [(1 + 0.25%)¹²⁰ - 1] =  20,961.21

From 10th year onward till year 25, the interest rate, r = 6% compounded quarterly = 6% / 4 = 1.5%

Let n* be the number of periods starting from end of year 10, that will be required to get a FV of $ 45,000.

Hence, n* = NPER (Rate, PMT, PV, FV) = NPER (1.5%, 0, -20961.21, 45000) =  51.31

Hence, the total time required = 10 years + 51.31 quarters = 10 years + 51.31 x 3 months = 10 years + 153.94 months = 10 years + (12 years + 10 months) = 22 years and 10 months.

Part (c)

FV at retirement = 45,000

Hence, FV at the end of year 10 = FV at retirement / (1 + r)^{(4 x 15)} = 45,000 / (1 + 1.5%)⁶⁰ =  18,418.32

We need to find the monthly deposit that will amount to 18,418.32 in 10 years.

Hence, monthly deposit = - PMT(Rate, Period, PV, FV) = - PMT(0.25%, 120, 0, 18418.32) = $  131.80

Part (d)

Accumulated amount = FV of period beginning annuity = FV (Rate, period, PMT, PV, 1) = FV (5.4%/12, 25 x 12, -500, 0, 1) = $  317,618.58

Part (e)

The amount calculated above should be the PV of period beginning annuity of $ 2,000

Hence, desired interest rate per month = RATE (Period, PMT, PV, FV) = RATE (12 x 20, -2000, 317618.58, 0) = 0.3704%

Hence, nominal interest rate compounded monthly = 12 x interest rate per month = 12 x 0.3704% = 4.45%