Show
PMT Formula in ExcelPMT 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:
OPTIONAL PARAMETER:
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 #1Suppose 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: 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 #2PMT 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 12 months (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
Things to Remember
Situations are below:
It occurs when any of the arguments provided are non-numeric.
Recommended ArticlesThis 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 –
What is the formula behind PMT function in Excel?In cell C6, the PMT function calculates the monthly payment, based on the annual rate, which is divided by 12 to get the monthly rate, the number of payments (periods) and the loan amount (present value): =PMT(C2/12,C3,C4)
What is the PMT equation?=PMT(rate, nper, pv, [fv], [type]) The PMT function uses the following arguments: Rate (required argument) – The interest rate of the loan. Nper (required argument) – Total number of payments for the loan taken.
What is PMT in FV formula?FV=PMT(1+i)((1+i)^N - 1)/i where PV = present value FV = future value PMT = payment per period i = interest rate in percent per period N = number of periods.
|