Documents
Resources
Learning Center
Upload
Plans & pricing Sign in
Sign Out

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           Your Investments
                      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