   # PMT Function

Guide to Understanding the Excel "PMT" Function ## How to Use PMT Function in Excel (Step-by-Step)

The Excel “PMT” function is used to determine the payments owed to a lender by a borrower on a financial obligation, such as a loan or bond.

The payment owed is derived from a constant interest rate, the number of periods (i.e. loan term), and the value of the original loan principal.

The three variables are assumed to remain fixed across the entirety of the borrowing term.

Note that while the PMT function factors in the original loan principal and interest payments—the two sources of returns to the lender—there can be fees or taxes on the side that impact the lender’s “actual” yield.

• Borrower → Because the payment represents an “outflow” of cash from the perspective of the borrower, the resulting payment value will be a negative figure.
• Lender → If wanting to determine the “inflow” of cash received from the viewpoint of the lender, a negative sign can simply be placed in front of the “PMT” equation (to result in a positive figure).

## Excel PMT Function Formula

The formula for using the PMT function in Excel is as follows.

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

The first three inputs in the formula are required while the latter two are optional and can be omitted. (Hence, the brackets around “fv” and “type” in the equation.)

In order for the implied payment to be accurate, consistency in the units used (i.e. days, months, or years) is essential.

Monthly
• Annual Interest Rate ÷ 12
• Number of Years × 12
Quarterly
• Annual Interest Rate ÷ 4
• Number of Years × 4
Semi-Annual
• Annual Interest Rate ÷ 2
• Number of Years × 2
Annual
• N/A
• N/A

For instance, if a borrower has taken out a twenty-year loan with an annual interest rate of 5.0% paid on a quarterly basis, then the monthly interest rate is 1.25%.

• Quarterly Interest Rate (rate) = 5.0% ÷ 4 = 1.25%

The number of periods must also be adjusted by multiplying the borrowing term in years (20 years) by the frequency of payments (quarters) per year (4x).

• Number of Periods (nper) = 20 × 4 = 80 Periods (i.e. quarters)

## PMT Excel Function Syntax

The table below describes the syntax of the Excel PMT function in more detail.

Argument Description Required?
rate
• The fixed interest rate on the loan as stated in the lending agreement.
• To reiterate from earlier, the interest rate must be adjusted to remain consistent with the periodicity of the payment schedule (e.g. monthly, quarterly, semi-annual, annual).
• Required
nper
• The total number of periods in which payments must be issued over the borrowing term of the loan.
• Just like the interest rate, the number of payment periods must also be adjusted, or else the payment value will be incorrect.
• Required
pv
• The present value (PV) is the value of a series of payments based on the current date, i.e. the original principal of the loan on the date of issuance.
• Required
fv
• The future value (FV) is the ending loan balance on the date of maturity.
• If left empty, the remaining principal is assumed to be zero, i.e. there is no outstanding balance left at maturity.
• Optional
type
• The timing of when the payments are assumed to be received.
• “0” = End of Period (EoP)
• “1” = Beginning of Period (BoP)
• If omitted, i.e. left blank, the default setting in Excel is “0”.
• Optional

## PMT Function Calculator – Excel Model Template

We’ll now move on to a modeling exercise, which you can access by filling out the form below. Submitting ...

## Excel PMT Mortgage Loan Payment Calculation Example

Suppose a consumer has taken out a \$400,000 mortgage loan to finance the purchase of a house.

The mortgage loan has an annual interest rate of 6.00% per annum, with payments made on a monthly basis at the end of each month.

• Loan Principal (pv) = \$400,000
• Annual Interest Rate (%) = 6.00%
• Borrowing Term in Years = 20 Years
• Compounding Frequency = Monthly (12x)

Since all the necessary assumptions have been provided, the next step is to convert our annual interest rate to a monthly interest rate by dividing it by 12.

• Monthly Interest Rate (rate) = 6.00% ÷ 12 = 0.50%

In order to add the option to switch the compounding frequency, we’ll create a drop-down list to pick the compounding frequency using the following steps:

• Step 1 → Select the “type” Cell (E8)
• Step 2 → “Alt + A + V + V” Opens Data Validation Box
• Step 3 → Pick “List” in the Criteria
• Step 4 → Enter “Monthly”, “Quarterly”, “Semi-Annual”, or “Annual” into the “Source” line The cell below it will then use an “IF” statement to output the corresponding figure.

=IF(E8=”Monthly”,12,IF(E8=”Quarterly”,4,IF(E8=”Semi-Annual”,2,IF(E8=”Annual”,1))))

While not necessary, per se, the additional step above can help reduce the chance of an error and ensure the correct adjustments are made to the “rate” and “nper” values.

The other adjustment is to the number of periods, in which we’ll multiply the borrowing term in years by the compounding frequency, which comes out to 240 periods.

• Number of Periods (nper) = 20 Years × 12 = 240 Periods

The “fv” and “type” argument will be left omitted since we’re assuming that the mortgage will be fully paid off by the end of the borrowing term, and earlier we stated that the payments are due at the end of each month, i.e. the default setting in Excel.

The final step is to enter our inputs into the “PMT” function in Excel, which calculates the implied monthly payment on the twenty-year mortgage as \$2,866 per month.

=PMT(0.50%,240,400k)  