Please help in answering these in excel spreadsheet! i will love you for life
1. STARTING YOUR RETIREMENT PLAN AT 50 Alan is 50, Joanne is 51. They have a large house worth $800,000 in Toronto and a cottage worth $200,000 in Muskoka. Mortgage payments on these prop- erties are $42,000 p.a. and they will be paid off in 10 years. They have no other debts, although they just finished paying off a car loan. They each own a recent model car, and they replace their cars every three or four years because they do not want to be seen driving older models. Alan's net income or take-home income was $100,000 last year, after taxes, CPP, EI premiums, medical insurance, etc. Joanne takes home $40,000 p.a. after the same set of deductions and contributions to her employer pension plan. They have two children whom they are currently helping through school, at a cost of $25,000 p.a. This cost will continue for another five years, after which the children are on their own. They are both carrying substantial life insurance and disability insurance, all of the cost of which is deducted from their gross pay in arriving at the net income reported above. They have $10,000 in a chequing account and a substantial line of credit at the bank if they need it. Alan has $30,000 in an oil and gas mutual fund. He has no pension plan. Joanne has $20,000 invested in GICs in an RRSP. She expects an indexed pension plan of $25,000 (in today's dollars) if she retires at age 61. They would each qualify for only 80% of maximum Canada age 65. Pension, and that would be further reduced if they start receiving it before Alan has been earning gross income over $100,000 p.a. for five years, and he expects the $100,000 net income of last year to be sustainable until he retires. He started saving money only in the last two years. Last year he deposited $15,000 in the mutual fund (this amount is included in the $30,000 balance). Joanne has been contributing $1,000-$2,000 p.a. to her RRSP for 10 years. They would like to retire in 10 years. Advise them in their retirement planning. 1. Retirement Goals: How much money do they need? Calculate required income (Starting point not given, estimate it from present income and expenditures) o What level of retirement income is appropriate? How long do they need the income? (Based on Alan's life expectancy of 91 and Joanne 96) 2. How much have they got? o Determine OAS full amount each at 67 Determine CPP, assume they qualify for 80% Determine Joanne's pension Outline any assumptions (income splitting, investment portfolio, RRSP's, TFSA's) 3. How much additional saving is required over the next 10 years? Assuming that they save $17,000 for 10 years in RRSP's (Alan $15k. Joanna $2K) How does this affect their taxes? Calculate Alan's marginal tax rate Calculate Joanne's marginal tax rate Determine tax credits (federal and provincial) 4. Recommendations o Feasibility, Reasonability, Pros and Cons & Why 5. Overall written report o Introduction, data collection, Recommendations, Appearance (use of charts/tables) and Clarity You are expected to undertake calculations to provide to this couple an amount (if any) of additional savings over the next 10 years prior to retirement needed to support (in inflation-adjusted dollars) their disposable income at a level equivalent to 80% what they are currently experiencing These calculations will be undertaken on an EXCEL spreadsheet. The exercise will be based on: Calculating the net present value (at the beginning of their retirement period) of net after-tax revenue and income during their retirement The second calculation will be to compare this to the future value of all their accumulated savings available at the beginning of their retirement period. In the event that the future value of the savings is not sufficient to support the present value of their net retirement expenditures, you will calculate what additional annual savings needed over the next 10 years to balance these two calculations In the event that the future value of the savings exceeds the present value of their net expenditures, you will calculate the reduction in annual savings that they would be able to afford over the next 10 years to balance these two calculations Assume in your analysis that the annual inflation rate over the entire pre and post retirement period is 2% and that investments they have or will make will yield a 4% return. CPP and OAS revenue, when received at age 65, will be in inflated dollars with the claw-back (if any) based on their retirement income at that time. Be sure in the EXCEL spreadsheet(s) that you submit that all links are included so I can follow how you carried out your calculations
