Check out my end to end Quickbooks Training. www.incomedigs.com/reab ($50 off w/ code TH-cam50) Link to Advanced Amort Calculator: www.incomedigs.com/amort-calculator
@@Incomedigs is there a formula to have the sheet update every month automatically to apply each periods principal to the balance? Rather than just having a detailed schedule with 360 rows?
That was awesome! You saved me so much time and taught me about locking my cells, which I have been trying to learn / figure out for several years! You rock!
You've the gift of teaching, and I learned way more about Google Sheets from this video than the primary topic you were addressing. Thanks for sharing.
Hi There! Great to hear! Excel/ Google Sheets can really build on itself. As you start to unlock formulas and functionality, you'll surely gain confidence in exploring more advanced functionality! Stick with it!
As a business teacher, this is exactly what I was looking for. Thank you! Quick question though -- and forgive me if you've already answered it (250 comments & I've read like seven) -- in the event the payment is greater than the established minimum, how do we futz around with this to make sure the spreadsheet still works? Thank you again! This is excellent, and I'll be bringing it into my classroom this month!
Hi Jeff! Thanks for watching....I recommend checking out the Advanced Amort schedule: th-cam.com/video/1JiY_UUjPKY/w-d-xo.html This includes a field to make extra payments!
Thank you so much!! Just getting a construction loan converted to a 5 year payoff and this is perfect. Will also complete one for the new vehicle I just bought. Super excited to know exactly how much to add to the principal each month to pay off quicker.
Hi Connie! So glad you found the calculator helpful! Just want to note that we have a follow up video with some advanced functionality: th-cam.com/video/1JiY_UUjPKY/w-d-xo.html Let me know if there is anything else you'd like to see! Happy Loan Repayment!
thanks for creating this video, it was very easy and helpful. How do I create a column or field for calculating advance payments? ie. if I would to make a payment to go direct to principal? thanks!
Fantastic tutorial, thank you. I modified this slightly for a business loan with a 2 year term that starts with lower payments and works to higher payments by the end of the term, so as to allow a little breathing room to get business moving at the onset. Thanks so much!
Hello! Thank you! That is exactly what I was looking for and I am all set up. In June we are going to pay extra money towards the principal balance, but we want to keep our monthly payments the same. How would I adjust the principal balance but still record the payment?
Hi Rachel! You would add a column for "additional principal"...this would be an input column for each month formatted as a number. You can go ahead and prefill all of these to "0". You would then adjust your "Loan Balance" calculation to subtract out this cell. So your "Loan Balance" would reduce by the principal that is part of your fixed payment as well as the additional principal payment. Let me know if you have any other questions!
You should make a video explaining how to add in an overpayment for each month. Say your monthly payment is $800 but you pay $850. (Helps to find out how long you'll be paying PMI)
Hi Linda...thanks for watching! We have a follow up video with some advanced features...including additional principal payments. You can check it out here: th-cam.com/video/1JiY_UUjPKY/w-d-xo.html
Hi Kanish, Thanks for watching! The calculator will always assume a consistent interest rate for any given period. If you wanted to have the interest rate change over time...we would need to devise a very different tool...one that allows the user to add an interest rate to every period. This can be done...but would be a unique case. Let me know if you would like some one on one help creating this. I can be reached at nick@incomedigs.com. Thanks!
For the period collum, I put an equation starting from 1. =SEQUENCE(E2*12). The sequence starts off with 1 so I put 0 as 0 and skipped to 1. E2 is the term (years) box. The only thing is that you still have to drag the rest down to said number but now you know where and when to stop.
Hi Nelson...thanks for watching! We recorded a follow up showing an "advanced" calculator...this includes making extra payments: th-cam.com/video/1JiY_UUjPKY/w-d-xo.html
Great video and explanations! Can you please share how you would handle a loan that's deferred. Would you just add the deferred months to the term? Thank you!
Hi Rich...a Deferred loan is pretty much "on hold". These loans sometimes still accrue interest...and sometimes they don't. The specifics of the loan would indicate how to handle it. In the case where interest is not accruing...you would just delay the "start date" of the loan". If interest is accruing...you would adjust the formulas for the months of deferment such that the total owed is only the interest: Interest Rate/ 12 x principal.
Great watch and a really big help to me personally:) have you got a Google sheet like this where I can add different overpayment each month. Thanks again
Super helpful tutorial, appreciate what you do! I have a question. I did this sheet for my car loan. If I wanted to pay more each month on top of the monthly payment, would I enter the amount I paid in the payment box? Would this mess anything up?
Hey Chaston...thanks for watching! Check out the advanced calculator. This includes the ability to add extra payments: th-cam.com/video/1JiY_UUjPKY/w-d-xo.html
Hi Maria! Thanks for watching! 30/60 would be a slight adjustment to your PMT calculation =PMT(C3/360*30,E2*12,C2). In the above example, the monthly rate would be identical.
This is very helpful. How does one factor in if you have a Balloon/Residual Payment that you must still pay? I tried to add a Balloon Payment separately with the Principal Amount but it doesn't add up.
Hi there!...thanks for watching. I'll first direct you to the advanced amort schedule: th-cam.com/video/1JiY_UUjPKY/w-d-xo.html To add a balloon payment, add a field to the top: "Balloon After". You can then setup, within your table, an if statement. This would indicate to the sheet: "If this period is the balloon payment date, pay the entire balance plus additional interest" I could set this up for you...please email: nick@incomedigs.com
Wow so simple. Can you assist me how can we compute the amortization for the last period without using excel (manual). Lets say amortization schedule for the last 6 periods.
Hi Japhet...thanks for watching! Wikipedia has a decent breakdown for manually calculating interest/ principal due for a given period: en.wikipedia.org/wiki/Amortization_calculator This is, in effect, everything that excel/ google sheets is doing.
Is there a way to calculate if they are not paying on the same day each month and the amount of the payment varies each month? What is the best way to calculate based on these variables?
Hi Kendra...thanks for watching! Please check out our video on the "advanced" amort calculator....you can add additional monthly payments: th-cam.com/video/1JiY_UUjPKY/w-d-xo.html
This is very helpful. thank you for making this video, I know there are some additional unknown variables but is there a way to calculate the positive equity using the formula?
Thanks Eric! Thanks for watching. You certainly could calculate equity. You would need a column or a fixed cell for "Market Value". You would then have a column to calculate Market Value - Loan Balance for each given period
Hi...thanks for watching! You would simply leave the monthly payment calculation out...insert the value of each payment. The rest of the calculation should accurately calculate the interest/ principal for each payment. A good idea would be to keep the calculation of the min monthly payment as the baseline...and then go in and override the monthly payment with whatever you ended up (or plan to) actually pay moving forward. We also have this calculator which allows you to play with additional payments to see how it impacts your prepayment: th-cam.com/video/_M7vt__a9vQ/w-d-xo.html
Hi Maria! Good suggestion! In simple terms, you would just be adjusting your "Periods". If each period is 2 weeks...instead of 1 month, you'll have 26 periods in a year. Therefore, instead of dividing/ multiplying by 12....you would divide/ multiply by 26. I will add the video to the queue.
Question on this video, if we pay more that what is needed, how does that calculate in? Do we just change the payment amount in the Monthly Payment column and it will adjust? This is the first amortization I've done and this tutorial was SO helpful! Thank you for this!
Hi Casey...thanks for watching. You would need to add an additional column for "Additional Principal Payment". Then you would simply take the calculation that is in the column "Principal Payment" (which calculates the amt of principal from the regular monthly payment) and add the new column for "additional principal payment"....does that make sense?
Hi, first of all, thank you! It was very clear and helpful. Just want to ask what to do if the dates are not always exactly 1 month - for example; initial date is 10 Dec 2018 then the first payment falls on 5 Jan 2019 then the next ones is 1st of the following month then after 5th payment, became 3rd of the month then go back again to the 1st? Hope you can help. I’m trying my hand to work it out but wish you can save me time. Thanks again
Amazing video. Lets say I wanted to pay it off faster, by paying an additional variable amount into the loan account, let say $100 here and $200 there, just random amounts at various times, how would I add that to the sheet to account for it, to update me about when the home loan would be paid off?
@@Incomedigs I did actually! This video (th-cam.com/video/_M7vt__a9vQ/w-d-xo.html) where you have an excel sheet loan repayment calculator, is there any way this can be made into a google sheet? I just don't want to buy Microsoft office just for this one reason.
Great video thank you. BIG QUESTION. If I miss a monthly payment I presume I just stick a zero in the 2 lines and it will still work by copying the formula from the month before? Secondly, when the interest changes, I want to keep the historical but change the interest going forward. Do I change the historical value from formula AND all else stays the same? What if I tack on 4 months so the period is not 15 years but 15 years and 4 months? So much thanks if you can let me know about this.
Hey Mark, Thanks for watching! The schedule is meant to be a "Plan"...of course, reality can make some changes. I would suggest "force feeding" the tool as needed. For example, if you miss a few payments, you could copy the past payments you made and "Past Values" in their exact same spot. This would work to "freeze" the past payments and they would not be impacted by your updated calculations. If the interest changes, you may benefit from bringing cell C3 into a separate row so that it can be calculated with each monthly payment. You can click and drag an interest payment down and change it as necessary. If you wanted the ability to use months instead of years, simply put the months into cell E2 (so 15 and 4 would be 184). Then, on your PMT calc, don't multiply by 12.
Great lesson! I enjoyed it. The problem I have in cell "F7," you have: SUM(E7,F6). Shouldn't it be: =F6-E7? I got something totally different with: SUM(E7,F6) in my cell. I'm working on making a purchase and want to learn this spreadsheet.
Hello. This has worked great but for some reason now when I drag it to load the new cells column 1 and the interest do not adjust. it sees those columns locked after a year or so. Any ideas?
Hi Rachel...not too sure. Make sure you are paying attention to the "$" within the formula. That will lock in cell references as you click and drag. Feel free to share the sheet you are working on with me at nick@incomedigs.com. I can take a look!
Hi Anna, thanks for watching! What you're describing would work. You would need to multiply current period's interest times the balance from the previous period (1 row above). You cannot calculate interest off of this period's balance as this would create a circular reference. Just make sure to divide your interest rate by 12 to get the monthly equivalent.
Hi There...thanks for watching! Please check out the advanced video...where we specifically allow for additional principal : th-cam.com/video/1JiY_UUjPKY/w-d-xo.html
Hi, first of all, thank you! It was very clear and helpful. Just want to ask what to do if the dates are not always exactly 1 month - for example; initial date is 10 Dec 2018 then the first payment falls on 5 Jan 2019 then the next ones is 1st of the following month then after 5th payment, became 3rd of the month then go back again to the 1st? Hope you can help. I’m trying my hand to work it out but wish you can save me time. The first payment is different amount but the rest is the same monthly payment. Thanks again
Hi There...thanks for watching! This template/ calculator assumes all payments are made as scheduled. If the loan calculates interest on the actual date of payment, this would not work. You would need to enter each payment as they occur with a calculation to determine "daily interest from last payment". This tool is designed to provide a fixed amort schedule. We cannot make a tool for variable payments...as we have no idea when each of those payments would be made. However, you could adjust the formulas/ layout to include "date of actual payment" and calculate interest/ balance off of that.
Hi Delia! Thanks for your question. Here is a new video that shows how to add that principal payment and more: th-cam.com/video/1JiY_UUjPKY/w-d-xo.html
Great video, but I need some help. So for my employees, once they work over 20:00pm I need a format to work with the Clock format to show me the Hours / Minutes they overdo past 20:00pm. Would you know how to do this?
Hi Jordan...this is surely possible...but a bit unrelated to this video. If you want to email directly we can look into a quick custom google sheets project for you: nick@incomedigs.com
Hey Todd...thanks for watching. To make weekly, you would first need to expand the left column so that it has 1,560 periods (52 x 30). You would then multiply/ divide by 52 instead of 12.
Hi Dada, I suggest checking out the advanced calculator: th-cam.com/video/1JiY_UUjPKY/w-d-xo.html We don't' discuss this specifically....but what you could do is insert a few lines, and make the payment 0. You would want the interest to continue calculating. It would get a bit specific but could be done.
So, I have a google sheets that was already created that I use and it has "extra principle" column. We make extra principle payments on our loan payment BUT we also make them with our mid-month check as a stand alone principle payment. I am wondering if there was a way to add in a line for those payments without messing everything up.
Hi There! Yes...you should be able to do that! We actually have a follow up video: th-cam.com/video/1JiY_UUjPKY/w-d-xo.html This is a more advanced Amort schedule with the ability to add an additional principal payment. You could certainly add a 2nd column as well!
Check out my end to end Quickbooks Training. www.incomedigs.com/reab ($50 off w/ code TH-cam50)
Link to Advanced Amort Calculator: www.incomedigs.com/amort-calculator
Thank you so much for this tutorial. It was very easy to create. Awesome job! ☺👏
Thanks so much for watching!
Thank you! I've watched 2 other tutorials to create an Amortization Schedule in excel. Yours was by far the best. Great narration!
Thanks Ryan! Really glad you enjoyed it!
You just saved me as well as made my day!. So simple, well articulated and easy to understand. Thank you!
So glad you found it helpful! Thanks for watching!
@@Incomedigs is there a formula to have the sheet update every month automatically to apply each periods principal to the balance? Rather than just having a detailed schedule with 360 rows?
That was awesome! You saved me so much time and taught me about locking my cells, which I have been trying to learn / figure out for several years! You rock!
Hey Ani...thanks for the kind words! Glad you found it helpful!
You've the gift of teaching, and I learned way more about Google Sheets from this video than the primary topic you were addressing. Thanks for sharing.
Great to hear Will! So happy you found it helpful!
Extremely easy to understand tutorial and so simple to apply in any loan! THANK YOU!
Thanks for watching!
i know its been 5 years but you've saved my life today..Thank you so much!!!
Hey Mary! Thanks so much for watching! I'm so glad you've found this useful!
4 Years later and it keeps helping!! great video, thank you so much!
So glad you found the video helpful! Thanks for watching!
OMG just brilliant, way more helpful than the teachers on line, can't even ind them half the time!
Thank you Pam! Really appreciate the feedback!
Thank you for being so clear and patient in your explanation. Till now, I only know how to use basic formula like sum. This is really helpful!
Hi There! Great to hear! Excel/ Google Sheets can really build on itself. As you start to unlock formulas and functionality, you'll surely gain confidence in exploring more advanced functionality! Stick with it!
Excellent video. I have no interest in real estate whatsoever but still found it so useful. Thanks!
this is the most amazing straight to the point tutorial that save me hours of figuring this out. wow thank you!
Thanks Jan! Really appreciate the comment! Challenge: Reply here with something else I can teach in Google Sheets! Always looking for new topics!
Wow. Thank you so much. Very clear explanation.
Thank you! This helped a lot.👍
This really helped. Thank you so much!
Thanks for watching! Glad you enjoyed!
Huge help! Thank you!
Thanks for watching! Glad you enjoyed!
Great video so much insight
Thanks for watching!
This is great! Trying to relearn everything from scratch. Last time I did this was 12 years ago.... Then customize some other values...
Thanks Rem!!
Thanks so much! So easy to follow and understand!
Thanks for watching Sarah!
Excellent content. Very easy to understand. Thank you!
Thanks Robert!
awesome, thank you so much. great vid
Thanks for watching!
As a business teacher, this is exactly what I was looking for. Thank you!
Quick question though -- and forgive me if you've already answered it (250 comments & I've read like seven) -- in the event the payment is greater than the established minimum, how do we futz around with this to make sure the spreadsheet still works?
Thank you again! This is excellent, and I'll be bringing it into my classroom this month!
Hi Jeff! Thanks for watching....I recommend checking out the Advanced Amort schedule: th-cam.com/video/1JiY_UUjPKY/w-d-xo.html
This includes a field to make extra payments!
Your instructions and your communication are on point.
Thank you!
SO well done! Thank you!
Thanks for watching!
Thank you so much, this made my assignment so much easier to understand.
So glad to hear!
You rock bro - thank you
Thanks for watching!
Thank you so much!! Just getting a construction loan converted to a 5 year payoff and this is perfect. Will also complete one for the new vehicle I just bought. Super excited to know exactly how much to add to the principal each month to pay off quicker.
Hi Connie! So glad you found the calculator helpful! Just want to note that we have a follow up video with some advanced functionality: th-cam.com/video/1JiY_UUjPKY/w-d-xo.html
Let me know if there is anything else you'd like to see! Happy Loan Repayment!
thanks for creating this video, it was very easy and helpful. How do I create a column or field for calculating advance payments? ie. if I would to make a payment to go direct to principal? thanks!
He shared it here: th-cam.com/video/Uz4nzuAwtKc/w-d-xo.html
@@sanelemthembu8447 Thank You!
Great video!!!
How do you add a colum for additional payments?
Hi Bruno...see the link here for the Advanced method which has a column for additional principal: th-cam.com/video/1JiY_UUjPKY/w-d-xo.html
Fantastic tutorial, thank you. I modified this slightly for a business loan with a 2 year term that starts with lower payments and works to higher payments by the end of the term, so as to allow a little breathing room to get business moving at the onset. Thanks so much!
Matt Singley Awesome! Great idea...glad you found it helpful!
Thank you so much!! This actual works perfectly for my amortization for bonds excel spreadsheet. You saved me many hours trying to figure this out
Great! Glad we could help out!
Thank you so much for doing this.
Thanks for watching Lindy!
Hello! Thank you! That is exactly what I was looking for and I am all set up. In June we are going to pay extra money towards the principal balance, but we want to keep our monthly payments the same. How would I adjust the principal balance but still record the payment?
Hi Rachel! You would add a column for "additional principal"...this would be an input column for each month formatted as a number. You can go ahead and prefill all of these to "0". You would then adjust your "Loan Balance" calculation to subtract out this cell. So your "Loan Balance" would reduce by the principal that is part of your fixed payment as well as the additional principal payment. Let me know if you have any other questions!
great video thanks so much. What if the interest rate changes/fluctuates throughout the life of the loan?
very helpful!
Awesome! So glad you found this helpful. Let me know if there is anything else you'd like to see!
Great and simple!
This is excellent! I can add a line to calculate extra principal payment and calculate the reduction in interest, right?
Hi Natali....yes! In fact, we demonstrate exactly how to do this with our "Advanced Amort Schedule" video: th-cam.com/video/1JiY_UUjPKY/w-d-xo.html
You should make a video explaining how to add in an overpayment for each month. Say your monthly payment is $800 but you pay $850. (Helps to find out how long you'll be paying PMI)
Hey Drew! We've done exactly that! Check out the advanced calculator here: th-cam.com/video/1JiY_UUjPKY/w-d-xo.html
How do you make an excell sheet with different loan payments? Can that be done? you videos are very easy to understand.
thanks
Hi Linda...thanks for watching! We have a follow up video with some advanced features...including additional principal payments. You can check it out here: th-cam.com/video/1JiY_UUjPKY/w-d-xo.html
You are a genius thank you this just helped me make my airbnb long term rental underwriting sheet!!! You rock!
Thanks Sean! Really appreciate the feedback!
Hi, Can you through an example show how to adjust the schedule for interest rate varying during the loan period.
Thanks!
Hi Kanish, Thanks for watching!
The calculator will always assume a consistent interest rate for any given period. If you wanted to have the interest rate change over time...we would need to devise a very different tool...one that allows the user to add an interest rate to every period.
This can be done...but would be a unique case. Let me know if you would like some one on one help creating this. I can be reached at nick@incomedigs.com. Thanks!
Great video, but how do you record a defaulted payment (no payment) of 3 months worth?
Very good video
Thanks for watching!
This was a great tutorial! Very simple to follow. Thank you for sharing this.
It would be great to see what an extra $100.00 or extra payment does too. Thanks for making the video!!
Hi Sara! Check out the advanced video: th-cam.com/video/1JiY_UUjPKY/w-d-xo.html
In this video we have a column for additional principal!
For the period collum, I put an equation starting from 1. =SEQUENCE(E2*12). The sequence starts off with 1 so I put 0 as 0 and skipped to 1. E2 is the term (years) box. The only thing is that you still have to drag the rest down to said number but now you know where and when to stop.
Excellent! Thank You!
The best ever explanation. Thank you 🙏
Thank you!
How do you change the formulas if you plan to pay more than the minimum monthly payment?
Wow! great tutorial! thank you very much! Question; how should I imput one or two extra payment against the principal at the end of the year?
Hi Nelson...thanks for watching! We recorded a follow up showing an "advanced" calculator...this includes making extra payments: th-cam.com/video/1JiY_UUjPKY/w-d-xo.html
Great video and explanations! Can you please share how you would handle a loan that's deferred. Would you just add the deferred months to the term? Thank you!
Hi Rich...a Deferred loan is pretty much "on hold". These loans sometimes still accrue interest...and sometimes they don't. The specifics of the loan would indicate how to handle it.
In the case where interest is not accruing...you would just delay the "start date" of the loan". If interest is accruing...you would adjust the formulas for the months of deferment such that the total owed is only the interest: Interest Rate/ 12 x principal.
@@Incomedigs Thanks a lot for the help! Your answer was just what I was looking for! Great channel!
Superb videos love your explanation ❤️
This was great, thank you. What if I wanted to make additional payments and how would I include them in this scheadule
Hey Bryon...thanks for watching! Check out the advanced amort schedule...we address additional payments: th-cam.com/video/1JiY_UUjPKY/w-d-xo.html
Wow. You made it so easy for me. Thank you soooooo much!!!
Bad ass and COMPLETELY helpful!
Thanks David! Appreciate you watching! More to come...
Thank you, this is going to facilitate my seller-financed property transfers and save me lots of fees.
So simple and smart! Thank you
Thanks for watching!
THANK YOU!!!
Great watch and a really big help to me personally:) have you got a Google sheet like this where I can add different overpayment each month. Thanks again
Yes! You can download here: www.incomedigs.com/amort-calculator
@Incomedigs thanks so much honestly you don't realise how much it will help me
@@joeyd1682 Great to hear! You're welcome!
Super helpful tutorial, appreciate what you do! I have a question. I did this sheet for my car loan. If I wanted to pay more each month on top of the monthly payment, would I enter the amount I paid in the payment box? Would this mess anything up?
Hey Chaston...thanks for watching! Check out the advanced calculator. This includes the ability to add extra payments: th-cam.com/video/1JiY_UUjPKY/w-d-xo.html
Thank you so much for the tutorial. How do I make adjustment when the interest methodology is 30/360.
Term is 10 yrs with a rate of 2.86%
Hi Maria! Thanks for watching! 30/60 would be a slight adjustment to your PMT calculation
=PMT(C3/360*30,E2*12,C2). In the above example, the monthly rate would be identical.
@@Incomedigs Hooray! That did it! Many thanks.
This is very helpful. How does one factor in if you have a Balloon/Residual Payment that you must still pay? I tried to add a Balloon Payment separately with the Principal Amount but it doesn't add up.
Hi there!...thanks for watching. I'll first direct you to the advanced amort schedule: th-cam.com/video/1JiY_UUjPKY/w-d-xo.html
To add a balloon payment, add a field to the top: "Balloon After". You can then setup, within your table, an if statement. This would indicate to the sheet: "If this period is the balloon payment date, pay the entire balance plus additional interest"
I could set this up for you...please email: nick@incomedigs.com
Wow so simple. Can you assist me how can we compute the amortization for the last period without using excel (manual). Lets say amortization schedule for the last 6 periods.
Hi Japhet...thanks for watching!
Wikipedia has a decent breakdown for manually calculating interest/ principal due for a given period: en.wikipedia.org/wiki/Amortization_calculator
This is, in effect, everything that excel/ google sheets is doing.
Is there a way to calculate if they are not paying on the same day each month and the amount of the payment varies each month? What is the best way to calculate based on these variables?
Hi Kendra...thanks for watching! Please check out our video on the "advanced" amort calculator....you can add additional monthly payments: th-cam.com/video/1JiY_UUjPKY/w-d-xo.html
how can you can compute with annual escaltion on interest rate?
Great video, super helpful thank you!
Hey Kevin! Glad you found the video helpful! Let me know if there are any other excel/ google sheets tricks I can demo for you and your business!
This is very helpful. thank you for making this video, I know there are some additional unknown variables but is there a way to calculate the positive equity using the formula?
Thanks Eric! Thanks for watching. You certainly could calculate equity. You would need a column or a fixed cell for "Market Value". You would then have a column to calculate Market Value - Loan Balance for each given period
can you add a way of making extra payments or if you pay more then the actual payment ?
Hi Catherine, we cover that in the "Advanced" tutorial...which can be found here: th-cam.com/video/1JiY_UUjPKY/w-d-xo.html
I want to keep monthly payment flexible (prepayment purpose). Please suggest the new calculations.
Hi...thanks for watching! You would simply leave the monthly payment calculation out...insert the value of each payment. The rest of the calculation should accurately calculate the interest/ principal for each payment.
A good idea would be to keep the calculation of the min monthly payment as the baseline...and then go in and override the monthly payment with whatever you ended up (or plan to) actually pay moving forward. We also have this calculator which allows you to play with additional payments to see how it impacts your prepayment: th-cam.com/video/_M7vt__a9vQ/w-d-xo.html
Well done. Very helpful.
Great video!!! Would you please show us how to convert the Annual Interest Rate to Annual Percentage Rate(APR)?
((i+1)^(1/n))-1 where I is the interest rate and n is 12 for the number of months in this case
What happens when you make a additional Principal Payment? It will affect your total? How would you make the change(s).
Hi Wallace...please see the follow up we recorded on an "advanced" amortization table: th-cam.com/video/1JiY_UUjPKY/w-d-xo.html
THANK YOU SO MUCH 👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍
YOU ARE WELCOME! thanks for watching!
Can you make a video on how to set up Bi-weekly amort in google sheets, please?
Hi Maria! Good suggestion! In simple terms, you would just be adjusting your "Periods". If each period is 2 weeks...instead of 1 month, you'll have 26 periods in a year. Therefore, instead of dividing/ multiplying by 12....you would divide/ multiply by 26.
I will add the video to the queue.
Do you guys have a video for amortization based on a fixed rate mortgage
Question on this video, if we pay more that what is needed, how does that calculate in? Do we just change the payment amount in the Monthly Payment column and it will adjust?
This is the first amortization I've done and this tutorial was SO helpful! Thank you for this!
Hi Casey...thanks for watching.
You would need to add an additional column for "Additional Principal Payment". Then you would simply take the calculation that is in the column "Principal Payment" (which calculates the amt of principal from the regular monthly payment) and add the new column for "additional principal payment"....does that make sense?
@@Incomedigs I'll give it a try and see if it works. If not, I'll let ya know. Lol thanks again!
Hi, first of all, thank you! It was very clear and helpful. Just want to ask what to do if the dates are not always exactly 1 month - for example; initial date is 10 Dec 2018 then the first payment falls on 5 Jan 2019 then the next ones is 1st of the following month then after 5th payment, became 3rd of the month then go back again to the 1st? Hope you can help. I’m trying my hand to work it out but wish you can save me time. Thanks again
Amazing video. Lets say I wanted to pay it off faster, by paying an additional variable amount into the loan account, let say $100 here and $200 there, just random amounts at various times, how would I add that to the sheet to account for it, to update me about when the home loan would be paid off?
th-cam.com/video/Uz4nzuAwtKc/w-d-xo.html nevermind i see others have asked before and you answered with a second video.
@@BernardMyburgh beat me to it! Thanks for watching. Please let me know if you have any additional questions on this or other videos!
@@Incomedigs I did actually! This video (th-cam.com/video/_M7vt__a9vQ/w-d-xo.html) where you have an excel sheet loan repayment calculator, is there any way this can be made into a google sheet? I just don't want to buy Microsoft office just for this one reason.
Great video thank you. BIG QUESTION. If I miss a monthly payment I presume I just stick a zero in the 2 lines and it will still work by copying the formula from the month before?
Secondly, when the interest changes, I want to keep the historical but change the interest going forward. Do I change the historical value from formula AND all else stays the same? What if I tack on 4 months so the period is not 15 years but 15 years and 4 months? So much thanks if you can let me know about this.
Hey Mark, Thanks for watching!
The schedule is meant to be a "Plan"...of course, reality can make some changes. I would suggest "force feeding" the tool as needed. For example, if you miss a few payments, you could copy the past payments you made and "Past Values" in their exact same spot. This would work to "freeze" the past payments and they would not be impacted by your updated calculations.
If the interest changes, you may benefit from bringing cell C3 into a separate row so that it can be calculated with each monthly payment. You can click and drag an interest payment down and change it as necessary.
If you wanted the ability to use months instead of years, simply put the months into cell E2 (so 15 and 4 would be 184). Then, on your PMT calc, don't multiply by 12.
Great lesson! I enjoyed it. The problem I have in cell "F7," you have: SUM(E7,F6). Shouldn't it be: =F6-E7? I got something totally different with: SUM(E7,F6) in my cell.
I'm working on making a purchase and want to learn this spreadsheet.
Hi There! probably an issue w/ negatives! Is the value of E7 negative? It should be...if so, the sum function would work.
Hello. This has worked great but for some reason now when I drag it to load the new cells column 1 and the interest do not adjust. it sees those columns locked after a year or so. Any ideas?
Hi Rachel...not too sure. Make sure you are paying attention to the "$" within the formula. That will lock in cell references as you click and drag. Feel free to share the sheet you are working on with me at nick@incomedigs.com. I can take a look!
Can this be done with a variable interest rate?
Hi Carolyn....not really. You could indicate a rate "by row". But you would need to go in to each row and update the rate.
Great tutorial, thank you! For interest payment, instead of using the IPMT function, you could just multiply the rate by the balance, no?
Hi Anna, thanks for watching!
What you're describing would work. You would need to multiply current period's interest times the balance from the previous period (1 row above). You cannot calculate interest off of this period's balance as this would create a circular reference. Just make sure to divide your interest rate by 12 to get the monthly equivalent.
So useful thank you!
Thanks for watching!
Fantastic tool! Thank you so much!!
Hi Melisa! Thanks for watching! Glad you found the video helpful! Let me know if there is anything else you'd like to see!
How do you add additional principal payments
Hi! Thanks for watching! You can reference this video for a tutorial on how to add additional payments: th-cam.com/video/1JiY_UUjPKY/w-d-xo.html
For pmt formula you must divide rate by 12 and multiply period years by 12
Hi there...Yes! As demonstrated in the formula.
@@Incomedigs yeahit was a mental note for myself but thank you
great videos
thank you!
how would you add, a column for additional principal??? do you have a tutorial for htat???
Hi Marisol...this video addresses that exact question: th-cam.com/video/1JiY_UUjPKY/w-d-xo.html
Wonderful wonderful wonderful video! I'm surprised I was able to follow along, haha
how would this look for ballon payments / ARMs?
How do you modify this chart if you do lump sum payments on the principal?
I just found the part 2! Thanks this is so helpful!
Unfortunately, if you put in an additional principal payment, it will not maintain calculations past that date.
Hi There...thanks for watching! Please check out the advanced video...where we specifically allow for additional principal : th-cam.com/video/1JiY_UUjPKY/w-d-xo.html
Hi, first of all, thank you! It was very clear and helpful. Just want to ask what to do if the dates are not always exactly 1 month - for example; initial date is 10 Dec 2018 then the first payment falls on 5 Jan 2019 then the next ones is 1st of the following month then after 5th payment, became 3rd of the month then go back again to the 1st? Hope you can help. I’m trying my hand to work it out but wish you can save me time. The first payment is different amount but the rest is the same monthly payment. Thanks again
Hi There...thanks for watching!
This template/ calculator assumes all payments are made as scheduled. If the loan calculates interest on the actual date of payment, this would not work. You would need to enter each payment as they occur with a calculation to determine "daily interest from last payment".
This tool is designed to provide a fixed amort schedule. We cannot make a tool for variable payments...as we have no idea when each of those payments would be made. However, you could adjust the formulas/ layout to include "date of actual payment" and calculate interest/ balance off of that.
Hello thank you for help... how or where can you add a column for "additional principle payments "?
Hi Delia! Thanks for your question. Here is a new video that shows how to add that principal payment and more: th-cam.com/video/1JiY_UUjPKY/w-d-xo.html
Exactly what I was going to ask..lol.
Great video.. and will be going to.look at video u suggested.
Thanks
Great video, but I need some help. So for my employees, once they work over 20:00pm I need a format to work with the Clock format to show me the Hours / Minutes they overdo past 20:00pm. Would you know how to do this?
Hi Jordan...this is surely possible...but a bit unrelated to this video. If you want to email directly we can look into a quick custom google sheets project for you: nick@incomedigs.com
How would you change this to a weekly payment?
Hey Todd...thanks for watching. To make weekly, you would first need to expand the left column so that it has 1,560 periods (52 x 30). You would then multiply/ divide by 52 instead of 12.
That was extremely clear and very helpful. Thank you.
What of if I want to add moratorium in the amortization schedule?
Hi Dada, I suggest checking out the advanced calculator: th-cam.com/video/1JiY_UUjPKY/w-d-xo.html
We don't' discuss this specifically....but what you could do is insert a few lines, and make the payment 0. You would want the interest to continue calculating. It would get a bit specific but could be done.
So, I have a google sheets that was already created that I use and it has "extra principle" column. We make extra principle payments on our loan payment BUT we also make them with our mid-month check as a stand alone principle payment. I am wondering if there was a way to add in a line for those payments without messing everything up.
Hi There! Yes...you should be able to do that! We actually have a follow up video: th-cam.com/video/1JiY_UUjPKY/w-d-xo.html
This is a more advanced Amort schedule with the ability to add an additional principal payment. You could certainly add a 2nd column as well!