Use the Excel PMT Function for Finance

Introduction

Excel provides a variety of financial functions that make it easy to generate the reports you need. The Excel PMT function can be used to calculate the periodic payment needed to pay off a loan. This function allows you to determine the amount of the monthly or other periodic payment as long as the loan has a fixed interest rate and fixed payments. In this article, we'll take a closer look at the PMT function in Excel and discuss some examples of its use.

Prerequisites

A couple key prerequisites need to be in place in order to follow along with this tutorial:

  • You'll need to have Microsoft Office installed on your computer.

  • You'll need some basic knowledge of Excel.

Excel PMT function

To calculate recurring payments in Excel, simply pass rates and values to the PMT() function call and create a formula within a cell in your spreadsheet.

How to use the PMT function in Excel

Shown below is the syntax for the PMT function:

= PMT (rate, nper, PV, [ FV ], [ type ])

The PMT() function takes the following three arguments:

  • rate - the interest rate
  • nper - number of periods over the loan or investment to be paid off
  • PV - the present value of loan or investment

The last two arguments in the formula are optional:

  • FV - the future value signifying the amount at the end of the periodic payments
  • type - specifies whether a payment is due at the beginning or end of the period

Optional type value

The optional type parameter for the PMT function can have a value of either 0 or 1:

0 - indicates that payment is made at the end of the period
1 - indicates that payment is made at the beginning of the period

Note: If type is omitted, the default value is 0.

PMT Excel function examples

Now that we understand the basic syntax of the PMT function, let's put that knowledge to work and review some examples of the function's use:

Example 1:

Sally has a monthly payment on a $20,000 loan payable over 5 years with a fixed interest rate of 3 percent per year. Payment shall be made at the end of each month.

An example of the PMT function being used to calculate periodic payments

To calculate the total amount to be paid for the loan, multiply the PMT value by a number of periods ($359.37*60).

There are a few key points to note in the PMT function shown above:

  • The future value is applied and since payment is to be made at the end of each month, the arguments FV and type can be omitted.

  • Payment is to be made monthly with an annual interest rate of 3%. We need to convert this rate into a monthly rate (= 3%/12), and we also need to convert the five years into months (=5*12).

  • The outcome will be negative because this is considered an outflow of money.

Example 2:

Jerry has a quarterly investment allowance of $0 that is supposed to reach $5000 over three years with an interest rate of 4 percent per year. Payment is made at the start of each month.

Screenshot of the PMT function being used to calculate quarterly payments

Note: - The amount will always return a negative value because it reflects an outgoing payment. If you want to express the amount as a positive value, just place a minus sign after the formula's equal sign.

In this example of the PMT function, there are a few important things to note:

  • The type has been set to "1", which means the investment payment is made at the beginning of each quarter.

  • The payment was payable on a quarterly basis, which means that the four percent annual interest rate must be converted to a quarterly rate (4%/12). Similarly, the number of years will be converted into quarters (3*4).

Why PMT function may return an error

There are two reasons why PMT function might return an error:

  1. VALUE! - This usually happens when there is a supplied argument that is non-numeric.

  2. #NUM! - This may occur due to:

  • The supplied value of rate is less than or equal to negative one.
  • The number of the period is equal to zero.

When calculating monthly or quarterly payments, make sure that the nper and interest rate arguments are presented in the appropriate units:

Month = 12years
Quarter = 4
years
Monthly Rate = Annual Interest Rate/12
Quarter Rate = Annual Interest Rate/4

Conclusion

When you need to include financial calculations in your spreadsheet, Excel offers many functions to help you get the job done. In this article, we focused on the PMT function, which is used to calculate periodic payments on a loan. Using our examples as a guide, you'll have no trouble implementing the Excel PMT function in your own worksheets.

Published: 
March 24, 2020
Author:
500 Rockets
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram