To build an amortization schedule in Excel, calculate the regular payment with PMT, split each payment into interest and principal, then carry each period’s ending balance into the next row. For a fixed-rate loan with equal monthly payments, Excel’s PMT, IPMT and PPMT functions do the calculations; a balance roll-forward makes the result easy to verify.
Set up the loan inputs
Create a small input block and label each value clearly. For a monthly schedule, enter the annual interest rate as a percentage and set payments per year to 12. The periodic rate and total number of periods must use the same cadence: divide the annual rate by payments per year, and multiply the term in years by payments per year.
As an Amazon Associate I earn from qualifying purchases.
- Principal: the amount borrowed, entered as a positive value if you want Excel’s financial functions to return borrower payments as negative amounts.
- Annual interest rate: for example,
5%. - Payments per year:
12for monthly payments. - Term in years: for example,
30. - Payment timing:
0for payments at the end of each period, or1for payments at the beginning. - Future value: usually
0for a loan intended to be fully repaid.
Microsoft defines PMT(rate, nper, pv, [fv], [type]); the rate is the interest rate per period, nper is the total number of periods, and pv is the present value of the loan. For a monthly loan, the formula is =PMT(annual_rate/payments_per_year, years*payments_per_year, principal, 0, payment_type). With a positive principal, the result is commonly negative because Excel treats money paid by the borrower as an outflow. Use a consistent sign convention throughout the worksheet. Microsoft’s PMT function documentation explains the inputs, timing and sign convention.
Calculate the scheduled payment
Suppose the principal is $180,000, the annual rate is 5%, the term is 30 years and payments are monthly at the end of each period. The formula =PMT(5%/12,30*12,180000) returns about -$966.28 under Excel’s cash-flow sign convention. For a borrower-facing table that shows payments as positive amounts, use the absolute value consistently or use a negative principal in the financial functions. Microsoft’s example reports a $966.28 monthly payment for those assumptions; it excludes insurance and taxes, so it is not a complete housing-cost estimate or a current loan offer. See Microsoft’s payment and savings formula examples.
#1 Best Overall
PMT calculates principal and interest only. It does not include taxes, insurance, reserve payments or fees that may be part of a borrower’s total bill.
Build one row for each payment period
Use a row for each scheduled payment. A practical layout is:
Rank #2
- Period
- Payment date, if known
- Beginning balance
- Payment
- Interest
- Principal
- Ending balance
Keep the period number separate from the calendar date. The financial functions calculate regular periods; a simple monthly calculation does not necessarily reproduce a loan contract’s day-count conventions or date-adjustment rules.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Repair Windows errors before they cause bigger problems3Scan for outdated or missing drivers - takes under a minuteAssume the input cells contain the periodic rate, total periods, principal and payment type. In the first schedule row, set the period to 1 and the beginning balance to the principal. Use these formulas, replacing the example cell references with the ones in your workbook:
- Payment: use the result of
PMTfor every period in the fixed-payment case. - Interest:
=IPMT(periodic_rate, period_number, total_periods, principal, 0, payment_type). - Principal:
=PPMT(periodic_rate, period_number, total_periods, principal, 0, payment_type). - Ending balance: subtract the period’s principal repaid from its beginning balance.
- Next beginning balance: link it to the previous row’s ending balance.
IPMT returns a period’s interest component and PPMT returns its principal component. Their period numbering starts at 1. With the standard borrower cash-flow convention, both can return negative values; if the schedule shows positive amounts, normalize the signs consistently before subtracting principal from the balance. See Microsoft’s documentation for IPMT and PPMT.
Use a visible balance calculation as a cross-check
For a fixed-rate, fixed-payment loan, you can calculate each row directly from its beginning balance: interest equals beginning balance multiplied by the periodic rate; principal equals the payment minus interest; ending balance equals beginning balance minus principal. This makes the balance movement visible and provides a check against the financial-function results. The formulas are equivalent only when the payment timing and sign convention are handled consistently.
Rank #4
Display amounts to cents, but do not round intermediate calculations in every row unless the contract requires it. Repeated rounding can leave a small balance at the end; the actual final payment may need adjustment under the contract.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Check the schedule before relying on it
- Make sure the periodic rate and number of periods use the same payment frequency. For monthly payments, an annual rate of 5% becomes
5%/12, and a 30-year term becomes30*12periods. - Confirm whether payments occur at the beginning or end of each period. In
PMT,IPMTandPPMT,type0 or omitted means end-of-period payments;type1 means beginning-of-period payments. - Check that interest and principal reconcile to the scheduled payment once signs are normalized.
- Verify that every ending balance links to the next row’s beginning balance, with no skipped or duplicated periods.
- At the final period, expect the balance to be approximately zero before rounding effects. Investigate a residual rather than hiding it; it can result from rounding or a mismatch between the simplified schedule and the contract’s final-payment rules.
Choose the repayment pattern that matches the contract
The standard PMT/IPMT/PPMT schedule models a constant rate and equal total payments. Another pattern is equal-principal repayment: the principal portion stays level, while the interest and total payment decline as the balance falls. Microsoft documents ISPMT for calculating interest in this even-principal pattern; its period numbering starts at zero, unlike the period numbering for IPMT and PPMT. Each payment in an even-principal schedule is the equal principal amount plus that period’s interest. Microsoft’s ISPMT documentation describes the calculation.
| Repayment pattern | Payment shape | Principal per period | Excel functions |
|---|---|---|---|
| Equal total payment | Payment stays level for a constant-rate loan. | Changes from period to period. | PMT, IPMT and PPMT. |
| Equal principal | Payment declines as interest falls. | Stays level. | ISPMT calculates interest; principal is set evenly across periods. |
These are different ways a loan can be structured, not interchangeable spreadsheet settings. Use the pattern specified by the loan contract. A variable-rate loan, irregular payment dates, fees or other contract-specific accrual rules require a model that accounts for those terms; the standard fixed-rate annuity functions alone do not establish the actual schedule.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
