Problem 3 (25 pts) Most of you will work for companies that will no longer have pension systems and you may not be able to count on having Social Security at all in retirement. Therefore, you will need to generate your own retirement income by saving a large quantity of money in a 401K or other similar retirement account over your lifetime. The mistake many people make is to wait to start saving any significant amount of money until they are in their later 30s or 40s (or worse even later). In this problem, we will analyze several situations to see what the impact of those choices are on how much vou must save each year to generate the retirement income you will need. Most conventional wisdom says that when you retire, you want to be able to replace 65% of your take home earnings from your job (i.e. this basically assumes that 35% of your earnings were being used to pay the mortgage on your house and that you will have paid off your house by the time you retire, so to maintain your lifestyle you only need income to replace the other 65% of your take home earnings that you were spending on everything else). So how much do you need your retirement savings to generate in yearly income? Let's assume you were graduating today, and you were just turning 22 years old. Also assume that as a Chemical Engineer your starting salary was $70,000 per year. If we assume that on average you earned a 2% raise per year at the end of each year (i.e. not an unreasonable raise to keep up with inflation), then if you wait until your 65 birthday to retire (i.e. you would work 43 complete years), your salary when you are 65 would be, $70,000.x (1.02) = $164,023.25 Let's assume that you will be paying an average personal income tax rate and social security tax rate combined of 22% at that time, so your take home earnings per year when you retire at 65 would be, $164,023.25 x 0.78 = $127,938.14 So to replace 65% of your take home earnings your investments would need to generate a yearly cash flow of Yearly Retirement Withdrawal Cash Flow = $127,938.14 x 0.65 = $83,159.79 Using a Future Value Cash Flow Table to Analyze a "Safe Plan Analyze the following scenario by using Excel to calculate a Future Value cash flow table to provide answers to the questions posed. In the PDF containing your answers, be sure to paste in copies of the relevant Excel tables you used to determine your answers. Let's assume you want to generate the required $83,159.79 per year in retirement by switching over your retirement savings into a very safe 4% interest yield account and that you only withdraw the interest to live on (l.e. you never deplete the balance of the account and can thus be secure no matter how long you live and also can leave it to your children, etc.). You would need to amass a final account value by the time you retire and transition it to the 4% yielding safe account of $95,853.89/0.04= $2,078,994.75 to generate that much interest to live on for the 250 or your life. While you are working, let's assume you use a moderate to aggressive risk investment account that can generate 7% interest per year on your savings. A. If you start saving the same amount each and every year when you start your job by saving each year and making your first deposit on your 23rd birthday (i.e. you save up during the year from age 22 to age 23 and place your deposit in the bank at the beginning of each year starting at age 23), how much must you save each year to provide the needed income for retirement? Again, assume you work the year you are 65 and retire right at the end of that year, so you do not make a yearly deposit into your retirement account at the end of that year (i.e. the start of your age 66 year) and instead use any money you saved the year your are turning 65 to make your retirement transition. So your last retirement deposit will come at the beginning of your age 65 year from money you save the previous year and will only have one year to accrue interest before you have to convert your entire account over to the safe 4% yield interest account. Future Value Your answer in your Excel table should look something like: Perody Age (Beginning of Year Timm Frame for Each Cash Flow) Cash Flow Appreciation Factor 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 an 61 62 63 84 Net Future Values $2,078,994.75 B. Do the same analysis as in Part A, but complete it using a Present Value Analysis instead. Using this analysis, how much should you save each year? Be sure to show your cash flow table in your