Summary

The Excel PMT function returns the payment required for a loan or annuity when the interest rate, number of periods, and present value are known. Microsoft describes PMT as a function for loans based on constant payments and a constant interest rate, which is why it appears so often in amortization and savings-plan models.

PMT is not limited to borrowing. The same structure can be used to solve the recurring contribution needed to reach a future savings target. What changes is not the mathematics of the function, but the financial interpretation of the cash flows and their signs.

The result is only as reliable as the setup. Rate and period units must match, sign convention must be consistent, and payment timing should be chosen deliberately rather than left to assumption.

Syntax

=PMT(rate, nper, pv, [fv], [type])

rate is the interest rate per period, nper is the total number of periods, and pv is the present value. Optional fv represents the desired balance after the last payment, and type determines whether payments occur at the end of each period (0) or at the beginning (1).

Microsoft's examples emphasize unit consistency. If the model is monthly, use a monthly rate and a monthly period count. For example, an annual rate of 8% over 5 years becomes 0.08/12 and 5*12.

Arguments

• rate - Interest rate per period.
• nper - Total number of payment periods.
• pv - Present value, or the amount borrowed or invested at the start.
• fv - [Optional] Desired ending balance after the last payment. If omitted, Excel assumes 0.
• type - [Optional] Payment timing. Use 0 for end-of-period payments and 1 for beginning-of-period payments.

Before relying on a PMT result, check three things: the period unit, the sign convention, and the timing assumption. Most interpretation errors come from one of those three, not from the function itself.

PMT vs Related Functions

PMT solves the payment amount. Other time-value functions in the same family solve different unknowns from a similar financial structure.

In amortized loan models, PMT also connects directly to IPMT and PPMT, which split the payment into interest and principal components for a given period.

Using the PMT Function

PMT is most often used in loan budgeting. A worksheet can take principal, annual rate, and term as assumptions, convert them to consistent periodic units, and return the exact payment required by that structure. That is more reliable than estimating from a rule of thumb because the function reflects the compounding and term precisely.

PMT is also useful in savings planning. If the target balance is known, the same function can solve the periodic contribution required to reach that target. In that case, the payment is not a debt service amount but a planned contribution stream.

• Use PMT for fixed-rate loans and fixed-contribution annuity-style plans.
• Document whether the worksheet is using end-of-period or beginning-of-period payments.
• Pair PMT with total-cost or affordability checks so the payment figure is interpreted in context.

Example 1 - Monthly Loan Payment

This is the standard borrowing case. The annual rate is converted to a monthly rate, the 5-year term is expressed as 60 months, and PMT returns the required monthly installment. The negative result reflects cash leaving the borrower's side under the usual sign convention.

=PMT(0.08/12,60,10000)

Example 2 - Annual Saving Target

Here the function is solving a contribution requirement rather than a loan installment. The target future balance is $50,000, and PMT calculates the annual deposit that would be needed to reach that goal at 8% over 10 years.

=PMT(0.08,10,0,50000)

Example 3 - Monthly Budget Check

Sometimes the worksheet needs a decision flag rather than only a payment number. Comparing PMT directly against a budget threshold turns the output into a simple affordability test. Because PMT is negative in this setup, the comparison is made against a negative limit as well.

=PMT(0.08/12,60,10000)>-210

Example 4 - Total Interest Paid

PMT gives one payment, but the full borrowing cost is easier to understand when the payment is scaled across the entire term. Multiplying the absolute payment by 60 and subtracting the original principal isolates the total interest paid over the life of the loan.

=(ABS(PMT(0.08/12,60,10000))*60)-10000

Conclusion Recap

• Summary: PMT returns the periodic payment implied by a fixed-rate annuity structure.
• Syntax: =PMT(rate,nper,pv,[fv],[type]).
• Core setup: Keep rate and period units aligned, and use a consistent sign convention.
• Best use: Loan budgeting, savings-plan contributions, affordability checks, and total-cost analysis.