If you are doing something similar in Excel, it is almost exactly the same. You will have to input the interest rate in decimal form though. Google Sheets PMT function is happy with a whole number percent, but Excel expects the decimal form, i.e. 5% = 0.05
I did something wrong. haha When I throw an extra $4000 at my loan, it takes away from my total principal payments. It should still equal my loan amount. Everything else seems perfect until I load in an extra principal only payment.
Hi Brent, thank you very much. I find your tutorial so easy to follow. Can you make an amortisation that shows when fixed term expires and interest rate changes? Like first year is 6% then after that fix for two years for 5% and so on...
Heya! This would need to be a bespoke calculation requiring more complex formulas. Making a video would be hard as it's so unique to every loan.The basic formula of =(rate/365)*(running loan balance)*(days between instalments) should be used for interest at each instalment, catering to the interest rate effective for each instalment. You can then use Excel Goal Seek function to find the instalment across the life of the loan, solving for zero in the final instalment. Hopefully that gives a useful starting point. Cheers!
Hi, thank you for the share. I have done what you did, but something wrong with the frequency I want to use by weekly so 52, for a result of my balance is not right anymore? Thank you for your help
Hey! Are there enough lines in the schedule? For weekly, you'd need years*52 rows (e.g. 30yrs*52wks=1560 rows). Try dragging the formula down as many rows as are needed, until the balance goes to $0. Hope this helps!
Okay, when I calculate 360 payments my column C zeros out. However, the moment I apply an extra payment column C goes negative. Which formula did I mess up
Hiya! The extra payment would be directly coming off the loan principal. This example assumes that your lender allows this, and the interest is based off the remaining balance after the lump-sum payment (principal) is made. Hope this helps!
Thank you for making this video! I didn't know I needed this until now. You've inspired me to be more smart with loan repayments than to just "set and forget" with the bank default settings, so thank you!
i'm confused. What if you make $100 payment every month and it shaves off years of the loan. How do I prevent payment from applying in the terms where I no longer have a balance. when I double clikc the bottom right it drags all the way down.
SOOOO helpful! this made the concept of loans/mortgages so much easier for me to understand - thank you so much for sharing!!!! Only thing I edited was to add the "Extra Payments" column to the "Total Principal Payments" sum - hopefully that doesn't mess anything up!
Thanks for the kind words! This was my first video ever so it had a couple gaps, but I’m glad it was useful overall :) hope you have a wonderful end to 2023!
Hey! It depends whether the insurance is a lump sum charge (e.g. $2000 charge), a periodic charge (e.g. $100 a week), or a percentage charge added to your interest rate (e.g. 7.05% + 0.3%, common with low equity lending). The first would be generally be added to the loan balance, the second would simply be added to the instalment amount, and the third one has the insurance percent added to the loan percent. Hope this helps!
Heya! Unfortunately building a variable rate isn't possible with this approach as it messes up the amortisation. Every "reprice" would result in the amortisation profile changing for the remaining instalments.
If I add an extra payment, the loan balance never really reaches zero, it just goes from a positive number to a negative one eventually, how do I fix this?
Thank you for creating this and I am in the middle of creating my own, but I need to add in the monthly fees that are attached to my loan (accounting keeping fees) how and where would I do this to represent accurately? Thanks
I thought this was very helpful but I found when I tried to enter my initial payment calculation, the very last entry in the formula was a prompt for beginning or end. I did not see or understand what you entered. When I did your same steps it kept telling me I had an error with not providing enough information. Would like to know what I missed.
Heya! Beginning or end refers to when interest is paid. Sometimes this will be in advance of a period, or after. With a loan it will generally be after so we use a value of 0. Hope this helps! support.microsoft.com/en-us/office/pmt-function-0214da64-9a63-4996-bc20-214433fa6441
Thanks, will give it a try. I thought that was what it was for just not sure what enter for the variable. Is it binary? I tried putting in a 1 and nothing happened.
We are having our son pay us back on a cabin we had finished out and he is wanting to pay it back sooner. Would this be a good table to set up? Say if he paid $200 more on one particular month and so on and so forth? It looks to me it would keep track of it and adjust toward the end of the loan.
@@BrentColeman my daughter helped set it up and followed your video. She had a class 2 years ago on Google sheets and not it is serving our purpose perfectly! Thank you for posting.
One question. If we do an extra payment, the balance will be reduced and why the next monthly payment doesn't reflect that, should be lower, not exactly the same, what do you think? Thx
Thanks for the question! Most financial institutions would offer two options: a) keep the same instalment, meaning the loan term would reduce, or b) reduce the instalment and keep the default term. The bank makes more money off you with option b. When we make a lump sum payment, subsequent instalments will have a relatively larger principal component than interest, as the loan balance has reduced, meaning it will be paid off quicker. Hope this helps!
Thanks for this, I was able to build it just how you showed. I have a question though. Why does the Total Priciple Payments amount drop when adding extra principle payments. Is there a way to have that remain the same, or would I want it to drop for some reason?
Nevermind, I figured it out. I just used this formula for that cell (=SUM(E:E,G:G)). This way the extra payment was being subtracted from the principle but then added in the extra payment column so it remains the same then.
Could you help me understand what a front loaded interest loan is? A typical loan as I've shown here will multiply a daily interest rate by the balance, which of course would be highest when the loan is taken out. The only kind of front loaded loan I can imagine is where you finance an upfront low equity charge, which will result in you paying interest on interest. For this case, you would capitalise it as part of the loan amount
![[UA] Eternal Fire проти NAVI | EPL Season 20 Malta](http://i.ytimg.com/vi/chvlsDygrZM/mqdefault.jpg)
Thank you for making the first video I watched that didn't try to make this harder than it needs to be!!!!
Glad it was useful! 🙏
If you are doing something similar in Excel, it is almost exactly the same. You will have to input the interest rate in decimal form though. Google Sheets PMT function is happy with a whole number percent, but Excel expects the decimal form, i.e. 5% = 0.05
Thanks for sharing!
Thank you for this schedule Brent. Very Helpful. :)
Thanks! Glad it was helpful!
Thank you! Extremely useful! :)
Glad it was helpful!
I did something wrong. haha When I throw an extra $4000 at my loan, it takes away from my total principal payments. It should still equal my loan amount. Everything else seems perfect until I load in an extra principal only payment.
How would you calculate the schedule if the first payment was $99 and the remaining payments were amortized?
Hi Brent, thank you very much. I find your tutorial so easy to follow. Can you make an amortisation that shows when fixed term expires and interest rate changes? Like first year is 6% then after that fix for two years for 5% and so on...
Heya! This would need to be a bespoke calculation requiring more complex formulas. Making a video would be hard as it's so unique to every loan.The basic formula of =(rate/365)*(running loan balance)*(days between instalments) should be used for interest at each instalment, catering to the interest rate effective for each instalment. You can then use Excel Goal Seek function to find the instalment across the life of the loan, solving for zero in the final instalment. Hopefully that gives a useful starting point. Cheers!
This is awesome, thank you so much!
I'm glad you enjoyed it! Thank you for watching!
@@BrentColeman it was very helpful man, if it wasn’t for this video I would have been fired from my job lol
@@monkeyman321 oh wow! If there's anything else I can help with let me know!!
Hi, thank you for the share. I have done what you did, but something wrong with the frequency I want to use by weekly so 52, for a result of my balance is not right anymore?
Thank you for your help
Hey! Are there enough lines in the schedule? For weekly, you'd need years*52 rows (e.g. 30yrs*52wks=1560 rows). Try dragging the formula down as many rows as are needed, until the balance goes to $0. Hope this helps!
Okay, when I calculate 360 payments my column C zeros out. However, the moment I apply an extra payment column C goes negative. Which formula did I mess up
I also want to know this. Also, I want to make sure everything below the 0 loan balance is hidden, anyone know?
Is this another pre payment or directly off the principle?
Hiya! The extra payment would be directly coming off the loan principal. This example assumes that your lender allows this, and the interest is based off the remaining balance after the lump-sum payment (principal) is made. Hope this helps!
what if the payment is not monthly but every 6 months?
Heya! You can simply change the payment frequency to 2 (2 payments a year). Hope this helps!
Thank you for making this video! I didn't know I needed this until now. You've inspired me to be more smart with loan repayments than to just "set and forget" with the bank default settings, so thank you!
Glad it was useful!!
i'm confused. What if you make $100 payment every month and it shaves off years of the loan. How do I prevent payment from applying in the terms where I no longer have a balance. when I double clikc the bottom right it drags all the way down.
Excellent video! I'd love it if you had a download available with all the formulas already created. I'll see how I go recreating it. 😁
SOOOO helpful! this made the concept of loans/mortgages so much easier for me to understand - thank you so much for sharing!!!!
Only thing I edited was to add the "Extra Payments" column to the "Total Principal Payments" sum - hopefully that doesn't mess anything up!
Thanks for the kind words! This was my first video ever so it had a couple gaps, but I’m glad it was useful overall :) hope you have a wonderful end to 2023!
How do you make a one-time purchase of this program / software so you don't lose amortization spreadsheet due to internet glitch and subscriptions 🤔
It’s google sheets, all free to use through your google account
Thank you very much
Glad it was useful!
how do you add if you had to pay a monthly insurance too with the mortgage? thank you
Hey! It depends whether the insurance is a lump sum charge (e.g. $2000 charge), a periodic charge (e.g. $100 a week), or a percentage charge added to your interest rate (e.g. 7.05% + 0.3%, common with low equity lending). The first would be generally be added to the loan balance, the second would simply be added to the instalment amount, and the third one has the insurance percent added to the loan percent. Hope this helps!
Thank you this is amazing. How would I add variable interest rate into this spreadsheet?
Heya! Unfortunately building a variable rate isn't possible with this approach as it messes up the amortisation. Every "reprice" would result in the amortisation profile changing for the remaining instalments.
If I add an extra payment, the loan balance never really reaches zero, it just goes from a positive number to a negative one eventually, how do I fix this?
Thank you for breaking this down!! I was able to create a schedule for our ARM with this as a basis.
Glad it was useful!
Thank you for creating this and I am in the middle of creating my own, but I need to add in the monthly fees that are attached to my loan (accounting keeping fees) how and where would I do this to represent accurately? Thanks
I thought this was very helpful but I found when I tried to enter my initial payment calculation, the very last entry in the formula was a prompt for beginning or end. I did not see or understand what you entered. When I did your same steps it kept telling me I had an error with not providing enough information. Would like to know what I missed.
Heya! Beginning or end refers to when interest is paid. Sometimes this will be in advance of a period, or after. With a loan it will generally be after so we use a value of 0. Hope this helps! support.microsoft.com/en-us/office/pmt-function-0214da64-9a63-4996-bc20-214433fa6441
Thanks, will give it a try. I thought that was what it was for just not sure what enter for the variable. Is it binary? I tried putting in a 1 and nothing happened.
We are having our son pay us back on a cabin we had finished out and he is wanting to pay it back sooner. Would this be a good table to set up? Say if he paid $200 more on one particular month and so on and so forth? It looks to me it would keep track of it and adjust toward the end of the loan.
Hi Mandy! Yes this should work well!
@@BrentColeman my daughter helped set it up and followed your video. She had a class 2 years ago on Google sheets and not it is serving our purpose perfectly! Thank you for posting.
is there a link to download the spreadsheet?
Unfortunately not - this was my first video and I hadn't worked out how to share a spreadsheet at that point!
Thank you for this amazing video! It is of great help :D
Glad it was helpful!
Very informative, thank you so much. Any chance you can share that sheet at all?
Hi Simon, thanks for watching! I have been hunting around for it but have no idea where it has gone! Sorry about that
This is awesome spreadsheet please
Yay!!! Thank you so much. Was a breeze to do with your help. 🥰
Glad it helped!
Your a Legend very informative video keep up the good work
Thanks glad you enjoyed it!
Awesome!! Exactly what I needed to customize my table! Thank you!!
Glad it was useful! :)
Got lost the moment you said multiply the two didn't know how to do that :0(
Wow! So impressive
One question. If we do an extra payment, the balance will be reduced and why the next monthly payment doesn't reflect that, should be lower, not exactly the same, what do you think? Thx
Thanks for the question! Most financial institutions would offer two options: a) keep the same instalment, meaning the loan term would reduce, or b) reduce the instalment and keep the default term. The bank makes more money off you with option b. When we make a lump sum payment, subsequent instalments will have a relatively larger principal component than interest, as the loan balance has reduced, meaning it will be paid off quicker. Hope this helps!
Thanks for this, I was able to build it just how you showed. I have a question though. Why does the Total Priciple Payments amount drop when adding extra principle payments. Is there a way to have that remain the same, or would I want it to drop for some reason?
Nevermind, I figured it out. I just used this formula for that cell (=SUM(E:E,G:G)). This way the extra payment was being subtracted from the principle but then added in the extra payment column so it remains the same then.
I did the same at J2 as well to add in the extra payments into the total repayments if any extra payments were made.
DUH, you forgot to add the extra payments to the total payments sum
seems wild wood does not comprehend his error
Be handy if you could do one for a front loaded interest loan
Could you help me understand what a front loaded interest loan is? A typical loan as I've shown here will multiply a daily interest rate by the balance, which of course would be highest when the loan is taken out.
The only kind of front loaded loan I can imagine is where you finance an upfront low equity charge, which will result in you paying interest on interest. For this case, you would capitalise it as part of the loan amount
Screen info too small, can not read. the presenter talks to fast
Thanks - will improve going forwards