Assignment 1: RESP Objective: Suppose you are a financial planner at Tangerine Bank. A client of yours has recently become a parent. The family wants to save for their child's university expenses, periodically, so that they can afford the child's education expenses. Your advice for them is to open an RESP account (see below for more details) and take advantage of the tax and other benefits that such programs offer. The purpose of this assignment is to help the parents figure out how much they should contribute each month to the RESP account to meet their investment goal. Registered Education Savings Plan (RESP) . 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 input Data: The parents plan to send their daughter to study Bachelor of Commerce at McMaster University, at the age of 18. [Find the current tuition fees in each year for such program by online search, e.g. on McMaster University's website] Assume McMaster Tuition increases with the current Canadian inflation rate for the investment period. (Find the current inflation rate by online search, e.g. on Bank of Canada's website] The parents are very risk averse and would like to save in a GIC-type account. Assume this rate will stay constant in the next 18 years [Find the current long- term GIC rates by online search, e.g. on Tangerine Bank's website.] Model Output: In MS-Excel, create a model, where you input the above data points and the model gives the following information as an output: How much do the parents need to have in their investment account, by child's aged 18, to be able to fund their child's university expenses? For that, how much should it be saved in the investing account each month? How much of the saving is the parents' contribution, the Government's contribution, and the Investment Income? If the GIC rate changes, say +/- 1%, how much their monthly payments should change? i.e. calculate the saving numbers for two alternative scenarios, optimistic case, where GIC rate increases by +1%, and pessimistic case where the GIC rate decreases by - 1%
