Calculate the Payment of a Loan with the PMT Function in Excel

Thanks to Excel’s PMT function, computing the monthly payment of a loan with various terms is easy.

In summary, you provide the function with the basic loan information, including the loan amount, the interest rate, and the term, and the function will compute the payment.

You provide this basic loan information to the function through the function arguments, or parameters. There are actually five function arguments, three of the arguments are required and two are optional. An optional argument assumes a default value when omitted.

 

The function works like this:

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

Where:

  • rate is the rate per period, and must be consistent with the nper argument and with the period you wish to return.  In other words, if you want to compute an annual loan payment, then you should express this as an annual interest rate and nper should be expressed in years as well.  If you are looking for a monthly payment, then you want to convert this rate into a monthly rate and should express nper in months as well.
  • nper is the number of periods.  This needs to be consistent with the period expected for the monthly payment and with the rate argument.  If you want to return a monthly payment, express this argument in months, for example 360 for a 30 year term.
  • pv is the amount of the loan, or, present value.
  • [fv] is the optional argument for future value. In most cases, this will be 0 and since it is an optional argument if you omit it, the default value is 0.
  • [type] is an optional argument to define when the payment occurs.  0 or omitted tells the function the payment is at the end of each period, and 1 means the beginning of the period.

Let’s see how this might work in a worksheet.

Let’s try to determine the monthly payment of a home loan.

We are borrowing $200,000 for 30 years at 4%.

Since we want the monthly payment, we know we need to express the function arguments in monthly periods. So, we would set up the function as follows:

=PMT(.04/12,30*12,200000)

Where:

  • .04/12 is the annual interest rate divided by 12 so that it is expressed as a monthly rate
  • 30*12 is the number of periods, 30 years expressed as the number of months
  • 200000 is the amount borrowed

Of course, it is more likely that we would place the basic loan values into cells, and then use the cell references in the formula, something more like what is shown in the screenshot below.

 

 

 

 

 

 

 

 

 

 

 

For the most part, Excel’s financial functions work on a cash flow model, and since the loan amount is a positive inflow, the subsequent payments are an outflow. This is why the monthly payment amount is returned as a negative number. One small tweak is that we’ll flip the sign of the returned value by preceding the function with a negative, as follows:

=-PMT(B1/12,B2*12,B3)

Where:

  • B1/12 is the annual interest rate divided by 12 to convert to a monthly rate, since we want a monthly payment to be returned
  • B2*12 is the number of years multiplied by 12 so that the argument is expressed in months
  • B3 is the amount of the loan

And, the final sheet is shown below:

 

 

 

 

 

 

 

 

 

 

 

Thanks Microsoft!

This article was written by Jeff Lenning

104 comments:

  1. Denise Weber
    Reply

    I came to the Blog to see if I can reinforce what I have learned.

  2. Kelli C
    Reply

    As is the case in the classroom, Jeff you make it so clear and relevant in the BLOG format. THANKS.

  3. Sunny K.
    Reply

    Came over here from the online course to learn more about the PMT function. Thanks, Jeff!

  4. Carter Wicks
    Reply

    Great function – All these years of only using TValue. I hope to pick up many more useful functions throughout the course.

  5. Benjamin Wilkinson
    Reply

    Yes, very useful function…Obviously not for compound interest..?

  6. Kando O.
    Reply

    I wanted to see the further explanation/reinforcement on the PMT formula, you made the explanation on this exercise so clear and simple. Thanks!

  7. Renanah P.
    Reply

    This is a very useful function to know!

  8. Chris Wagner
    Reply

    I’ve been using the payment function for a while. One tip: Make sure you use the round function if you are building a schedule of payments, like an amortization schedule. After all, a borrower can’t pay in increments less than a penny.

  9. Amy W
    Reply

    Very helpful!

  10. Christian P
    Reply

    The PMT formula is a powerful tool when its explained in a simple manner. Thank you!

  11. James
    Reply

    Very interesting, and completely applicable

  12. CHAO JIN
    Reply

    It’s really nice example. It’s very helpful. Thank you.

  13. Brian M
    Reply

    Use this function all the time in banking and financial analysis. Thanks

  14. Alex
    Reply

    Will be useful in the future. Thanks.

  15. Elizabeth Wareing
    Reply

    This trick won’t only help me at work, but will help me with personal financial planning! Thanks Jeff!

  16. Anita
    Reply

    This will definitely be helpful in the future…great comment!

  17. Victor
    Reply

    Thanks. Very helpful.

  18. Jesse B
    Reply

    I was surprised that you divide the percentage by 12 for the monthly amount. I totally would not have figured that out on my own. 0.4/12! It’s always good to study these functions in detail, or you may forget what goes where.

  19. Adelina
    Reply

    New information and the examples are great.

  20. Anneliese Gilbert
    Reply

    Clearly explained example – well stepped out so it is easy to follow- good explanation around required and optional values.

  21. Hannah Wiser
    Reply

    Great tip! I know I will be able to use this!

  22. Wendy Rohaly
    Reply

    Thanks, very helpful.

  23. MaryAnn R.
    Reply

    Thank you very helpful. I know that I will be able to use this.

  24. Yesenia A.
    Reply

    Excellent PMT function explanation. Thanks alot.

  25. Kathy Shannon
    Reply

    Thanks Jeff! Great formula to know!

  26. Wan-LI Wu
    Reply

    1) Normally when we mention about interest rate and the term in our mind set – it refers to annual interest rate and annual term.
    But to use the “PMT” function to calculate the constant loan payment – it refers to “monthly” payment. So, I re-construct the “PMT function” and find out it is very helpful for me to use as follows:
    =PMT(annual rate/12, annual term*12, pv, [fv], [type])

    2) Knowing “ loan received” as positive inflow and monthly” loan payment” as negative outflow are very beneficial concept.

  27. Jeff Madden
    Reply

    The hardest part about using the PMT function is to make sure the rate and term agree. (I always forget to divide the annual rate by 12 when calculating a monthly payment!)

  28. Christof Mohr
    Reply

    Great function! It is good to always be aware that a percentage rate is almost always given in annual terms, unless specified.

  29. Allison J Lausten
    Reply

    A useful tool.

  30. Sheri
    Reply

    Interesting tool. I’m curious to put the info for my original home loan in and see if it works out to the same monthly payment. Something to try when I get home and have access to that info.

  31. Cindy Johnson
    Reply

    A familiar tool, but prefer lectures to reinforce concepts……thanks!

  32. Joe Horne
    Reply

    Thanks for the reinforcement!

  33. Christine Mast
    Reply

    Thanks once again for an informative post!

  34. Latha Nair
    Reply

    Jeff explained this function very simple. I am going to use it in future for sure.
    Thank you

  35. Wanda Hagen
    Reply

    Explained the concept very easily.

  36. Michael Fullen
    Reply

    Useful information, thank you.

  37. Beth Meador
    Reply

    Very easy to follow and understand!

  38. Lori
    Reply

    Good to know! Thanks!

  39. Lisa Croyle
    Reply

    Love this function. Well explained, easy to follow, and useful.
    Thanks.

  40. Paul Burns
    Reply

    Easy to use!

  41. Mirna
    Reply

    Fun exercise, thanks!

  42. Teresa
    Reply

    You have a way of explaining things to make them seem so simple!

  43. Melaney
    Reply

    Very helpful!

  44. Jazmyne
    Reply

    initially I was highly confused, but after reading through it (a few times) and opening a new sheet to put the formula to work – it makes perfect sense! love it!

  45. karina larsen
    Reply

    That was a great piece of information!

  46. Penne Steidl
    Reply

    I have to compute payments all the time. This really helps.

  47. Dwain Hendrickson
    Reply

    Great tip. Is semi-annual payments expressed by dividing the rate by 6 and the multiply the payments by 6?

    1. jefflenning Post author
      Reply

      Dwain,
      Thanks! Semi-annual payments made twice per year would be accommodated by diving the annual interest rate by two and multiplying the annual number of payments by two.
      Hope it helps!
      Thanks
      Jeff

  48. Ryan Gendron
    Reply

    Thank you for better explaining the PMT function. I learned this in my undergrad studies on a calculator and excel . Your post explains it very well.

  49. Bill Winkler
    Reply

    Week one review. Have used this function for a long time, frequently as a part of a loan amortization table.

  50. James Higginson-Rollins
    Reply

    Good explanation of the simple interest calculator function.

  51. Sachin Naik
    Reply

    what if we want to calculate the quarterly installment? the interest rate will be divided by what? and the period will be multiplied by what?

    plz do reply

    1. Kurt LeBlanc
      Reply

      Hey Sachin,

      All interest rates are stated in annual terms, so QUARTERLY is divided by 4 and the payment periods multiplied by 4.

      Let me know if you need more help:)
      Kurt LeBlanc

  52. Kelly Williams
    Reply

    Clear and concise explanation. Thanks!

  53. Logan Nemeth
    Reply

    i thought this page was very helpful.

  54. Michael Ferguson
    Reply

    Great page!

  55. Tony Mehle
    Reply

    Good to know. Very helpful

  56. Monique` O'Dell
    Reply

    Thank you for the breakdown of exactly why each function is either multiplied or divided.

  57. Cynthia Hart
    Reply

    Cool! Where I work, we bought a separate software package that does that and present values and that sort of thing. We knew Excel was supposed to be able to do all this, but we could never get it to work!

  58. pavan kumar ms
    Reply

    its helpful

  59. hassan Al khatab
    Reply

    does this function presume the simple or compound interest rate?!

    how did you get the monthly payments to show the value between parenthesis? when i did this function the result came negative like this -$85.61 ( in red). how did you make the negative sign like parenthesis (85.61)?

    1. Kurt LeBlanc
      Reply

      Hey Hassan

      The formula uses compounding interest.

      As for the negative value…it is a cash flow issue. The PV is positive, so the cash flow would have to be negative to equal $0 when its done: one in, one out. You can simply add a negative sign before the function (=-PMT) to change the negative result to a positive:)

      Kurt LeBlanc

  60. Simona Opran
    Reply

    Very helpful, good explanation.

  61. Li
    Reply

    Useful formula!

  62. Sayed Ali Reza Kazemi
    Reply

    thanks…. very useful

  63. Justin
    Reply

    I am loving the wealth of information I have access to, this is yet another function I could see myself using regularly. Thanks!

  64. Valeri Stevens
    Reply

    SImple and clear explanation — per your usual style. Thank you!

    I’m guessing that you left out the ROUND function to keep the example simple. I think you’ll agree that rounding is essential when dealing with money and multiplication — such as is involved with the PMT function — especially if you calculate more than one payment and then add them together. For example, rounding and displaying the result to the same number of decimal places assures that the total returned by Excel will be the same as it would be if you used a calculator to add up the displayed values. For anyone interested, Excel offers several types of rounding (e.g. round up, round down, round nearest) — go to the Excel help and search for ROUND for details.

    1. jefflenning Post author
      Reply

      Thanks for the comment about rounding 🙂

  65. Jon Johnston
    Reply

    I have created worksheets the long, hard way. This will be very helpful.

  66. Ron
    Reply

    I found the “ctrl + arrow” and “ctrl+shift+arrow” commands useful when working with large speadsheets.

  67. James Solbakken
    Reply

    On those occasions when I had to compute functions like “payments” on loans I always constructed the formulas manually. It worked but why do that when there is a prefabricated function for that? I confess I was easily intimidated by the complex appearance of the formulae. Now it seems so easy I don’t know what I was so scared of.

  68. Maryann Losier
    Reply

    Very useful info. Thanks Jeff 🙂

  69. Marco
    Reply

    Very helpful!

  70. Felicia Walton
    Reply

    Great tool!!!

  71. Tara
    Reply

    Very helpful. Thanks for communicating this function in a way that could be easily understood!

  72. christy
    Reply

    Good to know.

  73. Sharon McAlister
    Reply

    Thanks!

  74. TK
    Reply

    Very useful. Thanks

  75. Lina Tsankova
    Reply

    Useful function

  76. sudip mishra
    Reply

    u r great .because I understood this pmt function after reading your post.thanks lot jf Sir

  77. Gladys Starks
    Reply

    Thanks for the simplified explanation. Very useful for helping others, especially non accountants, to understand how loan payments are derived.

  78. Bonnie Walston
    Reply

    Useful function!

  79. Tina
    Reply

    Thanks Jeff. Good to know!

  80. Nika Carter
    Reply

    Very detailed info for the PMT function.

  81. Karen
    Reply

    Great refresher!

  82. Lisa Hughes
    Reply

    I knew some of the conditions in a function were optional. From this example I learned that the brackets [ ] designate which are optional.

    1. Kurt LeBlanc
      Reply

      Very good Lisa!

      Kurt LeBlanc

  83. Alex
    Reply

    Very useful and practical function!

  84. Olga
    Reply

    great, thanks!

  85. Brittni
    Reply

    Very useful function!

  86. Chandra
    Reply

    Thanks for sharing this.

  87. Kari Docekal
    Reply

    Very helpful. Thank you!

  88. Pamela Roller
    Reply

    Great explanation of the payment function!

  89. Erica Christensen
    Reply

    Thank you!

  90. Keith Lober
    Reply

    The PMT function is useful and quick and easy to use.

  91. Colleen Flynn
    Reply

    I’ll be using the PMT function now that I know about it

  92. Donna Young
    Reply

    This will definitely come in handy Thanks!

  93. Morris E. Antar
    Reply

    useful formula

  94. Linda Hoyle
    Reply

    I see myself using the PMT function quite a bit in the future

  95. Nick Grgas
    Reply

    Nicely done

  96. Yasuko
    Reply

    PMT is one of few functions that I know.

Leave a Reply

Your email address will not be published. Required fields are marked *

By submitting this form, you accept the Mollom privacy policy.