Loan Installment Calculator Excel: Formula and Example

A loan installment calculator in Excel estimates the fixed monthly payment on a fully amortizing loan from its amount, annual percentage rate (APR), and term. Enter those three figures to calculate the payment and total interest; the worksheet-style calculator below uses the standard monthly-payment formula.

Formula

The fixed monthly installment is calculated with the level-payment amortization formula:

Payment = P × r ÷ [1 − (1 + r)−n]

• P is the amount borrowed, in dollars.
• r is the monthly interest rate as a decimal: annual APR divided by 12 and then by 100. In this calculator, r = apr / 1200.
• n is the number of monthly payments: loan term in years multiplied by 12.

Total interest is estimated as monthly payment × n − P. The calculator rounds the payment to cents before using it to estimate interest. That convention is useful for a payment schedule, though a lender’s exact figures can differ because of its rounding and payment-processing methods.

Worked Example

Using the calculator’s default inputs—$20,000 borrowed, 8% APR, and a five-year term:

1. Convert APR to a monthly decimal rate: 8 ÷ 1200 = 0.0066667.
2. Find the number of payments: 5 × 12 = 60.
3. Substitute into the formula: 20,000 × 0.0066667 ÷ [1 − (1 + 0.0066667)−60].
4. Round the monthly payment to cents: $405.53.
5. Estimate total interest using that rounded payment: $405.53 × 60 − $20,000 = $4,331.80.

The payment covers interest due for the period and reduces principal. Early in the schedule, interest is a larger share of each payment; as the balance falls, more of the same payment goes toward principal. The example is an estimate based on the stated inputs, not a lender quote.

Assumptions

• The interest rate stays fixed for the full loan term.
• There are equal monthly payments, made at the end of each monthly period.
• Interest compounds monthly at APR divided by 12.
• The calculation does not include fees, taxes, insurance, private mortgage insurance, escrow, or other charges.

How to Use the Calculator in Excel

To build the same calculation in Excel, place the loan amount in one cell, APR as a percentage in another, and the term in years in a third. For example, if those values are in B1, B2, and B3, respectively, the unrounded payment formula is:

=-PMT(B2/12,B3*12,B1)

Excel’s PMT function returns a negative value when the loan amount is entered as a positive present value, because it treats payments as cash flowing out. The leading minus sign displays the payment as a positive number. Format the cell as currency and set it to two decimal places. If you want to use the explicit formula instead, enter =B1*(B2/12)/(1-(1+B2/12)^(-B3*12)) when B2 is stored as an Excel percentage, such as 8%.

For total interest consistent with the calculator’s cent-rounded payment, use =ROUND(-PMT(B2/12,B3*12,B1),2)*B3*12-B1. Confirm that the APR cell is actually a percentage, rather than the number 8 interpreted as 800%. Keep the term in years for this formula; if you enter months instead, do not multiply the term by 12 again.

What the Result Means—and What It Leaves Out

The monthly payment is the scheduled principal-and-interest installment for a fixed-rate loan that is repaid through equal payments over the selected term. It is not necessarily the full amount collected each month. A mortgage payment, for instance, may also include costs such as taxes or insurance, which this calculation does not model. Some loans also have fees or irregular payment dates that change the amount or timing of cash flows.

Total interest is the estimated sum of interest across all scheduled payments, based on the rounded monthly payment. It does not include upfront charges or other costs. A longer term generally means more payment periods and can increase total interest for the same principal and rate, even though the installment calculation spreads repayment over more months. This is a mathematical relationship, not a recommendation about which term to use.

Get the actual APR, term, payment schedule, and included charges from the lender’s disclosure or loan documents. If the rate can change, the loan has a balloon payment, payments are not monthly, or extra principal is paid, this fixed-payment formula will not reproduce the full schedule.

Common Excel Errors

• Entering APR in the wrong scale: If a cell contains 8%, Excel stores it as 0.08. Dividing that by 12 is correct. If the cell contains 8, divide by 1200 instead.
• Mixing years and months: PMT needs the number of payment periods. For a five-year monthly loan, use 60 periods.
• Reading PMT’s sign as an error: A negative result follows Excel’s cash-flow sign convention. Reverse the sign for a positive displayed installment.
• Rounding too soon: Keep the underlying rate and formula at full precision, then round the payment to cents. Rounding the monthly rate first can alter the result.
• Assuming every loan is fully amortizing: Interest-only periods, variable rates, balloon balances, fees, or nonmonthly schedules require additional inputs and a different model.

Frequently Asked Questions

What is the Excel formula for a monthly loan installment?
Use =-PMT(APR/12, years*12, amount) when APR is stored as a percentage and amount is positive. The minus sign displays the payment as positive.

Does Excel PMT include interest?
Yes. PMT calculates a level payment that covers both interest and principal, assuming the rate and payment periods supplied to the function apply throughout the loan.

How do I calculate total interest in Excel?
Multiply the monthly payment rounded to cents by the number of monthly payments, then subtract the original loan amount. This estimates interest for a fully amortizing loan without extra charges.

Why is my Excel payment different from the lender’s figure?
Check APR formatting, term units, payment frequency, and whether the quoted amount includes fees or other charges. The lender may also use different timing or rounding conventions.

Can this calculator handle extra payments or a changing rate?
No. It assumes equal monthly payments and a fixed rate. Extra payments or rate changes affect the balance schedule and need a calculation that models those events.

This result is an estimate; the lender’s disclosure gives the exact figures.

Related

• Boost Your Score Installment Loan Agreement Calculator
• Business Loan Low Installment Calculator
• Bank Rate Installment Loan Calculator and Payment Formula

in p | Loan amount | $ | 20000 | 1 | 100000000
in apr | Interest rate (APR) | % | 8 | 0.01 | 100
in yrs | Loan term | years | 5 | 0.0833 | 50
out pmt | Monthly payment | $ | p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)) | 2
out int | Total interest | $ | pmt * yrs * 12 - p | 2