Question

Suppose you are a financial planner at Tangerine Bank, A client of yours has recently become a parent. The family wants to open up an RESP account for their child to save for her university expenses, They plan to send their daughter to study Bachelor of Commerce at UOIT, at the age of 18. (solve using excel formulas)

1. How much should they save before her school starts

2. For that, how much should the parents save in the account each month to fund their child’s university expenses, They are very risk averse and would like to save in a GIC-type account (use the long term interest rates as their average investment rate)

3.How much of the saving is the parents’ contribution, the Government’s contribution, and the Investment Income.

4. If the rates changes, say +/- 1%, how much their monthly payments should change? Hint: FV of payments up until year 17 so that from years 18-21 they can pay $8000/year for tuition

RESP INFO:

•A Registered Education Savings Plan (RESP) is a government-registered plan that helps you save for a child’s post-secondary education.

•The money that you invest in an RESP grows tax-deferred, and the federal government helps contribute to your savings along the way in the form of education grants.

•Tax is applied to the investment income and government grants received when withdrawn from the RESP, not on the contributions you made using your own funds. These amounts are taxed in the hands of the student, and this usually means little or no tax will be paid, because students typically fall into the lowest tax bracket.

•The Canada Education Savings Grant (CESG) matches 20% of annual contributions, up to $500 per year

•The matching contributions can continue until the lifetime limit of $7,200 per child has been reached

Answer 1

Solution :   Longterm interest rate = 1.5% annually   
      
1)      
If they need to pay $8000 every year as college fees for four years. Then money they save should be the present value of four $8000 payments.      
      
Required amount =PV(1.5%,4,-8000,0,1)   $31,297.60  
      
2) Amount to be saved at the beigning of each month for 17 years (204 months)= PMT(1.5/12%,17*12,0,-31297.6,1)   $134.62  
      
3) Total annual contributions = 134.62*12 =   $1,615.39  
Total amount = Payment contribution(x) + (.2 * x) parent contributions matched by government =   $1,615.39  
1.2x =   $1,615.39  
So, x = 279.6/1.2 =   $1,346.16  
Total Parents Payment contribution = 1346.16*17 =   $22,884.74  
Total Government contributions = (279.6-233) *17 =   $4,576.95  
Investment income= (31297.60-3961-792.2) =   $3,835.91  
      
4) if rate increases by 1%      
Amount to be saved at the beigning of each month for 17 years (204 months)= PMT(2.5/12%,17*12,0,-31297.6,1)   $123.02  
Amount monthly contibuted by parents = 123.62/1.2 =   $102.52  
      
if rate decreases by 1%      
Amount to be saved at the beigning of each month for 17 years (204 months)= PMT(2.5/12%,17*12,0,-31297.6,1)   $146.96  
Amount monthly contibuted by parents = 123.62/1.2 =   $122.47  

      
      

TZ 13 Solution : Longterm interest rate = 1.5% annually 14 151) If they need to pay $8000 every year as college fees for four years. Then money they save should be the present value of four $8000 payments. 17 Required amount =PV(1.5%,4,-8000,0,1) $31,297.60 19 2) Amount to be saved at the beigning of each month for 17 years (204 months)= PMT(1.5/12%,17*12,0,-31297.6,1) $134.62 $1,615.39 3) Total annual contributions = 134.62*12 = Total amount = Payment contribution(x) + (.2 * x) parent contributions matched by government = 1.2x = $1,615.39 $1,615.39 25 So, x = 279.6/1.2 = $1,346.16 26 Total Parents Payment contribution = 1346.16*17 = $22,884.74 27 Total Government contributions = (279.6-233) *17 = $4,576.95 Investment income= (31297.60-3961-792.2) = $3,835.91 29 4) if rate increases by 1% 31 Amount to be saved at the beigning of each month for 17 years (204 months)= PMT(2.5/12%,17*12,0,-31297.6,1) $123.02 32 Amount monthly contibuted by parents = 123.62/1.2 = $102.52 33 34 if rate decreases by 1% 35 Amount to be saved at the beigning of each month for 17 years (204 months)= PMT(2.5/12%, 17*12,0,-31297.6,1) $146.96 Amount monthly contibuted by parents = 123.62/1.2 = $122.47 37 38