Loan Amortization Calculator
Generates a complete amortization schedule for a loan, showing the principal and interest portion of each payment. Displays the total interest cost over the life of the loan and the remaining balance after each payment.
Accounting - Loan Amortization Calculator.xlsx
Excel (.xlsx) — No macros — Works in Excel, Google Sheets, LibreOffice
What This Spreadsheet Solves
- Seeing exactly how much of each payment goes to principal vs. interest
- Calculating total interest paid over the full loan term
- Understanding how extra payments accelerate payoff
- Planning cash flow around fixed debt service obligations
- Comparing loan offers with different rates and terms
Who This Is For
- Business owners evaluating financing options
- Accountants recording loan transactions in the general ledger
- Financial analysts comparing debt structures
- Individuals planning mortgage or vehicle loan payments
Inputs
- $Loan principal
- %Annual interest rate
- #Loan term (months)
- dateStart date
- $Extra monthly payment (optional)
Outputs
- Monthly payment amount
- Principal portion per payment
- Interest portion per payment
- Remaining balance after each payment
- Total interest paid over the loan term
- Payoff date (adjusted for extra payments)
How Calculations Work
The monthly payment is calculated using the standard annuity formula based on the principal, monthly interest rate, and number of periods. Each period, interest is computed on the remaining balance, and the remainder of the payment reduces the principal. Extra payments go entirely toward principal reduction, shortening the loan term. The schedule continues until the balance reaches zero.
Example Use Case
Scenario: A $200,000 loan at 6% annual interest for 30 years (360 months), with no extra payments.
Result: Monthly payment is $1,199.10. In the first payment, $1,000 is interest and $199.10 is principal. Total interest over 30 years is $231,677. By month 180 (halfway), the remaining balance is still $142,098 because early payments are interest-heavy.
What You Get — 5 Sheets
Technical Details
Frequently Asked Questions
Can I model biweekly payments?
Yes. Change the payment frequency in CONFIG to biweekly. The template adjusts the schedule to 26 payments per year, which effectively adds one extra monthly payment annually.
How do extra payments affect total interest?
Extra payments reduce the principal faster, which reduces the interest charged in subsequent periods. Even small extra payments can save substantial interest over a long-term loan.
Does this handle variable interest rates?
No. The template assumes a fixed rate. For variable-rate loans, you would need to re-run the schedule each time the rate changes.
What if I want to see the balance at a specific date?
Find the row in the amortization schedule that corresponds to that payment period. The remaining balance column shows the exact figure.
Can I compare two different loan offers?
Duplicate the INPUT sheet with each loan's terms. The LOGIC sheet generates a separate schedule for each, and the OUTPUT sheet can show them side by side.
Download Loan Amortization Calculator
Ready to use immediately. Enter your data in the INPUT sheet, see results in OUTPUT.