Financial Functions IPMT and PPMT in Excel
You can use PMT function to calculate the periodic payments for a loan assuming the payments and interest rate are constant. If you want to calculate the amount of interest you pay towards the loan and also the amount of the balance paid down, you can use IPMT and PPMT functions. The amount of balance paid down is also called payment on the principal. The syntax of IPMT function is IPMT(rate, period, nper, pv, fv, type) and the syntax of PPMT function is PPMT(rate, period, nper, pv, fv, type). Here, rate is the interest rate per period on the loan. The period is the period for which the interest or principal is calculated and nper is the number of payments made. The pv is present value or the amount of the loan and fv is the cash balance you want to attain after the last payment is made. The type is a logical value to identify whether the payment is due at the beginning of the period (type =1) or at the end of the period (type =0). The arguments fv and type are optional for both IPMT and PPMT functions.
To illustrate the concept, imagine that you borrow $10,000 for one and a half year period with an annual interest rate of 24%. You plan to pay back the loan amount in monthly instalments. Let’s now calculate the amount of interest you pay each month towards the loan and also the payment on the principal. Create an Excel sheet with data as given below. You should format the cells containing amount to currency of your choice. In the given below Excel sheet, B2, C6:C23, D6:D23, E6:E23, F6:F23 and G6 to G23 are formatted to accommodate currency. Here, the currency format selected is $.
First let’s calculate the monthly payment using PMT function. Click in cell E1 and enter the formula =PMT(B3/12,B2,B1). Now you will get the value -$667.02 in cell E1. It is true that the minus sign is to indicate a debt. Still, it would be great if all amount values are shown without minus sign. So, just negate the equation in cell E1 by changing it as =-PMT(B3/12,B2,B1) (It is not part of the PMT formula: it is just for clarity and convenience). This is the value to be shown in cells D6 to D23. You just need to copy the value from E1. Don’t forget to go for absolute cell reference. So enter the formula =$E$1 in cell D6 and copy it in cell from D7 to D23. We need to enter the beginning balance for the first month. So click on cell C6 and enter the formula =B1.
Now, if you wish to calculate the interest paid towards loan each month, you need to use the function IPMT. As per the syntax IPMT(rate, period, nper, pv, fv, type), we know value of rate, number of payments made and also the loan amount. The second argument period is the period number for which you compute the interest. So, when we calculate the interest for the first month, the value of period will be 1 and for second month, the value will be 2 and so on. So, the value in the corresponding B cell (Months) can be passed as the period argument value. Now, click on cell F6 to calculate the interest for the first month and enter the formula = -IPMT($B$3/12,B6,$B$2,$B$1). Here, you have to absolute reference the cells B1, B2 and B3. The value of period should be relative referenced as this value should change from 1 to 2, 3, 4 and so on. Copy this formula in cells F7 to F23.
Now to calculate the payment on principal for the first month, the arguments to be passed are same. So click on the cell E6 and enter the formula =-PPMT($B$3/12,B6,$B$2,$B$1). Copy this formula in cells E7 to E23. Again remember that the negative sign in IPMT and PPMT formulas are not part of the formula. As explained earlier, it is just to display all amount values without minus sign. It is clear that you get the ending month balance when the payment towards principal is subtracted from beginning month balance. So, click on cell G6 and enter the formula =C6-E6 and copy this formula in cells G7 to G23. To display beginning balance in required cell, click on cell C7 and enter the formula =G6 and copy this in cells C8 to C23. After entering all these formulas, you will get a screen as follows. As all calculations are using cell references, you should have entered the values exactly in the same cells shown in the picture above.
Now, if you analyze the sheet, you could find that when you add the principal and interest of each month together, you will get the monthly payment value. Moreover, the amount paid towards the principal increases each month and the amount of interest paid decreases each month. You could find that with a beginning balance of $10,000 in first month, the ending balance became $0 by the end of 18 months (loan period).
Read More about Excel here