HomeОбразованиеRelated VideosMore From: TeachExcel

How to find Interest & Principal payments on a Loan in Excel

902 ratings | 492587 views
More help: https://www.teachexcel.com Excel Forum: https://www.teachexcel.com/talk/microsoft-office?src=yt How to find the interest and principal payments on a fixed rate loan in excel. This tutorial will walk you through using the PPMT() and IPMT() functions in excel in order to find out how much of a monthly payment on a loan actually goes to pay off the loan amount and how much is just an interest payment. More free excel stuff such as macros, tutorials, articles, etc. go to: TeachExcel.com
Html code for embedding videos on your blog
Text Comments (81)
Dinesh Babu (29 days ago)
Nice one.. please send spread sheet link to download this spread sheet
Lone Wolf (1 month ago)
I cant find the spread sheet in website teachexcel.com
Cameron Young (3 months ago)
cheers boss, youre literally doing my finance assessment u fuck
Manish Mitra (3 months ago)
How to get monthly rate
Vincent Bursey (4 months ago)
The correct payment, given $250,000 (pv), and 6.50% (i), at 30 years (n) is $1,580.17.
Rachel Zaragoza (4 months ago)
what about if they pay one month later? Howto track the interest?
Toucan Sam (5 months ago)
You are the man
tt cc (2 months ago)
Oh no he is not the video is rubbish.
david violet (5 months ago)
I copied the excel sheet and got the same results. Then I plugged my own loan number in. I am paying about $50 higher than what the Excel sheet gives. I asked my loan agent. He showed me online calculators that give my actual payments as correct. I then plugged the values shown in this tutorial into the online calculators. The excel sheet consistently gives payments slightly lower than what the online calculator gives. There is a slight problem with this Excel sheet it seems.
Mohd Zulfahmi Zakaria (5 months ago)
Love this tutorial
TeachExcel (5 months ago)
Thanks :) And sorry for the bad audio, its an old one haha
mandy zhang (7 months ago)
how to deal with the various interest rate?
Jikook Fandom (9 months ago)
Kannan Jeya (10 months ago)
how i knew my principle amount sbi housing loan when 13 month after
Ali Edwards (11 months ago)
Is this channel still current? Do you have the related video where you teach the IF function for calculating the number of months and ending balance?
TeachExcel (11 months ago)
The channel is current, new videos every Tuesday, but this video is rather old and I don't have the file for it anymore.
Rich L. (11 months ago)
You have a great voice for teaching. Good rhythm and cadence--no monotone, brain-numbing speaking. I appreciate that. Thanks.
Arseniy Olevskiy (1 year ago)
Or, use www.mortgagecalculatorplus.com
Emilia Clarke (1 year ago)
Proxydocker will give you the best possible experience with proxies <3 http://bit.ly/2nnPesd
Victor P (1 year ago)
Showing people how to input numbers into an Excel formula doesn't show "How to" calculate things, when I clicked the video I was expecting a math tutorial not a typing tutorial.
Scarlett Ohara (1 year ago)
not sure what I am doing wrong but I entered all the information the way you have it and for the principal payment I keep getting $232.22 not the $234.30 you have. I am lost.
burgees jason (1 year ago)
Hello, Looking for business loans, personal loans, home loans, car loans, student loans, debt consolidation loans, unsecured loans, venture capital, etc. Or you've been denied credit from a bank or financial setting for one or more reasons. You well your credit solutions! I am a pastor, a private lender, loans to businesses and individuals at a low and affordable interest rate of 3%. Interest? Please contact us for processing credit transfer follow within 48 hours. CONTACT:[email protected]
Kia Presley (1 year ago)
Thank you thank you thank you. My assignment is now done thanks to this video.
FinanceKid (2 years ago)
Great info! I also uploaded a quick video covering the different ways in calculating monthly mortgages payments, the outstanding principal on a mortgage, and the monthly equivalent rate. Check it out to learn more!
Strawberrysunset23 (2 years ago)
How about anual payments
junu thokar (2 years ago)
plz help me!! if we deposited Rs. 1000 every month fro 1 year and at the end if we get Rs. 13000 then in this case what is the interest rate and how to calculate it????
Damien Pileggi (2 years ago)
Is there a MACRO that automatically inserts rows for months and copies the formulas based on the years entered?
Mondi Safa (3 years ago)
Hi There, would you tell me your name. thx
Mondi Safa (3 years ago)
Hi There, I w'd like to THANK YOU for talking so nicely, clearly for me to understand you and I've to say I LOVE LOVE learning and YOU're for sure a Great teacher. I like to find out if you have videos to teach excel 2013. Thank you
Juan RDZ (3 years ago)
That's the wrong way to calculate interest ! Especially at 6.5%. More money should be going towards your principal if your paying 6.5% interest.
Cervantes (3 years ago)
may I also request a copy of the excel file, I am unable to find it on the web site? thank you
Carlos Almegure (3 years ago)
How did you get 1549.72 ? I got 1571.66
Greg Northover (3 years ago)
+Carlos Almegure he has a formula in the monthly Rate cell F4 =((1+B4)^(1/12))-1
Ronald Pornobi (3 years ago)
may I request a copy of the excel file
Emigdio Alaniz (3 years ago)
The file for this page is not on you site, i look and did a good search but the file for this tutorial in not on your site, i care about the logic for the months that is all what i was hoping to get from this tutorial. it will be nice if you actually had a link that will send us directly to your site and be able to download.
Stivie_G (3 years ago)
if you pay you can get, that's what for the link of his website
Sardar Ali (3 years ago)
thanks to educate the community!!!!
Red Pants (3 years ago)
The second s/s follows the first so I'm not sure why people are struggling to find it.  This is good, thank you, but due to the age of the upload the 4:3 screen doesn't show all the cells of interest together so I had to keep stopping and rewinding to keep up!  Some of you people commenting are clearly so inert you need to be spoon fed.
Aminul Islam (3 years ago)
can you give the attachment
Mona Page (3 years ago)
Took me a bit but here is how to get it to calculate the payments you need. For only the payments you need. so the spread sheet changes based on 5 years or 25 years or what ever. In the Months put 1 for the first month in box A9. then for the A10 put =IF(A9="","",IF($B$5*12>=A9+1,A9+1,''")) Then drag and copy all lines. Note the lines that go past the number of payments you need, you will not see.
Richard Vargas (3 years ago)
mine just gives the #Num and #val sign when dragging down, and how do you get it to change months with the update info
Charith Hewawasam (3 years ago)
+Richard Vargas you have to add $ between the cell letter as $A$9 therefor the particular cell does not change as you drag. but you should not use $ sign for (per) cell. because it should be changed as u drag. A9,A10,A11,... likewise.
marieearthangel (4 years ago)
CAVEAT: Hi ExcelisHell, be careful of the monthly rate. If the annual rate is APR then the monthly % rate, is APR/12.= 6.5%/12=.5416%, If the annual rate is expressed as "Effective Annual Rate" then the formula is what is given in the tutorial, Monthly rate = [(1+r) ^(1/M)]-1
Qian Li (4 years ago)
thanks a lot
Leon Pronin (4 years ago)
@ExcelisHell Well if you do not explain how to calculate the monthly payment, then at least post a link to the video where it is explained!! 
Kaien S (3 years ago)
@Kaien S *360 = 30 x 12
Kaien S (3 years ago)
@Leon Pronin Periodic Payment(monthly payment)=Amount($250000) / ( (1- ( 1/ ( ( 1+ ( Interest Rate / 12 ) ) ^ 360 ) ) ) / ( Interest Rate / 12 ) )
Veronica Sanchez (4 years ago)
Thank you super easy, very helpful
Jc Buffington (4 years ago)
I can't find the spreadsheet and your website...Can you help?....thanks for your time.
Kaien S (3 years ago)
@Jc Buffington http://www.excel-easy.com/examples/loan-amortization-schedule.html
Jeremy Laplace (4 years ago)
I have a question, How would you calculate if you make additional payments, or pay extra on your payments?
Rachel Zaragoza (4 months ago)
or late payments?
laydeerada (5 years ago)
yay! =D
Drew Pierson (5 years ago)
With all due respect, you don't at all explain how you actually created the calculator and subsequent table (w/ beginning balance, ending balance, and the self-adjusting rows according to the # of periods). Is this featured in another tutorial? Or am I missing something? Its very misleading. Would appreciate any further guidance. Thanks.
marduktr (5 months ago)
first you are calculating the periodic payment,which is $1549.72, then you are subtracting simple interest of that month,which is $1315.42(250k*0,0053*1). you get principal payment of the interest by subtracting 1315.42 from 1549.72. for the second month, the simple interest rate is (250k-234.30)*0,0053*1...and so on.
KdM rocks rocks (1 year ago)
pls guide upload ur video if possible which can clarify
X7 Double Intro (2 years ago)
Get pppayyyydayyyy loooaaaans fast heere => https://twitter.com/bbf93c9f268db4e8d/status/822777246943326208
_SiaH_ (2 years ago)
Gᴇᴛ Рᴀʏᴅᴀʏ Lᴏᴀɴs Fᴀsᴛ ᴀᴛ: https://twitter.com/5938d68528b690df0/status/822777246943326208 Hооoow tо find Intеrеst Рrinсciраl pаyууments оn аа Lоаn in Ехххcel
Chen Bai (5 years ago)
what about the beginning balance and ending balance? man
Rachel Zhang (5 years ago)
Thank you for your help, very nice and clear.
Pravallika Bindu (1 year ago)
Rachel Zhang 5kh
Julio Domínguez (5 years ago)
Thank you!
Dominic Berger (5 years ago)
i cant find the spreadsheet
Ranjan Shankar (6 years ago)
I have been asking the loan officers for this calcultion, which they dont knw.. really a big thanks to you... I tried to divide the interest rate with 12 months i.e. (6.50%/12= 0.54%)... By the way what formula is used for Monthly Rate? Waiting for your reply.
Frank Devonski (6 years ago)
Have you guys heard of Firepa.com ? It's a great place to make money online ! I am making over $3,000+ per month! Visit FIREPA.COM and start making money now! Check it out and earn more than $3.000 per month. Due to the growing importance of the financial sector in modern times,[1] the term real economy is used by analysts[
TheMoneyTruth (6 years ago)
I found a way to make money online. Check this website Money94.Com I subscribed to the website and after 15 days using the System I will able to make at least $500 a month. Now I am working with this system for last three months and my current income is $3000 a month, by using the system part Time. I got all the tools and training that I need to build my online business Empire. Currently I am totally focused to achieve the goal of earring at least $10,000 a month at the end of this year.
tdbl2079 (6 years ago)
You are the absolute best thank you so much, I subscribed.
yungstud171 (6 years ago)
thanks man!!
yungstud171 (6 years ago)
i'm just about to watch this hope i can be like the other guys
sliktrixdc (6 years ago)
Thanks!!! I've used Excel for over 20 years on a daily basis. My mind thinks like an Excel spreadsheet. Even though, what Excel is capable of doing is far more than what I use it for. I'm good at what I do know about it, but I've been missing out on its potential. Very glad I ran across you while searching for this type of calculator. You are awsome and I look forward to learning more from you. Dave C.
azazel322 (6 years ago)
Thank you so much!!! I' am currently a real estate and finance graduate student. Your video and your instructions are so easy to follow. You have simplified so many formulas to calculate payments, interest, principal paid to date etc... Into straight to the point illustration and table! I don't know why they don't teach this in school first then the formulas and theory as supplemental lectures... Thanks once again, I was doing all these step by step on paper :)
Thu Hoài Trần (6 years ago)
thank a lot
Rue Matty (7 years ago)
thank you you have helped a lot!!
a1cswiz1611 (7 years ago)
I'm looking all over your website and I can't seem to find this spreadsheet. Can't you send me a link?
Denis Maiorov (7 years ago)
Elliottsr1 (7 years ago)
Gave me answers I was looking for.
Paintbl99 (7 years ago)
I am having issues continuing the function down through the following 359 rows. It is giving me #NUM! when I click and drag the function down.
TheGiselleBella (9 years ago)
Merci beaucoup
Rachelle Berven (9 years ago)
Thank you very helpful

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.