How to Calculate Loan Payments with Excel PMT Function
ฝัง
- เผยแพร่เมื่อ 21 มิ.ย. 2013
- www.contextures.com/excelpmtfu... Go to this page to download the free sample file.
To calculate a loan payment in Excel, you can use the PMT function. The PMT function calculates the payment for a loan that has constant payments and a constant interest rate.
Enter an interest rate, the number of payments, and the loan amount on the worksheet. Then, refer to those cells in the PMT formula.
Watch this short video to see the steps for setting up a payment calculation, using the PMT function.
Instructor: Debra Dalgleish, Contextures Inc.
Get Debra's weekly Excel tips: www.contextures.com/signup01
More Excel Tips and Tutorials: www.contextures.com/tiptech.html
Subscribe to Contextures TH-cam: th-cam.com/users/contextu...
'----------------------
Transcript - Calculate Loan Payments with Excel PMT Function
In Excel, to calculate monthly payments, you can use the PMT function.
In this example, we're going to enter the annual rate, and then the number of payments we have to make, the amount that we'll be borrowing, and then we'll use the PMT function in this cell to calculate the monthly payment.
The annual rate that we're going to pay is 5%.
In this cell, we're going to borrow the amount over four years. There are 12 months per year, so 48 months. I'll be making 48 payments, and the amount that we're going to borrow is $10,000.
Those are the three numbers that we need in order to calculate the monthly payment.
Here is the syntax for the PMT function. We'll start by typing equals, and then PMT, open bracket, and now I'm going to click on the cell where I entered the rate.
I'll click here where it says 5%, but it's not 5% per month. That's the annual rate. I'm going to click after that cell reference, type a slash for division. then I'll type 12, because we're paying that rate over 12 months.
Then I'll type a comma, and the next argument is the number of periods. I'll click on the Number of Payments cell, and type another comma.
The present value, or the amount of the loan, is 10,000, so I'll click on that cell.
The other two arguments are optional, so I'm not going to use them.
The fv is the future value, so that's what you want left at the end of all your payments, and if you don't enter it, we assume zero. We want to pay everything off, so I don't have to enter anything there.
The type is also optional. If I omit it, we assume that it's zero, and you're going to be making your payments at the end of the period. If you type a one, then you'd be paying at the beginning of the period.
I'll close the bracket and press Enter, and there's the monthly payment.
It's in this cell as a negative amount because it's a payment that I owe, but if you wanted to show it as a positive number, just click after the equals sign, right before the PMT function name, and type a minus sign, and press Enter. Now that shows as a positive amount in the payment cell.
For more Excel tips and tutorials, and to download the sample file for this video, please visit my Contextures website at www.contextures.com. - วิทยาศาสตร์และเทคโนโลยี
Straight to the point with no fluff. Thanks!
Thank you very much, I learn more from these youtube videos than in my actual class. Kind of sad because I have to pay for the class.
Me too :(
Same
Thanks again, you are always there when i need you.
Now THIS makes sense! Thank you!
Thanks! I'm glad it helped.
When ya IT Homework due tomorrow lmaoo
Hi there, I recently stumbled upon your video tutorials. Oh wow! They are so simple but very informative. I can't imagine how good you are at codes too. I am new to codes and was trying to build a simple assessment gradebook for my primary school class that records all their yearly grades. Your short video tutorials will greatly help me. Thank you so much. I am a big fan.
You're welcome, and thanks for your lovely comment! I hope your gradebook project is going well
yes much better than my teacher!!! thanks
Thank you!! Great tutorial.
This helped me so much. Thanks!
Thank you for the video. It was much easier than trying to read guideline on Microsoft website.
Thank you!
Thank you so much. This really helps a lot.
So helpful! Thank you!
thank you very much it was so helpfull and simple!
Whoever you are, you're rock!! Thanks
You're welcome! I'm Debra Dalgleish, the owner of the Contextures website.
This helped me. Thank you
Thank you!! Helped a bunch. :D
thank you that was great. It helped me on an assignment
You're welcome! Thanks for letting me know that the video helped you.
Your presentation is good and good voice, I see your video . I also trying excel tutorial . I subscribed your channel and keep my features channel.
Very helpful video!!
world needs more people like you, who make this sometimes annoying ass, unnecessarily complicated ass shit simple to get. thank you so much for this :D
Now, this was helpful!
Thank you, you make this video, easy!
You're welcome, Josue! Thanks for your comment
Thank you!!!
Thank you! Very helpful.
Thank you, Lauren!
@Ashley Stock So true.. much better than my teacher. Simple formula with easy to understand explanation.
Thank you! I'm glad that you found it easy to understand
Thank you so much!
You're welcome! Thank you for your comment
Thank you thank you thank you!
OMG! Thank you!!!!
You're welcome, Mary!
Thank you!
Thanks for the video!
You're welcome! Thanks for watching
Great video. Thanks!
Thanks Scott!
YOU ARE A LIFE SAVERRRRRR
Thank you! I didn't know I had to divide the rate by 12!!
You're welcome, Avian, and I'm glad the video helped you.
Informative videos
Wait what does the pmt do, does it multiple stuff together?
I need to know this for my comp programming class. Thanks!
Thanks a lot
such a good help
Thank you!
thank you
Thank you
function button makes it even easier
Im working with the PV, APR, Nper, and I also have # of years. How do I inser that in to the equation. I understand its =PMT(APR/12,Nper*12,PV) but where does the # of years go?
What would be the formula when payment is made monthly, but interest is compounded quarterly?
Thanks for your question, and I don't have an example for that
I have a question. What if interest is compounded monthly instead of yearly?
in other words I understand part of this context, but what function does the comma have in this formula?
The comma just separates the variables used by the function (sorry it took me 2 years to answer) :)
Dear Sir,
What I understand the interest amount is $2,000( $10,000 x 5% x 4 years) on total loan. So each installment is $250 ($12,000/48 months) instead of $230.29. Can you please help me to understand the calculation method.
I'm confused I'm trying to put my question into Excel it's a giving me a answer
what category is the PMT function in?
PMT is in the Financial category
Gеet peеeеrsоnal lоаааn herе => twitter.com/3ac3041c00204e45d/status/809350192776347648 Hоооow to Calсulаааtе Lоааn Рауmеnts with Еxсеl PМT Funсtiоn
I'm looking for help in my tutorial on how to create a school timetable. It is necessary - in this table - that the upper grade be assigned to nine classes (first a, b, c, d, C)(second a,b)(third a,b,c ).
There is no doubt that there is no teacher in two different classes and in the same class.
I had no problem with this condition, I was able to achieve it.
But my challenge is to fulfill the previous condition with these conditions:
1 - Mr. Maher takes the second and third classes in mathematics.
2 - Ms. Shaima take the first classes in mathematics + third classes in physics.
3 - Ms. Hana takes the second classes B + third classes in English.
4 - Ms. Nada take the third classes only in the Arabic language.
I need to work this table in a way drop down menus; to make changes constantly, provided there is a column showing me the article that is repeated in more than one classes.
Please help me by video if you have time.
thank you.
How calculate just for 10 months with rate 25% ?
why didn't you just use the dialogue?
Toones lun
Thank you so much!
You're welcome, Sonja! Thanks for watching the video
Thanks a lot