Mortgage Calculator

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:

46 comments

  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)

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

    Reply
  3. nice easy to follow, excellent, thankyou.

    Reply
  4. Very Helpful video, Thanks

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

    Reply
  6. Thank you very much for creating this video

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

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

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

    Reply
  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

    Reply
  11. Thanks for your valuable gudance Mr.Dough

    Reply
  12. Fabulous video. Thanks Doug

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

    Reply
  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

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

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

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

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

    Reply
  19. Excellent

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

    Reply
  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

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

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

    Reply
  24. @Doug Thank You, Keep up the good work 🙂

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

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

    Reply
  27. Thanks it was great help….

    Reply
  28. i wish you speak so I can understand better

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

    Reply
  30. really am benefited,thanks

    Reply
  31. Hvala bilo mi je od pomoci

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

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

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

    Reply
  35. Nice video. Good explanation.

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

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

    Reply
  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

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

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

    Reply
  41. I need a amortization template that tracks late payments?  Please help

    Reply
  42.  thank you so much.

    Reply
  43. You Sir have the BEST video on creating an amortization table.  I searched all over youtube to find one that teaches how to add in the extra payment on the principal and the one I did find wasn't anywhere near as good as yours.
    Thank you very much for posting this.

    Reply
  44. Very helpful. Thank you!

    Reply
  45. very useful dear. do you have other videos

    Reply
  46. I tried to do select the cells for 360 by enter the cell a360 and did shift+enter key. It does not work in MAC. Please help.

    Reply

Leave a Reply