PMT function is one of the function of the Financial Function in Excel. This function helps us to calculate the amount payable on a monthly basis. It is also known as an EMI calculator. When we have a Loan Amount, Number of payments and annual interest rate then easily we can calculate the payable EMI monthly.
Let’s take an example If we want to buy a brand-new car of INR 10,00,000 and we don’t have enough cash, then we take a loan from a bank or any company with a 4% annual interest rate. Now we must pay for the instalment every month for the next 20 years.
This PMT function helps us to know How much amount we will have to pay every month?
Are you excited as I am? Let’s get started!
As we can see in the above screenshot, if we take a loan of INR 10 lacks with 4% annual interest rate and we wanted to calculate how much EMI we have to pay monthly for 20 years.
The syntax of PMT function-
Below mentioned the syntax of PMT function in details-
Rate: – It is the interest which we need to pay in every period. If EMI is on monthly basis then Interest rate/12, If it is on a quarterly basis then interest rate/4 should be.
Nper: – It is the number of periods in which the whole amount to be paid.
PV (Present Value): – It is the present value of the loan. for example, in this case, the present value is INR 10,00,000.
FV (Future Value): – This option is Optional. It is the future amount which we want at the end of the instalment. If we want only to get paid the loan amount and nothing else, omit it or type zero. Type:- This option is also optional. If the due amount is to be paid at the end of the month omit it or type zero. If the amount is to be paid at the beginning of the month then type 1. For example, if due amount is on 30 April then type zero or omit it, If a due amount is on 1 April then type 1.
How to apply Formula-
Rate:- 4%/12 ( Convert the interest rate into monthly because we pay EMI monthly)
NPER:- 20*12 ( Multiplying it by 12 to convert the year into the month)
PV:- INR 1000000 ( this is the present value )
We can omit the optional arguments as both are not required for this case.
The final formula to calculate the EMI- =
Point to remember:- EMI will come into the red colour that is a symbol of the Negative sign. If we want to turn negative sign into positive then just put a minus sign before the present value. Amount of EMI will be constant same for whole periods.
We would like to share one more example for PMT Function:-
As we can see in the above screenshot, we want to know, how much amount we have to invest to get INR 50000 for 10 years with 3.33% interest rate?
Here is the solution:-
Rate:- 3.33%/12
NPER:- 10*12
PV:- Not given it, so omit it with putting another comma (,)
FV:- This question is asking for the future amount, so we will choose the expected amount INR 50000
Type:- Omit it
Final Answer is given below-
Point to be noted:-
- The payment returned by PMT function is included of principal and interest amount.
- Make sure if we are calculating EMI monthly then Interest rate/12 and NPER*12 should be.
- By default EMI’s values comes in negative because it is a cash outflow. IF you want to change it, simply put a minus sign before the present value.
We hope this blog post helps you to calculate the EMI. Download the practice file and try it by yourself. If you are stuck anywhere feel free to write down in the comment box or directly connect to info@nurturetechacademy.com We would love to help you.
In our next blog post, we are going to discuss How do we calculate the principal amount and interest amount? So, Stay connected with info@nurturetechacademy.com
Happy Learning 😊