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.
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:
- Rate: Required argument. The interest rate for the term of the loan.
- Nper: Required argument. The total number of payment periods for a loan.
- Pv: Required argument. Present value, also known as principal.
- 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).
- 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:
- 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.
- 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.
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.
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.
Hopefully the article above helps you to apply the PMT function in Excel. Good luck!
You should read it
- Excel date function - Usage and examples
- Function Address - The function returns the address of a cell in Excel (usage, examples, examples)
- OR function in Excel, how to use the OR function, and examples
- DCOUNT function in Excel - Usage and practical examples
- FIND function in Excel - Usage and examples
- Match function in Excel - Usage and illustrative examples
- VLOOKUP function to use and specific examples
- ROUNDUP function in Excel - Usage and examples
May be interested
- DSUM function in Excel, how to use DSUM function and examplesdsum 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 examplesthe 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 examplesthe 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 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. 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. 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 examplesthe 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 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. 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 examplesthe 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.