If you've used the Excel PMT function, you'll know that it can be used to calculate period repayments for a loan, such as a mortgage, taken out for a particular term at a fixed interest rate. This article recaps how to use the PMT payment function, and also describes two other very useful payment functions IPMT and PPMT used to calculate the interest and capital elements of these period repayments.

The Excel PMT Function to calculate fixed monthly repayment amount

This function calculates the regular repayment amount for a capital loan taken out over a certain numbers of years at a fixed interest rate. The parts of the PMT function are =PMT(Interest, periods, Amount). Suppose we enter the annual interest rate as 4% in cell D2, the term in years as 25 in cell D3, and the loan amount 100,000 in cell D4. We want to calculate the monthly repayment amount in cell D5.

Because we want to work out the monthly repayments, we need to enter the interest rate per month, and the loan term in months. So the interest rate will be D2/12 and the term will be D3*12. To calculate the monthly repayment in cell D5 we type the following: =PMT(D2/12,D3*12,D4) and press the Enter key. The resulting outgoing monthly payment will show as -527.84. To make this a positive figure, add a minus sign to the formula which now shows as =-PMT(D2/12,D3*12,D4). This monthly payment amount is fixed for the life of the loan.

Although your monthly repayments over the loan term are always for the same amount, the proportion of interest and capital you repay each period varies over the term. At the start of the loan you pay mostly interest and a little capital, but by the end of the term you pay a little interest and mostly capital.

For each period of the loan you can calculate the interest amount by using the IPMT function, and the capital amount by using the PPMT function.

Using the Excel IPMT Function to calculate the first month's interest payment

The IPMT function calculates the interest repayment for each period for a capital loan taken out over a term at a fixed interest rate. The parts of the IPMT functions are =IPMT(Interest, period number, periods, Amount). You can see the extra element "period number" in this function compared to the PMT function. In this example we want to calculate the interest payment for period 1 (the first month) and we'll use the same data as in the above example. We want to type the IPMT function into cell E5.

If we want to calculate monthly amounts remember the interest should be D2/12 and the term D3*12. In cell E5 we type in the formula =-IPMT(D2/12,1,D3*12,D4) and press the Enter key. The first month's interest repayment will show as 333.33.

Using the Excel PPMT Function to calculate the first month's capital repayment

The PPMT function works in the same way but calculates the capital repayment for each period for the capital loan taken out over a term at a fixed interest rate. The parts of the PPMT functions are =PPMT(Interest, period number, periods, Amount). Rate is still D2/12 and the term is still D3*12 and this function also has the extra element "period number" compared to the PMT function. We'll again use period number 1. We enter the PPMT function in cell F5, so we type in the formula =-PPMT(D2/12,1,D3*12,D4) and press the Enter key. The first month's capital repayment will show as 194.50.

So the first monthly payment of 527.84 consists of 333.33 interest and 194.50 capital.

Using PMT, IPMT and PPMT to calculate the last month's payments

The last monthly payment amount is still the same as for all the other monthly payments as the amount is fixed. We'll type this into cell D6, so we type =-PMT(D2/12,D3*12,D4) which still gives us 527.84. However the interest and capital amounts are different. The last month number for a loan over 25 years will be month number 25*12 equaling 300. So in cell E6 type in the interest due for the last month as =-IPMT(D2/12,300,D3*12,D4) and press Enter key. The interest equals just 1.75. We'll type the capital repayment into cell F6 as =-(PPMT(D2/12,300,D3*12,D4) and press Enter key. The capital repayment equals 526.08.

So the last monthly payment of 527.84 consists of 1.75 interest and 526.08 capital. You might like to create a table showing the PMT, IPMT and PPMT payments for every month of the loan to see how the proportions change over time.

So in conclusion, Excel has three very useful payment functions. PMT calculates the fixed monthly repayment of a loan taken out over a certain timescale at a fixed interest rate. However the amount of interest and capital you repay varies through the life of the loan. You can calculate these for any particular period. IPMT calculates the interest amount and PPMT calculates the capital amount so you can always determine the proportions for each payment.

Want to learn more about Excel financial functions? A really effective way is to attend a training course and really watch your Excel skills increase.