An amortization schedule is a table that shows each loan payment and how it is applied to both the principal balance and the interest owed on the loan. The schedule shows the remaining balance after each payment is made.
To create an amortization schedule in Excel, you will need to use the PMT function. This function calculates the periodic payment for a loan based on constant payments and a constant interest rate.
The PMT function takes the following arguments:
- rate: The interest rate per period. For example, if you are making monthly payments, the rate per period would be the annual interest rate divided by 12.
- nper: The total number of payments for the loan. For example, if you are making monthly payments, the total number of payments would be the number of years multiplied by 12.
- pv: The present value of the loan. This is the total amount that you borrowed.
- [fv]: The future value of the loan. This is what you expect to have left over after making all of your payments. If you do not want to have any money left over after making your last payment, you can omit this argument.
- [type]: The type of payment. The default is 0. If you make your payments at the beginning of each period (known as prepaying), use 1.