How to discover Desire & Principal payments on a Mortgage in Excel
How to discover the interest and principal payments on a preset charge loan in excel. This tutorial will walk you as a result of applying the PPMT() and IPMT() capabilities in excel in get to discover out how substantially of a regular payment on a loan essentially goes to pay off the loan quantity and how substantially is just an interest payment.
To abide by along with this spreadsheet as perfectly as to get extra absolutely free excel things such as macros, tutorials, articles or blog posts, etc. go to:
42 comments
Strawberrysunset23 March 13, 2017 10:21 am
How about anual payments
Replyjunu thokar March 13, 2017 10:21 am
plz help me!!
Replyif 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 March 13, 2017 10:21 am
Is there a MACRO that automatically inserts rows for months and copies the formulas based on the years entered?
ReplyJuan RDZ March 13, 2017 10:21 am
That's the wrong way to calculate interest !
ReplyEspecially at 6.5%.
More money should be going towards your principal if your paying 6.5% interest.
Cervantes March 13, 2017 10:21 am
may I also request a copy of the excel file, I am unable to find it on the web site? thank you
ReplyCarlos Almegure March 13, 2017 10:21 am
How did you get 1549.72 ? I got 1571.66
ReplyEngr Ronald Pornobi March 13, 2017 10:21 am
may I request a copy of the excel file
ReplyEmigdio Alaniz March 13, 2017 10:21 am
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.
ReplySardar Ali March 13, 2017 10:21 am
thanks to educate the community!!!!
Replyredpant5 March 13, 2017 10:21 am
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.
ReplyAminul Islam March 13, 2017 10:21 am
can you give the attachment
ReplyMona Page March 13, 2017 10:21 am
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.
ReplyRichard Vargas March 13, 2017 10:21 am
mine just gives the #Num and #val sign when dragging down, and how do you get it to change months with the update info
Replymarieearthangel March 13, 2017 10:21 am
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
ReplyQian Li March 13, 2017 10:21 am
thanks a lot
ReplyLeon Pronin March 13, 2017 10:21 am
+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!!
ReplyVeronica Sanchez March 13, 2017 10:21 am
Thank you super easy, very helpful
ReplyJc Buffington March 13, 2017 10:21 am
I can't find the spreadsheet and your website…Can you help?….thanks for your time.
ReplyJeremy Laplace March 13, 2017 10:21 am
I have a question, How would you calculate if you make additional payments, or pay extra on your payments?
Replylaydeerada March 13, 2017 10:21 am
yay! =D
ReplyDrew Pierson March 13, 2017 10:21 am
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.
ReplyChen Bai March 13, 2017 10:21 am
what about the beginning balance and ending balance? man
ReplyRachel Zhang March 13, 2017 10:21 am
Thank you for your help, very nice and clear.
ReplyJulio Domínguez March 13, 2017 10:21 am
Thank you!
ReplyD Berger March 13, 2017 10:21 am
i cant find the spreadsheet
Replymanamanah88 March 13, 2017 10:21 am
Great tutorial. I tried to download the existing file but could not find it.
How do you finish the spreadsheet? i.e. set up the list of payment months so that they change according to the term entered (and the cumulative data updates accordingly)? I have managed to set up line 1 but am stuck now! Thanks 🙂
ReplyRanjan Shankar March 13, 2017 10:21 am
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.
ReplyFrank Devonski March 13, 2017 10:21 am
Have you guys heard of Firepa.com ? It's a great place to make money online !
ReplyI 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 March 13, 2017 10:21 am
I found a way to make money online.
ReplyCheck 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 March 13, 2017 10:21 am
You are the absolute best thank you so much, I subscribed.
Replyyungstud171 March 13, 2017 10:21 am
thanks man!!
Replyyungstud171 March 13, 2017 10:21 am
i'm just about to watch this hope i can be like the other guys
Replysliktrixdc March 13, 2017 10:21 am
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.
Replyazazel322 March 13, 2017 10:21 am
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 🙂
ReplyThu Hoài Trần March 13, 2017 10:21 am
thank a lot
ReplyRue Matty March 13, 2017 10:21 am
thank you you have helped a lot!!
Replya1cswiz1611 March 13, 2017 10:21 am
I'm looking all over your website and I can't seem to find this spreadsheet. Can't you send me a link?
ReplyDenis Maiorov March 13, 2017 10:21 am
helpful
ReplyElliottsr1 March 13, 2017 10:21 am
Gave me answers I was looking for.
ReplyPaintbl99 March 13, 2017 10:21 am
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.
ReplyTheGiselleBella March 13, 2017 10:21 am
Merci beaucoup
ReplyRachelle Berven March 13, 2017 10:21 am
Thank you very helpful
Reply