Table of Contents
This guide explains how to use PMT Function in Excel, including its syntax, required arguments, practical examples, and common errors.
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 apply the PMT function in Excel. Good luck!
FAQ
What does the PMT function do in Excel?
The PMT function performs the calculation described in this guide. Use the correct arguments and compatible data types for reliable results.
How do I enter the PMT function correctly?
Start with an equals sign, enter PMT, add the required arguments inside parentheses, and confirm that cell references and separators are correct.
Why does the PMT function return an error?
Common causes include missing arguments, invalid data types, incorrect cell references, unsupported values, or regional separator settings.
Reader Comments 0
Sign in with email or Google to join the discussion.