PMT formula in excel is a financial formula which is used to calculate the payment for a loan and it takes rate, nper, present value and future value as an input, this is an inbuilt function in excel and can be accessed by simply writing =PMT( ) and providing the required input in integers, the output returned by this formula is also an integer value.
PMT Formula in Excel (Table of Contents)
Excel PMT Formula
PMT formula is categorized under the financial formula in excel. The syntax for PMT formula in excel is explained as follows:-
The details of the arguments in the function are
- Rate:- This is the interest rate for a loan expressed in percentage
- Nper:- The number of loan payments to be made.
- Pv:- This is the present value of the future payments that are worth as of now.
- [fv]:- This is an optional argument, here the future value describes the amount required to be attained after the last loan payment. If this argument is omitted or left as blank, then the function will assume that the future value is zero
- [type]:- This is again an optional argument, which describes when the periodical payment is due. The function gives two options in the argument:-
- ‘0’:- States that the payment at the end of the period
- ‘1’:- Payment to be made at the beginning of the period.
If no argument is given or this is left blank or omitted then the function will assume ‘0’ or the payment will be done at the end of the period.
How to Use PMT Formula in Excel? (with Examples)
There are various uses of PMT formula in Excel which we will learn through various examples.
Consider the below data table to understand the calculation of PMT. Assume that you need to purchase land worth $2,500,000 and you have taken a loan from the bank of $2.5M at 14% interest rate and the amount must be repaid in 2 years on a monthly basis. By using the PMT formula in Excel will compute the payment to be made at the start of each month and at the end of the month.
Step 1:- Let us understand the input to be given to the parameters in the PMT function.
- Interest rate should be divided by 12 to convert the annualized interest rate to monthly interest rate.
Interest rate = 14/12 = 1.67%
- Duration of 2 years should be converted into months.
Duration = 2*12 = 24months
Step 2:- Now that we know the inputs, let us enter the PMT formula in Excel to calculate the monthly payments.
At the end of the month.
As can be seen in the above screenshot, the interest rate is being divided by 12 and the duration is multiplied by 12 in order to arrive at the amount for monthly payment. Also, the calculated amount is shown as negative red because the payment would be flowing out of your bank account. We have not given the values for future value and type argument in PMT formula as these syntaxes are optional.
The ‘type’ argument if not entered is assumed to be computed for end of the period by default. If the amount to be calculated has to be at the beginning of the period or month then the type argument must be entered as ‘1’.
The calculated amount in cell’s C10 and C11 factors into account both interest and principal which is usually associated with the loan amount to be paid.
Now assume that you need to build an investment amount of $1,000,000 by the end of 20 years with the interest rate of 10%. Payments should be made on an annual basis at the end of each year. You need to compute the amount to be paid annually by using the excel PMT formula.
Step 1:- As can be seen in the above screenshot, the present value argument has been left blank as we do not need it in our calculations and the future value required is $1 Million. Here interest rate and duration would be the same as the amount to be computed is required to be an annualized basis.
The ‘type’ argument can be omitted or ‘0’ can be also entered in the PMT formula in excel while calculating the annual payment.
As can be seen above, the amount is calculated as negative. This amount would be flowing out from your bank account on an annual basis.
If you must pay a one-time payment as a lump sum amount of $1,500,000 in a tax saving mutual fund, at an assumed interest rate of 25% for a duration of 5 years. Then the amount that you would be receiving on a quarterly basis can be calculated by using the PMT formula in excel.
Below is the data table with the required parameters for the calculation.
The present value is entered as negative as this amount would be flowing out of your bank account.
Step 1:- As the amount received would be on a quarterly basis, we must make the following calculations for the parameters.
- Interest rate:- The annualized interest rate should be divided by 4.
Interest Rate = 25/4 = 6.25%
- Duration:- The annual duration must be converted to a quarterly basis by multiplying the duration by 4.
Duration = 5*4 = 20
Step 2:- Using the above calculations to arrive at the required result by entering them into the PMT formula.
As can be seen in the above screenshot, the amount to be received is positive as the payments would be flowing into your bank account. The PMT formula in excel has the parameters calculated for interest rate and duration. If we multiply the final payment received (computed from PMT formula, cell C10), we will arrive at the total annual amount for the entire year.
Things to Remember
- When computing PMT for the loan borrowed, based on the periodic installment such as monthly, quarterly, etc. The interest rate and the number of installments (nper) to be paid must be multiplied to month or quarter accordingly.
- If any argument entered within the PMT function is non- numeric then the formula returns ‘#VALUE!’ error message.
- Moreover, if the values entered for the interest rate is in negative or the value entered for the number of payments ‘nper’ is zero then the PMT formula will return ‘#NUM!’ error message.
You can download this PMT Formula Excel Template from here – PMT Formula Excel Template
This has been a guide to PMT Formula in Excel. Here we learn how to use PMT formula in Excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –