The PPMT & IPMT function comes under the financial function group. We all know that the PMT (EMI) amount is consisting of two things one is the principal amount, and another is an interest amount. In our previous blog post we have learned how can we calculate the EMI in Excel?
However, if we want to extract and understand that how much principal amount and Interest amount, which we are paying in EMI then we will use the function is “PPMT” & “IPMT” Function.
Hope all of you are excited to learn as we are! Let’s dive in the ocean of Excel and understand the PPMT & IPMT function.
Note: PPMT stands for – Principal per month and IPMT is standing for – Interest per month
Syntax of PPMT & IPMT function-
Before elaborating the syntax of PPMT & IPMT, we would like to tell one thing that the arguments of both functions ( PPMT & IPMT) seem to be similar however their work is totally different.
Just look at the above screenshot, we can see the syntax of both functions seems to be similar. we will understand it with some practical example further but first, let’s understand the syntax-
Rate (mandatory arguments)-: It is the interest rate per period
Per (mandatory arguments)-: It is the bond maturity date, that is the date of expires
NPER (mandatory arguments)-: it is the total number of payments terms
PV (mandatory arguments)-: The present value on which EMI will calculate.
FV (optional arguments)-: It specifies the total amount, which we monthly invested. It’s an optional argument so we can omit it or type zero.
Type-: It specifies that the payment is made at the beginning of the month or the end of the month. It can understand the value by putting 0 and 1. If we put 0, it means that payment is made at the end of the month. If we put 1, it means that payment is made at the beginning of the month. If we omit it, it will take by default 0 meaning payment is made at the end of the month.
How to use PPMT & IPMT function in Excel:-
Example:-
We have taken a loan of INR 4,50,000 with the rate of interest 8% (p.a.) for the time period of 1 Year. So, we have to calculate the payable monthly EMI as well as we want to calculate the Principal and Interest per month for the time period of 12 months. Refer the right side given table into the screenshot.
First, we will calculate the EMI in cell E7 with formula PMT-
Note: For complete understanding PMT function, kindly go through our previous blog post.
We get the result below-:
Now, we will calculate the principal and interest amount-:
Apply the formula as explained below-
=PPMT ($B$4/12, E2, $B$5*12, -$B$3)
Rate-: We are paying a monthly amount that’s why interest rate should be divided by twelve.
Per-: as explained above, it is the bond maturity date which starts from the first month (cell F2)
NPER-: as we are calculating PPMT on a monthly basis that’s why we multiplied by 12, to convert year into months.
PV-: Loan amount B3.
(when we calculate the EMI, it gives us a negative answer because it behaves like a cash outflow, however, If we want to turn a negative number into positive, just put a minus sign before PV. Refer the formula)
FV and type -: Omit it, as it is not required here.
Notes: Except for Cell (F2), every cell should be locked by pressing cell (F4) because we will drag this formula towards down to calculate all the month’s value, Cell address should not be changed)
To calculate Interest amount, apply the formula as explained below-
=IPMT($B$4/12,E2,$B$5*12,-$B$3)
We got the result below-
Now, pull the edge of cell F2 and G2 towards downward till the end of month 12. we would get all the principal and interest amount for the entire period.
Look at the screenshot below:-
Now, As we all know that, we pay constant EMI every month, so if we add the values of (PPMT+IPMT), it would be the same for the entire month.
Look at the screenshot below:-
Isn’t it cool? We can observe that, as we keep paying the EMI, our principal values are increasing and liabilities( interest ) is going down.
We hope, this blog post helps you to understand, about the calculation of Principal and Interest amount. You can download the practice file and try it by yourself. If you have any query feel free to ask in the comment box and if you like this, share with your friends and colleagues.
Stay tuned with www.nurturetechacademy.in
Happy Learning 😊 😊