Help

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 (i.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 $83,159.79/0.04= $2,078,994.75 to generate that much interest to live on for the rest of 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. Your answer in your Excel table should look something like: Cash Flow Appreciation Factor Future Value Period/Age (Beginning of Year Time Frame for Each Cash Flow 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 SO 51 52 53 54 55 56 57 58 59 60 61 62 63