The PMT Function in Microsoft Excel – Equal Monthly Payments Calculator
The PMT function in Microsoft Excel is one of the most widely used functions. It calculates the payment for a loan based on constant payments and a constant interest rate. It is assumed that you will make a periodic (monthly, yearly, etc.) payments of a loan and that the interest rate will not change during the payment period. This is the exact scenario when you take a car or house loan from a bank. In case the interest rate changes you’ll have to adopt an appropriate calculations strategy which is also quite easy using MS Excel spreadsheets.
The syntax of the function is:
The ‘rate’ is the interest rate of a loan you take. For example, the interest rate on our loan is 12.5% per annum.
The parameter ‘nper’ refers to the number of payment periods for the loan. We have taken the loan for 60 months or 5 years in our example. Since we wish to pay back our loan in installments every month, our payment numbers will total 60.
The parameter ‘pv’ or present value refers to the total amount of loan we take today. In our case it is 200000. It is also known as the ‘principal’.
‘FV’ or future value is the cash balance that we wish to achieve after the last payment. Most people are expected to make a full payment by the end of the payment period and therefore the value of FV is zero.
The parameter ‘type’ refers to the timing of the payments. When you take a loan you may wish to pay your monthly installments at the end of the month and then the value of type is ‘0’ or you may agree to make the monthly installment payment at the beginning of the month and then the type is ‘1’. When you make the payment at the end of the month as in our case the interest for the extra days is added to your installment and therefore you pay more at the end of the month and less if you agree to make the payments at the beginning of the month.
Note: The interest rates are generally per year and therefore if your calculations for payments have to be made on a monthly basis you must take this into account and divide the annual interest rate by 12 to calculate the monthly interest rate as in our example.
Also you can observe that you can use the PMT formula to calculate ‘FV’ or ‘Number of periods’ or the ‘annual interest’ depending on whether the other parameters are known to you.
Of course, the training video describes the PMT function in MS Excel thoroughly.
Watch the video:
MS Excel: PMT Function (WS, VBA)