Yes, you can build a fully functional loan amortization schedule in Excel using the PMT, IPMT, and PPMT functions, or you can download a pre-built template from Microsoft’s official template gallery. The simplest approach is to use Excel’s built-in template: open Excel, search for “loan amortization” in the template search box, and choose one that fits your loan type. For full control and customization, however, building your own is straightforward and takes less than five minutes once you know the formulas.
What a Loan Amortization Schedule Actually Shows
An amortization schedule is a table that breaks down every payment on a loan into two parts: the interest portion and the principal portion. For each period (usually monthly), it shows the beginning balance, the payment amount, how much of that payment goes to interest, how much goes to reducing the principal, and the remaining balance after the payment. Early payments are mostly interest; later payments are mostly principal. This is because interest is calculated on the current outstanding balance, which is highest at the start.
For example, a $200,000 mortgage at 6% annual interest over 30 years (360 monthly payments) has a monthly payment of about $1,199.10. The first payment splits into $1,000.00 interest and $199.10 principal. By payment 360, the interest portion is $5.95 and the principal portion is $1,193.15. The schedule shows this progression clearly.
How to Build a Basic Amortization Schedule in Excel
Assume you have the following inputs in cells B1 to B4:
- B1: Loan amount (e.g., 200000)
- B2: Annual interest rate (e.g., 6% entered as 0.06)
- B3: Number of years (e.g., 30)
- B4: Payments per year (e.g., 12)
Then set up your table starting at row 7. In A7 put “Period”, B7 “Payment”, C7 “Interest”, D7 “Principal”, E7 “Balance”. In A8, enter 1. Fill down to A367 (for 360 payments). In B8, enter the payment formula:
=PMT($B$2/$B$4, $B$3*$B$4, -$B$1)
This returns $1,199.10 for the example. The negative sign on the loan amount makes the payment positive. Copy B8 down to B367. In C8, enter the interest for the first period:
=E7*($B$2/$B$4)
Assuming E7 holds the initial balance (put =B1 in E7). This gives $1,000.00. In D8, enter the principal portion:
=B8-C8
This gives $199.10. In E8, the new balance:
=E7-D8
This gives $199,800.90. Select C8:E8 and drag down to row 367. The final balance should be $0.00 (or a few cents off due to rounding).
Key Excel Functions for Amortization
Excel has three core functions for loan math, all available in every version:
PMT(rate, nper, pv)– returns the payment per period. Rate is the periodic interest rate (annual rate divided by payments per year). Nper is the total number of payments (years times payments per year). Pv is the present value (loan amount). Use a negative pv to get a positive payment.IPMT(rate, per, nper, pv)– returns the interest portion for a specific period. The “per” argument is the period number (1 for first payment).PPMT(rate, per, nper, pv)– returns the principal portion for a specific period.
You can use IPMT and PPMT directly instead of calculating interest manually. For period 1 in the example, =IPMT($B$2/$B$4, 1, $B$3*$B$4, -$B$1) returns -$1,000.00 (negative because it’s a cash outflow). Add a minus sign to make it positive: =-IPMT(...). Similarly, =-PPMT(...) returns $199.10.
How to Choose a Template: Built-in vs. Custom
Microsoft provides several official loan amortization templates in Excel. To find them, open Excel, click File > New, type “loan amortization” in the search box, and press Enter. Options include a simple schedule, a schedule with extra payments, and a car loan calculator. These templates are free and pre-formatted, with formulas already in place. They are a good starting point if you want a quick solution.
However, built-in templates often have limitations: they may not allow variable interest rates, they might not handle extra payments cleanly, or they may lock certain cells. Building your own gives you full control over assumptions like payment frequency (monthly, bi-weekly, quarterly) and allows you to add extra payment columns. If you need a professional-looking schedule for a business or legal context, custom is often better because you can verify every formula.
Common Problems and How to Fix Them
Several issues trip up users when working with amortization templates:
- Negative payment values – If your PMT returns a negative number, it’s because you entered the loan amount as positive. Use a minus sign before the pv argument, or multiply the result by -1.
- Final balance not zero – This happens due to rounding. Payments are rounded to cents, so the last payment may be slightly off. Fix by making the final payment equal to the remaining balance plus interest, or use the ROUND function in your formulas. For example, wrap PMT in ROUND:
=ROUND(PMT(...),2). - Incorrect interest rate – Always divide the annual rate by the number of payments per year. If you enter 6% as 6 instead of 0.06, your payment will be 100 times too high. Use the percent format or enter 0.06.
- Extra payments not reflected – If you add extra payments, you must recalculate the balance manually. The standard PMT formula doesn’t account for them. You’ll need to adjust the schedule by reducing the balance before calculating the next interest.
- Copying formulas incorrectly – Use absolute references (with $) for the input cells, as shown in the example. Otherwise, dragging down will shift the references and produce errors.
Adding Extra Payments to Your Schedule
Many borrowers want to see the impact of making extra principal payments. To add this, insert a column for “Extra Payment” (say column F). In F8, enter the extra amount (e.g., 100). Then modify the principal formula in D8 to include the extra payment:
=B8-C8+F8
And the balance formula in E8 becomes:
=E7-D8
Now, when you drag down, the balance will reduce faster. However, you must ensure that the payment in B8 remains the same as the original (unless you’re recalculating). The schedule will show a zero balance earlier than the original term. To see the number of periods, you can use a formula to count how many periods until balance is zero, or simply look at the row where balance hits zero.
For a more sophisticated approach, you can use the NPER function to calculate how many payments are needed with extra payments, but that requires a different setup. The simple column method is clear and works for most users.
Comparing Templates: Excel vs. Google Sheets vs. Online Calculators
Excel templates are offline, fully customizable, and work with all Excel functions. Google Sheets has similar templates (search in the template gallery) and allows real-time collaboration, but its functions are mostly identical to Excel. Online calculators (like those on bank websites) are quick but often don’t let you export or edit the schedule. They also may not show the full breakdown for free. Excel is the best choice if you need to keep a permanent record, run scenarios, or integrate with other financial data.
One key difference: Google Sheets uses the same PMT, IPMT, and PPMT functions, so the formulas above work identically. The only difference is the menu path for finding templates: in Google Sheets, click File > New > From template gallery, then look for “Loan amortization schedule”.
Safety and Accuracy Tips
Before relying on any amortization schedule, verify the math with a known example. Use a simple loan like $10,000 at 5% for 3 years. The monthly payment is $299.71. Check that your schedule’s first interest is $41.67 (10,000 * 0.05/12). If your numbers don’t match, review your formulas.
Also, be aware that amortization schedules assume a fixed interest rate and fixed payment. If you have an adjustable-rate mortgage, the schedule will change when the rate changes. Always check your loan agreement for the exact terms, including any prepayment penalties. This article is for educational purposes, not financial advice. Consult a qualified professional for decisions about your specific loan.
Frequently Asked Questions
How do I create an amortization schedule in Excel without using a template?
Follow the steps in the “How to Build a Basic Amortization Schedule” section above. You need three columns for payment, interest, and principal, and you’ll use the PMT, IPMT, and PPMT functions. The key is to set up your input cells with absolute references and then drag formulas down for the total number of payments.
What is the difference between PMT and IPMT in Excel?
PMT returns the total periodic payment (interest + principal). IPMT returns only the interest portion for a specific period. For example, PMT for a $100,000 loan at 5% over 30 years is $536.82 per month. IPMT for period 1 is -$416.67 (interest only). PPMT for period 1 is -$120.15 (principal only). Together, IPMT and PPMT sum to PMT.
Can I use an amortization schedule for a car loan?
Yes, the same schedule works for any fixed-rate loan, including car loans, personal loans, and mortgages. Just enter the loan amount, interest rate, and term. For car loans, terms are typically 36 to 72 months, and interest rates are often higher than mortgages. The schedule will show how much you owe each month and how much interest you pay over time.
Why does my amortization schedule show a negative balance at the end?
This usually happens because of rounding. If your payment is rounded up, the final payment may overpay slightly. To fix, adjust the last payment to equal the remaining balance plus interest, or use the ROUND function on all formulas. Alternatively, you can set the final payment to zero and manually calculate the last payment as the outstanding balance plus interest.
How do I calculate total interest paid over the life of the loan?
Sum the interest column in your schedule. In Excel, if your interest amounts are in C8:C367, use =SUM(C8:C367). For the example of a $200,000 mortgage at 6% for 30 years, the total interest is approximately $231,676.38. You can also use the CUMIPMT function: =CUMIPMT($B$2/$B$4, $B$3*$B$4, $B$1, 1, $B$3*$B$4, 0) which returns -231,676.38 (negative as an outflow).
Is it better to use a template or build my own schedule?
If you need a quick, reliable schedule and don’t want to learn formulas, use a built-in template. If you want to customize extra payments, variable rates, or payment frequency, build your own. Building your own also helps you understand how amortization works, which is valuable for comparing loan offers. Templates are fine for personal use, but custom schedules are easier to verify and adapt.
Related
- Merge Two Tables in Excel Power Query: Full Guide
- Excel Power Query Prevent Refresh: How to Stop It
- Excel Power Query Slow to Refresh? Fixes That Work
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Embed a Loom Video in PowerPoint and Play ItTo put a Loom video in PowerPoint, try inserting Loom’s embed code with Insert > Video > Online…
- How to Apply Styles in a Word Document for Clear FormattingTo apply a style in Microsoft Word, click in a paragraph or select text, then choose a style…
- Excel Accounting Number Format: Show Negative Numbers in RedYes. To show negative values in red while keeping an accounting-style currency display in Excel for Microsoft 365…
- Scenario Manager in Excel: How It Works and When to Use ItScenario Manager in Excel is a What-If Analysis tool for saving different sets of input values and comparing…
- Access vs Excel: Pros, Cons, and Best Uses ComparedMicrosoft Access is usually the better choice for managing related records, enforcing consistent data entry, and supporting a…
- Insert a Virtual Signature in Word: Steps and OptionsTo insert a virtual signature in Word, add a picture of your handwritten signature using Insert > Pictures…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.