Using Excel to Calculate Repayments & Interest using PMT, IPMT & PPMT Functions
If you’re borrowing or saving money, and want to compare rates, perhaps perform some What If analysis, there are built in function in Excel which can quickly help you
PMT()
The main one is PMT()
This function takes a value, a rate and a ‘number of periods’ value and gives you the repayment.
There are also two optional values, which we’ll cover.
In this example, we have a £5,000 loan, with a 5% annual rate, and want to pay it back over 36 payments.
The function should look like this…
=PMT((ANNUAL_RATE/12),NUMBER_OF_PAYMENTS,STARTING_VALUE)
You’ll note that I’ve divided the interest rate by 12 – this is because we’re provided an annual % rate, but want to provide the payments monthly, so we need the rate to be in the same denomination as the payment frequency.
The answer you should get is…
Note: Excel will always return it as a negative value. If you want it as a positive value, simply wrap the PMT function with ABS()
As I said, there are two other options you can specify within the PMT function
[fv] = Final Value. That is, what balance do you want to ‘close’ with? By default, this is zero [type] = Specifies if the payment is made at the beginning (=1) ,or end (=0), of each payment period. By default, it’s set to 0 (end of the month).
IPMT()
If you want to take it further, and want to see the split between your interest payment and your capital payment. This is especially useful if you’re calculating larger loans, such as a mortgage
To calculate the interest payment, use the IPMT function
=IPMT((ANNUAL_RATE/12),PAYMENT_NUMBER,NUMBER_OF_PAYMENTS,STARTING_VALUE)
So it uses the same options and values as the PMT function, but this time you must tell it which month you want to see the interest for – this will be a value between 1 and the number of payments.
As with PMT, you can specific the [fv] and [type] values for IMPT
PPMT()
To calculate the capital payment, use the PPMT function, and use all the same values as for IMPT
=PPMT((ANNUAL_RATE/12),PAYMENT_NUMBER,NUMBER_OF_PAYMENTS,STARTING_VALUE)
Once again, specify [fv] and [type] if required.
And there you have it. Three complimentary functions to understand your repayments better.
In my example below, changing the ‘Month’ value updates the IPMT and PPMT functions accordingly
Leave a Reply