Summary

The Excel PMT function is a financial function that returns the periodic payment for a loan. You can use the PMT function to figure out payments for a loan, given the loan amount, number of periods, and interest rate.

Purpose 

Get the periodic payment for a loan

Return value 

loan payment as a number

Syntax

=PMT(rate,nper,pv,[fv],[type])
  • rate - The interest rate for the loan.
  • nper - The total number of payments for the loan.
  • pv - The present value, or total value of all loan payments now.
  • fv - [optional] The future value, or a cash balance you want after the last payment is made. Defaults to 0 (zero).
  • type - [optional] When payments are due. 0 = end of period. 1 = beginning of period. Default is 0.

How to use 

The PMT function can be used to figure out the future payments for a loan, assuming constant payments and a constant interest rate.  For example, if you are borrowing $10,000 on a 24 month loan with an annual interest rate of 8 percent, PMT can tell you what your monthly payments be and how much principal and interest you are paying each month.

Notes:

  • The payment returned by PMT includes principal and interest but will not include any taxes, reserve payments, or fees.
  • Be sure you are consistent with the units you supply for rate and nper. If you make monthly payments on a three-year loan at an annual interest rate of 12 percent, use 12%/12 for rate and 3*12 for nper. For annual payments on the same loan, use 12 percent for rate and 3 for nper.

PMT function examples

Excel formula: Estimate mortgage payment

Estimate mortgage payment

In this example, the goal is to calculate a monthly mortgage payment based on three inputs: The loan amount The annual interest rate The loan term in years The worksheet shown also takes into account the down payment, which is calculated using a simple formula in C8 (see below) and then subtracted...
Excel formula: Mortgage payment schedule

Mortgage payment schedule

In this example, the goal is to generate a basic mortgage payment schedule. A mortgage payment schedule is a detailed breakdown of all the payments you will make over the life of a mortgage. It provides a chronological list of each payment, showing the amount that goes toward the principal (the...
Excel formula: Calculate periods for annuity

Calculate periods for annuity

The NPER function returns the number of periods for loan or investment. You can NPER to get the number of payment periods for a loan, given the amount, the interest rate, and periodic payment amount. An annuity is a series of equal cash flows, spaced equally in time. The goal in this example is to...
Excel formula: Calculate payment for a loan

Calculate payment for a loan

Loans have four primary components: the amount, the interest rate, the number of periodic payments (the loan term) and a payment amount per period. You can use the PMT function to get the payment when you have the other 3 components. For this example, we want to find the payment for a $5000 loan...
Excel formula: Payment for annuity

Payment for annuity

The PMT function is a financial function that returns the periodic payment for a loan. You can use the PMT function to figure out payments for a loan, given the loan amount, number of periods, and interest rate. An annuity is a series of equal cash flows, spaced equally in time. The goal in this...

Related functions

Excel FV function

FV Function

The Excel FV function is a financial function that returns the future value of an investment. You can use the FV function to get the future value of an investment assuming periodic, constant payments with a constant interest rate.
Excel PV function

PV Function

The Excel PV function is a financial function that returns the present value of an investment. You can use the PV function to get the value in today's dollars of a series of future payments, assuming periodic, constant payments and a constant interest rate.
Excel RATE function

RATE Function

The Excel RATE function is a financial function that returns the interest rate per period of an annuity. You can use RATE to calculate the periodic interest rate, then multiply as required to derive the annual interest rate. The RATE function calculates by iteration.
Excel NPER function

NPER Function

The Excel NPER function is a financial function that returns the number of periods for a loan or investment. You can use the NPER function to get the number of payment periods for a loan, given the amount, the interest rate, and periodic payment amount.
Excel PMT function

PMT Function

The Excel PMT function is a financial function that returns the periodic payment for a loan. You can use the PMT function to figure out payments for a loan, given the loan amount, number of periods, and interest rate.
Excel PPMT function

PPMT Function

The Excel PPMT function can be used to calculate the principal portion of a given loan payment. For example, you can use PPMT to calculate the principal amount of a payment for the first period, the last period, or any period in between.
Excel IPMT function

IPMT Function

The Excel IPMT function is a financial function used to calculate the interest payment for a given period of an investment or a loan, based on constant periodic payments and a constant interest rate. For example, you can use IPMT to get the interest amount of a loan payment for the...
Excel CUMPRINC function

CUMPRINC Function

The Excel CUMPRINC function is a financial function that returns the cumulative principal paid on a loan between a start period and an end period. You can use CUMPRINC to calculate and verify the total principal paid on a loan, or the principal paid between any two payment periods.
Excel CUMIPMT function

CUMIPMT Function

The Excel CUMIPMT function is a financial function that returns the cumulative interest paid on a loan between a start period and an end period. You can use CUMIPMT to determine the total interest paid on a loan, or the interest paid between any two payment periods.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Formulas

Functions

Links