Reducible interest Activity
When someone borrows money, typically interest is only charged on the remaining balance of the loan.
Example
Little Timmy borrows $5 000 to buy a car. The bank charges him 8% interest and he makes monthly payments of $250. How much does little Timmy owe after 1 year?
There is not a particularly easy way to do this, but Excel can help! Let’s take the opportunity to learn a little about loans and Excel at the same time. WINNING! If you get stuck with anything in Excel, you can click on the bold, underlined words for help.
Example
Little Timmy borrows $5 000 to buy a car. The bank charges him 8% interest and he makes monthly payments of $250. How much does little Timmy owe after 1 year?
There is not a particularly easy way to do this, but Excel can help! Let’s take the opportunity to learn a little about loans and Excel at the same time. WINNING! If you get stuck with anything in Excel, you can click on the bold, underlined words for help.
1. Label your columns as shown below. (Appropriate table headings are important for clear communication. Always give careful thought to this on investigations!)
Column A: Month
Column B: Balance at beginning of month
Column C: Interest rate
Column D: End of month balance with interest (before payment)
Column E: Monthly payment
Column F: End of month balance after payment
Column G: Amount of interest paid
Column H: Amount paid toward principal
Column B: Balance at beginning of month
Column C: Interest rate
Column D: End of month balance with interest (before payment)
Column E: Monthly payment
Column F: End of month balance after payment
Column G: Amount of interest paid
Column H: Amount paid toward principal
2. In column A, enter “1” in cell A1. We want the rest of the column to be 2, 3, 4…. etc down as far as we might want. Do this with a formula that you can fill down.
3. For now, in column B enter a beginning of month balance of $5000 for the first month. (Remember to not enter a $ in the cell or Excel will not like this when you try to do calculations! How can we have Excel display the dollar sign without typing it in?)
4. In column C, enter an interest rate of 8% for now. (Remember to not enter a % in the cell or Excel will not like this when you try to do calculations! How can we have Excel display this as a percentage without typing it in that way?)
5. In column D, enter a formula in cell D1 so that Excel will calculate the value for you. Remember the symbols for the basic operations: + - * /
6. For now, in column E, enter a monthly payment of $250.
7. In column F, enter a formula in cell F1 so that Excel will calculate the value for you.
8. In column G, enter a formula in cell G1 so that Excel will calculate the value for you. (This is the interest that accumulated this month.)
9. In column H, enter a formula in cell H1 so that Excel will calculate the value for you. This will be the amount of your payment that remains after paying the interest that can therefore be used toward the principal.
10. In cell B3, enter a formula for the beginning balance for month 2.
11. You should now be able to “fill down” in every column. For now, fill down for 12 months.
3. For now, in column B enter a beginning of month balance of $5000 for the first month. (Remember to not enter a $ in the cell or Excel will not like this when you try to do calculations! How can we have Excel display the dollar sign without typing it in?)
4. In column C, enter an interest rate of 8% for now. (Remember to not enter a % in the cell or Excel will not like this when you try to do calculations! How can we have Excel display this as a percentage without typing it in that way?)
5. In column D, enter a formula in cell D1 so that Excel will calculate the value for you. Remember the symbols for the basic operations: + - * /
6. For now, in column E, enter a monthly payment of $250.
7. In column F, enter a formula in cell F1 so that Excel will calculate the value for you.
8. In column G, enter a formula in cell G1 so that Excel will calculate the value for you. (This is the interest that accumulated this month.)
9. In column H, enter a formula in cell H1 so that Excel will calculate the value for you. This will be the amount of your payment that remains after paying the interest that can therefore be used toward the principal.
10. In cell B3, enter a formula for the beginning balance for month 2.
11. You should now be able to “fill down” in every column. For now, fill down for 12 months.
Questions
1. How much does Tommy owe at the end of one year?
2. Look at columns G & H. What happens to these values as time progresses? Explain.
3. The beauty of Excel is that you can now make adjustments to numbers and see what happens. How long will it take Tommy to fully repay this loan? (Fill down more rows and see what happens).
4. If Tommy wants to pay off the loan by the end of the year, what would his monthly payment have to be? (Adjust the value of the monthly payment and see what happens).
5. If he can only afford a monthly payment of $300 but still wants to pay off the loan in one year, how much can he afford to spend on the car? (Set the monthly payment to $300 each month, and then adjust the principal and see what happens).
6. While Excel can do calculations like #3, 4 and 5, it can be a bit tedious using guess and check to figure these things out. Instead we use something called an amortization calculator like the one found here. Use this calculator to confirm you answers in #3, 4 and 5.
2. Look at columns G & H. What happens to these values as time progresses? Explain.
3. The beauty of Excel is that you can now make adjustments to numbers and see what happens. How long will it take Tommy to fully repay this loan? (Fill down more rows and see what happens).
4. If Tommy wants to pay off the loan by the end of the year, what would his monthly payment have to be? (Adjust the value of the monthly payment and see what happens).
5. If he can only afford a monthly payment of $300 but still wants to pay off the loan in one year, how much can he afford to spend on the car? (Set the monthly payment to $300 each month, and then adjust the principal and see what happens).
6. While Excel can do calculations like #3, 4 and 5, it can be a bit tedious using guess and check to figure these things out. Instead we use something called an amortization calculator like the one found here. Use this calculator to confirm you answers in #3, 4 and 5.