To calculate a loan payment from an APR in Excel, convert the annual percentage rate to a monthly rate and use the PMT function: =-PMT(APR/12, years*12, loan_amount). This page’s calculator uses the same fixed-payment method: enter the loan amount, annual APR, and term in years to see the monthly payment and total interest.
Formula
For a fixed-rate loan repaid through equal monthly payments, the payment formula is:
Payment = P × r / (1 − (1 + r)−n)
In Excel, the equivalent is =-PMT(apr/12, yrs*12, p) when APR is entered as a decimal such as 0.08, or =-PMT(apr/1200, yrs*12, p) when APR is entered as a percentage number such as 8. The minus sign displays the payment as a positive amount because Excel treats a loan received and payments made as opposite cash flows. Round the displayed payment to cents; the calculator does so before calculating total interest.
- P is the initial loan principal, in dollars.
- r is the monthly interest rate as a decimal: annual APR divided by 12, or by 1,200 when APR is entered as a percentage number.
- n is the number of monthly payments: years multiplied by 12.
- Payment is the level monthly payment, in dollars.
Total interest in this estimate is monthly payment × number of payments − principal. The payment is rounded to the nearest cent first, so this total follows the displayed payment rather than an unrounded intermediate result.
Worked Example
Using the calculator’s default inputs—$20,000 principal, 8% APR, and five years—the monthly rate is 8 / 1200 = 0.0066667, and the number of payments is 5 × 12 = 60.
- Substitute into the payment formula:
20,000 × 0.0066667 / (1 − (1.0066667)−60). - The monthly payment is approximately $405.53, rounded to the nearest cent.
- Total of the rounded payments:
$405.53 × 60 = $24,331.80. - Estimated total interest:
$24,331.80 − $20,000 = $4,331.80.
Assumptions
- The APR is fixed throughout the loan term.
- Payments are equal and made monthly, with the standard amortizing-loan calculation.
- Interest is modelled monthly at APR divided by 12; this calculator does not model daily accrual or a different compounding convention.
- Fees, taxes, insurance, PMI, and escrow are not included. Any lender-specific charges must be checked separately.
Using the APR Formula in Excel
Set up worksheet cells so the inputs and formulas are easy to audit. For example, put principal in B2, APR as a percentage in B3, and the term in years in B4. If B3 contains the number 8 rather than the Excel percentage value 8%, use:
=-PMT(B3/1200,B4*12,B2)
If B3 is entered as 8% (the stored decimal value 0.08), use =-PMT(B3/12,B4*12,B2) instead. Do not divide an already-decimal percentage by 1,200: that would make the rate far too small. To calculate total interest from a payment rounded to cents in B5, use =ROUND(B5,2)*B4*12-B2. If B5 already contains the rounded payment, multiplying it by the number of payments and subtracting principal is sufficient.
Excel’s PMT function assumes a constant interest rate and equal periodic payments. Its optional fourth and fifth arguments control the future value and whether payments occur at the beginning or end of a period. For a basic fully amortizing loan that ends at a zero balance, the default future value is zero; the default timing is end of period. These assumptions should match the loan terms before you use the result as an estimate.
What APR and the Result Mean
APR is an annualized rate expressed as a percentage. For this calculator, it is treated as a fixed annual rate and converted to a monthly rate by dividing by 12. The calculation does not infer an APR from fees or a lender’s disclosure; it uses the APR you enter to estimate payments under a simplified monthly model.
The monthly payment combines interest and principal. Early in a typical amortization schedule, the outstanding balance is larger, so a greater share of each payment goes toward interest. As the balance declines, the interest portion generally shrinks and the principal portion grows. The payment remains level under the assumptions above.
Total interest is the sum of the rounded scheduled payments minus the amount borrowed. It is not the same as the monthly payment, and it does not include extra charges or account for changes in payment timing. A real lender’s calculation can differ because of the contract’s rate conventions, fees, payment dates, rounding, or other terms.
Common Excel Errors and Limits
- Mixing percentage formats: Use APR/12 for a cell containing 8%, but APR/1200 for a cell containing the number 8.
- Sign confusion: PMT commonly returns a negative value when principal is entered as positive. Add a leading minus sign to show the payment as a positive cash outflow.
- Wrong period count: If the term is in years, multiply by 12 for monthly payments. If it is already in months, do not multiply again.
- Rounding too early or inconsistently: The calculator rounds the monthly payment to cents, then uses that payment for its total-interest estimate. A lender’s final payment may be adjusted to clear a small remaining balance.
- Assuming APR includes every cost in this formula: The PMT calculation does not separately add fees, insurance, taxes, or other charges. It is a payment estimate from the inputs, not a complete loan quote.
This method is for a fixed rate and a regular schedule of level payments. It is not a complete model for variable rates, irregular payment dates, interest-only periods, balloon payments, or loans with changing payment amounts. Those cases need additional inputs and a formula suited to their contract.
Frequently Asked Questions
What is the Excel formula for a monthly loan payment using APR?
Use =-PMT(APR/12, years*12, principal) if APR is stored as a decimal percentage, such as 8%. If the cell contains 8 as a number, use =-PMT(APR/1200, years*12, principal).
Does Excel’s PMT function calculate APR?
No. PMT calculates a periodic payment from a rate, number of periods, and present value. This formula uses an APR you already know; it does not determine APR from a loan offer or fees.
How do I calculate total interest in Excel?
For a fully repaid fixed-rate loan, multiply the monthly payment by the number of payments and subtract the principal. For a cent-rounded payment in B5, principal in B2, and years in B4, use =ROUND(B5,2)*B4*12-B2.
Why does PMT show a negative number?
Excel uses cash-flow signs: loan proceeds and repayments have opposite signs. Entering the principal as positive makes PMT negative; putting a minus sign before PMT displays the payment as positive.
Will this formula match my lender’s payment exactly?
Not necessarily. The estimate assumes fixed APR, monthly periods, regular payments, and no included fees or extra charges. Check the lender’s disclosure for the contractual figures and calculation details.
This result is an estimate, and the lender’s disclosure gives the exact figures.
Related
Formula used
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
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard loan formula for this calculation, fixed rate and monthly compounding unless stated, rounded as each result states. Not financial advice; no lender, rate or product is recommended. See formulas and assumptions, the disclaimer or report an error.
More in this section
- Closing Costs on a House Loan: What to Expect$405.53
- Secured Loan for Home Refinancing: Payment Calculator$405.53
- Closing Cost House Calculator: Estimate Buyer Costs$8,500.00
- Closing Cost House Estimate Calculator
- Loan Repayment Extension Request Letter: What to Include$405.53
- Refinance Calculator With PMI and Loan Term