Saving for retirement is an important element for workers today as most of you will not receive a pension when you retire. Therefore the onus is on you to save. The most commonly used retirement accounts are the 401(k) and the Individual Retirement Account or IRA. Money that you contribute into these accounts is pretax (i.e. they are contributed before the government taxes your income), and grows tax free until you withdraw it. Using Excel, I would like you to determine the influence of numerous factors on your retirement account / style of living (both now and later). Class Problem/Homework 2 1) In this scenario you diligently invest $6000 (annuity) a year for your entire working career starting at 22 and finishing at 65 (i.e. you make 44 equal contributions, N=44). How much do you have invested at age 65? Assume that your savings will earn 8% per year. How much of the total final value comes from contributions and how much from compound interest? Hint: We don't collect interest in the last year. a. Use the idea that you make 8% every year on your savings i. Hint first year you have $6,000*1.08 = 6,480; Second year you have ($6000+6,480)*1.08 =13,478 ii. Set up your excel spreadsheet in this format Column A Column B Column C Year 1 Year 2 Year 3 Year 4 $$$ at Interest beginning of Earned year Column D + $6000 Column B *0.08 Column D Total amount end of year Column D = Column C+ColumnB iii. Now use the formula: FV = Annuity × (1+i) N-1 i 2) How much do you need to invest every year if you want to retire with $1,000,000? Again, assuming an 8% rate of return (RoR). a. Hint: we will use goal seek, found in the data ribbon in the what if analysis pull down menu. b. Pick a guess value for the annuity and solve for Future Value (see Goal Seek Zoom recording) Homework Problems 3) Repeat questions 1 and 2 assuming that you wait until age 32 (34 Equal Contributions) to begin contributing. 4) What if, due to bad luck, you earn a 5% RoR on your investments? How will this change your above numbers, look at 44 equal contributions? b. How does the value change with 34 equal contributions? 5) How about if a large bull market increases your average RoR to 12%? What happens to the numbers above, assume 44 equal contributions? b. How does the value change with 34 equal contributions? Table1: Age 22 Age 22 Interest Rates 8% 5% 12% Table 2: Age 32 Age 32 Interest Rates 8% 5% 12% Total Nest Egg Total Nest Egg Annuity Amount for $1 Mil Annuity Amount for $1 Mil Please upload this completed document/tables along with your excel workbook on Blackboard by 1/28 at 11:59 pm. (Tables can be summarized in Excel File or in Word, whichever you prefer).