The **PMT** function is a **Financial function** in Excel. Interestingly, the name **PMT** stands for â€˜**payment**â€™. We can use this function to get the periodic payment for a loan. Besides, you can use this function to get the loan payment for different payment frequencies (weekly, monthly, quarterly, etc.). In this article, I will discuss the **PMT** function in detail along with some examples.

**Table of Contents**Expand

**Introduction to Excel PMT Function**

**Summary**

The **PMT** function calculates the payment for a loan based on a constant interest rate. For instance, you have applied for a 10- year home loan of $110,000 with an interest rate of 3%. Now, using the **PMT **function, you can **calculate the periodic payment for the loan**.

**Syntax**

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

**Arguments**

Argument | Requirement | Explanations |
---|---|---|

rate |
Required | The interest rate of the loan. |

nper |
Required | This is the total number of payments per loan. |

pv |
Required | This is the present value i.e. the total value of all the loan payments at present. In other words, it is simply the original amount borrowed. |

fv |
Optional | The future value i.e. the cash balance one wants to have after the last payment is done. If this parameter is omitted, the future value is assumed to be zero (0). |

type |
Optional | This specifies when the payment is due. If this parameter is 0 or omitted, the payments are due at the end of each period. If it is 1, the payments are due at the beginning of each period. |

**Return Value**

The **PMT** function returns the loan payments as a number.

**Available in Version**

Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel 2007.

**How to Use Excel PMT Function: ****4 Examples **

**1. Calculate Annual Payment Using the PMT Function**

You can calculate the annual loan payment by applying the **PMT** function. For example, you have taken a** 5**-year home loan with an interest rate of **3%** and the loan amount is **$110,000**. Now, if you want to calculate the annual loan payment for the home, here are the steps involved.

**Steps:**

- First, type the following formula in
**Cell C9**.

`=PMT(C4,C5,C6,C7)`

Here, in this formula, we did not mention the argument **[type]**. Previously, in the function argument section we have mentioned that if this argument is omitted, excel assumes that the payment will be due at the end of the period. Moreover, most of the loans are of these type. In case your loan payment is due at the beginning of the period, put** 1** as the argument.

- As a result, you will get the annual payment for your home loan.

The result of the formula will be in the currency format, red color, and rounded to two decimal places. By default, the result will be enclosed in parenthesis, which means the loan amount is negative (As the loan payment will be subtracted from your bank account).

**Note:**

âž¤ If you want to see the loan amount as a positive number, simply put a **minus** (**â€“**) sign at the beginning of the **PMT** formula. Such as:

`=-PMT(C4,C5,C6,C7)`

âž¤ Consequently, you will get a positive annual payment.

**2. Apply Excel PMT Function to Get Weekly, Monthly, Quarterly, and Semi-Annual Payments**

Using the **PMT** function you can calculate periodic loan payments. Such as, you can calculate a loan payment on a weekly, monthly, quarterly, or semi-annual basis. However, to do that, you have to adjust the **rate** and **nper** arguments, depending on the number of payments per year. More elaborately,

âž¤ In the case of **rate**, divide the annual interest rate by the number of payments per year.

âž¤ On the other hand, for **nper**, multiply the number of years with the number of payments per year.

Now, assume a **7**-year home loan with an annual interest date of **5**% and a loan amount of **$250,000**. According to the above-mentioned discussion, we will follow the below steps to calculate loan payments for different periods.

**Steps:**

- Initially, type the below formulas in
**Cell C10**to**Cell C13**.

The formula for the weekly payment:

`=PMT(C4/52,C5*52,C6)`

We have used **52** here because there are **52** weeks in a year.

Similarly, the formula for the monthly payment:

`=PMT(C4/12,C5*12,C6)`

Here, **12** is used as there are** 12** months in a year.

`=PMT(C4/4,C5*4,C6)`

While doing payments on a quarterly basis, we do pay **4** times per year.

Finally, the formula for semi-annual payment is:

`=PMT(C4/2,C5*2,C6)`

This is because we do pay twice a year on a semi-annual payment period.

- As a consequence, you will get the following results.

**3. Use PMT Function to Determine Periodic Loan Payment (In case of Down Payment)**

Often we buy things on down payments. In that case, a certain percentage of the price is paid initially. So, in such cases, to calculate the loan payment you have to subtract the paid price from the original price. For instance, you have bought a car for **$30000 **and as a down payment, you have paid 10% of the $30000. Subsequently, you have to pay the loan within **5** years at a **5%** annual interest rate. Now, we will calculate the monthly payment for this car using the **PMT **function.

**Steps:**

- First, type the following formula in
**Cell C10**.

`=PMT(C6/12,C7*12,C4*(1-C5),C8)`

Here, the price of the car is multiplied by **(1-C5) **which is** C4*.9**. That means you have to pay **90%** of the price as a loan (as you paid **10%** of the price as the down payment).

- Finally, you will get the amount of monthly payment from the formula.

**4. Create Loan Payment Calculator Applying PMT Function in Excel**

Till, now in this article, we have calculated loan payments individually for a certain period. But, if you have several options of loans and you want to compare those loans, then you can create your own loan payment calendar. Letâ€™s assume a** 5**-year loan amount of **$110,000** with a **3%** interest rate. So, here are the steps involved to make a loan calendar.

**Steps:**

- Firstly, enter the loan details in
**Cell C4**to**Cell C7**and prepare two lookup tables:**Period**(**F5:G9**) and**Type**(**F11:G12**).

- Secondly, prepare a drop-down list for the periods (weekly, monthly, quarterly, semi-annual).

- Thirdly, make a drop-down list for type (when the payments are due).

- Next, using the combination of the
**IFERROR**and**VLOOKUP**functions type the below formula in**Cell D8**.

`=IFERROR(VLOOKUP(C8, F5:G9, 2, 0), "")`

Here **the VLOOKUP function** finds the value of **C8 **in the lookup table **Period** and brings the corresponding period value according to the drop-down selection. Later, **the IFERROR function** hides any error if it occurs.

- Similarly, type the following formula in
**Cell D9**.

`=IFERROR(VLOOKUP(C9, F11:G12, 2, 0), "")`

- Now, the calendar is ready. Then, to find the semi-annual payment where the loan will be due at the end of the period,Â type the below formula in
**Cell C12**.

`=IFERROR(-PMT(C4/D8,C5*D8,C6,0,D9),"")`

Here, the **PMT **function calculates the loan payment. And the **IFERROR** function hides errors if any input values are not defined.

- After that, upon entering the above formula, you will get the semi-annual loan payment for the loan.

- In conclusion, you can
**calculate the monthly payment for the same loan**using the calendar.

**Things to Remember**

While using the **PMT** function, keep the below points in mind.

- The payment returned by the
**PMT**function includes only principal and interest rate. However, it does not include any fees, taxes, or reserved amounts that might be associated with the loan sometimes. - Be certain that you are consistent about the units you use for specifying
**rate**and**nper**. For example, if you make monthly payments on a five-year loan at an annual interest rate of**12 percent**, use**12%/12**for**rate**and**5*12**for**nper**. On the other hand, if you make annual payments on the same loan, use**12 percent**for**rate**and**5**for**nper**.

**Common Errors of PMT Function**

Although using the PMT function is much simpler, sometimes errors might occur.

**#NUM!**: This occurs when the **rate** argument is negative or the **nper **value is **zero** (**0**).

**#VALUE!**: It occurs when one or more arguments are text values.

**Download the Practice Workbook**

You can download the practice workbook that we have used to prepare this article.

**Conclusion**

In the above article, I have tried to discuss the methods elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.

**<< Go Back to Excel Functions | Learn Excel**