# 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!

Get a quick email notice when a new Excel article is available

• This field is for validation purposes and should be left unchanged.

This article was written by Jeff Lenning

## 205 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

1. Nad
Reply

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

1. Jeff Lenning Post author
Reply

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.

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. Maryann Losier
Reply

Very useful info. Thanks Jeff 🙂

68. Marco
Reply

Very helpful!

69. Felicia Walton
Reply

Great tool!!!

70. Tara
Reply

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

71. christy
Reply

Good to know.

72. Sharon McAlister
Reply

Thanks!

73. TK
Reply

Very useful. Thanks

74. Lina Tsankova
Reply

Useful function

75. sudip mishra
Reply

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

76. Gladys Starks
Reply

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

77. Bonnie Walston
Reply

Useful function!

78. Tina
Reply

Thanks Jeff. Good to know!

79. Nika Carter
Reply

Very detailed info for the PMT function.

80. Karen
Reply

Great refresher!

81. 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

82. Alex
Reply

Very useful and practical function!

83. Olga
Reply

great, thanks!

84. Brittni
Reply

Very useful function!

85. Chandra
Reply

Thanks for sharing this.

86. Kari Docekal
Reply

Very helpful. Thank you!

87. Pamela Roller
Reply

Great explanation of the payment function!

88. Erica Christensen
Reply

Thank you!

89. Keith Lober
Reply

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

90. Colleen Flynn
Reply

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

91. Donna Young
Reply

This will definitely come in handy Thanks!

92. Morris E. Antar
Reply

useful formula

93. Linda Hoyle
Reply

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

94. Nick Grgas
Reply

Nicely done

95. Yasuko
Reply

PMT is one of few functions that I know.

96. Linda
Reply

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

97. Lou
Reply

Learned something else new! Thanks

98. Lonni
Reply

Used this a TON in my MBA Financial Management course!

99. Susan Lippold
Reply

Very helpful! Thanks!

100. Aurdrea Weiman
Reply

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.

101. Danielle Serdar
Reply

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

102. Mirna Cereceres
Reply

Great to learn the PMT function!

103. Linda Vogel
Reply

Nice to know, but not necessary to my business.

104. Hector Aparicio
Reply

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

105. Vivian Hauer
Reply

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!

106. Sonia Mack
Reply

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

107. Yolanda Williams
Reply

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

108. Dawn Taylor
Reply

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

109. Callaway Dorsey
Reply

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

110. Justin Nguyen
Reply

Thanks for the tip

111. Renee Kelly
Reply

Thanks for the tip

112. Jessica
Reply

Sent her from the Excel University class – good post!

113. Cindy Wrecza
Reply

Good post. Thank you.

114. Doris
Reply

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

115. Marty Teresi
Reply

We are currently refinancing, so this came in handy.

Thanks,
Marty

116. cynthia osborne
Reply

this will come in hand in refinancing.

117. Andrew S. Haines
Reply

Learning already!

118. Jose
Reply

Thanks Jeff for this helpful payment function insight!

119. Hai
Reply

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.

1. Jeff Lenning Post author
Reply

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

120. Jeff Lightfield
Reply

Great info Jeff!

121. Christy Sciscoe
Reply

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

122. Jeremy Sparling
Reply

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

123. Chris
Reply

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

124. John Braslin
Reply

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!

125. Susan Luhrs
Reply

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

126. Heidi Hintze
Reply

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

127. Cheryl Semenza
Reply

Very useful to know this formula–thanks!

128. Brandi
Reply

Will try it

129. Cindy
Reply

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.

130. John O'Sullivan
Reply

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

131. Tom M
Reply

Good concise explanation.

132. Sue
Reply

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

133. Kristina G
Reply

Thanks!

134. Dan C
Reply

Thanks!!

135. Ed Holmes
Reply

Good stuff!!
Thanks, Ed.

136. Cooper Wood
Reply

Very interesting.

137. Charles Roskowiak
Reply

Easy to pick up on. Very accesible

138. Rebecca Cooper
Reply

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

139. caitlin wolff
Reply

Seems easy!

140. David
Reply

Thanks, Jeff.

141. Kelley
Reply

Thanks Jeff

142. Jennifer Shepherd
Reply

Thanks, Jeff

143. De Ann Quintana
Reply

Very helpful! Thanks Jeff

144. Angela Kampanis
Reply

Awesome and easy!

145. Melinda
Reply

Good, good.

146. Julia
Reply

Thanks Jeff!

147. Mary Ann
Reply

Make it easy to calculate.

148. Chenfei
Reply

This is easy to use. Thanks.

149. Juie Ann
Reply

Can’t wait for the opportunity to use this.

150. Jeff Clark
Reply

Awesome stuff.

151. RogerB
Reply

Reinforcement is always good, removes the rust.

152. Ray Solorio
Reply

Great tip.

153. Lynne
Reply

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

154. Marcus Manuel
Reply

These formulas help with my home mortgage.

155. Tanya
Reply

Easy peasy. Thanks!

156. Dorothy
Reply

Great function

157. Kate Johnson
Reply

Easy to understand as always Jeff. Thanks!

158. Brad
Reply

Great formula and information.

159. Claire Whitehurst
Reply

Thank you!

160. Sarah
Reply

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

161. Marisa
Reply

Thank you! Great information!

162. Tom
Reply

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

163. Denise Heifort
Reply

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

164. Kandi
Reply

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!

165. Sarah Moon
Reply

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!

166. Liz Alexander
Reply

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

167. Scott Kosmo
Reply

Good stuff as always.

168. Roxane White
Reply

This is great, so hepful

169. Roxane White
Reply

This is really great and helpful stuff

170. Stacie
Reply

Nice refresher!

171. Denise A.
Reply

Very easy to understand!

172. Theresa H.
Reply

Blog posts are a great tool for reinforcement!

173. Rachael H.
Reply

I find the blog posts an excellent learning tool.

174. Natalie Stene
Reply

thanks for the shortcuts!

175. Jack Revier
Reply

Great learning tools here!

176. samantha hansen
Reply

Thanks for the shortcuts! They’re super helpful.

177. Samantha Hansen
Reply

Thanks! They’re super helpful.

178. Anita Hale
Reply

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

179. Jennifer Roberts
Reply

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

180. Tracey Thesmar
Reply

Very easy to understand, thanks!

181. Mohamed
Reply

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

182. Jeonghwan Bae
Reply

Thanks a lot for some great information.

183. Jeonghwan Bae
Reply

Thanks a lot for some great information!

184. Xiaohong
Reply

very useful! Thank you.

185. Clara Lee
Reply

This was very useful and thorough! Thank You!

186. Elizabeth
Reply

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.

187. Suzanne Petronic
Reply

Very useful, Thank you

188. Abby Kay Choate
Reply

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

189. Helen Park
Reply

Great thank you!

190. Michelle
Reply

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

191. WILLIAM B FORD
Reply

Thanks Jeff for the reinforcement on a very handy function!

192. Nicole
Reply

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

193. David
Reply

I came to better understand PMT in excel.