Business Loan Amortization Calculator in Excel: Steps

How-to basics · Updated November 21, 2026

Yes. You can build a business loan amortization calculator in Microsoft Excel using the PMT function for the regular payment and a short table to track each payment’s interest, principal, and remaining balance. With the loan amount in B2, annual interest rate in B3, payments per year in B4, and total number of payments in B5, calculate the payment in B7 with =-PMT(B3/B4,B5,B2).

The example below assumes a fixed-rate loan with equal payments made at regular intervals. It shows how to set up the inputs, create a schedule, interpret the results, and recognize when a basic spreadsheet does not match a lender’s terms.

What an amortization calculator shows

An amortization schedule breaks a loan into payments over time. Each payment generally covers interest accrued for that period and repays some of the principal, which is the amount borrowed. As the balance falls, the interest portion usually falls too, while the principal portion grows. A standard fixed-payment schedule assumes the interest rate, payment timing, and payment amount follow the same pattern throughout the loan.

This is useful for estimating the payment required to repay a loan by a chosen date and for seeing how much of the total cost is interest. It is not automatically a complete business-loan cost calculator: lender fees, collateral costs, late charges, and other contract terms may not be reflected in the amortization table.

Set up the loan inputs in Excel

In a blank worksheet, enter these labels in column A and the corresponding values in column B. The cell locations matter because the formulas below refer to them:

  • A2: Loan principal; B2: 100000
  • A3: Annual interest rate; B3: 8%
  • A4: Payments per year; B4: 12
  • A5: Number of payments; B5: 60
  • A6: Loan term in years; B6: 5, for reference
  • A7: Regular payment; B7: formula described below

For this illustration, the loan is $100,000 at a nominal annual rate of 8%, repaid monthly over 60 payments, or five years. Enter the rate as 8%, not 8, and use 12 payments per year for monthly installments. The term in years is a useful check, but the payment formula uses the total number of payments in B5.

With the principal in B2, annual rate in B3, payment frequency in B4, and number of payments in B5, enter this formula in B7: =-PMT(B3/B4,B5,B2). Excel returns approximately $2,027.64 per month. PMT requires a rate for each payment period, so dividing the annual rate by 12 converts this example’s annual rate to a monthly rate. The minus sign makes the result display as a positive payment because Excel treats money paid out and money received as opposite cash-flow directions.

Build the payment-by-payment schedule

Use row 10 for column headings: A10 Period, B10 Beginning balance, C10 Payment, D10 Interest, E10 Principal repaid, and F10 Ending balance. Row 11 will hold the first payment. Keep the underlying values at full precision and format the results as currency for readability; rounding every calculation to cents can create small differences in the final balance.

With the loan inputs in B2:B7 and the schedule headings in A10:F10, enter =1 in A11 for the first payment period.

With the loan amount in B2 and the first schedule row in row 11, enter =$B$2 in B11 to set the opening balance.

With the payment amount in B7, the opening balance in B11, and the monthly rate represented by B3 divided by B4, enter =MIN($B$7,B11+D11) in C11. This uses the regular payment except when the amount needed to clear the remaining balance is smaller, as it should be in the final period.

With the beginning balance in B11 and annual rate and payments per year in B3 and B4, enter =B11*$B$3/$B$4 in D11 to calculate the period’s interest.

With the payment in C11 and interest in D11, enter =C11-D11 in E11 to calculate the principal repaid.

With the beginning balance in B11 and principal repaid in E11, enter =B11-E11 in F11 for the ending balance.

For the next row, with the preceding period number in A11, enter =A11+1 in A12. With the preceding ending balance in F11, enter =F11 in B12. Copy the formulas in C11:F11 down through the remaining payment rows, and copy the period formula down as well. For this 60-payment example, the last scheduled row is row 70. In the final row, the MIN formula limits the payment to the balance plus that period’s interest, preventing an unnecessary overpayment.

Check that the ending balance is zero, or very close to zero before display rounding, after the last payment. The first month’s interest in this example is $666.67: $100,000 multiplied by 8% divided by 12. The first payment therefore repays about $1,360.97 of principal, and the balance falls to about $98,639.03. Later interest amounts decline as the balance is reduced.

Understand the example’s total cost

Multiplying the approximate monthly payment of $2,027.64 by 60 gives about $121,658.28 in scheduled payments. Subtracting the $100,000 principal gives roughly $21,658.28 in interest over the term. These are illustrative figures, not a lender quote. The exact total can differ by a few cents depending on whether the final payment is adjusted and how amounts are rounded or posted.

The schedule also makes the timing of interest visible. In the first month, most of the payment is principal, but interest is still calculated on the full opening balance. Each subsequent period uses the new, lower balance. This is why multiplying the first month’s interest by 60 would not give the total interest on a standard amortizing loan.

Match the spreadsheet to the loan terms

The right payment frequency and rate convention depend on the loan documents. A monthly installment schedule commonly uses the annual nominal rate divided by 12, as in this illustration, but a lender may calculate interest daily, use a different day-count convention, or set payment dates that create an unusual first period. The payment dates and balance calculations in a simple monthly schedule will not reproduce those details exactly.

For a non-monthly schedule, change B4 to the number of payments per year and enter the total payment count in B5. For example, a quarterly schedule generally uses 4 payments per year. Make sure the rate conversion reflects the lender’s stated terms rather than assuming that every quoted annual rate can be divided by the payment frequency without qualification.

Also establish whether fees are paid upfront or included in the amount financed. If a fee is added to the loan balance, the principal used in the schedule may be higher than the cash the business receives. A fee paid separately does not reduce the scheduled principal, though it still affects the borrower’s overall cost. Do not treat the spreadsheet’s payment or interest total as an APR calculation; APR and disclosure requirements involve rules and costs beyond this basic schedule.

When a basic fixed-payment schedule is not enough

PMT is designed for regular, equal payments at a constant periodic interest rate. It does not, by itself, model every common business-loan structure. A variable-rate loan may need a new payment calculation when the rate changes. An interest-only period, a balloon payment, irregular draws on a credit line, or seasonal payment amounts also require a different schedule design. For daily simple interest or a first payment period of unusual length, a monthly estimate may diverge from the lender’s exact figures.

Before relying on a workbook, compare its assumptions with the loan agreement or a lender-provided payment schedule. In particular, check the payment dates, rate changes, fees financed into principal, any deferred payments, and whether the last payment is expected to be different. If those features are material, ask the lender how the balance and interest are calculated or have a qualified professional review the model.

Improve readability and check for errors

Format B2 and B7 and the balance and payment columns as currency, and format B3 as a percentage. In Excel for Microsoft 365 on Windows, use Home > Number to select a number format. You can add a total row beneath the schedule to sum payments, interest, and principal. Check that total principal repaid is approximately the original principal and that each period’s ending balance becomes the next period’s beginning balance.

Common spreadsheet mistakes include entering 8 instead of 8%, using 8% as the periodic rate without dividing by 12 for a monthly schedule, or entering five as the number of payments rather than 60. Another frequent error is mixing cash-flow signs in PMT, which can make Excel show a negative payment. The negative sign before PMT in the example changes the displayed sign; it does not change the loan economics.

When copying schedule formulas, preserve the dollar signs in references such as $B$3 and $B$7. These absolute references keep the input cells fixed as formulas are filled down. The balance references, such as B11 and D11, should remain relative so each row calculates from its own period. Save a separate copy before making major changes if you want to preserve the original assumptions and schedule.

Frequently Asked Questions

What Excel formula calculates a loan payment?
For a fixed-rate loan with regular payments, use PMT. With an annual rate in B3, payments per year in B4, payment count in B5, and principal in B2, =-PMT(B3/B4,B5,B2) returns a positive payment estimate.

How do I calculate monthly interest in an amortization schedule?
Multiply the beginning balance for that month by the annual rate divided by 12. In the example layout, the first period’s interest formula is =B11*$B$3/$B$4, where B11 is the opening balance and B3 and B4 contain the annual rate and monthly payment frequency.

Why does my final loan balance not equal zero?
Small residuals can result from displayed rounding, calculations rounded to cents, an incorrect payment count, or a mismatch between the assumed and actual interest method. Keep full precision in formulas, check the rate and period inputs, and verify whether the lender adjusts the last payment.

Does this calculator include business loan fees?
No. The basic schedule calculates payments and interest based on the principal, rate, and payment terms entered. Add financed fees to the modeled principal if the contract includes them in the balance, and account separately for fees paid upfront. Confirm the treatment with the lender.

Can I use the same model for a variable-rate loan?
Not without changes. A variable rate means the interest calculation can change over time, and the payment may or may not be recalculated when the rate changes. Model each adjustment according to the contract and check the results against the lender’s terms or schedule.

Use the estimate as a planning tool

An Excel amortization calculator is a practical way to inspect a fixed-payment loan and test how principal, rate, term, and payment frequency affect the schedule. Its usefulness depends on entering the actual contract assumptions, not just the headline rate and amount. Treat the result as an estimate, and check real terms with the lender or a qualified financial professional. This information is for general education, not financial, tax, investment, or legal advice.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.