PMT function in Excel - Usage and examples

The PMT function is one of the built-in financial functions of the Excel software used to calculate the payment for a loan based on regular payments and a constant interest rate. The PMT function is not only useful for businesses, but also very practical for users if you want to calculate a loan.

PMT function in Excel - Usage and examples Picture 1

Example: A loan worth VND 100,000,000 million has an interest rate of 27% per annum, made for 5 years. So, what is the average amount that needs to be paid each year to pay off the loan in five years? That sounds complicated, right? To solve the above problem, Dexterity Software will guide you on how to apply PMT.

Function structure and usage

The PMT function has the following structure: = PMT (rate, nper, pv, [fv], [type])

In which the arguments:

  1. Rate: Required argument. The interest rate for the term of the loan.
  2. Nper: Required argument. The total number of payment periods for a loan.
  3. Pv: Required argument. Present value, also known as principal.
  4. Fv: Optional argument. The future value or cash balance you want to obtain after making the final payment. If fv is omitted, it is defaulted to 0 (zero), which means that the future value of the loan is 0 (paid off).
  5. Type: Optional argument. The number 0 (zero) or 1 indicates the time the payment is due. If Type is omitted, it will default to a value of 0 representing the time of the final payment, and a value of 1 represents the time of payment at the beginning of the period.

Note:

  1. The payments that the PMT function returns include principal and interest but do not include the costs, fees, and taxes that sometimes accompany the loan.
  2. The interest rate rate argument and the nper payment term must be consistent in terms of time. For example, if you make a recurring monthly payment, then the interest rate must be converted to the monthly interest rate by dividing the annual interest rate by 12 months.

Specific examples

Example 1: With the problem like the example at the beginning, you enter the following formula in any cell = PMT (27%, 5,100000000,0,0); The result is -38,719,579.42. That is, to borrow one amount of VND 100,000,000 million, an interest rate of 27% per annum, pay for 5 years, and the interest paid at the end of the term, you will have to pay VND 38,719,579.42 annually.

PMT function in Excel - Usage and examples Picture 2

The result of the PMT function automatically adjusts the currency format. If you want to remove the dollar symbol and change the format of the result, select the cell you want to edit, press Ctrl + 1 and switch to the desired format.

PMT function in Excel - Usage and examples Picture 3

Example 2: You set a goal of monthly bank deposits within 2 years, the interest rate is 6% per year, the amount you want to collect after one year is 100,000,000 VND. To calculate the monthly amount to send to the bank, you use the formula: = PMT (6% / 12.2 * 12,0,100,000,00000). Because you do it monthly, interest rates and the total number of payment terms must be converted into months. And the results obtained monthly you must deposit at least VND 3,932,061.03.

PMT function in Excel - Usage and examples Picture 4

Hopefully the article above helps you to apply the PMT function in Excel. Good luck!

4 ★ | 1 Vote

May be interested

  • DSUM function in Excel, how to use DSUM function and examplesDSUM function in Excel, how to use DSUM function and examples
    dsum function in excel, how to use dsum function and examples. the dsum function in excel is a fairly common function with a fairly simple usage. but if you do not know how to use the dsum function, you can refer to the following article to understand h
  • How to use the MAXA function in Excel, detailed examplesHow to use the MAXA function in Excel, detailed examples
    the maxa function in excel helps find the largest value in a data set, including numbers, logical values, and text containing numbers. this is different from the max function, which only counts numbers. let's learn the syntax and usage of the maxa function with practical examples.
  • RANK function - Rank function in Excel - Usage and examplesRANK function - Rank function in Excel - Usage and examples
    the rank function arranges equal numbers of the same rank. however, the duplication will affect the ranking of subsequent numbers. for example, you have the sequence number: 1,2,2,3,4 and are arranged in ascending order, so the order of the number 1 in the series is 5.
  • DCount function in Excel - How to use the DCount function and examples using the DCount functionDCount function in Excel - How to use the DCount function and examples using the DCount function
    dcount function in excel - how to use the dcount function and examples using the dcount function you have a large list of students who want to get a high total score, but don't want to spend a lot of time. in this article, introduce to you the dcount function in excel. help c
  • The search function satisfies many conditions in Excel - Usage and examplesThe search function satisfies many conditions in Excel - Usage and examples
    the search function satisfies many conditions in excel - usage and examples. in the process of processing data in excel many times you need to search for data that satisfies many conditions to extract data, so you are looking for a function that satisfies many things.
  • INDIRECT function in Excel - How to use INDIRECT function and examples using INDIRECT functionINDIRECT function in Excel - How to use INDIRECT function and examples using INDIRECT function
    indirect function in excel - how to use indirect function and examples using indirect function. you want to refer to a range of data without changing the formula in the cell. the following article introduces you to the indirect function in excel to help you reference
  • Multiple if function - Usage and examplesMultiple if function - Usage and examples
    the following article gives you a detailed guide on how to use the multiple if function with examples. the if function is simplified in the sense that the function tests a value with a given condition, if it satisfies the condition that returns 1 value, if it does not satisfy the condition.
  • SUM function in Excel, sum function and examplesSUM function in Excel, sum function and examples
    sum is a basic excel function that allows users to quickly and accurately calculate the sum of columns or cells in an excel spreadsheet. for an overview of the concept, formula and how to use the sum function in excel, readers can refer to our article below. with many illustrative examples from simple to complex, readers can understand and quickly apply this function to practical work.
  • Exponential functions in Excel - Usage and examplesExponential functions in Excel - Usage and examples
    exponential functions in excel - usage and examples. are you looking for exponential functions in excel to use instead of the exponential operator '^'? so let's learn the exponential function in excel that the article shares below.
  • MATCH function in Excel, usage and examplesMATCH function in Excel, usage and examples
    the match function in excel helps determine the position of a value in a list or range of cells. this is a useful tool when working with large data sets and can be combined with the index function to retrieve more precise information.