How to Create a Loan Amortization Schedule in Google Sheets/ MS Excel

[@Incomedigs]

Check out my end to end Quickbooks Training. www.incomedigs.com/reab ($50 off w/ code KZbin50) Link to Advanced Amort Calculator: www.incomedigs.com/amort-calculator

[@CarmenTorres-YT]

Thank you so much for this tutorial. It was very easy to create. Awesome job! ☺👏

[@Incomedigs]

Thanks so much for watching!

[@flsplendorio]

Thank you! I've watched 2 other tutorials to create an Amortization Schedule in excel. Yours was by far the best. Great narration!

[@Incomedigs]

Thanks Ryan! Really glad you enjoyed it!

[@recdmavn]

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!

[@Incomedigs]

Hey Ani...thanks for the kind words! Glad you found it helpful!

[@willgiese5241]

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.

[@Incomedigs]

Great to hear Will! So happy you found it helpful!

[@maryjoycetrayco3530]

i know its been 5 years but you've saved my life today..Thank you so much!!!

[@Incomedigs]

Hey Mary! Thanks so much for watching! I'm so glad you've found this useful!

[@MrOutstanding123]

You just saved me as well as made my day!. So simple, well articulated and easy to understand. Thank you!

[@Incomedigs]

So glad you found it helpful! Thanks for watching!

[@314ktm]

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

[@marthavasquez-weber8653]

Extremely easy to understand tutorial and so simple to apply in any loan! THANK YOU!

[@Incomedigs]

Thanks for watching!

[@2annec97]

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!

[@Incomedigs]

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!

[@janleeatx]

this is the most amazing straight to the point tutorial that save me hours of figuring this out. wow thank you!

[@Incomedigs]

Thanks Jan! Really appreciate the comment! Challenge: Reply here with something else I can teach in Google Sheets! Always looking for new topics!

[@jeffmaxwell5350]

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!

[@Incomedigs]

Hi Jeff! Thanks for watching....I recommend checking out the Advanced Amort schedule: kzbin.info/www/bejne/Z3vMipKLis-Dgbs This includes a field to make extra payments!

[@andreslombana5830]

4 Years later and it keeps helping!! great video, thank you so much!

[@Incomedigs]

So glad you found the video helpful! Thanks for watching!

[@pambell9298]

OMG just brilliant, way more helpful than the teachers on line, can't even ind them half the time!

[@Incomedigs]

Thank you Pam! Really appreciate the feedback!

[@Mtnsunshine]

Thank you! This helped a lot.👍

[@pjakobsen]

Excellent video. I have no interest in real estate whatsoever but still found it so useful. Thanks!

[@eliaskampamba3322]

Great video so much insight

[@Incomedigs]

Thanks for watching!

[@DumonteBusinessAcademy]

Huge help! Thank you!

[@Incomedigs]

Thanks for watching! Glad you enjoyed!

[@josephrabinow8223]

This really helped. Thank you so much!

[@Incomedigs]

Thanks for watching! Glad you enjoyed!

[@marykennedy5079]

Wow. Thank you so much. Very clear explanation.

[@remjave2415]

This is great! Trying to relearn everything from scratch. Last time I did this was 12 years ago.... Then customize some other values...

[@Incomedigs]

Thanks Rem!!

[@createdbeing302]

Your instructions and your communication are on point.

[@Incomedigs]

Thank you!

[@2004helloWorld]

awesome, thank you so much. great vid

[@Incomedigs]

Thanks for watching!

[@justinkeogh123]

You rock bro - thank you

[@Incomedigs]

Thanks for watching!

[@conniegivens551]

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.

[@Incomedigs]

Hi Connie! So glad you found the calculator helpful! Just want to note that we have a follow up video with some advanced functionality: kzbin.info/www/bejne/Z3vMipKLis-Dgbs Let me know if there is anything else you'd like to see! Happy Loan Repayment!

[@chatterboxbrands6270]

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!

[@sanelemthembu8447]

He shared it here: kzbin.info/www/bejne/i6uXn62rdtyngcU

[@Incomedigs]

@@sanelemthembu8447 Thank You!

[@sarahcrandall2436]

Thanks so much! So easy to follow and understand!

[@Incomedigs]

Thanks for watching Sarah!

[@ambidriss]

SO well done! Thank you!

[@Incomedigs]

Thanks for watching!

[@NataliMorris]

This is excellent! I can add a line to calculate extra principal payment and calculate the reduction in interest, right?

[@Incomedigs]

Hi Natali....yes! In fact, we demonstrate exactly how to do this with our "Advanced Amort Schedule" video: kzbin.info/www/bejne/Z3vMipKLis-Dgbs

[@Lee-qu4ce]

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

[@Incomedigs]

Great! Glad we could help out!

[@robertmellen6427]

Excellent content. Very easy to understand. Thank you!

[@Incomedigs]

Thanks Robert!

[@mattsingley]

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!

[@Incomedigs]

Matt Singley Awesome! Great idea...glad you found it helpful!

[@rachelmaietta2044]

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?

[@Incomedigs]

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!

[@MrKanish99]

Hi, Can you through an example show how to adjust the schedule for interest rate varying during the loan period. Thanks!

[@Incomedigs]

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 [e-mail]. Thanks!

[@lindadigiovanni3248]

How do you make an excell sheet with different loan payments? Can that be done? you videos are very easy to understand. thanks

[@Incomedigs]

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: kzbin.info/www/bejne/Z3vMipKLis-Dgbs

[@SeanPGribbons]

You are a genius thank you this just helped me make my airbnb long term rental underwriting sheet!!! You rock!

[@Incomedigs]

Thanks Sean! Really appreciate the feedback!

[@nelsonlueiza1144]

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?

[@Incomedigs]

Hi Nelson...thanks for watching! We recorded a follow up showing an "advanced" calculator...this includes making extra payments: kzbin.info/www/bejne/Z3vMipKLis-Dgbs

[@richmonroe2903]

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!

[@Incomedigs]

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.

[@richmonroe2903]

@@Incomedigs Thanks a lot for the help! Your answer was just what I was looking for! Great channel!

[@mariontschirhart360]

Great video, but how do you record a defaulted payment (no payment) of 3 months worth?

[@mel-ch3sg]

very helpful!

[@Incomedigs]

Awesome! So glad you found this helpful. Let me know if there is anything else you'd like to see!

[@yesip4]

Thank you so much, this made my assignment so much easier to understand.

[@Incomedigs]

So glad to hear!

[@user-mi5yr9jk5g]

great video thanks so much. What if the interest rate changes/fluctuates throughout the life of the loan?

[@byronbolling2666]

This was great, thank you. What if I wanted to make additional payments and how would I include them in this scheadule

[@Incomedigs]

Hey Bryon...thanks for watching! Check out the advanced amort schedule...we address additional payments: kzbin.info/www/bejne/Z3vMipKLis-Dgbs

[@sontysportsladysoccerfanat235]

The best ever explanation. Thank you 🙏

[@NickBaldo]

Thank you!

[@bb_unplugged]

Great video!!! How do you add a colum for additional payments?

[@Incomedigs]

Hi Bruno...see the link here for the Advanced method which has a column for additional principal: kzbin.info/www/bejne/Z3vMipKLis-Dgbs

[@jordanoliphant7420]

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?

[@Incomedigs]

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: [e-mail]

[@sachinpanwer9030]

Great and simple!

[@joeyd1682]

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

[@Incomedigs]

Yes! You can download here: www.incomedigs.com/amort-calculator

[@joeyd1682]

@Incomedigs thanks so much honestly you don't realise how much it will help me

[@Incomedigs]

@@joeyd1682 Great to hear! You're welcome!

[@yolandagaeta428]

This was a great tutorial! Very simple to follow. Thank you for sharing this.

[@ChristCenteredShorts777]

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?

[@Incomedigs]

Hey Chaston...thanks for watching! Check out the advanced calculator. This includes the ability to add extra payments: kzbin.info/www/bejne/Z3vMipKLis-Dgbs

[@dfordham54]

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)

[@Incomedigs]

Hey Drew! We've done exactly that! Check out the advanced calculator here: kzbin.info/www/bejne/Z3vMipKLis-Dgbs

[@joshuawarren490]

How do you change the formulas if you plan to pay more than the minimum monthly payment?

[@mariareames924]

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%

[@Incomedigs]

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.

[@mariareames924]

@@Incomedigs Hooray! That did it! Many thanks.

[@SaraFoxleySmith]

It would be great to see what an extra $100.00 or extra payment does too. Thanks for making the video!!

[@Incomedigs]

Hi Sara! Check out the advanced video: kzbin.info/www/bejne/Z3vMipKLis-Dgbs In this video we have a column for additional principal!

[@ericsundman1]

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?

[@Incomedigs]

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

[@sanelemthembu8447]

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.

[@Incomedigs]

Hi there!...thanks for watching. I'll first direct you to the advanced amort schedule: kzbin.info/www/bejne/Z3vMipKLis-Dgbs 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: [e-mail]

[@davidzuercher506]

Bad ass and COMPLETELY helpful!

[@Incomedigs]

Thanks David! Appreciate you watching! More to come...

[@pikinskills]

I want to keep monthly payment flexible (prepayment purpose). Please suggest the new calculations.

[@Incomedigs]

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: kzbin.info/www/bejne/lX6ap6eVlMZsrLM

[@Alinagaeta]

Wow. You made it so easy for me. Thank you soooooo much!!!

[@lindyburt3238]

Thank you so much for doing this.

[@Incomedigs]

Thanks for watching Lindy!

[@8605kittykat]

can you add a way of making extra payments or if you pay more then the actual payment ?

[@Incomedigs]

Hi Catherine, we cover that in the "Advanced" tutorial...which can be found here: kzbin.info/www/bejne/Z3vMipKLis-Dgbs

[@African244]

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.

[@Incomedigs]

Excellent! Thank You!

[@japhetchiputire4551]

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.

[@Incomedigs]

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.

[@juhdah2146]

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.

[@Incomedigs]

Hi There! probably an issue w/ negatives! Is the value of E7 negative? It should be...if so, the sum function would work.

[@khadakbist8054]

Superb videos love your explanation ❤️

[@mariasalveta9314]

Can you make a video on how to set up Bi-weekly amort in google sheets, please?

[@Incomedigs]

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.

[@Digging_deeper_with_Birdie]

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?

[@Incomedigs]

Hi Kendra...thanks for watching! Please check out our video on the "advanced" amort calculator....you can add additional monthly payments: kzbin.info/www/bejne/Z3vMipKLis-Dgbs

[@ivybridge01]

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.

[@Incomedigs]

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.

[@NavjotSinghpatiala]

Very good video

[@Incomedigs]

Thanks for watching!

[@BernardMyburgh]

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?

[@BernardMyburgh]

kzbin.info/www/bejne/i6uXn62rdtyngcU nevermind i see others have asked before and you answered with a second video.

[@Incomedigs]

@@BernardMyburgh beat me to it! Thanks for watching. Please let me know if you have any additional questions on this or other videos!

[@BernardMyburgh]

@@Incomedigs I did actually! This video (kzbin.info/www/bejne/lX6ap6eVlMZsrLM) 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.

[@caseycb79]

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!

[@Incomedigs]

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?

[@caseycb79]

@@Incomedigs I'll give it a try and see if it works. If not, I'll let ya know. Lol thanks again!

[@cyclingqueenbee]

THANK YOU!!!

[@MosaicDay]

Great tutorial, thank you! For interest payment, instead of using the IPMT function, you could just multiply the rate by the balance, no?

[@Incomedigs]

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.

[@riverten2794]

Thank you, this is going to facilitate my seller-financed property transfers and save me lots of fees.

[@rachelmaietta2044]

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?

[@Incomedigs]

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 [e-mail]. I can take a look!

[@ace1926]

What happens when you make a additional Principal Payment? It will affect your total? How would you make the change(s).

[@Incomedigs]

Hi Wallace...please see the follow up we recorded on an "advanced" amortization table: kzbin.info/www/bejne/Z3vMipKLis-Dgbs

[@akn9066]

So simple and smart! Thank you

[@Incomedigs]

Thanks for watching!

[@msalazar51]

Great tutorial and thank you for the work. However, there was one problem that I had. The formula for the "Interest Payment" =IPMT($C$3/12,A7,$E$2*12,$C$2). The issue is with the "lock" on C2. If I lock C2 then the formula should always reference that Principle number in C2 and therefore the interest will always be the same? In your video however the number goes down? In my form it remained constant. I changes "$C$2" to "F6" and it started out perfect. However it fails to complete correctly? What an I doing wrong? I would appreciate a response to this. Thanks.

[@Incomedigs]

Marc, Thanks for watching! The lock on C2 should stay remain. The excel/ google formula needs to know the present value/ starting value of the loan. This amt. will not change. What changes in the period...column A. Try locking C2...just as I do in the video. Everything should work out.

[@kevinmantz]

Great video, super helpful thank you!

[@Incomedigs]

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!

[@MrsBigAud]

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.

[@Incomedigs]

Hi There! Yes...you should be able to do that! We actually have a follow up video: kzbin.info/www/bejne/Z3vMipKLis-Dgbs 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!

[@qdaddy357]

awesome and clear. but.>>>>....I need a chart to show daily interest reduction. Like when a note is scheduled to be paid on the 15th, but your borrower sends in his check on the 11th. The payment is credited for the date I deposit his payment, not the 15th, and I have to adjust and credit him for paying early.....I'm guessing it's a calc in the moths column? basically the interest would be a 365 day calc?....I'm a beginner ,here

[@Incomedigs]

Hi J Watts...thanks for watching! Yes...what you are describing is the difference between "scheduled" payment and "actual" payment. You would need a column for "Actual Payment Date". This would then subtract from the previous actual payment date to give you the # of days since the last payment. You would then multiply those days by the daily interest of the loan. This sheet could certainly be manipulated to manage that.

[@qdaddy357]

@@Incomedigs Thanks for responding- I've looked and cannot find a video on that specific type chart...could you maybe make a tutorial on that for us beginners?

[@fabfam97]

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

[@dbsk06]

For pmt formula you must divide rate by 12 and multiply period years by 12

[@Incomedigs]

Hi there...Yes! As demonstrated in the formula.

[@dbsk06]

@@Incomedigs yeahit was a mental note for myself but thank you great videos

[@bryanbambao]

how can you can compute with annual escaltion on interest rate?

[@lluiszardoya]

So useful thank you!

[@Incomedigs]

Thanks for watching!

[@fabfam97]

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

[@Incomedigs]

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.

[@marisolacevedo1256]

how would you add, a column for additional principal??? do you have a tutorial for htat???

[@Incomedigs]

Hi Marisol...this video addresses that exact question: kzbin.info/www/bejne/Z3vMipKLis-Dgbs

[@deliaescobar5557]

Hello thank you for help... how or where can you add a column for "additional principle payments "?

[@Incomedigs]

Hi Delia! Thanks for your question. Here is a new video that shows how to add that principal payment and more: kzbin.info/www/bejne/Z3vMipKLis-Dgbs

[@margaretmeaker2830]

Exactly what I was going to ask..lol. Great video.. and will be going to.look at video u suggested. Thanks

[@JazzyNapps]

How do you modify this chart if you do lump sum payments on the principal?

[@JazzyNapps]

I just found the part 2! Thanks this is so helpful!

[@jakechoi7393]

Do you guys have a video for amortization based on a fixed rate mortgage

[@rynespaulding8870]

Let’s say we have extra money to pay on the principal, how would you reflect that in the amortization schedule?

[@Incomedigs]

Great Question! We handle that with the "Advanced" amort schedule: kzbin.info/www/bejne/Z3vMipKLis-Dgbs

[@e505mp95]

Great video!!! Would you please show us how to convert the Annual Interest Rate to Annual Percentage Rate(APR)?

[@yacine6708]

((i+1)^(1/n))-1 where I is the interest rate and n is 12 for the number of months in this case

[@Carolynneyland]

Can this be done with a variable interest rate?

[@Incomedigs]

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.

[@zpot21801]

Is there an easy way to add an extra payment once every 12 periods? e.g. I get an annual bonus and want to make additional payment to the loan

[@Incomedigs]

Yes...you could add a column for "additional principal". You would then adjust your calculations to decrease the loan value by that amount prior to calculating interest.

[@raczyk]

Does this whole payment go towards the principal?

[@Incomedigs]

Depends how the loan is setup. On a traditional residential mortgage, yes, the payment would be all principal. Some loans will require that interest is calculated daily and that any payment will first go toward accrued interest and the balance would go toward principal.

[@Incomedigs]

Hi! To answer your question, watch Part 2 of this series: kzbin.info/www/bejne/i6uXn62rdtyngcU

[@zeal514]

When calculating IPMT, why do you select cell C2, the initial loan balance, then lock it? Shouldn't it be F6, unlocked? This way interest isnt calculated on the initial principle every month, rather it is calculated on the current principle every month?

[@Incomedigs]

Hi Kyle, Thanks for watching! The IPMT calculation is used to determine the fixed monthly payment over the duration of the loan. This formula will not calculate the amt. of interest we pay each month. The "PV" in the IPMT formula is "Present Value". This formula uses the following variables to get the fixed monthly payment: Interest, # of Periods, PV (present value or original principal). The formula in column C actually does not need to be copied down to each row. It could live at the top...b/c it is not changing. The other columns help us to calculate, on a given month, how much of that fixed payment is allocated to principal/ interest....and these formulas use the month to month balance to calculate interest.

[@Yanyan-gh9ug]

Why didn't you provide input to the Type portion of PMT formula?

[@Incomedigs]

Hi Yanyan....thanks for watching. The last argument is optional. If you don't put anything in...as I do not, it assumes that we are calculating the loan payment from the beginning of the term.

[@betharee990]

Unfortunately, if you put in an additional principal payment, it will not maintain calculations past that date.

[@Incomedigs]

Hi There...thanks for watching! Please check out the advanced video...where we specifically allow for additional principal : kzbin.info/www/bejne/Z3vMipKLis-Dgbs

[@melisahelbling1882]

Fantastic tool! Thank you so much!!

[@Incomedigs]

Hi Melisa! Thanks for watching! Glad you found the video helpful! Let me know if there is anything else you'd like to see!

[@BenjaminKreis]

Well done. Very helpful.

[@Djtrav121]

How can you do this with a construction loan where the first year of the loan is interest only?

[@Incomedigs]

Hi Trav....thanks for watching. This would be a unique situation....I would definitely copy the amort schedule for some modifications. I would adjust the formulas such that the principal payment in periods 1-12 is 0. The interest payment is simply calculated as Principal * Int Rate / 12. You can then start your amortization schedule in period 13...where the loan begins to amortize based on initial/ remaining principal.

[@microcontrolledbot]

Hey Nick, I was noticing that IPMT does not seem to give me the same results as is on my mortgage statements. Instead using the calculation of (interest rate / 12 * remaining balance) gives me what is on my statement. Just wondering if you know why that is.