Using a Spreadsheet to Analyze Annuities Name: 1. Creating an investment template a) Open a new spreadsheet document. Copy the headings, values, and formulas shown below. 1 Amount of an Annuity ? Regulament Annual terest * Compounding perioda per you Number of years 6 het per period 7 Number of periods 500 0.07 2 3 -C3C4 -CACS 9 Period 101 11 A101 Starting balance Interest Deposit Ending balance 0 0 -SCS2 -810-10-10 =E10 -ROLANDSC30-311.23 SCS -O11C11-011 . Format cells C2 and B10 to Ell as currency. Format cells C3 and C as percents to 2 decimal places. b) Refer to the formules in the spreadsheet in part a. Explain the formulas in cells and c7. Why is the interest in the first period? The formula = SCS2 appears in cells lo and Di. The S sign indicates that cell address C2 should not change when we copy the formula to other cells in column D. Explain why this makes sense. Explain the formulas for the ending and starting balances. d. Select cells All to Enl. Fill Down to copy the formulas in row 11 through row 15, Your spreadsheet should look like this. $500.00 7.00% 2 3 3.50% 1 Amount of an Annuity 2 Regular payments 3 Annual Interest rate 4 Compounding periods per year 5 Number of years 6 nterest rate per period 7 Number of periods 8 9 Porlod Starting balance 10 1 $0.00 11 2 $500.00 12 $1017.50 4 $1563.11 14 $2107.47 15 6 $2681 23 Interest $0.00 $17.50 $35.61 $54.36 $73.76 S93.84 Deposit $500.00 $500.00 $500.00 S500.00 $500.00 $500.00 Ending balance $500.00 S1017.50 S1553.11 $2107.47 $2681 23 $3275.07 The amount of the annuity is $3275.07