Mortgage Calculator

How to come across Interest & Principal payments on a Personal loan in Excel




How to come across the fascination and principal payments on a set amount financial loan in excel. This tutorial will walk you by way of using the PPMT() and IPMT() functions in excel in purchase to come across out how a lot of a regular payment on a financial loan in fact goes to fork out off the financial loan sum and how a lot is just an fascination payment.

To adhere to together with this spreadsheet as nicely as to get more free excel stuff these kinds of as macros, tutorials, posts, etcetera. go to:

42 comments

  1. How about anual payments

    Reply
  2. 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????

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

    Reply
  4. 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.

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

    Reply
  6. How did you get 1549.72 ? I got 1571.66

    Reply
  7. may I request a copy of the excel file

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

    Reply
  9. thanks to educate the community!!!!

    Reply
  10. 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.

    Reply
  11. can you give the attachment

    Reply
  12. 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.

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

    Reply
  14. 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

    Reply
  15. thanks a lot

    Reply
  16. +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!! 

    Reply
  17. Thank you super easy, very helpful

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

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

    Reply
  20. yay! =D

    Reply
  21. 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.

    Reply
  22. what about the beginning balance and ending balance? man

    Reply
  23. Thank you for your help, very nice and clear.

    Reply
  24. Thank you!

    Reply
  25. i cant find the spreadsheet

    Reply
  26. 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 🙂

    Reply
  27. 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.

    Reply
  28. 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[

    Reply
  29. 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.

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

    Reply
  31. thanks man!!

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

    Reply
  33. 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.

    Reply
  34. 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 🙂

    Reply
  35. thank a lot

    Reply
  36. thank you you have helped a lot!!

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

    Reply
  38. helpful

    Reply
  39. Gave me answers I was looking for.

    Reply
  40. 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.

    Reply
  41. Merci beaucoup

    Reply
  42. Thank you very helpful

    Reply

Leave a Reply