Ling Wong who is 50 years old would like to retire at age 60. He is currently earning $160,000 per year in todays $s and would like to have 60% of that income per year during retirement. He expects to live till age 90. He is eligible to receive maximum CPP and OAS starting at age 65 in todays $s. Use 2012 values from table 17.4. He will receive an employer pension of $50,000 per year in nominal $s after he retires at age 60. He would like CPP to start after he turns 60, and OAS to start after he turns 65. For each month earlier than age 65 CPP starts he will lose 0.6%. His marginal tax rate is 46%. He has $290,000 in RRSP now. He plans to deposit $6,000 per year in nominal $s into it each year until retirement. He will deposit tax refund on the RRSP contribution into a Tax Free Savings Account (TFSA). He expects to earn a real rate of return of 3% per year on his portfolio. Inflation rate is expected to be 2% per year. Assume deposits into the RRSP and TFSA are in nominal $s and are made at the beginning of the year. Savings in TFSA provide no income tax deduction, but the principal and income in TFSA are never taxed, even when withdrawn. Therefore, to make the TFSA comparable with other before tax values in this problem, multiply the accumulated TFSA value by 1.25

a. Do Lings savings provide enough to maintain the standard of living he desires if he lives to age 90? If not, how much will be the value of the shortfall in retirement savings at age 60.

b. How much more he contributes to his TFSA each year to reach his goal. Remember for each year maximum contribution allowed is $10,000.

Please Provide an excel spreadsheet to show each step effectively

(Table 17.4)

TABLE TP Maxhnum Gorenhment Pensions 2011 ald 2012 Maximum Monthly Pensions Details Canada Pension Plan [$986.67] $2,500 (lump sum) $1,153.37.[$1,185.50] Old Age Security $57,97 res40.12] 544.98 Guaranteed Income Supplement Single: $329.44 [\$732.36] Married to a pensioner: $487 ist [\$485.61] - For persons retiring at age 65 who contributed at the maximum rate on the MPE for the majority of their career - Actual amount varies according to lifetime profile of contributions, and age of retirement - Lump-sum death benefit to spouse or estate - Disability benefit prior to age 65 - Every Canadian at age 65 , provided they have lived in Canada for a sufficient number of years - Clawback of 15%, starts at net income of and is 100% of OAS for net income of 13 [2012: Clawback starts at $69,562 and is 100% at $112,772] - Reduced by 50% of income excluding OAS, GIS, and Spouse's Allowance - Maximum income limits: - single - \$2 [1324[$16,368] - couple both receiving OAS - \$21,52 [\$21,648] - couple, only one gets OAS - \$30,130 [$39,264] - couple, one with OAS, one with Spouses Allowance - $30,192[$30,336] OAS+ GIS Minimum Income Floor Single: $1,247 [ [$1,272.48] Couple: $2,051.46] - OAS + GIS for a single person/couple with no other income - Retired Canadians aged 65 or over are entitled to this minimum level of income, with some qualifications - Person aged 60 to 64 who is a spouse of a person eligible for OAS and GIS - Maximum is equal to OAS + GIS for someone aged 65 and spouse of a pensioner - Net income cutoff: 3230,336]