Using the Built-in Functions
We’ve now seen how the principal and interest components of each payment are calculated. However, you can use a couple of built-in functions to do the math for you. These functions also make it easier to calculate the principal and/or interest for any arbitrary payment.
The two functions from the Finance menu that we are going to use are the IPMT (interest payment) and the PPMT (principal payment) functions. These functions calculate the amount of interest or principal paid for any given payment. They are defined as:
IPMT(Rate, Per, NPer, PV, FV, Type)
PPMT(Rate, Per, NPer, PV, FV, Type)
So, using our data from above, we can calculate the amount of interest in the first payment with:
=IPMT(B4/B5,1,B3*B5,-B2)
and we get $1,125. The amount of the principal in the first payment is:
=PPMT(B4/B5,1,B3*B5,-B2)
which gives $172.20. Those answers match exactly the ones that we calculated manually above. Note that in both functions, we specified that Per (the payment period) is 1 for the first payment. We would specify 2 for the second payment, and so on. Obviously, we will use a cell reference in our amortization table.
Excel does not have a built-in function to calculate the remaining balance after a payment, but we can do that easily enough with a simple formula. Simply take the beginning balance minus the principal paid in the first payment and you will find that the remaining balance after one payment is $199,827.80:
Principal Balance After 1st Payment = 200,000 – 172.20 = $199,827.80