A B D Long-Term Personal Savings Plan Initial Amount at beginning of year 1($): Annual Interest rate (%): Annual $ in: Balance at the end of year 50: E F 200 2.5% 350 Annual Balance at year Year beginning ($) interest rate (%) ($) Annual yield $ put in during the year Balance at year end (S) 1 2 Simulation Project 1 Excel Worksheet for Long-Term Savings What you need to do in this project: Name (1) Develop an Excel spreadsheet for calculating personal long-term savings, (2) Do calculations by using your spreadsheet, and record the results in the Table of Results on the next page. Save the Excel Template to your computer: In Blackboard, Open the template of the Excel spreadsheet, Template-Project 1; and then save it ("Save as") in your computer as an Excel file (.xls or .xlsx) with the following new name: your name-Project 1, for example, John-Smith-Project 1. Develop your spreadsheet based on this template in your computer. The Template for Project 1: A 1 2 3 4 5 6 7 B C D E F Long-Term Personal Savings Plan Initial Amount at beginning of year 1($): Annual Interest rate (%): Annual $ in: Balance at the end of year 50: 200 2.5% 350 8 Year Balance at year beginning ($) Annual interest rate (%) Annual yield $ put in during the ($) year Balance at year end ($) 9 1 10 2 11 Guidelines and Notes: Your worksheet should do the calculations up to year 50. Make sure of understanding meanings of the columns in the Template, as well as the relationship among the columns. In the finished worksheet, you enter values in cells E3 (initial amount), E4 (interest rate) and E5 (annual $ in), the balance at the end of year 50 will show in cell E6. In the template, the numbers in cells E3, E4, and E5 are just arbitrary examples. Use Excel formulas or Excel functions to let Excel do all calculations, rather than doing calculations by hand or your calculators. Use relative or absolute cell addresses as far as possible, rather than specific numbers, in a formula or a function to facilitate the Excel worksheet generation. Start each formula with an equality sign "=". For example, =F10; =B9*C9. Use Copy-Paste in Excel as far as possible. The key F9 is used for "recalculating". Do Calculations by using your Excel worksheet and type the results in the blanks in the table below. Table of the Results: (Assume in each case "$ amount taken out during the year" = 0.) Balance at beginning of year 1 Annual interest rate $ amount put in annually every year Balance at the end of year 50 $10,000 3% $0 $10,000 8% $0 $0 $1,200 (i.e., $100 3% $0 5% $0 8% $0 3% $0 8% $10,000 4.5% per month) $1,200 $1,200 $4,800 (i.e., $400 per month) $4,800 $2,400 $7,200 (i.e., $600 $10,000 6% per month) O For your reference: Detailed Guidelines for doing the project Personal Long-Term Saving B C D E Long-Term Personal Savings Plan A 1 2 3 Initial Amount at beginning of year 1($): 4 Annual Interest rate (%): 5 6 Annual $ in: Balance at the end of year 50: 7 200 2.5% 350 F Annual Balance at year 8 Year beginning ($) interest rate Annual yield $ put in during the (%) ($) year Balance at year end ($) 9 1 10 2 11 (1) In cell B9, enter =$E$3, which is the initial amount. (2) In cell C9, enter =$E$4, which is the annual interest rate. ($-signs are for fixing the cell address in later-on copying. (3) In cell D9, enter =B9*C9, which calculates the annual yield of year 1. (4) In cell E9, enter =$E$5, which is annual $ in. ($-signs are for fixing the cell address in later- on copying. (5) In cell F9, enter =B9+D9+E9, which is the balance at the end of year 1. (6) In B10, enter =F9. (i.e., beginning balance of year 2 = ending balance of year 1) (7) Copy C9:F9 to C10:F10. (8) Copy Row 10 (A10:F10) all the way down to Row 58 (A58:F58, which is for year 50.) (9) In E6, enter =F58. (To bring ending balance of year 50 up to this cell.) (10) Carefully check the resulted worksheet, to avoid demonstrable or weird errors. (11) Now, you can change initial amount in E3, annual interest rate in E4, annual dollar in in E5, to see the result of 50-year savings in E6 in various scenarios and plans.