compound interest
Pane Compound Interest A Three Methods Approach Before you begin: . Look for your name on the sign-up sheet (given by your instructor) and copy the Line # Principal, and APR. Review the compound interest formula from section 4-B of your text. Remember that all formulas in an Excel spreadsheet begin with an equality symbol Recall that to reference a cell, you can click on it or type its column letter and row number Download Spreadsheet: Download from Canvas the file "Compound Interest Template.xls" which provides you a spreadsheet framework. This helps you concentrate on the three methods of calculating the lump sum investment without worrying about the formatting details. Procedure: Using the template you have downloaded and the prototype figure below, construct a compound interest spreadsheet using three different methods (iteration (steps), formula, and Excel function) that will arrive at the very same balance if properly done. Be sure to type in the Givens box the same principal, compound, and APR as the prototype figure. From Quarter #1 (row 8) and thereafter, you will be building formulas that are flexible enough to accommodate other values you type into the Givens box later. Iteration (Steps) Method (1) 1. Link by a cell reference, the total cell of Quarter #0 (cell E7) to the given principal value in cell D2. 2. Now let principal from Quarter I (cell C8) be referenced to cell E7 3. For the interest in Quarter #1 (cell D8), create a formula by multiplying the principal (cell C8) by the given APR (cell D4) divided by the given Compounds (cell D3). Note: because you will want to always use the same given APR and compound values even after you copy or fill the formulas down the columns you must use absolute cell referencing for those cell locations (locking them in). This can be done by pressing F4 (for Windows) or Command-T (for Macintosh) while the cursor is in the iddle or at the end of cell references D4 and D3 4. Now add the interest to the principal (that is, compound the interest) to make a new total (cell E8). This is done by adding cell C8 to cell D8 as a formula (that is, -C8+D8) 4. Now add the interest to the principal (that is, compound the interest) to make a new total (cell E8). This is done by adding cell C8 to cell D8 as a formula (that is, "-C8+D8"). 5. Complete the Iteration Method by dragging over the principal, interest, and total cells from Quarter #1 and filling their corresponding formulas down to Quarter #36. This can be done relatively fast by looking for the small black cross hair in the bottom right corner of your shaded rectangle and either dragging the three cells down or simply double-clicking. See your software documentation or instructor for help. Don't forget to check for accuracy. Formula Method (11) 1. In the Formula box at the bottom of your template, follow the directions for the compound interest formula as shown in the prototype. 2. Confirm that your answer matches the same one found for Quarter #18 using iterations. Excel Function Method () 1. In the Excel Function box at the bottom of your template, follow the directions for the formula as shown in the prototype. The focus here is to learn how to use the built-in function known as future value (FV) for Excel. You can find helpful dialog boxes to guide you through this process by looking for the "Insert" ribbon in the newer Office suite for Windows or by using the "Insert" pull-down menu and finding the feature "Function..." in the Macintosh Office suite. 2. You will notice that the template and prototype indicate cell references in the future value function rather than actual values. This reinforces the idea of keeping your formula generalized to accommodate future changes to the Givens box. 3. Be aware of the need to place a negative sign after the equality to keep the future value as a positive value. Polishing the Worksheet 1. Be sure to total your interest after Quarter #36 (D44) by using the function Sum' and dragging over the interest cells from Quarter #1 to Quarter #36 or take advantage of the Auto Sum tool that looks like A and may be on one of your tool bars at the top. 2. Check to see if the very last balance (Quarter #36) matches the prototype quantity Results Summary: Upon verifying that your compound interest spreadsheet (based on the prototype givens) has correct quantities throughout, modify your spreadsheet according to the sign-up sheet values you were given. Then answer the 5 reflection questions in the textboxes on the spreadsheet. Tum in your completed spreadsheet on Canvas. I Template C 1 B D E F G J H K 3 810 1 2. 3 4 5 6 7 8. 9 Line Number Givens: Principal Compounds APR 6.40% Interest Total 810.00 Quarter Principal 0 1 810 2 3 4 S 6 7 8 10 11 12 13 14 Methods 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 Qtr. #18 Hieration (Steps) Otr. 18 Formula 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 Qur. 18 Excel Function PVC Total: 5 45 46 Reflection 1. What would be another way to calculate total interest rather than just adding up the Individual interest amounts? 48 49 50 51 52 53 54 Reflection 1: What would be another way to calculate total interest rather than just adding up the individual interest amounts? Reflection 2: What percentage of the balance (end of Quarter #36) is the interest and what percentage is the principal? Do these percentages change if you modify the principal or APR? How? Reflection 3: What are some of the pros and cons to each of the three methods you have used? 5 6 7 +8 79 80 81 82 83 84 85 6 87 88 89 90 91 92 93 94 95 96 97 Reflection 4: Which of the three methods is your favorite way to calculate the balance and why? 99 100 101 102 103 Reflection Si What impressions or insights have you gained from this exercise Formatted Rin are flexible enough to accommodate r. B A F D G Givens: 1500 Principal Compounds APR 7309 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 Methods Quarter Principal Interest Total 0 1500.00 1 1500.00 28.131528.13 2 1528.131228.65 21556.78 1556.78 29.19 1585.97 4 1585.97 29,74 1615.70 3 1615.70 30.29 1646.00 6 1646.00 30.86 1676.86 7 1676.86 31.44 1708.30 8 1708.30 32103 174033 9 1740.33 32.631772.96 10 1772.96 037571433.24 1806.21 1806.21 33.87 1840.07 12 1840.07 34.50 1874.57 13 187457 35.151909.72 14 1909.72 35.81 1945.53 15 1945.53 36.48 1982.01 16 1982501 37A16 2019.17 17 2019.17 37.86 2057.03 18 205703 38 57 2095.60 19 2093.60 39229 R2134.89 20 2134.89 2010 217492 21 2174.92 40.78 22215.20 22 2215.70 2257.25 23 2257.25 42.32 2299.57 24 2299.57 43.12 2342.69 252342.69 43.93 2386,61 26 2386.61 44.75 2431.36 27 243136 45.59 2476.95 28 2476.95 46.44 232339 29 2523.39 47:31 2570.71 30 2570.71 48.20 2618.91 31 2618.91 49.10 266801 32 266801 30.03 271804 33 271804 50.96 2769.00 34 270900 5192 2020.92 35 2820.92 52.89 36 2873.81 5388 2927709 Total 1427.69 Qur. 18 Peration (Steps) EC25 D25 Qur. 718 Formula 2093.60 "-1500*(1+0.07544)*18 Qur. 118 El Punction 2095.60 FVDL/D3.325.0.D2) 44 Pane Compound Interest A Three Methods Approach Before you begin: . Look for your name on the sign-up sheet (given by your instructor) and copy the Line # Principal, and APR. Review the compound interest formula from section 4-B of your text. Remember that all formulas in an Excel spreadsheet begin with an equality symbol Recall that to reference a cell, you can click on it or type its column letter and row number Download Spreadsheet: Download from Canvas the file "Compound Interest Template.xls" which provides you a spreadsheet framework. This helps you concentrate on the three methods of calculating the lump sum investment without worrying about the formatting details. Procedure: Using the template you have downloaded and the prototype figure below, construct a compound interest spreadsheet using three different methods (iteration (steps), formula, and Excel function) that will arrive at the very same balance if properly done. Be sure to type in the Givens box the same principal, compound, and APR as the prototype figure. From Quarter #1 (row 8) and thereafter, you will be building formulas that are flexible enough to accommodate other values you type into the Givens box later. Iteration (Steps) Method (1) 1. Link by a cell reference, the total cell of Quarter #0 (cell E7) to the given principal value in cell D2. 2. Now let principal from Quarter I (cell C8) be referenced to cell E7 3. For the interest in Quarter #1 (cell D8), create a formula by multiplying the principal (cell C8) by the given APR (cell D4) divided by the given Compounds (cell D3). Note: because you will want to always use the same given APR and compound values even after you copy or fill the formulas down the columns you must use absolute cell referencing for those cell locations (locking them in). This can be done by pressing F4 (for Windows) or Command-T (for Macintosh) while the cursor is in the iddle or at the end of cell references D4 and D3 4. Now add the interest to the principal (that is, compound the interest) to make a new total (cell E8). This is done by adding cell C8 to cell D8 as a formula (that is, -C8+D8) 4. Now add the interest to the principal (that is, compound the interest) to make a new total (cell E8). This is done by adding cell C8 to cell D8 as a formula (that is, "-C8+D8"). 5. Complete the Iteration Method by dragging over the principal, interest, and total cells from Quarter #1 and filling their corresponding formulas down to Quarter #36. This can be done relatively fast by looking for the small black cross hair in the bottom right corner of your shaded rectangle and either dragging the three cells down or simply double-clicking. See your software documentation or instructor for help. Don't forget to check for accuracy. Formula Method (11) 1. In the Formula box at the bottom of your template, follow the directions for the compound interest formula as shown in the prototype. 2. Confirm that your answer matches the same one found for Quarter #18 using iterations. Excel Function Method () 1. In the Excel Function box at the bottom of your template, follow the directions for the formula as shown in the prototype. The focus here is to learn how to use the built-in function known as future value (FV) for Excel. You can find helpful dialog boxes to guide you through this process by looking for the "Insert" ribbon in the newer Office suite for Windows or by using the "Insert" pull-down menu and finding the feature "Function..." in the Macintosh Office suite. 2. You will notice that the template and prototype indicate cell references in the future value function rather than actual values. This reinforces the idea of keeping your formula generalized to accommodate future changes to the Givens box. 3. Be aware of the need to place a negative sign after the equality to keep the future value as a positive value. Polishing the Worksheet 1. Be sure to total your interest after Quarter #36 (D44) by using the function Sum' and dragging over the interest cells from Quarter #1 to Quarter #36 or take advantage of the Auto Sum tool that looks like A and may be on one of your tool bars at the top. 2. Check to see if the very last balance (Quarter #36) matches the prototype quantity Results Summary: Upon verifying that your compound interest spreadsheet (based on the prototype givens) has correct quantities throughout, modify your spreadsheet according to the sign-up sheet values you were given. Then answer the 5 reflection questions in the textboxes on the spreadsheet. Tum in your completed spreadsheet on Canvas. I Template C 1 B D E F G J H K 3 810 1 2. 3 4 5 6 7 8. 9 Line Number Givens: Principal Compounds APR 6.40% Interest Total 810.00 Quarter Principal 0 1 810 2 3 4 S 6 7 8 10 11 12 13 14 Methods 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 Qtr. #18 Hieration (Steps) Otr. 18 Formula 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 Qur. 18 Excel Function PVC Total: 5 45 46 Reflection 1. What would be another way to calculate total interest rather than just adding up the Individual interest amounts? 48 49 50 51 52 53 54 Reflection 1: What would be another way to calculate total interest rather than just adding up the individual interest amounts? Reflection 2: What percentage of the balance (end of Quarter #36) is the interest and what percentage is the principal? Do these percentages change if you modify the principal or APR? How? Reflection 3: What are some of the pros and cons to each of the three methods you have used? 5 6 7 +8 79 80 81 82 83 84 85 6 87 88 89 90 91 92 93 94 95 96 97 Reflection 4: Which of the three methods is your favorite way to calculate the balance and why? 99 100 101 102 103 Reflection Si What impressions or insights have you gained from this exercise Formatted Rin are flexible enough to accommodate r. B A F D G Givens: 1500 Principal Compounds APR 7309 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 Methods Quarter Principal Interest Total 0 1500.00 1 1500.00 28.131528.13 2 1528.131228.65 21556.78 1556.78 29.19 1585.97 4 1585.97 29,74 1615.70 3 1615.70 30.29 1646.00 6 1646.00 30.86 1676.86 7 1676.86 31.44 1708.30 8 1708.30 32103 174033 9 1740.33 32.631772.96 10 1772.96 037571433.24 1806.21 1806.21 33.87 1840.07 12 1840.07 34.50 1874.57 13 187457 35.151909.72 14 1909.72 35.81 1945.53 15 1945.53 36.48 1982.01 16 1982501 37A16 2019.17 17 2019.17 37.86 2057.03 18 205703 38 57 2095.60 19 2093.60 39229 R2134.89 20 2134.89 2010 217492 21 2174.92 40.78 22215.20 22 2215.70 2257.25 23 2257.25 42.32 2299.57 24 2299.57 43.12 2342.69 252342.69 43.93 2386,61 26 2386.61 44.75 2431.36 27 243136 45.59 2476.95 28 2476.95 46.44 232339 29 2523.39 47:31 2570.71 30 2570.71 48.20 2618.91 31 2618.91 49.10 266801 32 266801 30.03 271804 33 271804 50.96 2769.00 34 270900 5192 2020.92 35 2820.92 52.89 36 2873.81 5388 2927709 Total 1427.69 Qur. 18 Peration (Steps) EC25 D25 Qur. 718 Formula 2093.60 "-1500*(1+0.07544)*18 Qur. 118 El Punction 2095.60 FVDL/D3.325.0.D2) 44
