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

1. How about anual payments

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

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

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.

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

6. How did you get 1549.72 ? I got 1571.66

7. may I request a copy of the excel file

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.

9. thanks to educate the community!!!!

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.

11. can you give the attachment

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.

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

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

15. thanks a lot

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

17. Thank you super easy, very helpful

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

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

20. yay! =D

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.

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

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

24. Thank you!

25. i cant find the spreadsheet

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 🙂

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.

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[

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.

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

31. thanks man!!

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

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.

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 🙂

35. thank a lot

36. thank you you have helped a lot!!

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