The ratio of interest to principal changes with each payment, such that in the beginning of the loan term, you pay more interest than you do at the end. Likewise, more of each payment is applied to the principal balance, the further along you are in the loan term.
This difference is even more pronounced with long-term home loans that apply relatively little of each monthly installment to the principal at the beginning of the loan term.
That’s also why financial specialists recommend making additional, principal-only payments. Although these extra payments won’t reduce the payments themselves, paying down the principal balance will shorten the loan term. This article doesn’t factor in such additional payments, but it will show you how to calculate the portion applied to interest and principal for any payment using Microsoft Excel 2013.
Setting Up Your Spreadsheet
1. Set up your spreadsheet like the one above and enter the loan amount, interest rate and number of payments. Remember to enter the interest rate and loan term as monthly values by dividing the interest rate, and multiplying the loan years, by 12.
Two previous articles, Excel Functions for Calculating Monthly Payments and How to Calculate How Expensive of a Car or House You Can Afford Based on Your Monthly Budget, used a car payment as an example, so this article continues where those left off by using a $20,690.22 five-year loan with an annual percentage rate (APR) of six percent.
Thus, the interest rate is entered as “=6%/12” and the number of payments as “=5*12”.
2. Enter the numbers “1” and “2” in cells A6 and A7, respectively, and then highlight both cells.
3. Click and drag the selection’s lower right drag handle, indicated by a small, solid black square. When you release it Excel automatically increments the numbers down every highlighted cell.
In the example, dragging down to cell A65 was enough to reach 60 payments, but for longer loan terms, you’ll need to go down further. If you end up with too many numbers, simply highlight the excess numbers and press the “Del” key to remove them.
Making Your Calculations
4. Enter the formula
in cell B6 to calculate the amount of the first payment that is applied to interest. This function follows the format
Excel automatically formats the figure as a red, parenthetical expense. Keep the dollar signs (“$”) in the formula, because they’ll stop references to the top three variables from later changing.
5. Use the formula
in cell C6 to calculate the amount of the first payment applied to the principal balance. The format of the formula is exactly like the previous one, except it uses the “PPMT” function.
6. Enter the formula
in cell D6 to total the payment. You could also enter
to get the same result using the top values.
7. Highlight the three cells containing the formulas you just entered.
8. Double-click the selection’s lower right drag handle. Excel automatically fills and adjusts the formula for every payment number you previously entered. As you can see, the first payment allocates $103.45 to interest, but the last one applies only $1.99.
To illustrate the difference between this example and a long-term loan, here’s the output for a 30-year, $200,000 home loan using the same APR:
At the beginning of the loan, you’re paying more than five times more interest than principal, but at the end, almost all of the payment is applied to principal. That why, if you can afford it, it’s useful to make additional, principal-only payments early in the loan term to build more equity and reduce the loan term.