Home › Templates › Loan Amortization Schedule Template
Loan Amortization Schedule Template
A monthly amortization schedule for a fixed-rate loan of up to 40 years, with optional extra principal, a summary of interest saved, and the accounting entries that go with it.
Free. Excel .xlsx format, no macros, no registration.
Contents
| Tab | Purpose |
|---|---|
| Instructions | Purpose, steps, a guide to each tab, the colour key and a list of every built-in check with its result. |
| Inputs | Company details, dates and the source data. All entry cells are shaded. |
| Schedule | One row per payment: opening balance, interest, scheduled payment, extra principal, principal paid, closing balance and cumulative interest. |
| Journal Entry | The entry produced from the schedule, with a check that debits equal credits. |
| Reconciliation | Schedule balances compared with the general ledger, with differences flagged. |
| Lists | Account mappings and dropdown values, editable to match your chart of accounts. |
Features
- Shows the payoff date, total interest, and the interest and months saved by paying extra principal.
- Extra principal can be a recurring amount, one-off amounts against specific payments, or both.
- Journal entry for the payment in the period, split between interest and principal.
- Accrued interest at period end, with its journal entry.
- Current portion of long-term debt, based on scheduled principal, with the reclassification entry.
- Reconciliation of the balance to the lender statement and the general ledger.
Instructions
- On the Lists tab, enter the five general ledger accounts.
- On the Inputs tab, enter the period end date and the loan terms.
- Enter any extra principal: a recurring amount on the Inputs tab, or one-off amounts on the Schedule tab.
- Review the summary and the schedule.
- Post the entries on the Journal Entry tab that apply to the period.
- Enter lender and ledger balances on the Reconciliation tab.
Notes
Assumes a fixed rate and monthly payments. Accrued interest uses actual days over 365; confirm the day-count basis in the loan agreement.
Related calculators
- Amortization Schedule CalculatorBuild a year-by-year amortization schedule showing principal paid, interest paid and remaining balance.
- Loan Payment CalculatorCalculate the monthly payment, total interest and total cost of a fixed-rate loan.
- Extra Mortgage Payment CalculatorSee how much time and interest an extra monthly payment takes off a mortgage or loan.
- Mortgage Payment CalculatorEstimate a monthly mortgage payment with principal, interest, property tax, insurance and HOA dues.
- Auto Loan CalculatorCalculate a car loan payment with down payment, trade-in and sales tax, plus total interest and the full cost of the car.
- Interest-Only Loan CalculatorCalculate the payment during an interest-only period and the higher payment once principal repayment begins.
- Loan Comparison CalculatorCompare two loan offers side by side: monthly payment, total interest and total cost for different rates and terms.
- APR CalculatorCalculate the annual percentage rate of a loan once upfront fees are included, alongside the payment and the total finance charge.
- Refinance Break-Even CalculatorCalculate how many months it takes for a lower payment to recover the closing costs of a refinance.
More accounting templates
- Depreciation Schedule TemplateFixed asset register with monthly depreciation, disposals, roll-forwards by class, journal entries and general ledger reconciliation.
- Bank Reconciliation TemplateTwo-sided bank reconciliation with outstanding items, book adjustments and the adjusting journal entry.
- Month-End Close Checklist TemplateClose checklist with owners, business-day due dates and status, a recurring entry register and an account reconciliation tracker.
- Accounts Receivable Aging TemplateInvoice-level aging with customer totals, allowance calculation, journal entry and control account reconciliation.
- Prepaid Expense and Accrual Schedule TemplatePrepaid amortization and accrued expense schedules with both journal entries and ledger reconciliations.
- 13-Week Cash Flow Forecast TemplateWeekly receipts and disbursements for one quarter, with closing cash, minimum balance alerts and forecast-to-actual tracking.
Need a version built for your process? Request a custom template quote.
To report an error or request a calculator or template, send feedback.
Templates are provided for general information. They are not accounting, tax or legal advice. Review the formulas and results before relying on them.