I notice an error in your formula. @ 9:50/12:04 under the Principal pmt column wherein you subtracted the interest $1.82 from the Bal left $546.72 to give a value of $544.90. The last principal paid should equal the beg bal which is $546.72. Subtracting the last interest of $1.82 fr4om your final beg. bal will make your total principal payment short of $1.82.
This is wrong Doug. Someone already pointed it out. You subtracted the interest from the beginning balance instead of subtracting the principal. Time: 8:30ish
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.
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
Well presented! I was always curious on how prepayments affected a loan amortization. It seems that while one's monthly payment doesn't change, the loan is retired sooner.
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.
This is a really useful video - thanks. How about creating one that shows how to create a line of credit loan chart allowing for payments and withdrawals along the way?
This video was very helpful and I made this chart. Using this chart, is it possible to show how much less your monthly interest would be if you were to make a consistent prepayment of $50 a month. Thanks
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!
Hi Doug, your videos are great and I have learned a ton. A very quick, yet simple question. Sometimes when I am typing a formula that extends into an adjacent cell that has a value I need, how can I view the value in the adjacent cell without having to exit the cell I am typing the formula in. I know there is a way, but I can not seem to remember. thank you very much and pls keep the awesome videos coming.
Couple ways to do this watch feature or freeze frames or separate scrolling areas. See ua-cam.com/video/YK3Yo-phL9c/v-deo.html ua-cam.com/video/kXi8o3VLP2o/v-deo.html
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.
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?
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.
I would think you just take the actual cost for the Taxes and Insurance Annually and divide by 12. Could be accomplished by adding specifics at the top for those two fixed annual costs and then creating a column with that figure divided by 12 then adding to the right. Hope this helps
yes, if it's assuming that the interest in compounded daily, then the calculations would have to take into consideration of leap year. See this link for some discussion on this superuser.com/questions/1273265/calculating-compounding-daily-interest-issues-with-leap-years
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
Doug H, I know this is an old video but hope you can still help me with my question. I've almost got everything you've explained but, I'm trying to figure out how to make my payments show up as Bi-Weekly with additional money towards the principal every month but not at the very beginning of the loan. So I'm not sure if the loan/ interest rate would be calculated different also. I tried using 30 yr loan ÷ by 26 payments per year and totally threw everything off. Think you can help? Thanks
Sorry, don’t understand the comment/ question...but I think you may be referencing if the payment is at the beginning or end. That can be factored into the calculation under the type argument....PMT(rate, nper, pv, [fv], [type])..Type The number 0 (at the end) or 1(at the beginning) and indicates when payments are due.
@@DougHExcel since I started my loan I've changed my payment plan 2 times. The first 3 payments was regular payments due on 1st of month. Then I changed payments to half on 1st, half on 15th every month for maybe 6 months with $150 extra. Then I broke payment to bi-weekly payments along with the extra $150 towards interest for next 6 years. I feel like the bank doesn't calculate properly on my interest. They hold all payments until end of month , then pay everything at once, supposedly pro-rating my bi-weekly payments towards principal and interest. So I'm trying to figure out my precise amount owed. Now that my finances are better, I want to add more money towards the principal and try using your method to figure out my best payment plan to pay off my debt ASAP and see how much I need to pay in order to pay off by certain time frame. Thank you for answering and the video.
@@DougHExcel If I start my emi from Dec 2020 I want Sum of Principal & Interest amount till Mar 2021 And From Apr 2021 till Mar 2022 As India financial year starts on Apr every year So what formula should I use to Get sum of Principal and Interest from Dec 20 till Mar21
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.
You might need to branch out to a separate table that will calculate the daily interest rate summation up to that date. That can be done but I don't have a video that covers this use case.
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
I have trouble seeing what you're doing when your talk about the adding the word 'round' and the 'if' toward the end. (typing in the bar on the top.) could you explain what you're typing please?
How can I get this to work in google spread sheets. At 5:56, when you double click the fill handle and it fills in your formulas all away across, when I do that in google spread sheets, it gets all messed up and doesn't apply the formulas right????? HELP!!!
don't think the calculation done on row 275 as regards calculation principal is correct at 9:19. Interest isn't part of the unpaid loan principal balance. Your last month principle payment should be the unpaid principal balance (UPB) if UPB is less that scheduled monthly payment.
Big huge hiccup that is not seen I the video. By adding. $50,000 lump sum payment, it rescues the amount of total principal paid to $250k which is inaccurate. Put a sum formula in box e372 and tell me why a lump sum principal payment in box f16 reduces the total amount of principal paid over 360 months
Because your e372 only takes into account monthly Principal Payments and you completely ignored the Pre-payment principal in your sum. Put a sum formula at f372, and then add together e372 and f372. That is the total principal paid. All 300k accounted for.
I notice an error in your formula. @ 9:50/12:04 under the Principal pmt column wherein you subtracted the interest $1.82 from the Bal left $546.72 to give a value of $544.90. The last principal paid should equal the beg bal which is $546.72. Subtracting the last interest of $1.82 fr4om your final beg. bal will make your total principal payment short of $1.82.
i saw this too, good find
This is wrong Doug. Someone already pointed it out. You subtracted the interest from the beginning balance instead of subtracting the principal. Time: 8:30ish
Your "IF" not should be equal to your beg balance, not beg balance minus interest. That way your ending balance will be zero
I was with you right up to the last two formula changes in cells E275 and G275 =IF(C277>$B$9,ROUND($B$9,2)-D277,C277-D277)
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.
Excellent .. !! I am searching this form long time.. and now I got correct station.
Great 👍
Explained very well and each step.. Beautiful! Thank you Doug!!
Thanks for the comment!
Thanks
I needed something to calculate extra payments every month and this was very easy to set up
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
Hi emmanuel khawas, thanks for the kind words!
Just a tip: let the viewers know that you put a minus sign (-) before the PMT function.
Hi Lake Chocolate, thanks for the comment!
Great job! I was able to follow along and create a table for myself. Thank you!!!
Glad it helped!
Well presented! I was always curious on how prepayments affected a loan amortization. It seems that while one's monthly payment doesn't change, the loan is retired sooner.
Hi @ambientdjs2938, thanks for the comment!
Thanks - very clear, and learned a few Excel tricks (formula button, copying down, and so on).
This is such a great video...easy to follow and detailed explanations! Thank you!
Glad you enjoyed it!
Great explanation, thanks!
Glad you enjoyed it!
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.
Glad you found this helpful. Thanks for the comment!
Thanks for the Mortgage Amortization Schedule in Excel--you made it easy to do lots of things I've been needing!
This is a really useful video - thanks. How about creating one that shows how to create a line of credit loan chart allowing for payments and withdrawals along the way?
Hi Peter Ball, thanks for the comment and idea for potential future video!
This video was very helpful and I made this chart. Using this chart, is it possible to show how much less your monthly interest would be if you were to make a consistent prepayment of $50 a month. Thanks
the easiest and quickest way would be to make two of these tables and then find the difference in the interest column
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!
Hi Thear Khun, thanks for the comment!
Hey! Nice and helpful video! How about when interest rate is floating? Like euribor interest is checked every 12mo.
hi there, I don't seem to understand how you worked out the interest...please kindly explain!
Thank you so much for this detailed video.
You are so welcome!
How do you suggest I calculate the amount saved on my excel sheet because of my prepayment vs the original payment schedule ?
Hi Doug, your videos are great and I have learned a ton. A very quick, yet simple question. Sometimes when I am typing a formula that extends into an adjacent cell that has a value I need, how can I view the value in the adjacent cell without having to exit the cell I am typing the formula in. I know there is a way, but I can not seem to remember. thank you very much and pls keep the awesome videos coming.
Couple ways to do this watch feature or freeze frames or separate scrolling areas. See
ua-cam.com/video/YK3Yo-phL9c/v-deo.html
ua-cam.com/video/kXi8o3VLP2o/v-deo.html
Doug, This Video was very Useful..!! Thanks for your great work..!!
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.
Hi Nagendra Mishr, thanks for the comment! Yes can be done, but I don't have a video for that :-(
Thanks for your valuable gudance Mr.Dough
Hi Vinay Bhagwat, you're welcome!
All works fine up until Principal paid formula. The principle paid and monthly interest do not match monthly payment?
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?
There are many different ways to do the same thing in Excel :-) I just choose this one.
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?
How in the world did you get those dashes to show instead of zeros ? Love your videos.
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?
Thanks!!
Hi Crystal Mckay, thanks for the comment!
Cheers for the video, man! Will be using this to manage my loans.
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.
Escrow...like with prop tax and insurance? Hmm i'll look into maybe another video. :-)
Yes!!!! That would be awesome!!! Thank You!! 🤗
I would think you just take the actual cost for the Taxes and Insurance Annually and divide by 12. Could be accomplished by adding specifics at the top for those two fixed annual costs and then creating a column with that figure divided by 12 then adding to the right. Hope this helps
Dough H, did you ever do another video adding an escrow account? Was just wondering, thanks again for the video.. :)
At 7:48, why do you round just the monthly payment? You don't get rid of the extra decimals unless you round the interest with it. =round($B$6-D12,2)
Hi jeromilittle, thanks for adding to the thread!
Thank you Very Helpful !!!!!
Glad it helped!
Great video!
Thanks!
Thanks for posting. Great teaching!
This video REALLY helped me! THank YOU
Thanks Lisa Hanna, glad it helped!
Fabulous video. Thanks Doug
Hi Joanne Roper, thanks for the comment!
If a borrower is not paying his scheduled payments and interest rate is mounting how to accommodate that when calculating ending balance.
one approach is to zero it out on that payment period and eventually there would be additional payment period(s) added at the end
Is there a way to have a cell identify what month the payoff is?
Try conditional formatting ua-cam.com/users/dough517search?view_as=subscriber&query=conditional+formatting
Can one do this table on a daily basis over lets day 4 year amortization?
yes, if it's assuming that the interest in compounded daily, then the calculations would have to take into consideration of leap year. See this link for some discussion on this superuser.com/questions/1273265/calculating-compounding-daily-interest-issues-with-leap-years
Very Helpful video, Thanks
Glad you liked!
nice easy to follow, excellent, thankyou.
Glad you liked, thanks!
thank you and more power!
Thank you too!
My payment keeps coming up as a negative...what am i doing wrong
could you create a Interest only payment chart but allow for principal payments to be made to lower interest only loan?
I agree, this would be helpful as my student loans are set up as interest only payments :(
Thank you very much for creating this video
You're welcome!
if i have fixed quaterly payment, number of years and total amount payable, how to find AER or APR
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
Hello Doug... Thank you so much... Does this calculate leap years?
Hi Chris Kim, thanks for the comment! it should and that will depend on how you specific the interest calculation periods
Doug H, I know this is an old video but hope you can still help me with my question. I've almost got everything you've explained but, I'm trying to figure out how to make my payments show up as Bi-Weekly with additional money towards the principal every month but not at the very beginning of the loan. So I'm not sure if the loan/ interest rate would be calculated different also. I tried using 30 yr loan ÷ by 26 payments per year and totally threw everything off. Think you can help? Thanks
Sorry, don’t understand the comment/ question...but I think you may be referencing if the payment is at the beginning or end. That can be factored into the calculation under the type argument....PMT(rate, nper, pv, [fv], [type])..Type The number 0 (at the end) or 1(at the beginning) and indicates when payments are due.
@@DougHExcel since I started my loan I've changed my payment plan 2 times. The first 3 payments was regular payments due on 1st of month. Then I changed payments to half on 1st, half on 15th every month for maybe 6 months with $150 extra. Then I broke payment to bi-weekly payments along with the extra $150 towards interest for next 6 years. I feel like the bank doesn't calculate properly on my interest. They hold all payments until end of month , then pay everything at once, supposedly pro-rating my bi-weekly payments towards principal and interest. So I'm trying to figure out my precise amount owed. Now that my finances are better, I want to add more money towards the principal and try using your method to figure out my best payment plan to pay off my debt ASAP and see how much I need to pay in order to pay off by certain time frame.
Thank you for answering and the video.
do you know where I can get your excelsheet
Hi Cat making a come back, sorry don't have files to download :-(
What formula to use if I want from Apr to Mar (yearly)
Sorry, don’t understand the comment/ question... but if I'm understanding correctly then your start period 1 is Apr
@@DougHExcel
If I start my emi from Dec 2020
I want Sum of Principal & Interest amount till Mar 2021
And From Apr 2021 till Mar 2022
As India financial year starts on Apr every year
So what formula should I use to Get sum of Principal and Interest from Dec 20 till Mar21
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.
You might need to branch out to a separate table that will calculate the daily interest rate summation up to that date. That can be done but I don't have a video that covers this use case.
Very helpful. Thanks
Hi Ashwani Kumar, thanks for the comment!
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
Thor R, do you have any online sources for this equation? I've never seen it done this way.
Thank you!
You're welcome!
I have trouble seeing what you're doing when your talk about the adding the word 'round'
and the 'if' toward the end. (typing in the bar on the top.) could you explain what you're typing please?
Sorry, don’t understand the comment/ question...
@Doug Thank You, Keep up the good work :-)
Nice video. Good explanation.
Thanks it was great help....
very useful dear. do you have other videos
How can I get this to work in google spread sheets. At 5:56, when you double click the fill handle and it fills in your formulas all away across, when I do that in google spread sheets, it gets all messed up and doesn't apply the formulas right????? HELP!!!
Hi ericaleecannon, thanks for the comment, don't know GOOG sheets...
@@DougHExcel after playing around with it in google sheets for a while, I think I got it figured out!
Hvala bilo mi je od pomoci
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.
Hit this one out of the park!!!
Very helpful. Thank you!
don't think the calculation done on row 275 as regards calculation principal is correct at 9:19. Interest isn't part of the unpaid loan principal balance. Your last month principle payment should be the unpaid principal balance (UPB) if UPB is less that scheduled monthly payment.
Francis Nellary, that is very interesting...thanks for letting me know.
Thank you, very helpful!!!
You're Welcome!
I need a amortization template that tracks late payments? Please help
Nice Video Thanks
Hi Prakash kumar prasad...you're welcome, glad you liked!
youre the man
Thanks Orkhan Hasanov!
thank you so much.
Big huge hiccup that is not seen I the video. By adding. $50,000 lump sum payment, it rescues the amount of total principal paid to $250k which is inaccurate. Put a sum formula in box e372 and tell me why a lump sum principal payment in box f16 reduces the total amount of principal paid over 360 months
Because your e372 only takes into account monthly Principal Payments and you completely ignored the Pre-payment principal in your sum. Put a sum formula at f372, and then add together e372 and f372. That is the total principal paid. All 300k accounted for.
Hi Dan Smedley, thanks for the comment
Excellent
Thank god!
Hi kilgore smash, thanks for the comment!
Very well done, but please do not use "minus" as a transitive verb: should be "subtract".
...thanks...learned something different!
My head hurts!
R John, that is very interesting...thanks for letting me know.
i wish you speak so I can understand better
Thank you so much this is a great video
Thanks babyjudy8040!
Thank you so much this is a great video
+Douglas Fisk you're welcome!