PMT Function In Excel

This tutorial demonstrates how to use the Excel PMT Function in Excel to calculate the periodic payment amount of an investment.

PMT main

PMT Function Overview

The PMT Function Calculates the payment amount.

To use the PMT Excel Worksheet Function, select a cell and type:
pmt formula syntax

(Notice how the formula inputs appear)

PMT Function Syntax and Inputs:

rate – The interest rate for each period. For example, if a housing loan is obtained at an annual interest rate of 9% and payments are made monthly. Then the interest rate needed to be calculated in months and then entered into the formula. In this case, the interest rate would be 9%/12, or 0.75%, or 0.0075.

nper – The total number of payment periods. For example, if the housing loan was for 5 years and the payments are being made monthly. Then the total number of periods would be 5*12 or 60.

pv – The present value of the investment that the future payments are worth now. In the excel PMT function, if the PV argument is omitted, it’s assumed to be zero (0). The [fv] argument must be provided in case PV is omitted.

fv – OPTIONAL: The Future Value of the investment or loan at the end of the number of payment periods. For example, a number of payments are made monthly at a 10% interest rate in a bank with the goal of the savings to be totaled at $10,000, at the end of five years. Then the future value of the savings is $10,000. If the FV argument is omitted, it’s assumed to be zero (0). The present value, PV must be provided in case FV is omitted.

type – OPTIONAL: The type argument shows when the payments are made, either at the end of the period by 0 or at the start of the period by 1. By default, in the excel PMT function, the payment type is considered 0.

 

What Is PMT?

Periodic Payments or simply the payments are such payments that are made consistently for a number of periods to pay off the loan or investment. The periodic payments can be calculated from either using the present value formula or future value formula.

If the Present Value is given then the PMT is calculated using the following equation:

If the Future Value of the investment is given then PMT is calculated using this equation:

Where:

PV = Present Value

FV = Future Value

R = Rate of Return

N = Total Number of periods

What is the Excel PMT Function?

Excel PMT function calculates the total periodic payments made against an investment or loan at a constant interest rate for a specific number of periods. The periodic payments, PMT, can be made at the beginning of the period or the end of the period. By default, the excel function considered that the payments are made at the end of the period.

Example 1

Let’s assume that a student loan of $100,000 is to be paid off completely in 10 years with a 4% annual interest rate. The monthly payment of the loan has to be calculated.

As the payments are made monthly, the annual interest rate is converted into monthly interest by

Monthly Interest Rate – 4% (annual interest rate) / 12 (months per year) = 0.33%

and the number of payments per period is converted into the monthly number of payments by

NPER – 10 (years) * 12 (months per year) = 120

The present value of the loan in the example given is

PV = $100,000

And the future value of the investment was not mentioned in the above example, so the [fv] argument is simply omitted or entered the following value

FV = 0

And the type of the payments wasn’t mentioned in the example, so the [type] argument is simply omitted or have default value i.e.

Type = 0

PMT Example 01

The monthly payment of the loan is

PMT = -$1,012.

The result came in negative value because it shows that the cash is being paid out from the individual account.

Example 2

Suppose you want to buy a car for three years now. So you open a savings account intending to reach $40,000 by the end of the three years. You want to find out the monthly payments that you need to make. And consider the bank pays you an annual interest rate of 3% on your payments.

The payments are made monthly, so the annual interest rate is converted into monthly interest by

Monthly Interest Rate – 3% (annual interest rate) / 12 (months per year) = 0.25%

and the number of payments per period is converted into the monthly number of payments by

NPER – 3 (years) * 12 (months per year) = 36

Here, the present value of the investment is not given, so we entered the following value in the pv argument

PV = 0

And the future value of the investment is

FV = $40,000

The payments are made at the start of the month, so the value of [type] argument is

Type = 1

PMT Example 02

The monthly installment is

PMT = -$1061

The result came in negative value as the payments are being made out to the bank (from own’s account) every month.

Additional Notes

Make sure the units of nper and rate are consistent, i.e. in case of monthly interest rate the number of periods of investment should also be in months.

In the Excel Financial Functions the cash outflows, such as deposits, are represented by negative numbers and the cash inflows, such as dividends, are represented by positive numbers.

Return to the List of all Functions in Excel

 

PMT in Google Sheets

All of the above examples work exactly the same in Google Sheets as in Excel.

PMT Google

PMT Examples in VBA

You can also use the PMT function in VBA. Type:
application.worksheetfunction.pmt(rate,nper,pv,fv,type)
For the function arguments (rate, etc.), you can either enter them directly into the function, or define variables to use instead.