Amortization Schedule Excel Formula: Build It Step by Step

Formulas & functions · Updated November 18, 2026

To calculate a fixed monthly loan payment in Excel, put the principal in B1, the annual interest rate in B2, and the number of monthly payments in B3; with those inputs, enter =-PMT(B2/12,B3,B1) in B4. Then calculate each period’s interest from its opening balance, subtract interest from the payment to find principal repaid, and subtract principal from the opening balance to get the closing balance.

What an amortization schedule shows

An amortization schedule lays out how each loan payment is divided between interest and principal over time. It also tracks the balance still owed after each payment. Early in a typical fixed-rate loan, the balance is relatively high, so more of each payment goes to interest. As the balance falls, the interest portion usually shrinks and more of the same payment goes toward principal.

The schedule is useful for understanding the timing of repayment, estimating the remaining balance after a particular payment, and checking how an extra payment might affect the loan. It is a calculation based on the inputs and assumptions you provide; it does not replace the lender’s official statement, which can reflect fees, payment timing, rate changes, and contract-specific rules.

Set up the loan inputs

For a straightforward monthly, fixed-rate example, use this layout in Excel for Microsoft 365 on Windows:

  • B1: loan principal, such as 20000
  • B2: annual interest rate, entered as a percentage, such as 6%
  • B3: number of monthly payments, such as 36
  • B4: calculated monthly payment

With principal in B1, the annual rate in B2, and the number of payments in B3, enter =-PMT(B2/12,B3,B1) in B4. For a $20,000 loan at 6% annual interest over 36 monthly payments, the result is about $608.45 per month. Format B1 and B4 as currency and B2 as a percentage if you want the inputs and result to be easier to read.

PMT takes a rate per payment period, the total number of payment periods, and the present value of the loan. Dividing the annual rate by 12 assumes monthly periods and a nominal annual rate applied monthly. If the loan has a different payment frequency, use the rate and number of periods that match that frequency. A payment at the start of each period also differs from one at the end; PMT’s optional fifth argument, called type, is 0 for end-of-period payments and 1 for beginning-of-period payments. The formula above uses the default, 0.

Build the schedule columns

Starting in row 8, create these headings: Period, Date, Beginning balance, Payment, Interest, Principal, and Ending balance. Use row 9 for the first payment. The following formulas assume the inputs are in B1:B4 as described above and the schedule headings occupy A8:G8.

  • In A9, type 1 as the first period number. In B9, enter the first payment date as a date value.
  • With the opening balance for the first payment in C9 and the annual rate in B2, enter =C9*$B$2/12 in E9 to calculate that period’s interest.
  • With the scheduled payment in B4, opening balance in C9, and interest in E9, enter =MIN($B$4,C9+E9) in D9. This caps the final payment at the amount due rather than allowing a small overpayment.
  • With the payment in D9 and interest in E9, enter =D9-E9 in F9 to calculate principal repaid.
  • With the opening balance in C9 and principal repaid in F9, enter =MAX(0,C9-F9) in G9 to calculate the ending balance without showing a negative amount from rounding.

For period 1 in the example, interest is $100.00: $20,000 multiplied by 6% and divided by 12. The payment is about $608.45, so about $508.45 goes to principal, leaving an ending balance of about $19,491.55. These displayed amounts are rounded to cents; Excel keeps the underlying calculation precision unless you explicitly round it.

For the next row, with the prior period in row 9, enter =A9+1 in A10 to advance the period number, and enter =G9 in C10 to carry forward the ending balance as the next opening balance. With the date in B9 and the new period number in A10, enter =EDATE($B$9,A10-1) in B10 to produce the corresponding monthly date. Then copy the interest, payment, principal, and ending-balance formulas from E9:G9 and D9 down to the remaining rows, adjusting their relative row references automatically. The second payment’s interest is about $97.46, and its ending balance is about $18,980.56 when displayed to cents.

Fill down and check the result

Make enough schedule rows for the number of payments in B3. In this example, that means 36 payment rows, from row 9 through row 44. Select the formulas in the first schedule row and use the fill handle, or copy and paste them down the remaining rows. Excel’s dollar signs in references such as $B$2 keep the input cell fixed while the row references for each period change.

Format the payment, interest, principal, and balance columns as currency with two decimal places. Formatting changes what you see, not the stored value. Avoid adding ROUND to every intermediate formula unless the loan’s terms require cent-by-cent rounding at each step: rounding each period can create small differences from a schedule that carries full precision and rounds only for display. If the lender rounds interest to cents each month, model that rule consistently and compare the result with the lender’s disclosure.

Useful checks include confirming that each row’s payment equals interest plus principal, that one row’s ending balance equals the next row’s beginning balance, and that the final balance is zero or very close to zero. In this layout, the payment check is the relationship between D9, E9, and F9; the balance check is the relationship between G9 and C10. A residual of a few cents can arise from rounding or a mismatch in assumptions, while a large residual usually points to an incorrect rate, term, payment frequency, or formula reference.

Understand the formulas and sign convention

Excel’s PMT function follows a cash-flow sign convention: money borrowed and money paid back have opposite signs. With a positive principal entered in B1, PMT normally returns a negative payment because it treats the loan proceeds as money received. Placing a minus sign before PMT, as in the example, makes the displayed payment positive. Another valid approach is to enter the loan principal as a negative value, but mixing sign conventions can make the payment or balance confusing.

The schedule formulas express the basic accounting for a standard payment period:

  • Interest for the period = opening balance × periodic interest rate.
  • Principal repaid = payment − interest for that period.
  • Closing balance = opening balance − principal repaid.

The balance declines only when the payment exceeds the interest due. If a payment is smaller than accrued interest, the balance can grow instead, depending on the loan terms. That situation is not a normal fully amortizing schedule and should not be “fixed” by forcing a zero balance; check the contract and the assumptions behind the calculation.

Extra payments and changing loan terms

A standard PMT schedule assumes the same periodic payment, interest rate, and timing throughout the loan. To model an extra principal payment, add an Extra principal column and subtract that amount from the balance after calculating the scheduled interest and payment. For example, with the extra amount in H9, the adjusted ending balance could be calculated as =MAX(0,C9-F9-H9), assuming the scheduled principal is in F9 and H9 contains a nonnegative extra payment. The next row’s beginning balance must then link to this adjusted ending balance.

That simple adjustment illustrates how the balance changes, but it does not by itself establish how a lender will apply an extra payment. Some lenders require instructions to apply extra money to principal, and the effect on the due date or future payments depends on the agreement. Variable-rate loans also need a rate schedule and recalculated payments when rates change; a single PMT result is not a complete model of a variable-rate loan.

For biweekly, weekly, or other nonmonthly payment plans, match the periodic rate and number of periods to the actual contract. Do not assume that dividing an annual rate by 12 works for every frequency or compounding convention. Likewise, a mortgage with taxes, insurance, escrow, fees, or mortgage insurance may have a total bill that is larger than its principal-and-interest payment. A basic amortization schedule normally covers principal and interest only unless you add separate columns for other charges.

Common Excel problems

  • Payment looks negative: This is usually PMT’s cash-flow sign convention. Use a leading minus sign in front of PMT when the principal is entered as positive.
  • Balance does not reach zero: Check that the term counts payment periods, the rate is a percentage rather than a whole number such as 6, and the schedule contains the right number of rows. Also check that the payment formula uses the periodic rate.
  • Final balance is slightly below zero: A fixed displayed payment may be rounded. Keep full precision in the calculation and cap the last payment at the amount due, as the MIN formula does.
  • Interest is much too high: The annual percentage may have been divided by the wrong number, or the formula may use the annual rate directly instead of a monthly rate.
  • Dates drift or repeat: Use actual date values and a monthly date formula such as EDATE, rather than adding a fixed number of days. Calendar months have different lengths.

In Excel for Microsoft 365 on Windows, the formulas and functions described here work in a standard worksheet. EDATE is also available in current Excel for Mac and Excel for the web. The ribbon layout and keyboard shortcuts can differ between Windows, Mac, and the web, but the worksheet formulas themselves are the same. If formulas appear as text, check that the cells are not formatted as Text and that the entry begins with an equals sign.

When a spreadsheet is not enough

An Excel schedule is a transparent estimate when its assumptions match the loan, but lender calculations may use specific day-count rules, payment posting dates, rounding conventions, fees, or irregular first periods. Those details can make an official payoff amount differ from the schedule. Use the lender’s current payoff quote for a real payoff, and check the loan documents or ask the lender how payments and extra principal are handled.

This example is educational and is not financial, tax, investment, or legal advice. For a decision involving a real loan, verify the interest rate, payment frequency, fees, and repayment rules with the lender or a qualified professional.

Frequently Asked Questions

What is the Excel formula for a monthly loan payment?
Use =-PMT(annual_rate/12,number_of_months,loan_amount) when the annual rate, monthly term, and positive loan amount are the inputs. For example, if those values are in B2, B3, and B1, respectively, use =-PMT(B2/12,B3,B1).

How do I calculate the interest for each month?
Multiply the opening balance by the monthly rate. With the opening balance in C9 and annual rate in B2, the formula is =C9*$B$2/12. The result is that period’s interest, not the total interest over the whole loan.

How do I calculate the principal portion of a payment?
Subtract that period’s interest from its payment. With payment in D9 and interest in E9, use =D9-E9. The remainder is the amount that reduces the loan balance.

Why is my final loan balance not exactly zero?
Common causes include rounding displayed payments, using the wrong period count or rate, or calculating interest with a different convention from the lender. Keep full precision in formulas, cap the last payment at the amount due, and compare the assumptions with the loan terms.

Can I use this schedule for a variable-rate loan?
Not unchanged. The schedule assumes one fixed rate for all periods. A variable-rate model needs the applicable rate for each period and may require recalculating the payment when the rate changes; confirm the lender’s rules before relying on the result.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.