Question

Since the birth of her daughter, 18 years ago, Liz has deposited $100 at the beginning of every month into a Registered Education Savings Plan (RESP). The interest rate on the plan was 3.80% compounded monthly for the first 9 years and 3.80% compounded monthly for the next 9 years. a. What would be the accumulated value of the RESP at the end of 9 years? Round to the nearest cent

Answer 1

a) Monthly interest rate, RATE 0.3167% [Monthly interest rate = 3.8% / 12 = 0.32% (j12 implies it is compounded monthly)] # of Monthly deposits, NPER 108 [9 years x 12 --> As there will be 12 monthly deposits per year] Monthly deposits, PMT $100 Initial deposit made today, PV $0 Monthly deposit type, TYPE 1 = 0 if deposited at the end of each period; 1 if deposited at the beginning of each period Balance in account at the end of first 9 years $12,893.33 (Ref: NOTE] NOTE: Balance in account at the end of first 9 years is calculated using EXCEL FUNCTION FV(rate,nper,pmt,pv,type) where rate=0.3167%; nper=108; pmt=-100; pv=0; type=1; Here, value for pmt is negative as it denote cash outflows; type=1 as deposits are made at the beginning of each month itself. A 1 Monthly interest rate, RATE 2 # of Monthly deposits, NPER 3 Monthly deposits, PMT 4 Initial deposit made today, PV 5 Monthly deposit type, TYPE 6 Balance in account at the end of first 9 years 7 B с D E -3.8%/12 [Monthly interest rate = 3.8% / 12 = 0.32% (j12 implies it is compounded monthly)] =9*12 [9 years x 12 --> As there will be 12 monthly deposits per year] $100 $0 1 = 0 if deposited at the end of each period; 1 if deposited at the beginning of each period =FV(B1,B2,-B3,-B4,B5) [Ref: NOTE]

b) Monthly interest rate, RATE # of Monthly deposits, NPER Monthly deposits, PMT Initial deposit made today, PV Monthly deposit type, TYPE Balance in account at the end of 18 years 0.3167% [Monthly interest rate = 3.8% / 12 = 0.32% (j12 implies it is compounded monthly)] 108 [9 years x 12 --> As there will be 12 monthly deposits per year] $100 $12,893.33 [As calculated in part-(a)] 1 = 0 if deposited at the end of each period; 1 if deposited at the beginning of each period $31,034.23 [Ref: NOTE] NOTE: or = $31,034.24 [lf rounded off input is used for PV()] Balance in account at the end of 18 years is calculated using EXCEL FUNCTION FV(rate,nper,pmt,pv,type) where rate=0.3167%; nper=108; pmt=-100; pv=12893.33; type=1; Here, value for pmt is negative as it denote cash outflows; type=1 as deposits are made at the beginning of each month itself. А B C D E 1 Monthly interest rate, RATE -3.8%/12 [Monthly interest rate = 3.8% / 12 = 0.32% (j12 implies it is compounded monthly)] 2 # of Monthly deposits, NPER =9*12 [9 years x 12 --> As there will be 12 monthly deposits per year] 3 Monthly deposits, PMT $100 4 Initial deposit made today, PV $12,893.33 5 Monthly deposit type, TYPE 1 = 0 if deposited at the end of each period; 1 if deposited at the beginning of each period 6 Balance in account at the end of 18 years =FV(B1,B2,-B3,-B4,B5) [Ref: NOTE]

Balance in account at the end of 18 years Less: Amounts deposited Total interest earned over 18 years $31,034.23 [As calculated in part-(b)] $21,600.00 ($100 x 18 years x 12 months) $9,434.23 ($31034.23 - $21600.00] or = $9,434.24 [lf rounded off input is used for PV() in part-(b)]