Documents
User Generated
Resources
Learning Center

# RESP

VIEWS: 6 PAGES: 7

• pg 1
```									The problem:                                                                       See: http://www.yorku.ca/retire/resp-cesg.htm
You have 2 or 3 or 4 or 5 children of various ages.
You need to know how much you can contribute to an RESP, for each child (with the contraints imposed by the RESP regulations).
This is given in columns A through G on the RESP sheet as well as a chart which shows your contributions as well
as CESG contributions and the amount required to meet the cost of Post Secondary Schooling (PSS) cost.
If you have several children and can afford a prescribed annual amount, then how do you allocate to each child?
It is assumed that when the eldest child attends PSS, whatever had been invested in his/her RESP is then allocated to the
NEXT eldest child … and when that child attends PSS, investment in his/her RESP is allocated to the NEXT oldest etc. etc.
So, what is the OPTIMAL prescription for allocating limited funds to several children?
When you are old and grey, you want to be able to say to all your grown children:
"I paid the SAME percentage of your education … for each and every one of you."
THAT is optimal (in my opinion :^)
In columns I through R, the calculations are performed (by running through a bunch of allocations).
The increments for this are prescribed by you: the smaller the increment, the more time it takes !!
To add an extra child Copy & Paste like so:
(To delete a child just delete the column.)
Also, you must guarantee that the sum of the percentages add to 100%, like so
where you adjust the percentage for the last (youngest) child.
u.ca/retire/resp-cesg.htm

e RESP regulations).

oldest etc. etc.
Registered Education Savings Plan (RESP), Canadian Education Savings Grant (CESG) and Post Secondary Schooling (PSS)
Current Cost of one year of PSS: Cost = \$20,000                                          Cost for           4         years is
Annual Increase in this Cost: Inc =   2.0%
Maximum Annual RESP investment allowed: RESP_Max = \$4,000                                               For Child_Number 2           see chart below
Maximum Annual RESP with matching CESG: RESP_Cmax = \$2,000                                                 PSS, at a current cost of \$82,432 and increasing at 2.0%
Maximum Total RESP investment allowed: Maximum_Total_RESP = \$42,000                                          per year will cost \$106,635 in 13 years.
Max Annual CESG contribution as % of your contribution: CESG_pct = 20.0%                                          If you invest \$4000 each year, with CESG contributions
Maximum Annual CESG contribution to RESP: CESG_Max =        \$400                                          of \$400, the RESP portfolio will reach \$102,146 after
Maximum Total CESG contribution to RESP: Maximum_Total_CESG =    \$7,200                                         13 years. To reach the PSS cost of \$106,635 requires
Expected Return on Investments: Return =     8.0%                                          extra monthly contributions of \$193
Pick Child_Number 1, 2, etc.                    2
Child number                      1              2              3              4
Age of Child when RESP begins                             8              5             3               0
Age at which PSS begins                     18             18             18             18
Years until PSS begins                    10             13             15             18
Future_Cost of PSS after Years                   \$100,484       \$106,635       \$110,943       \$117,733
Annual_Investment required at Annual Return = Ret                    \$6,423         \$4,593         \$3,783         \$2,911
Total_Investment required (over all Years)                  \$64,226         \$59,714       \$56,750        \$52,395
Your_Contribution per year                    \$4,000         \$4,000         \$3,383         \$2,911
CESG_contribution                     \$400           \$400           \$400            \$0
Total: your contribution + CESG contribution                  \$4,400         \$4,400         \$3,783         \$2,911
Required Extra annual contribution                   \$2,023          \$193            \$0             \$0
Total_RESP portfolio, after Years                  \$68,840        \$102,146       \$110,943       \$117,733
Shortfall                \$31,644         \$4,489           \$0             \$0
Compare                    \$4,000         \$4,000         \$3,383
Range          10
RESP Total
Your Investments                CESG Contributions
0        \$4,000          \$400         \$4,400          \$4,000
RESP Total                      Req'd Investment                      1        \$8,320          \$832         \$9,152          \$4,000
\$120,000
2       \$12,986         \$1,299        \$14,284         \$4,000
3       \$18,024         \$1,802        \$19,827         \$4,000
\$100,000                                                                                         \$23,466         \$2,347        \$25,813         \$4,000
4
5       \$29,344         \$2,934        \$32,278         \$4,000
\$80,000                                                                                 6       \$35,691         \$3,569        \$39,260         \$4,000
7       \$42,547         \$4,255        \$46,801         \$4,000
\$60,000                                                                                 8       \$49,950         \$4,995        \$54,945         \$4,000
9       \$57,946         \$5,795        \$63,741         \$4,000
\$40,000                                                                                10       \$66,582         \$6,658        \$73,240         \$4,000
11       \$75,909         \$7,591        \$83,499         \$4,000

\$20,000                                                                                12       \$85,981         \$8,598        \$94,579         \$4,000
13       \$92,860         \$9,286       \$102,146           \$0
14                                                       \$0
\$0
15                                                       \$0
0   1      2   3     4     5    6   7      8   9   10      11   12   13
16                                                       \$0
17                                                       \$0
18                                                       \$0
Secondary Schooling (PSS)                           Children should be ordered from eldest (Child #1) to youngest.
\$82,432 today                   When Child #1 attends PSS, contributions to his RESP stop and are are invested in the RESP for Child #2.
When Child #2 attends PSS, contributions to his RESP stop and are are invested in the RESP for Child #3
see chart below                                 Click on Find Optimal to run through a bunch of Percentages (in row 15, below) to find the allocations
ost of \$82,432 and increasing at 2.0%                 which make the Percentage of PSS Cost the same for each child. (The error is shown in cell O8.)
106,635 in 13 years.
each year, with CESG contributions                P.S. Choose the percentage Increment in cell L9                                               error =
portfolio will reach \$102,146 after                                                                  Increment =       2.0%                best so far =
the PSS cost of \$106,635 requires
butions of \$193                                                           Maximum Annual Family Contribution         \$2,000    Children = 4

5                                                 Child number       1             2             3           4          5
Years until PSS begins     10            13            15          18
Percentage of \$2000 43.0%               25.0%         20.0%      12.0%
Your Annual Investment       \$860           \$500          \$400       \$240
CESG annual contribution       \$172           \$100           \$80        \$48
Total annual RESP Investment \$1,032               \$600          \$480       \$288
Increased annual RESP Investment*         \$0           \$1,632       \$2,112      \$2,400
PSS Cost \$100,484          \$106,635     \$110,943 \$117,733
Final RESP value \$14,950           \$16,247       \$16,428    \$17,642
Percentage of PSS Cost 14.9%                15.2%         14.8%      15.0%
Percentage of \$2000 devoted to child 43.0%             68.0%         88.0%     100.0%
* when each child is 18, NEXT oldest gets his/her investments thereby increasing the Total annual RESP investment for th

Req'd
CESG Contributions Investment
\$400         \$4,593
\$400         \$9,554
\$400        \$14,912
\$400        \$20,698
\$400        \$26,947
\$400        \$33,697
\$400        \$40,986
\$400        \$48,858
\$400        \$57,360
\$400        \$66,542
\$400        \$76,459
\$400        \$87,169
\$400        \$98,736
\$0         \$106,635
\$0
\$0
\$0
\$0
\$0
vested in the RESP for Child #2.
vested in the RESP for Child #3 etc.
below) to find the allocations
error is shown in cell O8.)

0.16%
0.16%

totals

100%      << must add to 100%
\$2,000    << must add to \$2000

<< try to make these percentages equal !!!
<< after older child is 18
Total annual RESP investment for that NEXT child

```
To top