Whether you are trying to buy a new house, looking at buying a new car or applying for MORE student loans, you have no doubt wondered what your monthly payment would be. Excel provides an easy to use formula in which you can enter the loan information, and it will calculate the monthly payment for you.  Once you learn to use it, it’s actually very simple.  So let’s go ahead and take a look at the PMT Formula:

=PMT(rate, nper, pv, fv, type)

where rate is your interest rate of the loan, nper is your number of payments for the loan (a 10 year loan with monthly payments would be 120 for nper), pv is the present value of the loan, fv is the future value of the loan (usually omitted from the calculation), and type is 0 if payments are at the end of the period or 1 if payments are the beginning of the period (also usually omitted from the calculations).

So normally we will format our formula like this: =PMT(rate, nper, -pv).  We take the negative of the present value here so that our loan payment calculation will be a positive payment.

Examples
Let’s go ahead and take a look at a few examples to clear this formula up for everyone.

Example 1: Calculate the monthly payment on a 10 year $50,000 loan with an interest rate of 5%.

=PMT(.05/12,120,-50,000) will return $530.33.  Since the rate is the rate is 5% a year and calculated monthly, you must divide the interest rate by 12 as I did above. The number of payments is 10 years at 12 per year which is total of 120.  And to get a positive payment amount for your answer you must make the present value of the loan negative.

Example 2: Calculate a 5 year loan for $20,000 at 10% interest, with quarterly payments.

=PMT(.1/4, 4*5, -20000) which also equals PMT(.025,20,-20000) will return $1282.94.  This time instead of dividing by 12 for monthly payments, we divided by 4 for quarterly payments.  Also when calculated the number of payments involved, we took 5 years times 4 payments per year for 20 payments.

Example 3: Calculate a 30 year loan for $200,000 at 6.5% interest, with monthly payments.

=PMT(.065/12, 30*12, -200000) which also equals PMT(.005417,360,-200000) will return $1264.14.  This is what a mortgage payment may look like for you.

Conclusion
This can be a very useful formula when trying to figure out what you can afford or hopefully help ease the shock of a new bill coming to your house for a new purchase.  There can be many variations of the formula used which we will explore soon, but for now this is a good place to start.  As always, if you have any questions, please you the contact form, or leave a comment below.