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 schedule.1. Input the data regarding amortization in the cells and calculate total number of payments (i.e. payment per year x number of years).total number of payments2. Make four columns (i.e. Payment number, Payment, Principal, interest and remaining balance).Make four columns

3. Write 1 in the first cell of payment number column.

write first cell

4. We will use PMT function to calculate the payment.

PMT function

5. We will use PPMT function to calculate principal.

PPMT function

6. We will use IPMT function to calculate interest.

IPMT function

7. We will simply compare the difference between amount and payment. Since Excel by default shows payment in negative, therefore we will add the amount and payment.

add amount and payment

8. We will 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.

drag formulas

9. Now, we will update the second row of the remaining balance column. We will add the remaining balance of the first column to the payment of the second column.

update second row

10. Finally, we will start dragging the second row of the remaining balance column to payment number 20 (which is the number of Total payments).

ready amortization schedule template


You can download the Template here – Download
Previous articleAvoid Errors Using IFERROR-Everyone Should Know
Next articleLinking Text Box To A Specific Cell