What is the PMT Formula in Excel Used For?
The use of the PMT formula in Excel is very popular among finance students and accountants. The acronym PMT is quite simply a shortening of the word Payment. However this interpretation of the word is quite specific in this application.
The PMT formula is used to calculate the Internal Rate of Return (IRR) which is an important financial tool used by many businesses. This article will discuss the use of the PMT formula to calculate the IRR in excel.
The IRR is a measure of how much money you will make if you invest $1,000 in an investment that has an expected return of, say, 10% per year. If the investment is expected to pay back the principal plus interest at the end of the year you will earn $10 per $1,000 invested. The formula to calculate this is as follows:
=PMT(10000,0.10,1,1)
The first parameter in the PMT formula is the initial investment amount. This is the $1,000 in the example above. The second parameter is the annual rate of return, which is 0.10. The third parameter is the number of years, which is 1. The fourth parameter is the time period, which is the same as the first parameter.
In the example above, the formula is:
=PMT($1,000,0.10,1)
The result is: $921.09
You Can Find Ready Made Excel Formulas By Clicking Here:
As you can see from the example above, the result of the PMT formula is expressed in dollars. The dollar value of the formula can be adjusted by changing the values in the parameters. For example, if you wanted to calculate the IRR using a different annual rate of return, you would change the second parameter in the formula to the new rate of return. The result of the new calculation would be:
=PMT((10000,0.10,2),1)
The result would be: $1,721.10
The PMT formula in excel is a very powerful tool when used correctly. It can be used to calculate the IRR of many different investments. For more information on the use of the PMT function in excel, check out my blog.
PMT Function Excel
The PMT Function Excel uses these following arguments. Some of these are required to make the PMT formula work, and some are optional.
- Rate: (Required Argument): This is the interest rate of the loan
- Nper: (Required Argument): This is the total number of the payments for the loan taken out
- Pv: (Required Argument): This is the present value or total amount of money that a series of future payments is worth now. This is also called the Principle of the Loan
- Fv: (Optional Argument): This is the Future Cash Balance we want at the end of the Payment cycle. If this value is omitted, it is assumed to be zero. The Future Value of the loan is also assumed to be zero
- Type: (Optional Argument): This figure signifies the type of day count basis to be used.
This Formula is shown as
PMT=(Rate,Nper,Pv,[Fv],[Type])
Many Ready Made Excel Spreadsheets Can Be Found by Clicking This Link:
Understanding the PMT Formula in Excel can take quite a time, let alone building a working formula. But, there are some ready made tables and spreadsheets to help speed up the process.
To check out the best, and probably easiest, way to move forward on this and many other Excel Spreadsheets, click the link below.
