Loan repayment schedule
Build the first year of a loan schedule: each month’s payment, interest, principal and balance.
1. Download your workbook
Each download has its own data, so your answers are yours alone.
2. Do the work in Excel
- Work out the payment. On the Loan sheet, in B5, use PMT to work out the monthly payment from B1, B2 and B3.
- Opening balances. On the Schedule sheet, B2 is the loan amount from the Loan sheet. Each later month opens with the previous month’s closing balance.
- Payment, interest and principal. Column C is the monthly payment. Column D is the opening balance times the annual rate divided by 12. Column E is the payment minus the interest.
- Closing balances. Column F is the opening balance minus the principal. Fill everything down to month 12.
- Total the interest. On the Loan sheet, in B6, add up the interest paid in year one.
Save the file as an Excel Workbook (.xlsx) when you’re done.