Brian, I just followed this tutorial step by step, even adding in a row for my taxes and insurance. This was the BEST tutorial ever! It came out so nice and super accurate! It was just what I was looking for, especially the extra payments. Thank you for this tutorial! 🤩
This vid was fantastic. I've been looking for something like this for a while to help me figure out whether it's better to over pay on mortgage vs leave money in savings vs pay off finance. One small error I spotted though which was driving me crazy for a while, you'll only notice it when you start playing with the excess payments. The total principal should include the excess payment. i.e. Total Principal = Previous period principal + Current Period Principal + Current Period Excess Payment i.e. =J9+E10+G10 Thanks again!
I believe the Payment formula is also wrong. It uses the interest calculation in the previous month's field instead of the same month's Interest field. This only affects the last line in the table. =MIN(C10+F10,$D$9)
Awesome, was trying to find out how mortgage interest vs principal payments are calculated. Building a spreadsheet along with your video really helps. Very well explained and presented.
Thank you so much for this video. I've been trying to calculate paying off my mortgage in the next two years based on additional principal. This worked perfectly!
Thank you ! for the first time i did it with you step by step and it was so easy. I have a interest only loan and i like to pay extra a month but i like to see how it works. most mortgage calculator is based on (P&I). I have not come a cross any video for interest only program. Thank you again
I guess the only formula I would change is that the total principal column should include the extra payment column, right? Since those payments are increasing your total principal?
Do you have a version of this that allows for variable interest rates, but the minimum monthly payment remains the from that change. ie original payment is $170 per week at 10.%pa, then 6 months later the rate changes to 11.25% and the new minimum payment is $173 without the minimum payment changing as the principal goes down. Thanks
you just have to manually type in the new rate(which is 11.25%/12) in the formula of the cell of the 7th month payment and make sure you lock the cell so that formula copies for the future cells. i hope you understood because english is not my native language.
Watched a lot of these to build my own and found your explanation to be perfect. My only issue is working on the dates for bi-weekly instead of monthly. I think I got it right, substituted the 12 for 26, but Im still slightly off as my amortization is 23.8yrs?? go figure.
Escrow doesn’t factor into the amortization schedule. It’s a separate account that pays taxes, insurance, and any PMI. It doesn’t impact your payment schedule. Also it changes every year due to property tax increases, insurance premium increases, etc.
You're getting that small error at the end of the amortization because you used a round function. You want to just limit it to displaying 2 places after the decimal, not round it off. Eliminate the round function and set it to display 2 decimal places (default with accounting formatting), and you'll eliminate that error.
Nice job! I once wrote a windows program that would display a chart of a given loan. I could alter loan parameters based on mouse movement. I noticed that a loan has a shape and from the shape, I could tell if it was a good loan or terrible loan.
Not sure if anyone has already answered your question. In case they haven’t, the easiest thing to do would be to add up all the payments made over the course of the month and make sure that “Payment” + “Excess Payment” equals that total. For instance, using this example, if you wanted to make bi-weekly payments of $2,000 , you would leave the monthly payment as $3,160.34 and mark the excess payment as $839.66 since you would be paying a total of $4000 per month. Interest is only accrued at the start/end of each month, not between bi-weekly payments. Hope that helps!
If I manually copy the value of "Int Saved" and then subtract that from the previous month's "Int Saved" I can see how much the "Excess Payment" had in regards to an amortized savings affect however for the life of me I can't get it to do these calculations automatically without having to do these extra steps. Any help is greatly appreciated if anybody can please figure it out. Thanks
I was totally following along as best I could in IOS but when it got to the drag down of the principal, interest, and ending balance after entering in all the formulas, my the numbers were totally off. Can't figure out what keeps going wrong as I re started twice, in case I missed a step or entered something wrong the first couple times. I would love to see an IOS Numbers version!
Hi. Does this work for any tracker? I downloaded your form, entered the #s for my car and the payment on the tracker and what my real payment is show to be off by a few dollars 😢
Mine was perfect until I started to scroll down and something is off with the principal and interest that’s making it. Go out of whack and turn it into a triple amount.
FYI Can you make a video about (what is your real interest rate) . As you may know if you get a 6.5% rate most your payment goes to interest for the first 1/3 of your loan term but the real rate of 6.5% on the first payment is not really 6.5% it's more like 68% and the accrual rate on your mortgage rate of 6.5% is the last payment you pay to the bank. This will help Manny home owners to understand what rate they are paying each month on their mortgage. I know it a lot to ask but there is no such video. Thank you again
Brian, I just followed this tutorial step by step, even adding in a row for my taxes and insurance. This was the BEST tutorial ever! It came out so nice and super accurate! It was just what I was looking for, especially the extra payments. Thank you for this tutorial! 🤩
This vid was fantastic. I've been looking for something like this for a while to help me figure out whether it's better to over pay on mortgage vs leave money in savings vs pay off finance.
One small error I spotted though which was driving me crazy for a while, you'll only notice it when you start playing with the excess payments. The total principal should include the excess payment. i.e. Total Principal = Previous period principal + Current Period Principal + Current Period Excess Payment
i.e. =J9+E10+G10
Thanks again!
I believe the Payment formula is also wrong. It uses the interest calculation in the previous month's field instead of the same month's Interest field. This only affects the last line in the table.
=MIN(C10+F10,$D$9)
Thank you for this!! You thought of everything most tend to leave out when creating an amortization sheet🎉🎉
Just discovered your videos🎉 they are amazing and you simplify Excel perfectly👌🏾 thank you
Awesome, was trying to find out how mortgage interest vs principal payments are calculated. Building a spreadsheet along with your video really helps. Very well explained and presented.
Thank you so much for this. Exactly what i was looking for. Thanks a bunch
Thank you so much for this video.
I've been trying to calculate paying off my mortgage in the next two years based on additional principal.
This worked perfectly!
This was amazing, thank you! It's so interesting to play around with these numbers.
Great video! I was hoping for a spreadsheet to also include Escrow payments.
Someone suggest how to calculate additional columns like escrow, taxes, insurance etc
Wowza Brian!! So informative and helpful. And thanks a bunch for the spreadsheet link. You're awesome!!
GREAT VIDEO. QUICK, SIMPLE, EFFECTIVE
Wonderful job!! I appreciate your time and effort
Thank you!! Great job!
I appreciate that you added the link to google.
Awesome job - thanks so much!
Awesome video mate! It helped me a bunch.
Great video. I was able to create my own mortgage calculator. Thank you.
This was very helpful I made it along with your video and all works great! Thanks
Thank you so much for your hard work!
Thank you so much for this, helped me a million.
omg, i copied the entire of this a didn't realize i could download it LOOOOOOOOOL
😅 I did the same, I guess it's good excel practice.
Bloody marvelous 😁
Thank you! Your are excellent!
thank you so much for sharing the sheet link
So helpful! Thank you
Thank you ! for the first time i did it with you step by step and it was so easy. I have a interest only loan and i like to pay extra a month but i like to see how it works. most mortgage calculator is based on (P&I). I have not come a cross any video for interest only program. Thank you again
Great video
Fantastic Video
This is brilliant!
I guess the only formula I would change is that the total principal column should include the extra payment column, right? Since those payments are increasing your total principal?
Thank you so much- it is very helpful!
Glad it was helpful!
Thank you for this!
Hi Brain
Will you be able to adding the HELOC Calculation method for comparison ?
Do you have a version of this that allows for variable interest rates, but the minimum monthly payment remains the from that change. ie original payment is $170 per week at 10.%pa, then 6 months later the rate changes to 11.25% and the new minimum payment is $173 without the minimum payment changing as the principal goes down. Thanks
you just have to manually type in the new rate(which is 11.25%/12) in the formula of the cell of the 7th month payment and make sure you lock the cell so that formula copies for the future cells. i hope you understood because english is not my native language.
Watched a lot of these to build my own and found your explanation to be perfect. My only issue is working on the dates for bi-weekly instead of monthly. I think I got it right, substituted the 12 for 26, but Im still slightly off as my amortization is 23.8yrs?? go figure.
Thank you for that!
Hi, How can I convert the monthly payments into bi-weekly payments? I tried so many rules. Sometimes, it goes 1 day ahead and 1 day back. Thanks
Could you explain how to add escrow into this amortization schedule?
Escrow doesn’t factor into the amortization schedule. It’s a separate account that pays taxes, insurance, and any PMI. It doesn’t impact your payment schedule. Also it changes every year due to property tax increases, insurance premium increases, etc.
If you have already paid for a few years on a 15 month loan, do you put in what you owe now or what the loan started at?
Loan started at so you will take into account the interest you already paid. Get the whole picture.
Thank you so very much
You're getting that small error at the end of the amortization because you used a round function. You want to just limit it to displaying 2 places after the decimal, not round it off. Eliminate the round function and set it to display 2 decimal places (default with accounting formatting), and you'll eliminate that error.
Hello, this is great! Can we change this to account for fortnightly mortgage repayments? I tried, but my sheet is now looking weird! Thanks :)
Nice job!
I once wrote a windows program that would display a chart of a given loan. I could alter loan parameters based on mouse movement. I noticed that a loan has a shape and from the shape, I could tell if it was a good loan or terrible loan.
Thank you, this is very helpful...I added an extra column for escrow 😞
How could this be modified for bi-weekly payments?
Not sure if anyone has already answered your question. In case they haven’t, the easiest thing to do would be to add up all the payments made over the course of the month and make sure that “Payment” + “Excess Payment” equals that total. For instance, using this example, if you wanted to make bi-weekly payments of $2,000 , you would leave the monthly payment as $3,160.34 and mark the excess payment as $839.66 since you would be paying a total of $4000 per month. Interest is only accrued at the start/end of each month, not between bi-weekly payments.
Hope that helps!
I copied yours into my Numbers in Mac and idk why I’m showing a negative balance by pay period 231 when my term should be 360 without extra payments….
Also, shouldn’t the excess payment go into the total principal?
Instead of the total amount of interest saved, could you do a calculation where it shows how much each extra monthly payment saved? If so, how?
If I manually copy the value of "Int Saved" and then subtract that from the previous month's "Int Saved" I can see how much the "Excess Payment" had in regards to an amortized savings affect however for the life of me I can't get it to do these calculations automatically without having to do these extra steps. Any help is greatly appreciated if anybody can please figure it out. Thanks
I was totally following along as best I could in IOS but when it got to the drag down of the principal, interest, and ending balance after entering in all the formulas, my the numbers were totally off. Can't figure out what keeps going wrong as I re started twice, in case I missed a step or entered something wrong the first couple times. I would love to see an IOS Numbers version!
this doesn't take biweekly payment into consideration, right?
how do you change the dates?
Hi. Does this work for any tracker? I downloaded your form, entered the #s for my car and the payment on the tracker and what my real payment is show to be off by a few dollars 😢
@brianturgeon ? Can you help with this?
Mine was perfect until I started to scroll down and something is off with the principal and interest that’s making it. Go out of whack and turn it into a triple amount.
FYI Can you make a video about (what is your real interest rate) . As you may know if you get a 6.5% rate most your payment goes to interest for the first 1/3 of your loan term but the real rate of 6.5% on the first payment is not really 6.5% it's more like 68% and the accrual rate on your mortgage rate of 6.5% is the last payment you pay to the bank. This will help Manny home owners to understand what rate they are paying each month on their mortgage. I know it a lot to ask but there is no such video. Thank you again
if extra Payment Regular Base & irregular base ? If interest rate change after some times ? if only Interest paid?
This sheet is assuming a fixed rate
@@brianturgeon can you make video as per above my question?
With every video paste a sample sheet
Do a video where no payments were made for an extended period as was done during covid.
Thanks
great
i can not downlaod
To download you have to click the link and then make a copy. Let me know if that still doesn't work
Thank you for creating the spreadsheet for us! It's perfect!!
Very helpful, thanks so much!