II. Retirement Planning (18 paints): A student began hin studies at RMC knowing that he wishes to retire in luxury after a 25 years career in the Canadian Forces. He has determined that it requires $500,000 in savings other thun his pension. He considers the following options: Invest $5,000 per year for a period of 25 year. - Invest 10% of his monthly salary each month for a period of 25 years He considers the second option especially since he acknowledge that it would be difficult to save $5,000 per year at the beginning of his career. To properly evaluate the option (b), you pot the pay scale of the officers of the Canadian Forces (see table attached at the end of this document). He considers reaching thic rank of Icol ut retirement. For the analysis, use the wages sociated with rows identified in Table 1. Table 1: Ranks achieved during a military career. Year 3 4 5 6 7 3 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 Rank Oct basic Oedt AL Oodt A2 Oodt A3 LA A basic LEA basic LA1 Capt basic Capt1 Capt 2 Capt Capt4 Capt 5 Cape 6 Capt Majorbade Major Major 2 Major Major 4 Major LC base LColl Loola Cal (a) What is the total amount invested over 25 years for both options? (2 points) (b) Calculate the value of each investment at the time of his retirement by using an interest rate of 9% compounded monthly. (4 points) (c) Is it feasible to achieve his objective of $500,000 with these investments? Highlights investment(s) that meet(s) his goal by applying conditional formatting to target cells. (1 point) (d) Calculate the value of his pension when he retires. As pension, he receives 2% of his annual salary for each year of service. The salary used in the calculation of the pension is his average carnings during his last 5 years of service. (2 points) (e) Do you think that the combination of his pension and his savings are enough to pay for a luxurious retreat? Comment. (1 point) REGULAR FORCE OFFICER RATES Disclaimer: These pay tables are for Information only. These consolidations have been prepared for onvenience of reference only and have no official sanction Gestions of an official natureconcerning these rates should be addressed to DPPD RANK PAY LEVEL GENERAL SERVICE OFFICERS MONTHLY RATES (in dollars) AFTER MARCH 2010 PAY INCREMENT 2 3 4 5 6 7 B 1517 ISS 32363362 BASIC 1 9 10 Officer Cadet B 3687 426 A 3 37 4272 Second Lieutenant 356397 3952 4261 4577 48905206 5518 465 4709 50 4933 5235 5391 570 580 6066 D 09:50 E 484 514 539 5456 5600641 6333 4993 5275 A 4644 : + teutenant 2006 2724653 5007 40 HD 4951 5163 3587 C 4776 4966 D 6 BURE SOX SHID 5275 5 6041 6985 076796 2 610 746530 7054 7 59 TH 583 0774 985 7961 1939 80 514 O Captain Major Lieutenant Colonel Colonel Brander General Major General Lieutenant General 106 11106 0950 1961 1960 1969 1905 1 19 2018 NAVIGATORS AFTER MARCHIO Captain II. Retirement Planning (18 paints): A student began hin studies at RMC knowing that he wishes to retire in luxury after a 25 years career in the Canadian Forces. He has determined that it requires $500,000 in savings other thun his pension. He considers the following options: Invest $5,000 per year for a period of 25 year. - Invest 10% of his monthly salary each month for a period of 25 years He considers the second option especially since he acknowledge that it would be difficult to save $5,000 per year at the beginning of his career. To properly evaluate the option (b), you pot the pay scale of the officers of the Canadian Forces (see table attached at the end of this document). He considers reaching thic rank of Icol ut retirement. For the analysis, use the wages sociated with rows identified in Table 1. Table 1: Ranks achieved during a military career. Year 3 4 5 6 7 3 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 Rank Oct basic Oedt AL Oodt A2 Oodt A3 LA A basic LEA basic LA1 Capt basic Capt1 Capt 2 Capt Capt4 Capt 5 Cape 6 Capt Majorbade Major Major 2 Major Major 4 Major LC base LColl Loola Cal (a) What is the total amount invested over 25 years for both options? (2 points) (b) Calculate the value of each investment at the time of his retirement by using an interest rate of 9% compounded monthly. (4 points) (c) Is it feasible to achieve his objective of $500,000 with these investments? Highlights investment(s) that meet(s) his goal by applying conditional formatting to target cells. (1 point) (d) Calculate the value of his pension when he retires. As pension, he receives 2% of his annual salary for each year of service. The salary used in the calculation of the pension is his average carnings during his last 5 years of service. (2 points) (e) Do you think that the combination of his pension and his savings are enough to pay for a luxurious retreat? Comment. (1 point) REGULAR FORCE OFFICER RATES Disclaimer: These pay tables are for Information only. These consolidations have been prepared for onvenience of reference only and have no official sanction Gestions of an official natureconcerning these rates should be addressed to DPPD RANK PAY LEVEL GENERAL SERVICE OFFICERS MONTHLY RATES (in dollars) AFTER MARCH 2010 PAY INCREMENT 2 3 4 5 6 7 B 1517 ISS 32363362 BASIC 1 9 10 Officer Cadet B 3687 426 A 3 37 4272 Second Lieutenant 356397 3952 4261 4577 48905206 5518 465 4709 50 4933 5235 5391 570 580 6066 D 09:50 E 484 514 539 5456 5600641 6333 4993 5275 A 4644 : + teutenant 2006 2724653 5007 40 HD 4951 5163 3587 C 4776 4966 D 6 BURE SOX SHID 5275 5 6041 6985 076796 2 610 746530 7054 7 59 TH 583 0774 985 7961 1939 80 514 O Captain Major Lieutenant Colonel Colonel Brander General Major General Lieutenant General 106 11106 0950 1961 1960 1969 1905 1 19 2018 NAVIGATORS AFTER MARCHIO Captain