Please do the assignment on Excel SPREETSHEET. I have attached the sample of how the assignment should be finished. It is a sample only. You should finish the same calculation for Lisa and Timothy, Retirement Planning Case Study

Part 1 of the assignment should be submitted in excel.

TheCase:Timothy and Lisa Falcone

PersonalInformation- Asat1 January 2024

Name	Timothy Falcone	Lisa Falcone
Date of Birth	January 1st, 1990	January 1st, 1988
Smoking Status	Non - Smoker	Non- Smoker
Health	In good health	In good health
Pension	Defined Benefit	Defined Contribution

Employment

Timothy is a school teacher at St. Thomas Aquinas Secondary School in London. Tim started teaching September 1^st, 2016. Tim started teaching as soon as he graduated teachers' college. His starting salary was $71,000. His current salary is $89,940. Tim's salary in indexed to inflation every year on January 1^st. Tim sees himself continuing to teach high school until his retirement.

Lisa works for London Life in an office administration role. Lisa has been employed by London Life ever since she graduated from the University of Waterloo in 2012. Lisa is a member of their defined contribution pension. Her current salary is $64,000 per year.

Interests

Timothy and Lisa have always been active. Timothy likes to golf, play volleyball and watch football, while Lisa likes to play soccer and go jogging. Recently they have decided they are going to try pickleball and have joined a league through the City of London.

Major Assets

Timothy and Lisa jointly own a home in London, Ontario that was purchased for $550,000. The house is currently valued at $775,000. They have no intentions of moving and have a mortgage remaining of $110,000, that they expect to pay off over the next 10 years.

Timothy and Lisa saved very little in retirement, as they believe the pensions they have through work will be enough to meet their retirement needs. See Appendix for asset values.

Pension Plans

Timothy is a member of his employer's mandatory defined benefit pension plan. He joined the pension when he was hired at the school board. The plan is based on the average of the best 5 years of employment and will pay Timothy a 1.325% credit per year of service up to the YMPE and 2% on the amount above the YMPE. The pension is indexed to inflation. The pension has a maximum of 35 years of service. The pension has a qualification factor of 85 for early retirement. The plan has a .25% penalty per month for early retirement. The pension plan provides survivor benefits to Lisa in the event ofTimothy's death. Lisa will be entitled to a spousal pension worth 60% of Timothy's pension at the time of his death.

Lisa is a member of a Defined Contribution Pension Plan. She contributes 2% of her salary to the pension plan and her employer contributes 2%. She has been a member of the pension plan since Jan 1, 2014. The plan is invested with a balanced mandate. Timothy and Lisa both have a moderate risk tolerance.

Expenditures

Please see appendix for a list of expenditures that Timothy and Lisa have provided.

Future

Timothy and Lisa have had some discussions with family members who have told them they need to complete a retirement plan. They have decided they want to retire on Jan 1, 2045.

See the following pages for appendices.

Appendix 1 - Timothy and Lisa's expenses

Timothy and Lisa havenothistoricallytracked theirexpenseswell.Theyhaveprovideddetailson specific itemsbelowbutdo not have an accurate breakdown oftheircurrentlifestyle.They haveprovidedyou withtheir gross salariesabove. However, on an annual basis they have a surplus of $10,000 per year.

Item	Amount	Frequency
Mortgage	$1,500	Monthly
Property Tax	$400	Monthly
Entertainment	$325	Monthly
Timothy RRSP	$325	Monthly
Lisa RRSP	$250	Monthly
Groceries	$1,100	Monthly
Heat/Hydro	$300	Monthly
Insurance	$400	Monthly
Cable/Internet	$180	Monthly

Appendix 2 - Timothy and Lisa's Assets

House $775,000

Timothy RRSP - $27,250

Lisa RRSP - $28,000

Lisa DC Pension - $62,250

Cars - $33,000

RRSP Carry Forward Room 2023 (amount to be carried forward to 2024)

Timothy - $40,000

Lisa - $32,000 Appendix 3 - Historical Incomes

Timothy's	Lisa's	Timothy's	Lisa's
Age	Age	Income	Income
	18	$0	$5,000
	19	$0	$5,500
18	20	$0	$5,750
19	21	$15,000	$10,000
20	22	$8,000	$18,000
21	23	$8,500	$37,500
22	24	$7,800	$39,000
23	25	$8,250	$41,000
24	26	$10,000	$42,350
25	27	$14,000	$44,000
26	28	$41,000	$45,000
27	29	$73,500	$47,750
28	30	$76,000	$49,000
29	31	$79,000	$52,000
30	32	$81,000	$54,890
31	33	$84,250	$56,000
32	34	$86,000	$60,000
33	35	$87,500	$62,500

Appendix 4 -Retirement Expenditures (in today's dollars)

Tim and Lisa expect that their annual retirement expenditures will be $80,000 after tax in today's dollars. This includes 4 months that they plan to live in Florida during the winter.

ASSIGNMENT INSTRUCTIONS

Your role is as that of a retirement planner. Your objective is to help your clients organize themselves in order to do some financial planning. In the real world, you would do so with clear step by step communication. Details on their income, expenses, assets and liabilities are provided in appendices. Clearly state what assumptions you need to make to complete your assignment.

Part 1 (5 marks out of 25)

1. EstimatetheCPPbenefitsthatTimothyandLisawillreceivein retirement,(calculatetheratioof earnings to YMPE) and the OAS benefits they will receive in retirement, in future dollars. Showyour calculations and list any assumptions you are making. Timothy and Lisa plan to take CPP and OAS as soon as they can. Hint:youwillneedtomakeanassumptiononfuture salary increases.
2. DeterminetheRPPannualpensionincomethatTimothywillreceivefromhispensionwhenhe retires. Hint:remembertobasethebenefitsonthefuturesalary.

1. EstimatethevalueofLisa'sDCPplanwhensheretires.Besuretostateyourassumptions.

Section	Out of:
OAS Estimate	8
CPP Estimate	16
DB pension Estimate	12
DC pension Estimate	12
TOTAL	48

SAMPLE OF THE ASSIGNMENT

A B C D E F G H Year Joe' Age Joe's YMPE Income Ratio 1992 18 $5,000 $30,500 0.16393 drop If W N 1993 19 $8,000 $32,200 0.24845 drop 1994 20 $10,000 $33,400 0.29940 drop 1995 21 $17,000 $34,400 0.49419 drop 1996 22 $18,000 $34,900 0.51576 drop 1997 23 $19,000 $35,400 0.53672 1998 24 $20,000 $35,800 0.55866 10 1999 25 $25,000 $36,900 0.67751 11 2000 26 $25,000 $37,400 0.66845 12 2001 27 $25,000 $37,600 0.66489 13 2002 28 $25,000 $38,300 0.65274 14 2003 29 $25,000 $39,100 0.63939 15 2004 30 $25,000 $39,900 0.62657 2005 31 $25,000 $40,500 0.61728 2006 32 $29,000 $41,100 0.70560 18 2007 33 $29,000 $42,100 0.68884 19 2008 34 $29,000 $43,700 0.66362 20 2009 35 $29,000 $44,900 0.64588 2010 36 $29,000 $46,300 0.62635 22 2011 37 $29,000 $47,200 0.61441 23 2012 38 $29,000 $48,300 0.60041 24 2013 39 $30,000 $50,100 0.59880 25 2014 40 $30,000 $51,100 0.58708 26 2015 41 $30,000 $52,500 0.57143 FP Canada Assumption Guidelines 2016 42 $30,000 $53,600 0.55970 28 2017 43 $30,000 $54,900 0.54645 Assumptions 29 2018 44 $30,000 $55,300 0.54250 YMPE 2% 30 2019 45 $30,000 $55,900 0.53667 drop .99 Salary 2% 31 2020 46 $30,000 $57,400 0.52265 drop Drop Years 47 times 17% = 7.99 47 $31,000 $58,700 0.52811 drop Average Ratio 0.58730 32 2021 33 2022 48 $34,000 $61,600 0.55195 Average YMPE $84,498 34 2023 49 $35,000 $64,900 0.53929 Max CPP 35 2024 50 $36,500 $66,600 0.54805 Joe's CPP HUHHHHHH# 36 2025 51 $37,230 $67,932 0.54805 2026 52 $37,975 $69,291 0.54805 38 2027 53 $38,734 $70,676 0.54805 2028 54 $39,509 $72,090 0.54805 40 2029 55 $40,299 $73,532 0.54805 2030 56 $41,105 $75,002 0.54805 2031 57 $41,927 $76,502 0.54805 2032 58 $42,766 $78,033 0.54805 2033 59 $43,621 $79,593 0.54805 45 2034 60 $44,493 $81,185 0.54805 2035 61 $45,383 $82,809 0.54805 2036 62 $46,291 $84,465 0.54805 48 2037 63 $47,217 $86,154 0.54805 49 2038 64 $48,161 $87,877 0.54805 50 51 26.21439 52 3.30379 total of dropped ratios 53 22.91060 54 39.01000 years 55 0.58730 56