HomeApplicationOffice informationPMT function in Excel - Usage and examples

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

You should read it

May be interested

  • Primary font, practice writing beautiful fontPhoto of Primary font, practice writing beautiful font
    primary font, practice writing beautiful font. the handwriting strokes of the students are always the most beautiful handwriting of each of us because then we are honed and molded the handwriting carefully from teachers and teachers. if you loved the handwriting, but couldn't write as well as a kid.
  • What is WordPad? Compare the differences between Notepad, WordPad and WordPhoto of What is WordPad? Compare the differences between Notepad, WordPad and Word
    what is wordpad? compare the differences between notepad, wordpad and word. on windows operating systems there are many different editing tools for users. but not everyone knows all those drafting tools on windows. the three main drafting tools on windows are notepad, wordpad and word.
  • Adding 1 or more blank lines in Excel is extremely fastPhoto of Adding 1 or more blank lines in Excel is extremely fast
    adding one or more blank lines in excel is a simple trick every excel user should know to be able to use this office software better. because of that, tipsmake.com we have written this tutorial for all interested readers.
  • Page numbering in Excel does not start from 1Photo of Page numbering in Excel does not start from 1
    excel does not split separate pages like in word, but when printed, they will need to be broken down into individual pages. and you can also number the page for excel like word, can optionally number the page from one or page numbering in excel does not start from 1.
  • Summary keyboard shortcuts in ExcelPhoto of Summary keyboard shortcuts in Excel
    summary keyboard shortcuts in excel. summing is one of the many calculations available in excel and it will serve a great deal in statistical accounting. sometimes you need to work with a lot of calculations so using keyboard shortcuts will save a lot of working time.
  • How to install and use Unikey for new usersPhoto of How to install and use Unikey for new users
    how to install and use unikey for new users. unikey is a famous vietnamese percussion with millions of installs on computers. with the advantages of compact, stable operation, unikey is an indispensable vietnamese typing tool on our pc.

Office information