PMT Function for Calculating Annuity Loan Payments in Excel

The PMT function in Excel is part of the “Financial” category. It returns the periodic payment amount for an annuity, assuming fixed payment amounts and a consistent interest rate. Let’s dive into the details.



Syntax and Features of the PMT Function

Function syntax: rate; nper; pv; [fv]; [type].

Explanation of the arguments:

  • Rate – the interest rate of the loan.
  • Nper – the total number of payments for the loan.
  • Pv – the present value, equivalent to the loan amount.
  • Fv – the future value of the loan after the final payment (if omitted, it is assumed to be 0).
  • Type – an optional argument indicating whether the payment is made at the end of the period (value 0 or omitted) or at the beginning (value 1).

Key features of the PMT function:

  1. The calculation includes only principal and interest payments, excluding taxes, fees, and other additional charges that may be associated with the loan.
  2. When specifying the "Rate" argument, consider the payment frequency. For a quarterly interest rate, use 6%/4; for monthly payments, use 6%/12.
  3. The "Nper" argument indicates the total number of loan payments. For monthly payments on a three-year loan, use the value 3*12.

Examples of the PMT Function in Excel

To ensure the function works correctly, enter the input data properly:

Entering data correctly.

Enter the loan amount as a negative value, representing the amount the lender is “giving” or “losing.” When entering the interest rate, use percentage format or a decimal (e.g., 0.08).

Click the fx button (“Insert Function”) to open the Function Wizard. In the “Financial” category, select the PMT function and fill in the arguments:

Function arguments.

When the cursor is in an argument field, a tooltip appears below, indicating what to enter. If the input data is in an Excel table, you can use cell references as arguments. Alternatively, numeric values can be entered directly.

Note: The "Rate" field divides the annual interest rate by 12, as loan payments are monthly.

Monthly loan payments.

According to the specified conditions, the monthly loan payment is 1,037.03 rubles.

To calculate the total amount payable over the loan term (principal plus interest), multiply the monthly loan payment by the “Nper” value:

Nper.

Exclude the payment made at the beginning of the period from the monthly loan payments:

Monthly payment calculation.

To do this, set the “Type” argument to 1.

For a detailed calculation, use the PPMT and IPMT functions to show the loan principal and interest amounts, respectively.

Create a table for a more comprehensive calculation:

Period.

Calculate the loan principal using the PPMT function. The arguments are filled in similarly to the PMT function:

PPMT.

In the “Period” field, specify the period number for which the principal is calculated.

Similarly, fill in the IPMT function arguments:

IPMT.

Copy the formulas down to the last period to calculate the total payment by summing up the principal and interest.

Example 1.

Calculate the outstanding loan principal. The resulting table should look like this:

Example 2.

Download PMT Loan Payment Calculation Example in Excel download file

The total loan payment matches the monthly payment calculated with the PMT function, as this is a fixed amount for an annuity loan.

In summary, the PMT function can be used to calculate monthly loan or deposit payments, assuming a constant interest rate and payment amount.