Easy Amortization Table With Extra Payments For Any Fixed-Term Loan

[@BeyondSkys09]

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!

[@BrentColeman]

Glad it was useful!!

[@emccarthy2]

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!

[@BrentColeman]

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!

[@LGreg307]

Thank you for breaking this down!! I was able to create a schedule for our ARM with this as a basis.

[@BrentColeman]

Glad it was useful!

[@bridgetconstant9464]

Thank you for making the first video I watched that didn't try to make this harder than it needs to be!!!!

[@BrentColeman]

Glad it was useful! 🙏

[@MADMIKE-w1z]

Very useful video. How do you go about layering in defaults in the amortization?

[@BrentColeman]

Sorry not sure what you mean by layering in defaults?

[@MADMIKE-w1z]

@@BrentColemanThanks for the quick response. I’d like to know how to model a scenario where we would also add a 2% default rate (as an example) to the assumptions you have in your exercise; so 2% of the loan balance is not repaid at the end of the term. Not sure if this would be relevant for home mortgages, but I wonder if this is an assumption that one could be asked to factor in for a different type of loan.

[@BrentColeman]

@@MADMIKE-w1z If I understand correctly do you mean a loan balloon at the end of the term? You could calculate payments and interest components throughout the loan with the PMT and IPMT formulas, using a [FV] of 0.02*LoanSum. This would give you a 2% balloon on the loan, and the schedule should not amortise to zero in such a case. Hope I understood that correctly?

[@hibaf85]

thank you for this video, honestly super helpful and straight to the point!

[@BrentColeman]

Glad it was helpful!

[@natk1105]

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

[@MYHammond]

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

[@BrentColeman]

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!

[@RitaBlake-id3zx]

Thank you this is amazing. How would I add variable interest rate into this spreadsheet?

[@BrentColeman]

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.

[@zoeeforder3844]

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

[@rhuwinestrella1935]

Thank you for this schedule Brent. Very Helpful. :)

[@BrentColeman]

Thanks! Glad it was helpful!

[@antoinedevin9618]

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

[@BrentColeman]

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!

[@mrpropilot007]

Awesome!! Exactly what I needed to customize my table! Thank you!!

[@BrentColeman]

Glad it was useful! :)

[@katherinegingrich3596]

Yay!!! Thank you so much. Was a breeze to do with your help. 🥰

[@BrentColeman]

Glad it helped!

[@nirmalavvaru]

Thank you! Extremely useful! :)

[@BrentColeman]

Glad it was helpful!

[@7X2TT]

how do you add if you had to pay a monthly insurance too with the mortgage? thank you

[@BrentColeman]

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!

[@mandykingsbury9656]

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]

Hi Mandy! Yes this should work well!

[@mandykingsbury9656]

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

[@the.true.mjdavis]

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

[@BrentColeman]

Thanks for sharing!

[@simonsebit2439]

Very informative, thank you so much. Any chance you can share that sheet at all?

[@BrentColeman]

Hi Simon, thanks for watching! I have been hunting around for it but have no idea where it has gone! Sorry about that

[@monkeyman321]

This is awesome, thank you so much!

[@BrentColeman]

I'm glad you enjoyed it! Thank you for watching!

[@monkeyman321]

@@BrentColeman it was very helpful man, if it wasn’t for this video I would have been fired from my job lol

[@BrentColeman]

@@monkeyman321 oh wow! If there's anything else I can help with let me know!!

[@johnhogg6982]

Your a Legend very informative video keep up the good work

[@BrentColeman]

Thanks glad you enjoyed it!

[@redman2751]

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?

[@redman2751]

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.

[@redman2751]

I did the same at J2 as well to add in the extra payments into the total repayments if any extra payments were made.

[@the411queen]

Is this another pre payment or directly off the principle?

[@BrentColeman]

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!

[@wesbrown738]

is there a link to download the spreadsheet?

[@BrentColeman]

Unfortunately not - this was my first video and I hadn't worked out how to share a spreadsheet at that point!

[@JamieHicks-ds4zd]

How would you calculate the schedule if the first payment was $99 and the remaining payments were amortized?

[@Spatrickscan]

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

[@FernieAriel]

I also want to know this. Also, I want to make sure everything below the 0 loan balance is hidden, anyone know?

[@RobertBarber-eq9qb]

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.

[@anishachawla]

Wow! So impressive

[@rehofmann33]

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.

[@BrentColeman]

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

[@rehofmann33]

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.

[@l8dyacgaming]

Thank you for this amazing video! It is of great help :D

[@BrentColeman]

Glad it was helpful!

[@LoganParis-eu7fb]

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.

[@FernieAriel]

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?

[@johnbriones6855]

Thank you very much

[@BrentColeman]

Glad it was useful!

[@BrittanyVD]

I’ve done this in excel for a personal loan. Loan principal was $43927 for 7 years at 8.39%. It seems like everything is fine with the calculator, but my total principal repayments is $88,072.74, which you said that figure should match the loan principal….what am I doing wrong, total repayments in correct, interest is correct (matches my contract)… I dunno!!!

[@BrentColeman]

Hey! Has the loan balance turned negative at the bottom of the schedule? The loan stops when the balance reaches zero, and if the schedule is built without stopping at zero, the numbers become a bit odd! Hope that fixes it!

[@karan18986007]

what if the payment is not monthly but every 6 months?

[@BrentColeman]

Heya! You can simply change the payment frequency to 2 (2 payments a year). Hope this helps!

[@egasmoura]

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

[@BrentColeman]

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!

[@jioce1]

Got lost the moment you said multiply the two didn't know how to do that :0(

[@Matt-mu1mg]

Be handy if you could do one for a front loaded interest loan

[@BrentColeman]

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

[@MaheshJoshi_wellington]

This is awesome spreadsheet please

[@jeffreylawrence6201]

DUH, you forgot to add the extra payments to the total payments sum

[@jeffreylawrence6201]

seems wild wood does not comprehend his error

[@bobbyalexander2632]

Screen info too small, can not read. the presenter talks to fast

[@BrentColeman]

Thanks - will improve going forwards