Updated May 8, 2023
Excel PMT Function
PMT function in Excel calculates the payments that need to be paid for any loan or investment amount at a fixed interest rate with the same constant amount. The PMT function is named after its purpose: to calculate the payment amount. This is just EMI that we pay for our loan or invested amount when we opt for any policy or loan from a bank.
PMT Formula in Excel
PMT formula in Excel has the following arguments:
Five parameters are used in this PMT function. Three are compulsory, and two are optional.
COMPULSORY PARAMETER:
- Rate: We need to pay the interest rate per period/time. If it is monthly payments, it will be like rate/12(rate divided by 12 months). If it’s quarterly, it will be rate/4(rate divided by 4 months).
- Nper: The number of periods the loan will be paid back.
- Pv: It is the present value of the loan.
OPTIONAL PARAMETER:
- [Fv]: It is the future value of payments we want after the loan is paid off. In this case, we only want to get the loan paid and nothing else; omit it or make it 0.
- [Type]: If the payment is due at the end of the month, omit this or make this 0. If the payment is due at the beginning of the month, make this 1. For example, if payment is due on 31st January, this will be 0, but if it’s due on 1st January, make this 1.
How to Use the PMT Function in Excel?
Let us now see how to use this PMT function in Excel with the help of some examples:
Example #1
Suppose we have to purchase a flat costing $9,00,000, and we know the interest rate, which is 9 percent, and the total months of the loan is 12 months.
In this case, we wanted to know the installment amount or EMI, which needs to pay each month for the loan amount of $9,00,000.
In this case, the PMT function helps determine the amount that must be paid each month.
Let’s see the below calculation:
Now we will see step by step calculation from the PMT formula in Excel to know the installment amount which needs to pay each month:
We must ensure that the interest rate should be monthly, which we calculate by dividing no. of months (12).
Here in the above example, we have to divide 9%/12 months.
i.e. F3/F7
Which results in 0.75%.
Now, we have to find out the no. of months for which the loan has been taken
=F5*F7 (i.e. 5 years *12 months)
Which results in 60 Months.
Now we will find the PMT by using the below formula.
=PMT(F4,F6,D5)
Hence, $18,682.52 is the EMI that needs to pay each month.
So, we calculated the total amount payable, including interest and principal.
So the Result will be $1,120,951.18.
Example #2
PMT function in Excel also helps us calculate the monthly amount we need to invest in getting the fixed amount in the future. There are many situations in our life where we have to deal with it for a few purposes or goals; in this situation, the PMT function helps.
For example: Suppose we want to invest in getting $1,00,000 in 10 years when the annual interest rate is 5%.
Below is the calculation:
The interest rate is calculated to be 0.417% by 5% divided by 12 months (because we are investing monthly, if we want to invest quarterly, then divide it by 4)
We can use 5% as the interest rate if the payments are made annually.
Below is the calculation:
Sign Convention:
As we can see in the above example that the output is negative because of Cash Outflows. If we pay an equal monthly installment or invest monthly, the cash is going out of our pocket; that is why the sign is negative.
PMT Function Error in Excel:
We can face the below error while performing the PMT function in Excel:
Common Errors:
#NUM! – It happens when the supplied value of the rate is less than or equal to -1;
The supplied value of nper is equal to 0.
#value! – It will occur if any of the supplied arguments are not numeric.
So, with the help of the above, we came to know that the above are the few common errors.
Below are the few errors which is also encountered by the users while applying the PMT function in Excel:
Common Problem:
The result from the PMT function is much higher or lower than expected.
Possible Reason:
When users calculate monthly, quarterly, or annual payments, they sometimes forget to convert annual interest rates or the number of periods according to the requirement. Hence, it gives the wrong calculation or results.
Relevance and Uses
- PMT function in Excel helps us give periodic loan payments.
- PMT function allows us to determine the installment amount.
- PMT function is generally used in financial institutions where a loan or investment is given.
Things to Remember
- #NUM! error –
The situations are below:
- The given rate value is less than or equal to -1.
- The given nper value is equal to 0.
- #VALUE! Error –
It occurs when any of the arguments provided are non-numeric.
- When users calculate monthly or quarterly payments, in this situation, they need to convert annual interest rates or the number of periods to months or quarters, as per their need.
- If users want to find out the total amount that was paid for the duration of the loan, we need to multiply the PMT as calculated by nper.
Recommended Articles
This has been a guide to PMT Functions in Excel. Here we discuss the PMT Formula in Excel, how to use the PMT function in Excel, and practical examples and downloadable Excel templates. You can also go through our other suggested articles –
