PMT Function in Excel
PMT Function is one of the financial functions which is available in Excel. PMT function in Excel helps us to give the periodic payment of a loan. If we know the total amount of loan, interest rate and the no. of payments that are constant or fixed. There are many situations where we want to know the monthly installments of the loan or investments with all the required data given, then the PMT function helps us to determine the installment amount. The interest rate should be constant throughout the whole period of the loan. PMT function is generally used in Financial institutions, where the loan is given or investment made.
PMT Formula in Excel
Below is the 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 which 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?
PMT function in Excel is a very simple and easy to use. Let us now see how to use this PMT function in Excel with the help of some examples.
PMT in Excel – 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 $9,00,000.
In this case, PMT function helps in determining the exact amount which has to pay each month.
4.5 (704 ratings)
View Course
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 have to make sure that the interest rate should be on a monthly basis 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 loan has been taken
=F5*F7 (i.e. 5 years *12 months)
which results in 60 Months.
Now we will find the PMT function by using the below formula.
=PMT(F4,F6,D5)
Hence, $18,682.52 is the EMI which 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.
PMT in Excel – Example #2
PMT function in Excel also helps us to calculate the amount which 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 the few purpose or goal, in this situation PMT function helps.
For example: Suppose we want to invest to get $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 in 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 with below error while performing 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 above, we came to know that above are the few common errors.
Below are the few errors which is also encountered by the users while applying 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 annually payments, sometimes forget to convert annual interest rates or the number of periods according to the requirement. Hence, it gives the wrong calculation or result.
Things to Remember About the PMT Function in Excel
- #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, at 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.
Hence, above are the basic explanation for PMT function in Excel. We have seen the usage, what is the output, how it works, how to use an Excel sheet, the errors while using, and the few points to remember while performing PMT Function.
Recommended Articles
This has been a guide to PMT Function in Excel. Here we discuss the PMT Formula in Excel and how to use PMT function in Excel along with practical examples and downloadable excel templates. You can also go through our other suggested articles –
