

The PMT Function in Microsoft Excel  Equal Monthly Payments Calculator 

Home  Back  Next  

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 sysntax of the function is: =PMT(rate,nper,pv,fv,type) 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 instalments 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 instalments at the end of the month and then the value of type is '0' or you may agree to make the monthly instalment 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 instalment 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 therfore 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. 



Home  Back  Next 