Reducing your debt

1. Reducing your debt

In the previous exercises, we've talked about how to invest money using the pmt() and nper() functions. Now let's discuss how to pay down debts.

2. Loan amortization tables

Most people are familiar with loans, as they are often used to buy a house, car, or college, and loans are borrowed money that you pay back over time. When you make payments on a loan, they are split between paying the principal, or the amount borrowed, and the interest on the principal. The interest portion of your payment will start high, as the principal balance will be high. As you make more payments, the interest amount of the payments will decrease over time. You can examine how these payments are split using an amortization table.

3. Example loan table

Here's an example of a loan amortization table model. You can see that the annual payment is the same each year of the loan, but that the interest decreases over time, while the payment on principal increases over time.

4. Beginning balances and payments

To get started, you will calculate the required payment using the pmt() function we learned in the last lesson. In B7, use equal pmt(), open parentheses, click on the interest rate, then click on the number of years for number of payments, and finally type the negative symbol and click on the initial loan balance for the present value. Next, in cell B10, you will enter the initial balance for year 1 in the year beginning balance. Type equals and click on the initial loan balance cell in B4. The annual payment will be an absolute reference to the required payment we just calculated. Type equals, then click on B7 for the required payment and add the dollar signs to set the absolute reference.

5. Calculate where your money went

To calculate the separation between interest and principal, we first add the interest component. To calculate the interest, multiply the current year beginning balance by the interest rate. Type equals, click on B10 for the year beginning balance, use the multiplication symbol, and then click on B5 for the interest rate. Be sure to include the interest rate as an absolute reference by adding the dollar signs. Next, the principal part of the payment is the annual payment minus the interest component. Type equals then click on C10 for the annual payment, the minus symbol, and then D10 for the interest portion of the payment. The year-end balance is the difference of the beginning balance from the principal part of the payment only. Type equals, then click on B10, the minus symbol, and then click on E10 for the principal payment.

6. Complete the model

To finish the model, you need to do a couple of things. First, the year beginning balance for year two and forward is the year-end balance from the previous year. In B11, type equals, then click on F10 to automatically transfer over the year-end balance. The cells with functions for annual payment, interest, principal, and year-end balance can be copied through the rest of the years of the life of the loan. You will know you've created the model correctly because the final year-end balance should be zero dollars.

7. Make your loan model!

Your turn to make a loan amortization table.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.