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

Подписаться 251 тыс.

Просмотров 729 тыс.

50% 1

Excel Courses: www.teachexcel.com/premium-co...
More help: www.teachexcel.com
Excel Forum: www.teachexcel.com/talk/micro...
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

Опубликовано:

29 июн 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 120

@fekrabusinesssolutions 11 месяцев назад

Man, it is 2023 and your video is still useful! And amazing! Thanks!

@richl.3740 6 лет назад

You have a great voice for teaching. Good rhythm and cadence--no monotone, brain-numbing speaking. I appreciate that. Thanks.

@sliktrixdc 11 лет назад

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.

@Jakeyosaurus 2 месяца назад

Thank you gamer, 15 years from the future!

@KiaPresley 6 лет назад

Thank you thank you thank you. My assignment is now done thanks to this video.

@azazel322 11 лет назад

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

@simonsam5541 2 года назад

you all probably dont give a damn but does anybody know of a tool to log back into an Instagram account?? I stupidly lost my login password. I would love any tips you can give me

@hotyoga1 8 лет назад

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

@vmraval5637 4 года назад

Thanks a ton for explaining difficult formula simply

@tdbl2079 11 лет назад

You are the absolute best thank you so much, I subscribed.

@julie5680 2 года назад

This is very helpful, thank you so much!

@vsanchez0721 9 лет назад

Thank you super easy, very helpful

@steven871 2 года назад

Thank you very much for this. How would these formulas change if the loan were repaid quarterly rather than monthly? And if the interest were compound vs simple? Thank you!

@ruematty 12 лет назад

thank you you have helped a lot!!

@sardaraliize 8 лет назад

thanks to educate the community!!!!

@rachelzhang1225 10 лет назад

Thank you for your help, very nice and clear.

@pravallikabindu4236 6 лет назад

Rachel Zhang 5kh

@prasathj7436 4 года назад

Nice explanation, thanks

@Elliottsr1 12 лет назад

Gave me answers I was looking for.

@marieearthangel 9 лет назад

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

@1VBURSEY 5 лет назад

The correct payment, given $250,000 (pv), and 6.50% (i), at 30 years (n) is $1,580.17.

@Raeb222 14 лет назад

Thank you very helpful

@cedargroventg19 4 года назад

Very Helpful thanks

@Amanda-lh6pc 4 года назад

King thank you so much

@mmitbitw 10 лет назад

Thank you!

@shivapal8216 3 года назад

Watching after 12 years thank you for a vdo 😌😌

@monapage6243 8 лет назад

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.

@fahmicanaries89 5 лет назад

Love this tutorial

@TeachExcel 5 лет назад

Thanks :) And sorry for the bad audio, its an old one haha

@yungstud171 11 лет назад

thanks man!!

@cervantes7392 8 лет назад

may I also request a copy of the excel file, I am unable to find it on the web site? thank you

@HonestFranklin 4 месяца назад

I think the Excel version at the time was 2016. Because employees had to drive to work, right-of-use assets and lease liabilities were accounted for, and monthly lease payments, interest expenses, and depreciation expenses were accounted for.

@fffppp8762 10 лет назад

what about the beginning balance and ending balance? man

@alirizvi9728 4 года назад

how to subtract paid amount from balance payment and how to add payments in list form, and how it works simultaneously

@malavikamaluss5638 4 года назад

Thankyou so much☺

@qianli7359 9 лет назад

thanks a lot

@davidviolet 5 лет назад

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.

@Tendertroll1 5 лет назад

You are the man

@thematrix5115 4 года назад

what's the formula to calculate the beginning and the ending balance?

@giahan1608 12 лет назад

thank a lot

@TheGiselleBella 14 лет назад

Merci beaucoup

@shaunsargen 4 года назад

Is there a way to calculate the interest rate if you have all other info except the rate?

@lookingglasssalon7093 Год назад

How did you automate the below columns to change amount of payments?

@damienpileggi6064 7 лет назад

Is there a MACRO that automatically inserts rows for months and copies the formulas based on the years entered?

@willinyaholdings8287 4 года назад

Helpful

@dinutintu 5 лет назад

Nice one.. please send spread sheet link to download this spread sheet

@doosara2000 11 лет назад

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.

@kannanjeya3605 5 лет назад

how i knew my principle amount sbi housing loan when 13 month after

@dominic9893 11 лет назад

i cant find the spreadsheet

@avrahampinhassian4994 3 года назад

I have to create a table to come up with the number of payments and there is a left over how to deal with it

@a1cswiz1611 12 лет назад

I'm looking all over your website and I can't seem to find this spreadsheet. Can't you send me a link?

@jesscrawshaw7229 Месяц назад

Same

@ciaraoconnor447 4 года назад

What calculation do you use to see if someone has been approved or declined a loan?

@drpicmeup Год назад

🤣🤣

@mandyzhang1125 5 лет назад

how to deal with the various interest rate?

@imthatguy4179 4 года назад

what if the loan payment is yearly. lets say 100k$, 2yrs, 10%. will it be the same total monthly amounts?

@Paintbl99 13 лет назад

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.

@DenisMaiorov 12 лет назад

helpful

@princeosei-kwarteng4627 4 года назад

what is the loan is 100,00$ for 2yrs at 10%. if payment is made monthly or yearly would the cash payment be equal?

@jaymuller6004 3 года назад

Hi , what would the total interest paid on a loan of $600,000-00 taken over 4 years at a rate of 12%. Just want to clarify an argument. Thanks

@ersem.karadag 2 года назад

Based on the yearly interest rate of 6.50%, the monthly rate should be (6.50/12) 0.54166; not 0.53%.

@junuthokar4781 7 лет назад

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

@AminulIslam-eg9rm 8 лет назад

can you give the attachment

@rachelzaragoza869 5 лет назад

what about if they pay one month later? Howto track the interest?

@ronaldpornobi8251 8 лет назад

may I request a copy of the excel file

@ionelus111 Год назад

how to i copy this formula to all 360 rows?

@blairwilson822 3 года назад

hi do you have a direct link for this ?

@jeremylaplace1712 10 лет назад

I have a question, How would you calculate if you make additional payments, or pay extra on your payments?

@rachelzaragoza869 5 лет назад

or late payments?

@yungstud171 11 лет назад

i'm just about to watch this hope i can be like the other guys

@Strawberrysunset23 7 лет назад

How about anual payments

@Madge778 6 лет назад

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.

@jesscrawshaw7229 Месяц назад

How do you download this sheet ?

@franklinyoung703 3 года назад

how come when i used your formula on ppmt the values returned was $232.22 & not $234.30 likewise for Ipmt the values is $1325.00 & not $1,315.42

@financekid3163 7 лет назад

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!

@dabikedude1 9 лет назад

I can't find the spreadsheet and your website...Can you help?....thanks for your time.

@blairwilson822 3 года назад

this

@dabikedude1 3 года назад

@@blairwilson822 Thank you very much.

@manishmitra4228 5 лет назад

How to get monthly rate

@needfood3052 2 года назад

For some reason I got it mixed up. 🤔 My interest rate is the smaller number and my Principle is the larger one.

@marvel-rock8908 3 года назад

May I have a copy of that excel file please share me

@natureaccent1133 3 года назад

Hi, I would like to get the spreadsheet from the website, however, I can't find it, could you tell me exactly where I can find it?

@hannahl4129 3 года назад

I couldnt' find it either but this other youtube video seems more helpful ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-rnR6jofEVXs.html

@cameronyoung5818 5 лет назад

cheers boss, youre literally doing my finance assessment u fuck

@financialbubbles1258 4 года назад

Nice Video #Financialbubbles

@leonpronin5395 9 лет назад

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

@KaienS 9 лет назад

Leon Pronin Periodic Payment(monthly payment)=Amount($250000) / ( (1- ( 1/ ( ( 1+ ( Interest Rate / 12 ) ) ^ 360 ) ) ) / ( Interest Rate / 12 ) )

@KaienS 9 лет назад

Kaien S *360 = 30 x 12

@emigdioalaniz 8 лет назад

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.

@TheZuOmurbek 8 лет назад

if you pay you can get, that's what for the link of his website

@vladimirensomo5547 3 года назад

i cant seem to find the link as well. Can you help me please and sent me a copy?

@TeachExcel 3 года назад

This video is so old that I don't even know where I was in the world when I made it lol. I do not think that file exists any more. It's not that difficult to make though and if you would like some help with it, you can ask in our forum: www.teachexcel.com/talk/microsoft-office?src=yt_comment (but give it a shot on your own as well)

@allisonedwards256 6 лет назад

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 6 лет назад

The channel is current, new videos every Tuesday, but this video is rather old and I don't have the file for it anymore.

@richardvargas5222 9 лет назад

mine just gives the #Num and #val sign when dragging down, and how do you get it to change months with the update info

@CharithHewawasam 8 лет назад

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

@drewpierson2674 10 лет назад

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.

@juanrdz2516 8 лет назад

Exactly. I'd never depend on Excel to do the math without reviewing the results on paper !

@TheZuOmurbek 8 лет назад

It dissapoints me as well. In the end of video he says to go to his website, where you can get everything for payment. Geez it's 21st century, education is free

@josiahparvattan6879 7 лет назад

Gᴇᴛ Рᴀʏᴅᴀʏ Lᴏᴀɴs Fᴀsᴛ ᴀᴛ: twitter.com/5938d68528b690df0/status/822777246943326208 Hооoow tо find Intеrеst Рrinсciраl pаyууments оn аа Lоаn in Ехххcel

@MyLife-ip8dk 7 лет назад

Get pppayyyydayyyy loooaaaans fast heere => twitter.com/bbf93c9f268db4e8d/status/822777246943326208

@kdmrocksrocks4515 6 лет назад

pls guide upload ur video if possible which can clarify

@jikookfandom2693 5 лет назад

2018?

@juanrdz2516 8 лет назад

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.

@Senthan631 9 месяцев назад

The interest calculation is wrong, the interest is calculated based on the remaining balance on the principal, so for the IPMT, you need to use =IPMT(F4, A7,B5*12, E9)

@excaliberx2009 8 лет назад

How did you get 1549.72 ? I got 1571.66

@gregnorthover5400 8 лет назад

+Carlos Almegure he has a formula in the monthly Rate cell F4 =((1+B4)^(1/12))-1

@laydeerada 10 лет назад

yay! =D

@victorp9575 6 лет назад

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.

@hotyoga1 8 лет назад

Hi There, would you tell me your name. thx