How to Create a Loan Amortization Schedule in Excel

A loan amortization schedule shows the interest payments for a fixed loan and how the principal balance is reduced over time. The schedule provides a detailed overview of each payment, allowing you to see how much of your payment goes towards the principal and how much goes towards interest. Moreover, you can easily create this schedule using Microsoft Excel. Follow the first step below to create your own loan schedule from home without paying for external services!

Label cells A1 to A4 as follows: Loan Amount, Interest, Month, and Payment.

Calculate the payment in cell B4 by entering " =ROUND(PMT($B$2/12,$B$3,-$B$1,0), 2)" into the formula bar (without the quotation marks) and press Enter.

• The dollar sign in the formula represents an absolute reference, ensuring the formula pulls data from specific cells even when copied elsewhere in the spreadsheet.
• The interest rate should be divided by 12 because it is an annual rate, and the calculation is done monthly.
• For example: if you borrow 3,450,000,000 VND at an interest rate of 6% for 30 years (360 months), your first payment will be 20,684,590 VND.

Label the columns from A6 to H7 as follows: Term, Beginning Balance, Payment, Principal, Interest, Cumulative Principal, Cumulative Interest, and Ending Balance.

Fill in the Term column.

• Enter the month and year of the first payment in cell A8. You may need to format the column to display the correct month and year.
• Select the cell, click and drag down to fill up to cell A367. Ensure that the Auto Fill option is set to "Fill Months".

Enter the remaining information in cells B8 to H8.

• Input the initial loan balance in cell B8.
• Type "=$B$4" in cell C8 and press Enter.
• Set up a formula in cell E8 to calculate the interest on the initial balance for that period. The formula will look like: "=ROUND($B8*($B$2/12), 2)". The dollar sign indicates a relative reference here, allowing the formula to find the corresponding cell in column B.
• In cell D8, subtract the interest from cell E8 from the total payment in cell C8. Use a relative reference to ensure the formula copies correctly. The formula will look like: "=$C8-$E8".
• In cell H8, create a formula that subtracts the principal from the initial balance. The formula is: "=$B8-$D8".

Proceed with the schedule by filling cells B9 to H9.

• Cell B9 will contain a relative reference to the previous period's ending balance. Enter "=$H8" in this cell and press Enter. Copy cells C8, D8, and E8, then paste them into C9, D9, and E9. Copy cell H8 and paste it into cell H9. This is where relative references begin to function properly.
• In cell F9, create a formula to calculate the cumulative principal paid. The formula will be: "=$D9+$F8". Similarly, for the accumulated interest in cell G9, use a formula like: "=$E9+$G8".

Complete the repayment schedule.

• Select cells B9 to H9, hover your mouse over the bottom right corner of the selected range until the cursor changes to a cross, then click and drag the selection down to row 367. Release the mouse button.
• Make sure the Auto Fill option is set to "Copy Cells" and that the final balance reads zero.

• If the final balance is not zero, double-check to ensure that you’ve used absolute and relative references as instructed, and that the cells have been copied correctly.
• You can now scroll to any point in the loan repayment process to see how much of each payment goes towards the principal, how much goes towards interest, and the total principal and interest you’ve paid so far.

• This method is only applicable to home loans that are calculated monthly. For car loans or loans where interest is paid daily, the schedule will only provide an approximate estimate of the interest rate.

• Calculator
• Microsoft Excel
• Loan details