PMT Calculator (Excel Loan Payment)

This calculator replicates the spreadsheet PMT function used in Excel and Google Sheets. Given a loan amount, an interest rate, and a number of payments, it returns the fixed amount you pay each period to clear the loan — plus the total interest, a yearly amortization schedule, and the exact =PMT(...) formula you can paste into a spreadsheet.

How to Use

1. Loan amount (present value) — how much you're borrowing today.
2. Annual interest rate — the yearly (nominal) rate. The calculator divides it by the number of payments per year for you.
3. Term and frequency — how long you'll repay, and how often (weekly, bi-weekly, monthly, quarterly, annually).
4. Advanced (optional) — a future value (a balloon balance still owed at the end) and whether payments fall at the end of each period (an ordinary loan) or the beginning (lease-style).

The headline figure is your payment per period. The Schedule tab breaks repayment down year by year.

Understanding the Results

• Payment — the constant amount due each period. In a spreadsheet this comes back negative because it's cash leaving your account; we show the positive magnitude.
• Total interest — the extra you pay on top of the principal over the life of the loan.
• Total paid — payment × number of payments.

The PMT Formula

PMT(rate, nper, pv, [fv], [type]) solves the time-value-of-money equation for a constant payment:

$$\text{PMT}=-\frac{(PV\cdot (1+r{)}^n+FV)\cdot r}{((1+r{)}^n-1)(1+r\cdot \text{type})}$$

Where:

• r — the rate per period (annual rate ÷ payments per year)
• n (nper) — the total number of payments (years × payments per year)
• PV — the present value (principal), entered as a negative number in Excel
• FV — the future value / remaining balance after the last payment (default 0)
• type — 0 if payments are made at the end of each period, 1 if at the beginning

When the rate is 0, the formula collapses to dividing the principal evenly: PMT = -(PV + FV) / n.

Sign Convention

Excel models cash flow direction with signs: money you receive (the loan) is positive, money you pay is negative. That's why =PMT(5%/12, 60, 10000) returns a negative number. Enter the principal as a negative pv (or read the result as a magnitude) to get a positive payment.

Related Spreadsheet Functions

• IPMT / PPMT — the interest and principal portions of a specific payment.
• FV — the future value of a series of payments.
• PV — how much you can borrow for a given payment.
• NPER — how many payments a loan will take.
• RATE — the interest rate implied by a payment schedule.

Tips

• To match a lender's APR exactly, confirm whether they compound monthly and whether fees are rolled into the principal.
• For a leasing or "payment in advance" arrangement, set payments to the beginning of the period — it lowers each payment slightly.
• A balloon payment (non-zero future value) reduces the regular payment but leaves a lump sum owed at the end.