Excel PMT Function
PMT function in Excel is used to calculate the payments that need to be paid for any loan or investment amount at a fixed rate of interest with the same constant 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. The reason behind naming the function as PMT is because it calculates the payment amount.
PMT Formula in Excel
PMT formula in Excel has the following arguments:
There are five parameters that are used in this PMT function. In which three are compulsory, and two are optional.
COMPULSORY PARAMETER :
- Rate: It is the interest rate that we need to pay 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: It is the number of periods in which the loan is to 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. In case 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 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 exact amount that has to pay 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:
18 Online Courses | 7 Hands-on Projects| 85+ Hours| Verifiable Certificate of Completion
4.5
View Course
We have to make sure that the interest rate should be monthly, which we to 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 as well, including interest and principal.
So the Result will be $1,120,951.18.
Example #2
PMT function in Excel also helps us calculate the amount we need to invest monthly to get the fixed amount in the future. There are lots of 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 0.417% by 5% divided by 12months (because we are investing monthly, if we want to invest quarterly, then divide it by 4)
In case the payments are made annually, we can use 5% as the interest rate.
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 are paying an equal monthly installment or investing monthly, then the cash is going out from our pocket; that is why the sign is in 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 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 are calculating monthly or 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 result.
Relevance and Uses
- PMT function in Excel helps us to give the periodic payment of a loan.
- PMT function helps us to determine the installment amount.
- PMT function is generally used in financial institutions, where the loan is given or investment made.
Things to Remember
- #NUM! error –
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 are calculating monthly or quarterly payments, in this situation, users 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 and how to use the PMT function in Excel, and practical examples and downloadable excel templates. You can also go through our other suggested articles –
