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.
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.
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.
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 ratenper - number of periods over the loan or investment to be paid offPV - the present value of loan or investmentThe last two arguments in the formula are optional:
FV - the future value signifying the amount at the end of the periodic paymentstype - specifies whether a payment is due at the beginning or end of the periodThe 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.
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.
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.
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).
There are two reasons why PMT function might return an error:
VALUE! - This usually happens when there is a supplied argument that is non-numeric.
#NUM! - This may occur due to:
rate is less than or equal to negative one.When calculating monthly or quarterly payments, make sure that the nper and interest rate arguments are presented in the appropriate units:
Month = 12years
Quarter = 4years
Monthly Rate = Annual Interest Rate/12
Quarter Rate = Annual Interest Rate/4
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.
