1. Calculation of interest and principal payments
Good job on working with the first formula, the payment formula. This lesson will introduce two more formulas to break the payments out into interest and principal.
2. Payments to interest and principal over time
This graph shows the percentage of a consistent payment on a 20-year amortizing loan which goes to interest and principal.
As you can see on the left side of the graph in blue, nearly two-thirds of the payments go to interest on the first payment, but by the last payment, interest makes up almost none of the payment.
And remember - the payment amount never changed. In an amortizing loan, payments need to remain the same throughout the entire term of the loan.
3. Payment Split
At any given point in time, a payment is made up of two components, the principal component and the interest component.
So far, we have calculated the interest component manually, and have subtracted the interest from the payment to come up with the principal payment.
In our spreadsheet tool, 2 financial functions are available in order to calculate these components - the PPMT() and IPMT() functions.
4. The IPMT and PPMT Functions
The interest and principal payment functions are very similar to the payment function, taking in parameters for interest rate, total periods and present value.
Remember; the interest rate needs to be in the same terms as the number of periods - a 6% annual rate is very different than a 6% monthly rate!
The only additional argument required in the IPMT() function as opposed to the PMT() function is the period that the payment is taking place.
This period reflects the period in the loan, not a date. At no point in the payment formulas is date ever referred to!
Since they are so closely related, the interest IPMT() function and principal PPMT() function take on the exact same arguments.
5. Interest and Principal Function Example
Now, we'll go through an example of all three financial functions in action.
The loan that we are using, in this case, is a very simple loan, with a 6% nominal interest rate, a $100,000 principal and 10 payments on the loan.
In period 1, the payment is $13,586.80, with a $6,000 interest payment and a $7,586.80 principal. These two values add back to the $13,586.80 payment, as expected.
Notice how the IPMT() and PPMT() formulas use cell A6 as an argument, but the PMT() function doesn't.
In period 2, the payment remains the same, but the interest decreases and principal increases to reflect the fact that some of the balance was paid off in period 1.
6. Time to use the financial functions!
It's time for you to get out and use the PMT(), IPMT(), and PPMT() functions to start building your first amortization schedule!