In this instalment of Microsoft Excel tips, we discuss how to us the PMT and CUMIPMT functions

Today's Excel tip for those less experienced users is how to use the payment (PMT) and cumulative interest payment (CUMIPMT) functions.

These formulas (functions) allow you to 1) work out the regular payments required and 2) display the cumulative interest payments made over the term of the loan.

Before you start ensure you have the ToolPak add-in for Excel turned it on (and installed). To double check follow these steps:

1. On the Tools menu, click Add-Ins.
2. In the Add-Ins available list, select the Analysis ToolPak box, and then click OK.
3. If necessary, follow the instructions in the setup program.

To demonstrate how these two functions work we have created the following example.

Bruce Lee is looking to buy a house and already has a deposit of $200,000. Bruce requires a loan of a further $400,000 to buy the house he wants. The bank is currently prepared to offer Bruce a loan (mortgage) for $400,000 at a fixed interest rate of 5.5% over 25 years. Loan repayments are made monthly at the end of each month.

How much are the monthly repayments?

Assuming there are no complexities with the loan and it is based on a standard table mortgage we can use the PMT function to calculate the monthly payments.

The formula is PMT( interest_rate, number_payments, PV, FV, Type )

- interest_rate = the interest rate for the loan which in this example is 5.5% p.a. or 0.46% per month.

- number_payments = the number of payments for the loan and in our example this is 25 years or 300 months (25 yrs * 12 mths)

- PV = the present value or principal of the loan and we know Bruce wants to borrow $400,000.

- FV is optional. It is the future value or the loan amount outstanding after all payments have been made. If this parameter is omitted, in Excel 2003 the PMT function assumes a FV value of 0 which is basically what we want to see anyway. For those using a later version of Excel enter the desired FV.

- Type is optional. It indicates when the payments are due. 1 means payment are due at the beginning of each period (each month, for example) 0 means payment at the end of each period. in our example we have assumed the payments are made at the end of each month. When the payments are made does alter the result of the calculation.

Entering our variables into the formula [=PMT(0.46%, 300, 400,000,0,0)] we get the monthly payments of $2,456.35. If we elected to pay the interest at the beginning of the month then the repayments change to $2,445.14 per month. The difference is approximately $11 per month which over 300 months is a considerable sum.

If we wanted to make the repayments every two weeks we would divide the interest rate by 26 and the number of years by 24. The effective repayments would then be $1,177.54 or $1,175.06 every two weeks depending on the timing of the payments.

Note: When using the formula the result appears as a negative number but in this particular instance it does not matter. However if you want to just see a positive PMT number then make the amount of the loan a negative number.

Work out how much cumulative interest is paid on the loan

Now we now how much we have to repay to the bank every month (or fortnightly) we can work out the cumulative interest payments.

- interest_rate = the interest rate for the loan which in this example is 5.5% p.a. or 0.46% per month.

- number_payments = the number of payments for the loan and in our example this is 25 years or 300 months (25 yrs * 12 mths)

- PV = the present value or principal of the loan and we know Bruce wants to borrow $400,000.

- Start and end period are numbers between 1 and the last period so in our example they would be between 1 month and 300 months or whatever number of periods you chose to use.

- Type is optional. It indicates when the payments are due. 1 means payment are due at the beginning of each period (each month, for example) 0 means payment at the end of each period. in our example we have assumed the payments are made at the begininng of each month. When the payments are made does alter the result of the calculation.

Entering the variables into our CUMIPMT formula it looks like this [=CUMIPMT(0.46%,300,400,000,1,300,0)] and the result is -$336,904.99. This means that over the life of this particular loan Bruce is paying a total of approximately $337,000 in interest on a $400,000 loan over 25 years.

If we wanted to know how much interest we had paid over 15 years for example, your end period value assuming monthly repayments would be 180 (15 years *12 months ) rather than 300 in the previous formula. The result should therefore be $268,479.88

To find how much cumulative interest you have paid over the last 10 years of the 25 year loan simply change the start period and end period to 180 and 300. The result is $69,468.96

We encourage you to use the comment section below to add in your own tips about this function. Or ask a question ...