II. Retirement Planning (10 points): A student began his 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 than 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 acknowledges that it would be difficult to save $5,000 per year at the beginning of his career. To properly evaluate the option (b), you got the pay scale of the officers of the Canadian Forces (see table attached at the end of this document). He considers reaching the rank of Icol at retirement. For the analysis, use the wages associated with rows identified in Table 1.Table 1: Ranks achieved during a military career. Year Rank Ocdt A basic Ocdt A 1 Oodt A 2 Oodt A 3 2Lt A basic Lt A basic Lt A 1 Capt basic Capt 1 10 Capt 2 Capt 3 Capt 4 Capt 5 14 Capt 6 15 Capt 7 16 Major basic 17 Major 1 18 Major 2 19 Major 3 20 Major 4 21 Major 5 22 LCol basic 23 LCol 1 24 LCol 2 25 LCol 3(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 earnings 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)GENERAL SERVICE OFFICERS MONTHLY RATES (in dollars) AFTER MARCH 2010 RANK PAY PAY INCREMENT LEVEL BASIC 1467 1517 1553 1581 Officer Cadet B 2687 2794 3236 3362 4261 1322 3387 3586 3927 4272 Second Lieutenant 3644 3952 4261 4577 1890 5206 4652 1769 4943 5082 5235 5391 5653 5720 5890 6250 4708 4847 4993 5142 5296 5456 5620 5767 5962 6141 6322 1644 4958 5275 5568 3586 3927 4272 4653 50:37 Lieutenant 4006 1732 4482 4644 4802 4956 5116 5275 5412 4776 19bb 5163 5372 57587 5812 6041 6285 6537 |6706 7070 4061 5159 5:366 5681 5607 6037 6279 6532 6791 7063 7347 Captain 5887 6112 6334 6558 6774 6985 7192 7406 7530 7654 7781 Major 7961 8101 3239 8376 8514 8652 8789 6028 Lieutenant- Colonel 9226 9376 9520 9671 Colonel 10685 11106 11527 11950 Brigadier- General 12647 12986 13343 13692 Major- General 14523 15355 16219 17056 Lieutenant- General 18642 19148 19679 20183 NAVIGATORS - AFTER MARCH 2010 Captain 7996