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!

Posted in

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Stay Connected

If you'd like to be notified when I write a new Excel article, enter your name and email and click SUBSCRIBE. You can unsubscribe anytime, and I will never sell your email address.

209 Comments

  1. Denise Weber on April 16, 2013 at 1:06 am

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

  2. Kelli C on April 30, 2013 at 11:38 pm

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

  3. Sunny K. on October 29, 2013 at 5:35 am

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

  4. Carter Wicks on January 15, 2014 at 5:07 pm

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

  5. Benjamin Wilkinson on March 15, 2014 at 3:29 am

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

  6. Kando O. on May 1, 2014 at 5:44 pm

    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. on May 5, 2014 at 9:38 am

    This is a very useful function to know!

  8. Chris Wagner on June 19, 2014 at 5:20 pm

    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 on June 24, 2014 at 7:40 am

    Very helpful!

  10. Christian P on August 19, 2014 at 2:22 pm

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

  11. James on August 26, 2014 at 12:03 pm

    Very interesting, and completely applicable

  12. CHAO JIN on September 5, 2014 at 6:32 pm

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

  13. Brian M on September 10, 2014 at 3:40 pm

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

  14. Alex on October 17, 2014 at 4:01 pm

    Will be useful in the future. Thanks.

  15. Elizabeth Wareing on October 27, 2014 at 1:21 pm

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

  16. Anita on November 19, 2014 at 10:10 am

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

  17. Victor on November 19, 2014 at 3:38 pm

    Thanks. Very helpful.

  18. Jesse B on November 20, 2014 at 8:32 pm

    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 on November 26, 2014 at 4:18 pm

    New information and the examples are great.

  20. Anneliese Gilbert on December 4, 2014 at 1:36 pm

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

  21. Hannah Wiser on December 23, 2014 at 12:02 pm

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

  22. Wendy Rohaly on December 28, 2014 at 4:37 am

    Thanks, very helpful.

  23. MaryAnn R. on January 14, 2015 at 3:07 pm

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

  24. Yesenia A. on January 20, 2015 at 1:48 pm

    Excellent PMT function explanation. Thanks alot.

  25. Kathy Shannon on January 23, 2015 at 8:03 am

    Thanks Jeff! Great formula to know!

  26. Wan-LI Wu on January 29, 2015 at 7:15 pm

    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 on January 30, 2015 at 9:00 am

    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 on February 3, 2015 at 3:54 pm

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

  29. Katherine Dawson on March 3, 2015 at 10:52 am

    Nice to have!

  30. Allison J Lausten on March 19, 2015 at 3:17 pm

    A useful tool.

  31. Sheri on March 24, 2015 at 2:14 pm

    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.

  32. Cindy Johnson on April 25, 2015 at 8:24 am

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

  33. Joe Horne on May 15, 2015 at 11:13 am

    Thanks for the reinforcement!

  34. Christine Mast on June 20, 2015 at 2:31 pm

    Thanks once again for an informative post!

  35. Latha Nair on July 13, 2015 at 7:11 am

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

  36. Wanda Hagen on August 6, 2015 at 12:22 pm

    Explained the concept very easily.

  37. Michael Fullen on August 19, 2015 at 12:34 pm

    Useful information, thank you.

  38. Beth Meador on August 20, 2015 at 12:18 pm

    Very easy to follow and understand!

  39. Lori on August 21, 2015 at 8:54 am

    Good to know! Thanks!

  40. Lisa Croyle on August 29, 2015 at 9:32 am

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

  41. Paul Burns on August 30, 2015 at 9:54 am

    Easy to use!

  42. Mirna on September 18, 2015 at 7:31 am

    Fun exercise, thanks!

  43. Teresa on September 28, 2015 at 10:30 am

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

  44. Melaney on November 6, 2015 at 11:24 am

    Very helpful!

  45. Jazmyne on November 20, 2015 at 3:53 pm

    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!

  46. karina larsen on November 21, 2015 at 10:16 pm

    That was a great piece of information!

  47. Penne Steidl on November 29, 2015 at 4:17 pm

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

  48. Dwain Hendrickson on December 8, 2015 at 8:38 am

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

    • jefflenning on December 8, 2015 at 8:55 am

      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

  49. Ryan Gendron on December 9, 2015 at 10:57 am

    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.

  50. Bill Winkler on December 17, 2015 at 9:43 am

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

  51. James Higginson-Rollins on December 27, 2015 at 2:11 pm

    Good explanation of the simple interest calculator function.

  52. Sachin Naik on December 28, 2015 at 6:47 am

    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

    • Kurt LeBlanc on August 16, 2016 at 12:55 pm

      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

      • Nad on October 2, 2017 at 3:29 pm

        How would I use PMT function to calculate the amount of the quarterly payment required to repay the loan.

        • Jeff Lenning on October 2, 2017 at 3:58 pm

          To determine the quarterly payment, enter the rate as quarterly (divide annual by 4), and number of payments to be the number of quarterly payments.

  53. Kelly Williams on January 11, 2016 at 12:17 pm

    Clear and concise explanation. Thanks!

  54. Logan Nemeth on January 15, 2016 at 6:32 pm

    i thought this page was very helpful.

  55. Michael Ferguson on January 16, 2016 at 8:02 am

    Great page!

  56. Tony Mehle on January 18, 2016 at 12:05 pm

    Good to know. Very helpful

  57. Monique` O'Dell on January 19, 2016 at 2:36 pm

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

  58. Cynthia Hart on January 20, 2016 at 12:25 pm

    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!

  59. pavan kumar ms on January 22, 2016 at 9:21 pm

    its helpful

  60. hassan Al khatab on January 24, 2016 at 8:48 am

    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)?

    • Kurt LeBlanc on October 19, 2016 at 8:52 am

      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

  61. Simona Opran on February 16, 2016 at 6:10 pm

    Very helpful, good explanation.

  62. Li on February 26, 2016 at 3:41 pm

    Useful formula!

  63. Sayed Ali Reza Kazemi on April 5, 2016 at 8:35 am

    thanks…. very useful

  64. Justin on April 12, 2016 at 11:23 am

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

  65. Valeri Stevens on April 19, 2016 at 8:54 am

    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.

    • jefflenning on April 19, 2016 at 9:09 am

      Thanks for the comment about rounding 🙂

  66. Jon Johnston on April 21, 2016 at 11:17 am

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

  67. Ron on May 5, 2016 at 3:34 am

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

  68. Maryann Losier on May 16, 2016 at 11:36 am

    Very useful info. Thanks Jeff 🙂

  69. Marco on May 20, 2016 at 6:08 am

    Very helpful!

  70. Felicia Walton on June 1, 2016 at 9:36 am

    Great tool!!!

  71. Tara on June 14, 2016 at 12:16 pm

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

  72. christy on June 17, 2016 at 11:31 am

    Good to know.

  73. Sharon McAlister on June 20, 2016 at 6:43 am

    Thanks!

  74. TK on June 21, 2016 at 8:14 am

    Very useful. Thanks

  75. Lina Tsankova on June 24, 2016 at 9:38 am

    Useful function

  76. sudip mishra on June 29, 2016 at 10:29 pm

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

  77. Gladys Starks on July 7, 2016 at 2:57 pm

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

  78. Bonnie Walston on July 8, 2016 at 9:39 am

    Useful function!

  79. Tina on July 12, 2016 at 5:26 am

    Thanks Jeff. Good to know!

  80. Nika Carter on July 12, 2016 at 2:44 pm

    Very detailed info for the PMT function.

  81. Karen on July 19, 2016 at 8:10 pm

    Great refresher!

  82. Lisa Hughes on August 2, 2016 at 4:02 pm

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

    • Kurt LeBlanc on August 3, 2016 at 6:50 am

      Very good Lisa!

      Kurt LeBlanc

  83. Alex on August 14, 2016 at 10:14 am

    Very useful and practical function!

  84. Olga on August 15, 2016 at 11:29 am

    great, thanks!

  85. Brittni on August 16, 2016 at 11:08 am

    Very useful function!

  86. Chandra on August 18, 2016 at 6:34 pm

    Thanks for sharing this.

  87. Kari Docekal on September 22, 2016 at 12:19 pm

    Very helpful. Thank you!

  88. Pamela Roller on October 13, 2016 at 3:03 pm

    Great explanation of the payment function!

  89. Erica Christensen on October 25, 2016 at 4:46 pm

    Thank you!

  90. Keith Lober on November 2, 2016 at 4:58 am

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

  91. Colleen Flynn on November 3, 2016 at 11:53 am

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

  92. Donna Young on November 9, 2016 at 3:11 pm

    This will definitely come in handy Thanks!

  93. Morris E. Antar on November 18, 2016 at 9:40 am

    useful formula

  94. Linda Hoyle on November 22, 2016 at 10:51 am

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

  95. Nick Grgas on November 28, 2016 at 7:13 am

    Nicely done

  96. Yasuko on December 13, 2016 at 12:51 pm

    PMT is one of few functions that I know.

  97. David L. Argabright on December 20, 2016 at 2:23 pm

    Thanks for the PMT blog. Helpful. Merry Christmas!

  98. Linda on January 20, 2017 at 5:11 pm

    Although it is useful to use Excel to compute when considering multiple scenarios, if only need to compute one, I prefer my TI calculator.

  99. Lou on February 14, 2017 at 10:24 am

    Learned something else new! Thanks

  100. Lonni on February 15, 2017 at 9:07 am

    Used this a TON in my MBA Financial Management course!

  101. Susan Lippold on February 16, 2017 at 6:39 am

    Very helpful! Thanks!

  102. Aurdrea Weiman on February 17, 2017 at 8:54 am

    I could see this as being useful in a repayment plan for a consumer. Those are unusual for us to have, so using something like this would make sense.

  103. Danielle Serdar on February 24, 2017 at 11:29 am

    Followed the course to look for the PMY function. Dont know if I’ll use this much since I have TValue 5.

  104. Mirna Cereceres on February 24, 2017 at 4:44 pm

    Great to learn the PMT function!

  105. Linda Vogel on February 27, 2017 at 1:24 pm

    Nice to know, but not necessary to my business.

  106. Hector Aparicio on March 2, 2017 at 11:05 pm

    thanks for the information about PMT. I am pretty sure it will useful soon

  107. Vivian Hauer on March 3, 2017 at 5:00 pm

    I like your work of using the formula in an example. I did not know that the rate needs too correspond to the number of periods. (monthly rate for no of months in the entire period). Very good tip!

  108. Sonia Mack on March 13, 2017 at 4:26 pm

    Great tool to use when contemplating large purchases. Will it fit in my budget?

  109. Yolanda Williams on April 20, 2017 at 10:01 am

    I used this tool to help me decide on which student loan payment plan was best for me.

  110. Dawn Taylor on April 20, 2017 at 12:55 pm

    I appreciate the clarity of your blog post about the handy-dandy PMT function!

  111. Callaway Dorsey on April 30, 2017 at 9:57 am

    Not a function I use much but, nice to know.

  112. Justin Nguyen on May 7, 2017 at 9:00 pm

    Thanks for the tip

  113. Renee Kelly on May 10, 2017 at 1:39 am

    Thanks for the tip

  114. Jessica on May 24, 2017 at 11:17 am

    Sent her from the Excel University class – good post!

  115. Cindy Wrecza on May 30, 2017 at 2:04 pm

    Good post. Thank you.

  116. Doris on June 20, 2017 at 11:08 am

    This seems like it could come in handy, and is pretty intuitive to use.

  117. Marty Teresi on June 26, 2017 at 7:00 pm

    We are currently refinancing, so this came in handy.

    Thanks,
    Marty

  118. cynthia osborne on June 27, 2017 at 6:43 pm

    this will come in hand in refinancing.

  119. Andrew S. Haines on July 4, 2017 at 5:59 am

    Learning already!

  120. Jose on July 5, 2017 at 10:08 am

    Thanks Jeff for this helpful payment function insight!

  121. Hai on July 14, 2017 at 6:28 am

    Dear Everybody,
    My name is Hai. I am from Viet Nam. I have a Excel’s problem (using PMT function) as below: “Mr. Adam wants to sign an insurance contract. He expects to earn $110000 after 18 years at 15% annual interest rate. How much money he must pay monthly for the insurance company, knowing that when signing the contract, Mr. Adam paid $10000?”
    With my understanding, where:
    rate: 15%/12
    nper: 18*12
    pv: 10000
    fv: 110000
    I tried calculate PMT in 4 formulars:
    1. =PMT(15%/12,18*12,10000,110000) = ($235.03)
    2. =PMT(15%/12,18*12,-10000,110000) = $33.31
    3. =PMT(15%/12,18*12,10000,-110000) = ($33.31)
    4. =PMT(15%/12,18*12,-10000,-110000) = $235.03

    I do not know which formula is right. I’m very confuse in using PMT function, especially using PV and FV parameter sign (- or +). Could you explain to me the problem above? I’ve read Excel’ help and other examples on the web but still not sure exactly.
    In general, for all the arguments, cash you pay out, such as deposits to savings, is represented by negative numbers; cash you receive, such as dividend checks, is represented by positive numbers?

    Thank you so much for your help!

    Sincerely yours,
    Hai.

    • Jeff Lenning on July 16, 2017 at 3:30 am

      Hai, the PMT function is based on cash flow, where positive numbers are inflows and negative numbers are outflows. So the pv and fv are positive numbers, and the related monthly payment is expressed as negative.
      Thanks
      Jeff

  122. Jeff Lightfield on July 14, 2017 at 11:51 am

    Great info Jeff!

  123. Christy Sciscoe on August 2, 2017 at 10:48 am

    Great to know this function – it will be really useful, and is a lot less cumbersome than what I had been doing!

  124. Jeremy Sparling on August 11, 2017 at 4:53 am

    I had no idea the [ ]’s meant something was optional in a formula string. Great hint!

  125. Chris on August 27, 2017 at 2:55 am

    Very useful more for Personal Finances than day to day , I didn’t know about the [] being optional in formula’s either.

  126. John Braslin on August 27, 2017 at 1:19 pm

    Great to see this up front! I have been working with my son to see how much car he can afford, and this will help us zero in on his heart’s desire!

  127. Susan Luhrs on August 29, 2017 at 12:44 pm

    Excel has lots of these great formulas. This is a handy one in particular

  128. Heidi Hintze on September 12, 2017 at 9:01 am

    Love this! Glad to know about this formula – extremely useful.

  129. Cheryl Semenza on September 23, 2017 at 12:31 pm

    Very useful to know this formula–thanks!

  130. Brandi on September 28, 2017 at 10:01 am

    Will try it

  131. Cindy on October 5, 2017 at 12:19 pm

    Good explanation. I’ve been using this function for years. Doesn’t always = the lending institution but not off by much. I suspect they use actual days outstanding between payments received.

  132. John O'Sullivan on October 29, 2017 at 7:23 pm

    Great explanation particularly the two optional fields; have needed this formula for recent discussions.

  133. Tom M on November 22, 2017 at 8:05 pm

    Good concise explanation.

  134. Sue on November 28, 2017 at 1:35 pm

    This function is very useful. I haven’t used it in a long time. Thanks for the reminder.

  135. Kristina G on December 13, 2017 at 6:04 pm

    Thanks!

  136. Dan C on December 16, 2017 at 12:47 am

    Thanks!!

  137. Ed Holmes on December 26, 2017 at 3:36 pm

    Good stuff!!
    Thanks, Ed.

  138. Cooper Wood on January 3, 2018 at 8:03 pm

    Very interesting.

  139. Charles Roskowiak on January 5, 2018 at 4:42 pm

    Easy to pick up on. Very accesible

  140. Rebecca Cooper on January 12, 2018 at 9:46 am

    Seems like a good function to know if you don’t have a financial calculator handy! Thanks!

  141. caitlin wolff on January 14, 2018 at 3:20 pm

    Seems easy!

  142. David on January 18, 2018 at 9:52 am

    Thanks, Jeff.

  143. Kelley on January 24, 2018 at 8:03 pm

    Thanks Jeff

  144. Jennifer Shepherd on January 25, 2018 at 2:55 pm

    Thanks, Jeff

  145. De Ann Quintana on January 29, 2018 at 3:16 pm

    Very helpful! Thanks Jeff

  146. Angela Kampanis on January 31, 2018 at 1:59 pm

    Awesome and easy!

  147. Melinda on February 9, 2018 at 10:28 am

    Good, good.

  148. Julia on February 12, 2018 at 4:40 pm

    Thanks Jeff!

  149. Mary Ann on February 15, 2018 at 10:39 pm

    Make it easy to calculate.

  150. Chenfei on February 20, 2018 at 11:12 am

    This is easy to use. Thanks.

  151. Juie Ann on February 20, 2018 at 2:43 pm

    Can’t wait for the opportunity to use this.

  152. Jeff Clark on March 12, 2018 at 9:59 am

    Awesome stuff.

  153. RogerB on March 23, 2018 at 9:58 pm

    Reinforcement is always good, removes the rust.

  154. Ray Solorio on April 4, 2018 at 3:45 pm

    Great tip.

  155. Lynne on April 14, 2018 at 6:39 pm

    While this can not help me as part of my job, it sure can help in my personal life!!! Thanks!

  156. Marcus Manuel on April 24, 2018 at 2:08 pm

    These formulas help with my home mortgage.

  157. Tanya on April 25, 2018 at 8:38 pm

    Easy peasy. Thanks!

  158. Dorothy on May 4, 2018 at 11:39 am

    Great function

  159. Kate Johnson on May 4, 2018 at 3:24 pm

    Easy to understand as always Jeff. Thanks!

  160. Brad on May 11, 2018 at 10:02 am

    Great formula and information.

  161. Claire Whitehurst on May 18, 2018 at 8:26 am

    Thank you!

  162. Sarah on May 24, 2018 at 12:53 pm

    So easy to forget the simple functions that are super useful. Great outline of it here.

  163. Marisa on May 29, 2018 at 3:38 pm

    Thank you! Great information!

  164. Tom on June 1, 2018 at 11:52 am

    Continues to be a great function! Used it many times!

  165. Denise Heifort on June 3, 2018 at 4:50 am

    I learned this one in college, but a very helpful reminder. Thanks!

  166. Kandi on June 13, 2018 at 4:01 pm

    This will help me show my kids where they need to be in price and interest rate for the new car they want to purchase. Thanks!

  167. Sarah Moon on June 15, 2018 at 2:42 pm

    This is a very helpful “cheat cheat” for those like me who are fairly new to Excel! It’s easy to forget or overlook the basics so this will be great to come back and look at!

  168. Liz Alexander on June 21, 2018 at 7:06 am

    Love the feature and use it when I remember it exists. Sadly, it’s often forgotten.

  169. Scott Kosmo on July 2, 2018 at 9:27 am

    Good stuff as always.

  170. Roxane White on July 13, 2018 at 2:17 pm

    This is great, so hepful

  171. Roxane White on July 13, 2018 at 2:23 pm

    This is really great and helpful stuff

  172. Stacie on July 27, 2018 at 6:30 pm

    Nice refresher!

  173. Denise A. on July 31, 2018 at 3:53 pm

    Very easy to understand!

  174. Theresa H. on August 1, 2018 at 10:30 pm

    Blog posts are a great tool for reinforcement!

  175. Rachael H. on August 28, 2018 at 11:20 am

    I find the blog posts an excellent learning tool.

  176. Natalie Stene on August 28, 2018 at 3:35 pm

    thanks for the shortcuts!

  177. Jack Revier on September 9, 2018 at 5:56 pm

    Great learning tools here!

  178. samantha hansen on September 10, 2018 at 1:41 pm

    Thanks for the shortcuts! They’re super helpful.

  179. Samantha Hansen on September 10, 2018 at 1:42 pm

    Thanks! They’re super helpful.

  180. Anita Hale on September 15, 2018 at 1:00 pm

    The PMT function is a great tool when comparing financing options on capital purchases.

  181. Jennifer Roberts on December 17, 2018 at 10:07 am

    Not sure if I will be using this in my line of work, but it’s good to know.

  182. Tracey Thesmar on December 21, 2018 at 5:09 pm

    Very easy to understand, thanks!

  183. Mohamed on January 29, 2019 at 6:21 am

    Hi everyone im trying to use this formula,

    i get 2 sets of values, when using the financial calculator and excel.

    i have the following formula in excel
    =PMT( 11%/12, 36, -276315.65, 166927.95, 0) Answer: R 5 111.39
    =PMT( 11%/12, 36, -276315.65, 166927.95, 1) Answer: R 5 064.96

    i used the calculator and my answers respectively are:
    1. R 5 040.67
    2. R 4 994.88

    difference of +- R70.00

    Anyone can explain why? and possible share a solution

  184. Jeonghwan Bae on February 12, 2019 at 10:44 am

    Thanks a lot for some great information.

  185. Jeonghwan Bae on February 12, 2019 at 10:46 am

    Thanks a lot for some great information!

  186. Xiaohong on March 26, 2019 at 1:48 pm

    very useful! Thank you.

  187. Clara Lee on April 11, 2019 at 3:20 pm

    This was very useful and thorough! Thank You!

  188. Elizabeth on May 31, 2019 at 7:33 am

    Always nice to read about loan formulas. Hoping to encounter an article in the future dealing with a complete amortization schedule, possibly even one in which the interest rate changes throughout the course of the loan.

  189. Suzanne Petronic on June 7, 2019 at 11:19 am

    Very useful, Thank you

  190. Abby Kay Choate on June 12, 2019 at 12:51 pm

    Very easy to remember and I’m sure it will be used a lot!

  191. Helen Park on July 24, 2019 at 7:11 pm

    Great thank you!

  192. Michelle on September 9, 2019 at 6:01 pm

    This is my go-to formula for creating loan amortization tables!

  193. WILLIAM B FORD on September 17, 2019 at 12:09 pm

    Thanks Jeff for the reinforcement on a very handy function!

  194. Nicole on December 19, 2019 at 12:55 pm

    PMT is a very important excel function and you make it seem so easy.

  195. David on January 3, 2020 at 4:00 pm

    I came to better understand PMT in excel.

  196. John on February 12, 2020 at 11:37 am

    I feel like I better understand the PMT function of excel after reading this article.

  197. Tamika on February 20, 2020 at 5:51 pm

    Very helpful!

  198. Robin Marshall on April 22, 2020 at 10:03 am

    Very helpful!

  199. John Insley on July 27, 2020 at 3:44 pm

    Use this all the time!

Leave a Comment





For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use.

I agree to these terms.