# Create an Amortization Desk with a Pre-Payment Choice

How to build an amortization table that also allow you work out pre-payment quantities ————————————————————————————————-

Subscribe to the Weblog:

1. I don't mean to put it so bluntly, rather just for the benefit of everyone else watching, but the formulae in this video are wrong.

The 'New Balance' column (column G) is set up to show the remaining Principle Balance. Therefore, column C is also the principle balance. However, if you go to 10:36 in your video, you are showing a 'Principal Value' of \$546.72 in cell C275. Interest on this principle (cell D275) is shown as \$1.82. The payment therefore (to bring your loan account to zero) is \$548.54 (principle of \$546.72 + interest of \$1.82). Yet your 'Principle Payment' column shows a principle payment of \$544.90.

Also, you are basically telling your table to give a zero balance if a certain condition is met, rather than letting the table check for itself that the final balance should be/is zero.

Your formulae in column G should simply be 'C-E-F' with no IF function. Then, you should change the VALUE IF FALSE part of the formula in column E to show as the value in column C, as oppose C – D.

I'm sure (in fact I've checked) that if you were to total up all of your 'Principle Payments' and all of your 'Interest Payments' that the sum of the two would not add up to the value of the repayments due (which would be calculated as the value of the payment in cell B6 multiplied by the number of payment periods in cell B2)

2. Very well done, but please do not use "minus" as a transitive verb: should be "subtract".

3. nice easy to follow, excellent, thankyou.

5. Thanks for the video. I was looking for a way to add an escrow payment to the amortization schedule as well. Is there a video for that too?? I'm definitely NOT excel savvy.

6. Thank you very much for creating this video

7. Hi Doug, i would like to ask, why weren't you use IPMT formula to Calculating Interest and PPMT formula to calculating Principal Paid instead?

8. This video was extremely helpful well done and easy to follow. I had tried other tutorials before but your instructions were the only ones I could make work.

9. Thanks this was really helpful. If I made two separate payments on different dates that that when totaled was my total payment owed for that month, what adjustments must I make with the chart to ensure I am calculating correctly. For example my monthly payment is \$2000.00 and I make a payment of \$1000.00 on June 2nd and my second payment of \$1000.00 on June 6th. what adjustments are needed to calculate correctly?

10. I have searched a lot in the net to check as to how we can create a loan repayment sheet in excel, saw many articles and few videos but I was able to make a perfect table only with your video. Thanks a ton Doug. You have amazing excel skills. I would like to learn from your videos, did you make few videos through which we can learn excel basic and also advanced?

Thank you once again. đź™‚

Emmanuel

11. Thanks for your valuable gudance Mr.Dough

12. Fabulous video. Thanks Doug

13. Doug, very helpful, any idea how I would add a feature that assumes I make an early payoff what I owe using the amort table. For example, I take out a loan on Dec 15, 2015, My first payment is Jan 15 2016 , I make all my payments on time and May 20, 2016 I want to pay off the loan. I would like to put a payoff date of May 20, 2016 into a cell and then have the system assume I made all my payments through the previous pay period and give me a payoff by taking the May 15 amort balance number and adding five days of interest and giving me a revised total.

14. Hello Doug, is it possible to hire you to make a quick excel for my company… It's a payment calculator… My name is chris and I can be reached kimchrisy@ gmail.com

15. Hello Doug… Thank you so much… Does this calculate leap years?

16. Doug, nice video.Â  Any way to calculate the prepayment without creating a table?Â  I would like to know the number of months reduced with making an identical prepayment every month.

17. This video is really helpful. To add on, how do you have the "number of years" or mature date change in the first row when you made an extra payment? I find it a bit of a pain to scroll down to look at the new mature date rather then it updating in the "number of years" cell. Thanks in advance for the reply!

18. Thanks for the Mortgage Amortization Schedule in Excel–you made it easy to do lots of things I've been needing!

19. Excellent

20. could you create a Interest only payment chart but allow for principal payments to be made to lower interest only loan?

21. If you round and create those if statements, what happens to the last balance of \$2.86.. By making it 0, it doesn't account for where it goes. Please advise

22. if i have fixed quaterly payment, number of years and total amount payable, how to find AER or APR

23. How do you suggest I calculate the amount saved on my excel sheet because of my prepayment vs the original payment schedule ?

24. @Doug Thank You, Keep up the good work đź™‚

25. How in the world did you get those dashes to show instead of zeros ? Love your videos.

26. I have a schedule made but I would like to add a column for cumulative principal. Is there a way to add this without redoing the whole spreadsheet?

27. Thanks it was great help….

28. i wish you speak so I can understand better

29. Hey! Nice and helpful video! How about when interest rate is floating? Like euribor interest Â is checked every 12mo.

30. really am benefited,thanks

31. Hvala bilo mi je od pomoci

32. I found my mistake. Â It should be =PMT instaed. Â Great video.

33. At 1:15 in the video concerning formula for the payment. Â Using your formula of =-IPMT(B4,B2,B5,0,0), I get \$0 in cell B6. Â In correcting it, I keep an error message.

34. Thank you for the help and extra explanation added at the end.Â

35. Nice video. Good explanation.

36. All works fine up until Principal paid formula. The principle paid and monthly interest do not match monthly payment?Â

37. hi there, I don't seem to understand how you worked out the interest…please kindly explain!

38. Nice sheet and I like the pre-payment feature but unfortunately you miscalculated the monthly interest rate. Just dividing by 12 is not the right way to do it and as a result everything else is slightly inaccurate. Here's the fix you need to put in cell B4:
=((1+B3)^(1/12))-1

39. Excellent job! I especially like how you went back and added to a few of the formulas and explained along the way. That is more helpful than trying to explain it all at once.

40. Hit this one out of the park!!!

42. Â thank you so much.