The standard payment function PMT calculates the payment of the loan.

We need to enter

1. interest rate of the loan
2. number of payment periods, that means what is the payback period
3. present value or the loan capital, how much loan are we taking.

These are the mandatory arguments for the PMT function.

Interest rate is per month, so we need to divide the rate by 12. Number of payment period is 22 multiplied by 12 as payback period is 22 years. The loan is 200 000. The payment function returns the value 1087.

We needed to have a formula for rate and number of payments. The function return the value in negative value. Also 1087,36 might not be exactly same as what bank tells us if we ask the bank how much is the monthly payment for a loan of 200 000, payback time of 22 years, and with 3,5 % interest. Bank might include eg. a transaction fee of 1,1 € for each payment.

We can include the transaction fee in the PMT sentence. A bank might add a transaction fee for each annuity. Even though the transaction cost does not sound very high, it is good to include in the calculations.

If we are using often PMT function or we make an important decision and we want to calculate the exact value, we might consider creating own payment function.

Function z_pmt(z_rate, z_nper, z_pv)

    z_pmt = ((z_rate / 12) * z_pv) / (1 – (1 + z_rate / 12) ^ (-z_nper * 12))

End Function

Here we have a customized payment function. It takes into consideration that rate is divided by 12 and number of periods is multiplied by 12. The function return the value in positive. Our function holds three arguments: rate (z_rate), number of periods (z_per), and loan capital (z_pv). Those are in the same order as with PMT.

I am not going into details how the formula was built. Financial mathematic books shed light how annuity is calculated manually.

The function could automatically round into two decimals and add fixed transaction fee 1,1 € per payment.

Function z_pmt(z_rate, z_nper, z_pv)

    z_pmt = Round(((z_rate / 12) * z_pv) / (1 – (1 + z_rate / 12) ^ (-z_nper * 12)), 2) + 1.1

End Function

As PMT returns a negative value, we need to minus 1,1 in order to add the value. Customized Z_PMT counts positive values, so we add 1,1. In VBA decimal separator is dot independently what we have defined in application. Comma is separating arguments in VBA.

Maybe bank presents 0,5 % higher monthly payment than PMT function, for some reason. If you want to simulate monthly payments from the bank, we can simply multiply PMT function with 1,005 or just modify custom Z_PMT.

Function z_pmt(z_rate, z_nper, z_pv)

    z_pmt = Round(((z_rate / 12) * z_pv) / (1 – (1 + z_rate / 12) ^ (-z_nper * 12)) * 1.005, 2)

End Function

PMT is one of the best functions in Excel. If you ever consider taking loan from the bank, check calculations from bank with PMT in Excel.

If you need to modify the function, it can be useful to have your own customized function. Also taking a house loan might be the biggest finance decision you ever make. Therefore it is better to be precise when you calculate monthly payment for your house loan. That you don’t forget to budget a transaction fee of 1,1 € which bank might charge you per every transaction.