A loan payment Excel sheet estimates a fixed monthly payment from the amount borrowed, annual interest rate, and loan term, then calculates total interest using that rounded payment. Enter the figures below to see the estimated payment and interest; the calculation assumes equal monthly payments and a fixed rate.
Formula
The monthly payment for a fixed-rate, fully amortizing loan is calculated as:
Payment = round(P × r ÷ [1 − (1 + r)−n], 2)
Here, P is the loan principal in dollars, r is the monthly interest rate as a decimal, and n is the total number of monthly payments. Convert an annual percentage rate to a monthly decimal with r = APR ÷ 1200. The calculator uses the APR input as a nominal annual rate divided by 12; it does not infer fees or other costs from APR.
Total interest is estimated as rounded monthly payment × n − P. The payment is rounded to cents before the total is calculated, so the result reflects a schedule of equal cent-rounded payments. A lender’s final payment or payoff amount can differ because of its precise payment schedule, rounding, and account terms.
Worked Example
Using the calculator’s defaults: principal P = $20,000, APR 8%, and term 5 years.
- Convert the APR to a monthly decimal:
r = 8 ÷ 1200 = 0.0066666667. - Convert the term to payments:
n = 5 × 12 = 60. - Substitute into the payment formula:
$20,000 × 0.0066666667 ÷ [1 − (1.0066666667)−60] = $405.53, rounded to the nearest cent. - Calculate total interest using the rounded payment:
$405.53 × 60 − $20,000 = $4,331.80.
The estimated monthly payment is $405.53, and estimated total interest is $4,331.80.
Assumptions
- The interest rate stays fixed for the full term.
- Payments are equal, made monthly, and begin on a regular schedule.
- Interest is modeled monthly at APR divided by 12.
- The calculation excludes fees, taxes, insurance, PMI, escrow, and optional extra payments.
Build the Same Calculation in Excel
A spreadsheet can reproduce the estimate with ordinary cell formulas. Put the loan amount in B1, annual APR as a percentage in B2, and term in years in B3. For example, enter 20000, 8%, and 5. Excel stores a percentage such as 8% as 0.08, so do not enter 8 as a percentage unless the formula is adjusted.
- Monthly payment:
=ROUND(-PMT(B2/12,B3*12,B1),2) - Total interest based on the rounded payment:
=ROUND(B4*B3*12-B1,2), assuming the payment formula is in B4.
Excel’s PMT function generally returns the payment as a negative cash flow when the loan principal is entered as a positive value. The minus sign in the formula displays it as a positive payment. The spreadsheet formula and calculator both use the same fixed-rate, monthly-payment model. If the rate cell contains the number 8 rather than the percentage 8%, use B2/1200 in the function instead.
When to Use the Estimate
This calculation is useful for understanding the principal-and-interest portion of a standard installment loan and checking how principal, rate, or term changes affect the modeled payment. It can help you inspect a repayment schedule or compare arithmetic scenarios using figures supplied to you. It is not an approval decision or a complete estimate of the amount due each month.
For instance, a longer term increases the number of payments and usually lowers the formula’s monthly payment for the same principal and rate, while increasing total interest under this model. A higher rate increases the payment when principal and term are unchanged. These are consequences of the formula, not a recommendation about what loan or term to select.
Use the exact amount, APR, and term stated in the lender’s documents when available. If you are entering a quoted rate, check whether it is the APR or a different rate: the calculator’s input is annual APR, and its simplified formula applies APR divided by 12 without modeling separate charges.
Reading Results and Common Errors
The monthly payment shown covers modeled principal and interest only. It is not necessarily the full monthly bill. Depending on the loan, other amounts may be collected separately or included in a payment, but this calculator has no inputs for them. The total-interest output is the sum implied by equal rounded payments over the stated number of months, not a quote for a payoff on a particular date.
- Entering the rate in the wrong format: In Excel, enter 8% or 0.08 for an 8% rate when the formula divides by 12. Entering 8 in that setup treats it as 800%.
- Using months as years: The calculator’s term is in years; its formula multiplies the term by 12. A spreadsheet formula must use the same unit.
- Rounding too early: Round the displayed periodic payment to cents, but do not round the monthly rate or intermediate powers in the calculation. The specified total uses the rounded payment.
- Assuming all loans amortize this way: Interest-only, variable-rate, balloon-payment, and irregular-payment arrangements do not follow this level-payment formula throughout their full terms.
Limits and Alternatives
The formula assumes a fixed rate, equal monthly periods, a regular payment, and a balance that is fully repaid after the final scheduled payment. It does not account for daily interest accrual, payment dates that vary, fees financed into the balance, rate changes, deferred payments, or extra principal. A real loan’s contract and payment schedule determine how those details are handled.
For a more complete estimate, use the lender’s disclosed payment schedule and include any applicable charges or payment components separately. For a variable-rate loan, a single fixed-rate calculation can only illustrate one assumed rate; it cannot predict future adjustments. To model extra payments or a changing balance, a spreadsheet needs a month-by-month amortization table with separate interest, principal, and ending-balance calculations.
Frequently Asked Questions
What is the Excel formula for a loan payment?
Use =ROUND(-PMT(APR/12,years*12,amount),2) when APR is stored as a percentage, such as 8%. The minus sign makes the displayed payment positive.
Does this payment include taxes and insurance?
No. It estimates principal and interest only. Taxes, insurance, PMI, escrow, and fees are not included unless separately added to a broader estimate.
Why does Excel show a negative payment?
Excel’s PMT function represents outgoing payments as negative cash flows when the loan amount is entered as positive. Put a minus sign before PMT to display a positive payment.
Why might the lender’s payment differ from the spreadsheet?
The lender may use different timing, rounding, rate details, fees, or payment components. This formula also assumes fixed monthly periods and a constant rate.
Can I use the sheet for a variable-rate loan?
Only as a snapshot using one assumed rate. The result does not model future rate changes or recalculate the payment as the balance and rate change.
This result is an estimate; 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
- Trade In an Old Laptop for a New One?
- Trade In an Old Phone: Value, Steps, and Alternatives
- Trade In an Old iPhone: Options, Value and Steps
- Trade In an Old Laptop? Options, Value, and Safety
- AT&T Trade-In Offers: How They Work
- Trade In an Old iPad: Options, Value, and Preparation