Amortization schedule template
Here you have the lesson about counting your loan amortization fee. Learn here how to do it in Excel application and download a free template of amortization payment schedule.
Preparation of data for loan payment
Input the data regarding amortization in the cells and calculate total number of payments (i.e. payment per year x number of years).
Make four columns (i.e. Payment number, Payment, Principal, interest and remaining balance).
Inserting loan repayment schedule
Write 1 in the first cell of payment number column.
Use PMT function to calculate the payment.
Use PPMT function to calculate principal.
Use IPMT function to calculate interest.
Compare the difference between amount and payment. Since Excel by default shows payment in negative, therefore we will add the amount and payment.
Drag the first four columns (i.e. payment number, payment, principal, interest) till 20 (which is the number of Total payments). Thus, Excel will automatically calculate it.
Update the second row of the remaining balance column. Add the remaining balance of the first column to the payment of the second column.
Start dragging the second row of the remaining balance column to payment number 20 (which is the number of Total payments).
Here, you can download free amortization schedule template.
Using Excel to Create a Loan Amortization Schedule with Extra Payments
An amortization schedule is a table that shows how a loan is repaid over time. It includes the following information:
- Payment number
- Payment amount
- Principal amount
- Interest amount
- Remaining balance
You can use Excel to create an amortization schedule with extra payments. This can be useful if you want to pay off your loan faster or save money on interest.
Here is an example of an amortization schedule with extra payments:
| Payment number | Payment date | Payment amount | Principal amount | Interest amount | Remaining balance |
|---|---|---|---|---|---|
| 1 | 2023-09-16 | $1,000 | $600 | $400 | $29,000 |
| 2 | 2023-10-16 | $1,000 | $620 | $380 | $28,380 |
| 3 | 2023-11-16 | $1,000 | $640 | $360 | $27,740 |
| 4 | 2023-12-16 | $1,500 | $720 | $280 | $27,020 |
| 5 | 2024-01-16 | $1,000 | $660 | $340 | $26,360 |
In this example, the borrower is making an extra payment of $500 in the fourth month. This reduces the remaining balance by $500, which saves the borrower interest on the loan.
You can use Excel to create an amortization schedule for any type of loan, including mortgages, car loans, and student loans. By creating an amortization schedule, you can track your progress and make sure that you are on track to pay off your loan on time.
Leave a Reply