2. Spreadsheet 2: Calculate the future value of your retirement investment. Assume you have invested toward your retirement in $10,000 in stock (equities), two bonds (assume face value) and a $2.500 savings account. Calculate what these investments will be worth upon your retirement. Using function wizard, calculate the future value of your savings account and your stock investments. (me STOCK BONDS SAVINGS TOTAL Annual average growth Rate: You will need to make Ner: (years) from above I'veit: zero PV: given $10.000 m $2.040 wadjacom $2.500 $14.500 Type: 0 D Future Value (calc) It should be apparent that this initial investment of stocks, bonds and savings, even adjusted for stock appreciation and bond interest, will not be enough to get you to your (Part 1) calculated nest cuz desired! (Unless, of course, you don't retire for a few hundred years). So let's go on. . . . 3) Spreadsheet 3: Now we know what you need, let's calculate additional annual savings required to reach this retirement goal. You have just created 3 possible retirement fund goals for yourself: low, medium, and high. Now you are able to calculate what you need to start setting aside each year (for a per month rate divide by 12) to achieve your retirement goals, in the given amount of time you have assigned yourself. Put the future value of your stock, bond and savings accounts in the table below. then calculate the rest. Low Medium High Source: a) Retirement Goal $1.040,579 Spreadsheet I (varies with low, med. high ) b) Future Value of TOTAL:545,858 Spreadsheet 2 (same for each Stock, Bonds, and (Stock - 536.165 scenario) Savings Bonds = 56.051 SavingS: 53,642 c) Additional "calculation: (a-b) Funds needed upon $994,721 retirement d) Annual savings 529,464 annually "Use function wizard and required to build (d) Payment function (for a rate, us 6%, n = years to retirement, PV 0, FV = result of c)Name Accounting 202 COMPUTER LAB-Retirement Calculating YOUR Retirement-How soon? How M How MUCH $$$7 Go to Excel. (Click on the Excel icon or search for the Excel program). Using the function wizard fix buffon on your menu bar, practice calculating future value (2 ) tables in your textbook). the first one, and then see if you can do the rest. (Calculate the alove (2 ) and present value problems. See below Lab: Modified an used with permission ile the data using function wizard, NOT the Click on function wizard fix) button Select Financial from the menu, Select FV from the menu. Eckel OK Rate: .OH (836 per year) Nper: 3 (years) Pymi: 0 PV: -$7,934 (Input as a negative) Type: 0 (zero) Click enter Answer should be 19 979 59 Problem Solution (can you get to this solution Future Value of an annuity of $2,000, 3 years, compounded using Excel?) annually at 5%. For amavities, use the Fym field $6.305 You win the lotto and can choose to receive $10,000 in 3 $7.938 years or taking the present value of it today. If you received it today, what would you receive, adjusted for $% discount rate? You bought a car, with 5 annual payments of $6,000. How $21.628.68 much would you pay today, adjusting for a 12% interest rate? Now, after practicing....let's get on with the Retirement Calculation. 1. Spreadsheet 1: : Calculate how much you want (desire, need) for your retirement fund (the nest egg, in $5$5555585). Create the following spreadsheet in Excel (instructions below): E F Retirement fund ss goal Annual Year to Calculate Future Assumed (the Big Pot of money or the "nest income retirement (use Value of Annual 55 annual interest egg". You will live on its interest desired now sumer number Income adjusted for rate upon throughout) 24annual inflation retirement (D/E) (use 7%%) $50.000 19 (rears) $72,841 7% $1,040,579 Low Medium 7% High 7% In column B, input your assumptions regarding your desired annual income now (for you to retire in comfort now, in today's dollars). Input a low, medium, and high scenario (one per line). In column C, imput your estimated years to retirement; use the SAME number for each row. In column D. calculate the Future Value of your annual income (for each scenario). Use EXCEL! In column E, we input an assumed average annual interest rate you will earn as Interest Income on your retirement account, upon retirement. (Assume you will be earning interest at a mixed rate of 7%) In column F, calculate D/E