An amortization schedule is a list of payments for a mortgage or loan, which shows how each payment is applied to both the principal amount and the interest. The schedule shows the remaining balance still owed after each payment is made, so you know how much you have left to pay. To create an amortization schedule using Excel, you can use our free amortization calculator which is able to handle the type of rounding required of an official payment schedule. You can use the free loan amortization schedule for mortgages, auto loans, consumer loans, and business loans. If you are a small private lender, you can download the commercial version and use it to create a repayment schedule to give to the borrower.
This spreadsheet-based calculator creates an amortization schedule for a fixed-rate loan, with optional extra payments.
Start by entering the total loan amount, the annual interest rate, the number of years required to repay the loan, and how frequently the payments must be made. Then you can experiment with other payment scenarios such as making an extra payment or a balloon payment. Make sure to read the related blog article to learn how to pay off your loan earlier and save on interest.
The payment frequency can be annual, semi-annual, quarterly, bi-monthly, monthly, bi-weekly, or weekly. Values are rounded to the nearest cent. The last payment is adjusted to bring the balance to zero.
Loan Payment Schedules: The workbook also contains 2 other worksheets for basic loan payment tracking. The difference between the two has to do with how unpaid interest is handled. In the first, unpaid interest is added to the balance (negative amortization). In the second (the one shown in the screenshot), unpaid interest is accrued in a separate interest balance.
Note: In both cases, the Payment Date column is for reference only. This spreadsheet handles loans where calculations are not based on payment date. See the Simple Interest Loan spreadsheet if you have a loan that accrues interest daily and the payment date matters.
