This Tips and Tricks is in continuation of Tips & Tricks 84 – Financial Function – Calculate EMI. Now the EMI for a month = Interest for that month and Principal for that month. IPMT is used to calculate the interest portion of your EMI. This I talked about in Tips & Tricks 87 – Financial Function – Calculate Interest Part of an EMI. To calculate the principal part of an EMI, you will need to use PPMT.
Excel defines PPMT as "Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate."
The syntax of PPMT is PPMT(rate, per, nper, pv, [fv], [type])
rate: You rate of interest
per: Period for which you want to calculate Principal
nper: No. of payments. Your nper and rate should be on the same scale. i.e if you are planning to pay up monthly, the rate in your formula should be monthly only. Generally, interest rate is specified yearly i.e. 10.5% per year. This you should divide by 12 to arrive at monthly rate. Hence, if you wanted 3 years loan, it means nper would 3×12=36 months.
If it is quarterly, rate = 10.5%/4 = 2.625% and nper would be 3×4 = 12
If it is annual, rate = 10.5% and nper = 3
pv: Your loan amount. You will need to put negative value of this in your formula. If you don't put negative value, your EMI would be in negative but answer would be same though with negative sign.
+ve / -ve PPMT requires some explanation though you may choose to ignore. It depends upon your cashflow. If you are taking a loan, hence cash in, hence pv is +ve. But every month, you will have to pay up something, hence cash out. Hence, PPMT is -ve. If you are investing, hence cash out. Hence pv is -ve. But every month, you will be receiving something, hence cash in. Hence, PPMT is +ve.
Now what is +ve or -ve is simply your preference. I recommend you should not worry about this.
fv: Your remaining value after you finish your installment. Generally, it is 0 as any lender will like to recover its money fill. (Default is 0)
type: 0 – At the end of the period, 1 – At the beginning of the period (Default is 0)
Also note, fv and type are optional and may not be required in your formula.
The formula used in the below picture is =PPMT(B1/12,B2,B3,-B4,B5,B6)
Also, since Principal will vary every month, hence it makes sense to calculate it for each month. Columns H & I carry Principal for each month.
Bonus Tip = If you use ABS function, then there would be no need to put negative value of PV. Hence, formula in this case would be =ABS(PPMT(B1/12,B2,B3,B4,B5,B6))
The below EMI Calculator can be download from here Principal Payment Calculator
