Excel Total Loan Payments: Formula and Example

To calculate the total of all scheduled loan payments in Excel, multiply the monthly payment by the number of monthly payments. For a fixed-rate loan, Excel’s PMT function can calculate the payment; this calculator shows the same calculation from the loan amount, APR, and term, plus total interest.

Formula

For a fully amortizing loan with equal monthly payments, first calculate the monthly payment, then multiply that rounded payment by the number of payments.

• Monthly rate: r = APR / 1200, where APR is the annual percentage rate entered as a percent.
• Number of payments: n = years × 12.
• Monthly payment: PMT = P × r / [1 − (1 + r)⁻ⁿ]. Round PMT to the nearest cent.
• Total payments: total = PMT × n.
• Total interest: interest = total − P.

Here, P is the amount borrowed in dollars, r is the monthly interest rate as a decimal, and n is the count of monthly payments. APR is divided by 1200 because it is quoted as a percentage per year and this calculation uses a monthly rate. The calculator’s first result is the monthly payment; its second result is total interest. Multiply those results conceptually to get total payments: payment × months.

Worked Example

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

1. Convert the APR to a monthly rate: 8 / 1200 = 0.0066667.
2. Find the payment count: 5 × 12 = 60 months.
3. Apply the payment formula: $20,000 × 0.0066667 / [1 − (1.0066667)⁻⁶⁰] = $405.53 per month, rounded to cents.
4. Multiply the rounded payment by the payment count: $405.53 × 60 = $24,331.80 total payments.
5. Subtract the original principal: $24,331.80 − $20,000 = $4,331.80 total interest.

The payment and total are based on a cent-rounded monthly payment. A lender’s schedule can differ by a small amount if it uses unrounded internal values or adjusts the final payment.

Assumptions

• The interest rate stays fixed for the entire term.
• Payments are equal monthly payments, made at the end of each monthly period.
• Interest is modelled monthly at APR divided by 12.
• Fees, taxes, insurance, PMI, escrow, late charges, and optional extra payments are not included.

Using the Calculation in Excel

In Excel, enter the principal, APR, and term in separate cells. For example, put 20000 in B1, 8% in B2, and 60 in B3. Excel’s PMT function returns a cash-flow payment with the opposite sign from the principal if the inputs use the same positive sign. To display a positive payment, use:

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

This version assumes B2 contains the rate as an Excel percentage, such as 8%, and B3 is the number of monthly payments. If instead you type 8 as a whole number, use =-PMT(B2/1200,B3,B1). Do not divide by 12 and 100 twice: the right conversion depends on whether the cell contains 8% or 8.

To calculate total scheduled payments from the positive payment result, multiply it by the payment count. If the monthly payment is in B4, use =ROUND(B4,2)*B3 when you want the total to use the cent-rounded payment shown. If B4 is already formatted or calculated to two decimal places, the explicit ROUND makes the intended arithmetic clear.

How to Interpret Total Payments

Total payments are not the same as total interest. The total includes repayment of the borrowed principal as well as the interest charged over time. In the example, $24,331.80 is the sum of all 60 scheduled payments; $4,331.80 is the interest portion implied by subtracting the $20,000 principal.

The calculation describes the contractual schedule only under its assumptions. It does not necessarily equal the full amount paid if the borrower makes extra payments, misses a payment, pays a fee, or has a rate that changes. A variable-rate loan may have changing payments or a different payoff date, so one fixed APR and one fixed term cannot reproduce its future schedule.

Excel PMT Versus Total Loan Cost

PMT calculates the regular payment, not every cost associated with borrowing. Multiplying the PMT result by the number of periods gives scheduled payments under the entered assumptions, but it does not add origination charges or other costs that are outside the payment formula. A loan disclosure or payment schedule may show additional amounts or timing details.

Excel’s PMT function also has an optional future-value argument and a payment-timing argument. The simple formula above assumes a fully paid-off balance at the end and payments at period-end. If payments occur at the beginning of each period, the payment calculation changes. For comparing a calculation with a lender’s figures, check the quoted APR, term, payment frequency, rate structure, fees, and whether the first payment date affects the schedule.

Common Errors and Limitations

• Mixing up percentage formats: Entering 8% is not the same as entering 8. Use the matching conversion in Excel.
• Using years as periods: For monthly payments, a five-year term means 60 payments, not 5.
• Confusing payment with total: PMT gives one periodic payment; multiply by the payment count to get scheduled payments.
• Rounding inconsistently: A rounded monthly payment multiplied by many periods may differ slightly from a total based on full-precision amounts. This calculator uses the payment rounded to cents.
• Assuming APR captures every charge: The formula does not add separate fees, insurance, taxes, or escrow.

The standard payment formula applies to fixed-rate, level-payment amortizing loans with a positive APR and monthly periods. It is an estimate of scheduled arithmetic, not an account ledger or a substitute for a lender’s disclosed schedule.

Frequently Asked Questions

How do I calculate total loan payments in Excel?
Calculate the monthly payment with =-PMT(rate/12,months,principal) when the rate is stored as an annual percentage, then multiply the result by the number of monthly payments. Round the payment to cents first if you want the total to reflect the displayed payment.

What is the difference between total payments and total interest?
Total payments include both principal repayment and interest. Total interest is the total payments minus the original amount borrowed, assuming no other charges or payment changes.

Does Excel PMT include fees or insurance?
No. PMT calculates a periodic payment from the rate, number of periods, present value, and optional timing or future-value inputs. Add any applicable costs separately if you want a broader cash-outlay estimate.

Why is my Excel payment negative?
Excel uses cash-flow signs: money received and money paid are opposite directions. If the principal is entered as positive, prefix PMT with a minus sign to display the payment as positive.

Why does my Excel total differ from the lender’s figure?
Check the APR input format, term in months, payment timing, rounding, fees, and whether the rate is fixed. The lender’s schedule may also account for dates or a final adjusted payment that this standard formula omits.

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

Related

• How to Order a Payoff: Steps and What to Check
• How to Pay a J.Crew Credit Card
• Schedule K: How to Fill It Out Correctly

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