Monthly Payment in Excel: Formula and Example

To calculate a fixed monthly loan payment in Excel, use the PMT function with the monthly interest rate, the number of monthly payments, and the loan amount. This calculator shows the same payment formula and estimates total interest from the rounded monthly payment; enter the rate and term supplied by the lender.

Formula

Excel’s PMT function calculates a level payment that pays off a loan through equal payments over a fixed number of periods. For a monthly schedule, the rate and number of periods must both be monthly:

=PMT(annual_rate/12, years*12, -loan_amount)

The minus sign makes Excel display the payment as a positive cash amount when the loan amount is entered as a positive number. In this page’s calculator, the equivalent mathematical formula is:

Payment = P × r / (1 − (1 + r)−n)

• P is the amount borrowed, in dollars.
• APR is the annual percentage rate entered as a percent, such as 8 for 8%.
• r is the monthly rate as a decimal: APR ÷ 1200.
• n is the number of monthly payments: loan term in years × 12.

The payment is rounded to the nearest cent. Total interest here is the sum of those rounded payments minus the starting loan amount. Excel’s PMT function returns a periodic payment; it does not automatically include fees or other charges in the amount shown.

Worked Example

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

1. Convert the annual rate to a monthly decimal: r = 8 / 1200 = 0.0066666667.
2. Convert the term to monthly payments: n = 5 × 12 = 60.
3. Substitute into the payment formula: 20,000 × 0.0066666667 / (1 − (1 + 0.0066666667)−60).
4. The unrounded payment is about $405.5277, which rounds to $405.53 per month.
5. Multiply the rounded payment by 60: $405.53 × 60 = $24,331.80. Subtract the $20,000 principal: estimated total interest is $4,331.80.

The displayed total uses the rounded monthly payment, so it may differ slightly from a total computed using the unrounded PMT result. The exact final payment on an actual amortization schedule can also be adjusted for rounding.

Assumptions

• The interest rate remains fixed for the full term.
• Payments are equal, made monthly, and treated as end-of-period payments, as in Excel’s PMT function when its final argument is omitted or set to 0.
• Interest accrues monthly at APR divided by 12.
• The calculation excludes fees, taxes, insurance, PMI, and escrow; these are not included in the inputs.

How to Enter the Formula in Excel

If cells contain the inputs, a formula could be =PMT(B2/12,B3*12,-B1), where B1 holds the loan amount in dollars, B2 holds the annual rate as a decimal (for example, 8% or 0.08), and B3 holds the term in years. Excel accepts a rate typed as 8% as 0.08, so dividing that value by 12 gives the monthly rate. If the rate cell instead contains the number 8, use =PMT(B2/1200,B3*12,-B1).

In the PMT function, the arguments are rate per period, total number of periods, present value, future value, and payment timing. The future-value argument can be omitted for a loan intended to be fully repaid; the timing argument can also be omitted for end-of-month payments. Set timing to 1 only when payments are made at the beginning of each period, because that changes the result.

When to Use It and How to Read the Result

PMT is useful for estimating the scheduled payment on a conventional fixed-rate, fully amortizing loan with equal monthly payments. It can help you understand how the entered principal, rate, and term translate into a payment and an approximate total interest amount. It is a mathematical estimate, not a lender quote or an approval decision.

The payment is not necessarily the entire amount due each month. For example, a mortgage bill may include items such as taxes or insurance, and a loan may have fees that are not part of the principal entered here. Use the lender’s stated payment and disclosure figures when exact contractual amounts matter. Enter the actual APR and term from the lender rather than treating the example’s 8% and five years as current or typical figures.

Common Errors and Limits

• Mixing annual and monthly units: PMT needs a monthly rate and a monthly count for monthly payments. Do not use an annual rate with a count of months.
• Entering 8 instead of 8%: A cell containing 8 means 800% to Excel’s percentage arithmetic. Format the cell as a percentage or use 8/1200 in the rate calculation.
• Forgetting the cash-flow sign: A positive loan amount and a negative present-value argument make PMT return a positive payment. Reversing the signs reverses the result’s sign.
• Assuming every loan is level-payment: Variable-rate loans, interest-only periods, balloon payments, irregular payment dates, and extra principal payments do not follow this simple schedule throughout their full term.
• Confusing APR with a periodic rate: The formula converts the entered annual percentage rate to a monthly rate by dividing by 12. A lender may provide other pricing details or fees that the simplified calculation does not model.

For an amortization schedule, calculate each period’s interest from the outstanding balance and monthly rate, then subtract that interest from the payment to find the principal reduction. The balance changes each month, so the interest portion generally declines as principal is repaid. Extra payments or a changed rate require a revised schedule rather than relying on the original PMT result.

Frequently Asked Questions

What is the Excel formula for a monthly loan payment?
Use =PMT(annual_rate/12, years*12, -loan_amount) when the annual rate is entered as a decimal or percentage and the loan amount is positive. The minus sign returns a positive payment.

Should I enter the APR as 8 or 0.08?
If the cell is formatted as a percentage and contains 8%, Excel stores it as 0.08; divide by 12. If it contains the plain number 8, divide by 1200 to convert 8 percent per year to a monthly decimal.

Does PMT include interest?
Yes. For a standard amortizing loan, each level payment covers that period’s interest and repays part of the principal. The split between interest and principal changes over time.

Does the monthly payment include taxes and insurance?
Not in this calculation. PMT uses the loan amount, rate, term, and payment timing. Taxes, insurance, escrow, and fees need separate figures if you want to estimate a broader monthly bill.

Why does my Excel result differ by a few cents?
Excel may retain extra decimal places internally, while a lender’s schedule rounds interest and payments according to its servicing methods. Timing, fees, a different rate convention, or a final adjusted payment can also cause a difference.

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

Related

• Bi-Monthly Payments in a Year: What the Term Means
• BMW X1 Monthly Payment Calculator and Cost Factors
• Bi-Monthly Payment Meaning and Calculator

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 | $ | round(p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)), 2) | 2
out int | Total interest | $ | pmt * yrs * 12 - p | 2