Question

Problem 7 (12 marks) You are currently 25 years old and have decided to start paying into your registered retirement plan. You will start to deposit $125 at the end of each month starting with this month until you are 60 years old and you retire. If interest rates are expected to be 5% compounded monthly from the time you are 25 until you are 40 and then are 6% compounded monthly until you retire at 60. a. How much will be in your retirement account when you retire? (8 marks) b. According to your family history, you expect to live until you are 85. If interest rates during your retirement are 6.5% compounded monthly, how much will you be able to withdraw at the end of each month during your retirement? (4 marks)

Answer 1

a]

Amount in account at age 40 is calculated using FV function in Excel :

rate = 5%/12 (converting annual rate into monthly rate)

nper = 180 (total number of years from age 25 to 40 = 15 years. Total number of monthly deposits = number of years * 12 = 15 * 12 = 180)

pmt = -125 (Monthly deposit. This is entered with a negative sign because it is a payment into the account)

FV is calculated to be $33,411.12

A1 X Fax =FV(5%/12,180,-125) 1 $33,411.12

Amount in account at age 60 is calculated using FV function in Excel :

rate = 6%/12 (converting annual rate into monthly rate)

nper = 240 (total number of years from age 40 to 60 = 20 years. Total number of monthly deposits = number of years * 12 = 20 * 12 = 240)

pmt = -125 (Monthly deposit. This is entered with a negative sign because it is a payment into the account)

pv = -33411.12 (money in account at age 40. This is entered with a negative sign because it is like a payment into the account)

FV is calculated to be $168,352.74

А2 : x x f C =FV(6%/12,240,-125,-A1) D E F B А 1 $33,411.12 2 |$168,352.74

Money in account when you retire is $168,352.74

b]

Monthly withdrawal is calculated using PMT function in Excel :

rate = 6.5%/12 (converting annual rate into monthly rate)

nper = 300 (total number of years from age 60 to 85 = 25 years. Total number of monthly withdrawals = number of years * 12 = 25 * 12 = 300)

pv = 168352.74 (money in account when you retire)

PMT is calculated to be $1,136.73

A3 E fc C =PMT(6.5%/12,300,A2) D E B F A 2 $168,352.74 3 ($1,136.73)!