It helps you calculate the payment you need to make for a loan when you know the total loan amount, interest rate, and the number of constant payments. For example, suppose you buy a house for USD 200,000. Since you don’t have that kind of cash, you get a home loan at a 4% annual interest rate. Now, you have to pay the loan installments every month for the next 20 years. Excel PMT function can calculate the exact amount you need to pay every month.
PMT Function Syntax
Below is the syntax of PMT function in Excel: =PMT(rate, nper, pv, [fv], [type])
rate: It is the interest rate you need to pay per period. For example, if it’s monthly payments, it will be rate/12. Similarly, if it’s quarterly, it will be rate/4. 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. In the above house loan example, this would be USD 200,000. fv: [optional argument] It is the future value of your payments you want after the loan is paid off. In case you only want to get the loan paid and nothing else, omit it or make it 0. type: [optional argument] 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.
PMT Function Examples
PMT function can be used in many different ways in Excel. Below are some examples of using it.
Example 1 – Calculating the Monthly Loan Amount in a House Mortgage
Suppose you have a house loan of $200,000 that needs to be paid back in 20 years when the payment is made every month, and the interest rate is 4%. Here are details regarding the arguments:
rate – 4%/12 (since this the payment is monthly, you need to use the monthly rate). nper – 20*12 (since the loan is to be paid for 20 years every month) pv – $200,000 (this is the loan value that I get today)
You can omit the optional arguments as these are not needed. Below is the formula that will calculate the loan payment amount using the PMT function: =PMT(C3,C4,C2)
Note that the loan payment is negative as it’s a cash outflow. If you want it to be positive, make the loan amount negative. Also, remember that the interest rate remains constant throughout the period.
Example 2 – Monthly Payment to Grow Your Investment to USD 100,000
You can also use the PMT function to calculate how much you should invest per month to get a certain amount in the future. For example, suppose you want to invest in a way to get USD 100,000 in 10 years when the annual interest rate is 5%. Here is the formula that will calculate it: =PMT(C3,C4,,C2)
Note that since the payments are monthly, the interest is taken as 5%/12. In case the payments are made annually, you can use 5% as the interest rate (as shown below).
Calculating Weighted Average in Excel Calculating CAGR in Excel Calculating Compound Interest in Excel Calculating Standard Deviation in Excel Creating a Bell Curve in Excel Calculating NPV (Net Present Value) in Excel
“Mr. Adam wants to sign an insurance contract. He expects to earn $110000 after 18 years at 15% annual interest rate. How much money he must pay monthly for the insurance company, knowing that when signing the contract Mr. Adam paid $10000?” rate: 15%/12 nper: 1812 pv: 10000 fv: 110000 I tried calculate PMT in 4 formulars: 1. =PMT(15%/12,1812,10000,110000) 2. =PMT(15%/12,1812,-10000,110000) 3. =PMT(15%/12,1812,10000,-110000) 4. =PMT(15%/12,18*12,-10000,-110000) I do not know which formular is right I’m very confuse in using PMT function, especially using PV and FV parameter sign (- or +). Could you explain to me the problem above? I’ve found and read Excel’ help and other examples on the web but still not sure exactly. Thank you so much and looking forward to hearing from you! Keep sharing these tips, I really appreciate your tips.
