How to Setup Advanced Loan Amortization Schedule in Google Sheets (or Excel)

[@Incomedigs]

Check out my End-to-End Quickbooks Training. www.incomedigs.com/reab ($50 off w/ code KZbin50)

[@JLDAPDX]

Why do you use the simple interest calculation (-loan balance * interest rate/12) rather than the IPMT function? You used IMPT on your basic amort. schedule but not on this advanced/extra payment schedule. Thanks! Your videos have been sooo helpful!

[@Incomedigs]

Hi! No specific reason! I would use the formula that best matches the loan amort terms of your loan! So glad the videos have been helpful.

[@joshuaherman2118]

Great video and became easier as I continued watching.

[@Incomedigs]

Excellent!

[@iggyscott187]

I come up with issue Pay off months has #N/A

[@Incomedigs]

Bummer! Okay...I'd have to see what you're doing to dive in and figure out the issue! You can download the template at my website...maybe that would be helpful: www.incomedigs.com/amort-calculator

[@ace1926]

Wow. I am new to this site. I do notice that some of these comments are question(s) by Viewers and are responded by Income Digs, I am impressed.

[@Incomedigs]

Thanks Wallace! We try to stay on top of it! Let me know if there is anything specific you need to see! Thanks!

[@grayspainhour3051]

Such a useful video! Works like a charm! I do have a very short-term loan that is only 16 months, so when I put 1.333 in for the term, the Payoff fields no longer populate. I think this is due to the decimal in my term in years. Thanks so much for sharing!

[@Incomedigs]

Hi Gray...thanks for watching! You are correct...that will through it off a bit!

[@marandahoffman6242]

This is BRILLIANT! Thank you so much for teaching it!

[@Incomedigs]

Thanks for watching!

[@Ahlrrose]

I cannot thank you enough for this video. I have searched websites and apps to find what I want. You have saved me so much time and effort of writing it down!

[@akilaliyanage]

Is there a vedeo shows a calculator with variable interest with extra repayments

[@Incomedigs]

Hi Akila...thanks for watching! We do have a video with extra payments..kzbin.info/www/bejne/Z3vMipKLis-Dgbs I do not have anything with variable rates...yet! Stay tuned!

[@akilaliyanage]

@@Incomedigs thanks for reply. Would love to see a additional payment calculator with variable rate.

[@tammykalp4717]

I have been using this and loving it. But now my taxes have gone up and I need to make adjustments. HELP

[@Incomedigs]

Hi Tammy! I'm sorry...I'm not a CPA! If your taxes went up, you were likely recording your entire principal as a deduction in the past...which is certainly not allowed. I would contact your CPA to see if there is a change you can make moving forward.

[@tammykalp4717]

@@Incomedigs this just means my ESCROW goes up. But I did not want to mess up the previous years cells by changing it. Thought there might be a way around it.

[@katherineflinn1348]

Thank you for your video. You explained it very well. The only issue I'm having is that I can't see what you're doing because everything is so small.

[@Incomedigs]

Sorry Katherine! Try viewing on full screen on a computer/ ipad...seems to come up nicely.

[@TheVantasticLife]

Do you have one of these spreadsheets that you sell? I'm looking for a private loan amortization that will calculate early payments and is plug and play. Thanks!

[@Incomedigs]

Hi There! I do not...But this spreadsheet should take 10 minutes or less to re-create!

[@Glodiator]

GREAT Video! Need help. For some reason, my google sheet will only calculate the Payoff (Mos) if there is additional principal. It says #NA when there is no additional principal. If I add $1 to Add Principal, it can calculate it?

[@Incomedigs]

Hey Dan...thanks for watching! interesting! What if you put in a $0? Can you send some screenshots and/ or share the google sheet w/ me? [e-mail]

[@Incomedigs]

Hey Dan! The issue you were having was due to the loan balance not being exactly 0...but really really really close! We can address this by adjusting our Index formula in cell G3: =INDEX(A5:G366,MATCH(0,G5:G366,-1),1) Note the -1 instead of 0. This tells google sheets to search the range and find the last cell BEFORE 0....not exactly equaling 0.

[@masonfisher2742]

how would you convert this to bimonthly payments?

[@Incomedigs]

Hi Mason...thanks for the question! If you were to use bimonthly payments, the main change is that your "Period" will increase. Right now, "Period 1" assumes the 1st month. If you were to use bi-monthly payments, Period 1 would be the 1/2 half of a month. Instead of 12 periods in a year, we now have 24. Your minimum "monthly payment" would now be a minimum "bi-monthly payment"...with an adjusted formula in cell E3: =PMT(B3/24,D2*24,B2) We also have an adjusted interest calculation in cell C7, instead of dividing by 12, you would divide by 24: =-G6*$B$3/24 You would also want to make sure you have enough periods in column A. On a 30 year loan, you would need 720 Periods.

[@adrianpeett3793]

If the interest rate of the loan is variable over the term (i.e. not fixed at 5% as in your example) can you easily fix the past interest payment field so that its not affected if the interest rate field B3 is changed due to market interest rate changes?

[@Incomedigs]

Hi Adrian...thanks for watching! Yes...you have some options. I also prefer to make things "variable". In your example, I would likely add a couple fields. 1 - "Interest adjusts after (Yrs)" and 2 - "New Interest Rate". Let's assume our value for "Interest Adjusts After" is stored in cell J2 and "New Interest Rate" in J3 We can then use a conditional formula in column C for interest payment (Starting in cell C7): If($A7>$J$2*12,--G6*$J$3/12,-G6*$B$3/12) We are saying: "If my current payment period is greater than the date at which my interest rate changes, use the new interest rate...if not, use the original interest rate)

[@daltonkats7456]

Seriously the best video on this topic I've found - One quick question I had...would there be an easy way to convert this to a bi-weekly payment schedule instead of a monthly one - I've calculated the advantages for myself switching to bi-weekly on my student loan payments, and was wondering if there would be a way for me to transition my current spreadsheet (monthly) to a bi-weekly schedule - Open to comment from anyone who may have an idea!

[@Incomedigs]

Hey Dalton...thanks for watching! Yes! All we need need to do is adjust the # of "periods". We can do this by changing all the "12s" to "26s"....Moving from Months to "bi-weekly". In Cell E3, divide by 26, and multiply by 26...instead of 12 In Cell C7...you would divide by 26 instead of 12. Note that the "Periods" column now relates to "2 weeks"....so you'll need to expand that down further.

[@bradneilly]

Excellent job. This format and the output it produces is exactly what I was looking for. Appreciate you sharing!

[@Incomedigs]

Thank You!

[@davidzuercher506]

Another amazing one!

[@NickBaldo]

Thanks David!

[@brittanyeustice8599]

I was wondering if there is a way to still utilize the "Additional Payment" cell if you don't plan to start the additional payment until a year or so down the line. I don't want to place it in the "Ad Hoc Princ" column since I plan to do lump sum payments too and I want to keep it fairly automated. Is there a formula I could put in there to start the additional payments feature around payment 12? I am expecting a significant salary increase in about a year and would be able to be committed to much higher additional principal payments at that time. Thanks so much!!

[@Incomedigs]

Hi Brittany, thanks for watching! Sure...you could do that. Here's what you would need to do! Add a cell for "Additional Payment Start Date" (Let's assume the data for that lives in cell I3 In your formula starting in cell E7, adjust your formula: If($B7>=$I$3,$F$3,0) You are basically saying: If the date of this payment is beyond my "Additional Payment Start Date", add in the extra payment. If not, add in 0.

[@brittanyeustice8599]

@@Incomedigs When you say "adjust your formula", do you mean simply add in the "IF" statement to the additional principal column? This is the formula I ended with in the "Additional Principal" column by the end of your video. =IF(A8>$D$2*12,"",IF(-G6

[@Incomedigs]

@@brittanyeustice8599 Hi There! You would actually insert a column. In this new column, you would be able to add the formula to calculate the payment starting at year 1...assuming you insert a column at E, you would use the formula I indicated. To your second question. for this....I would just use the Ad Hoc column. Simply go down to each year and update the amount of added principal. I don't think its worth getting into formulas for something that is a bit more random. Of course it would can be done...but probably a bit too complicated to teach in the columns. I would be happy to show you on a coaching call though! Feel free to schedule and we can surely achieve whatever you have in mind for the google sheet! calendly.com/nickbaldo/1-on-1-coaching-session

[@chaitanyavyavahare4604]

How to do "Term" in months ??

[@Incomedigs]

Hi there! To do the term in months, you would adjust any formula that multiplies or divides by 12. Taking that out will do all calculations in months.

[@mrssarahnicholson]

Thank you!

[@ericlai118]

Thank you for an amazing video. I keep on getting #N/A for the payoff(months) though even when I followed the formula 100%, =INDEX(A5:G366,match(0,G5:G366,0),1) Was wondering why that is the case Thank you so much!

[@Incomedigs]

Hi there! Not sure! I would have to check it out...feel free to share with [e-mail]

[@iggyscott187]

I had the same problem did you get a fix

[@rachelrungdit4262]

This is awesome and so helpful! I have a question about this (anyone feel free to answer): when creating this for multiple student loans with multiple different interest rates...what would you do then? Thanks!

[@Incomedigs]

Hey There! Check out this video and tool...kzbin.info/www/bejne/lX6ap6eVlMZsrLM I set this up with the exact purpose of planning my Student Loan repayment!

[@machariajohnmwarari8402]

This was really a fantastic tutorial. look forward to seeing more videos on other financial maths using excel.

[@Incomedigs]

Thank You! We really appreciate your feedback!

[@daysirosales2731]

Best video EVER!

[@izelennla]

Just came across your videos. Do you happen to have something for those of us that have already been making payments? I got a 20 yr loan but I've since made extra payments towards principle, so the remaining months I have left on my mortgage have shortened. Love your videos, thank you!! :)

[@Incomedigs]

Hi there! Thanks for watching! I actually just did this for one of my own loans! Here's my suggestion.... Create the amort table with the original loan details. Then, pick the last period before your next payment and add a single "ad hoc payment" that gets your balance down to the exact current balance of the loan. Then, from this day forward, your interest/ principal should be accurate. You can then add additional ad hoc payments to see how it would impact the overall payoff.

[@tammykalp4717]

@@Incomedigs LOVE this suggestion. I am struggling in GOOGLE SHEETS with the Interest Payment calculating -35552.50 and my formula is just like yours!

[@tammykalp4717]

Nevermind....figured it out. The format was on percentage.

[@clkuski]

Hey man, i'm in the middle of buying a house & my wife and I wanted to see how an extra few mortgage payments per year would affect the mortgage. This is exactly what I needed. Thanks! (the first amort sched video was great also)

[@Incomedigs]

Hi Charlie...thanks! FYI...you might be interested in this brand new video as well...Just published just today...check out the new "Loan Repayment Calculator" - kzbin.info/www/bejne/lX6ap6eVlMZsrLM

[@wow_chiledawg]

This video has been of great help, thanks. I have a question in connection to the loan balance. I'm using your numbers before adding additional principal, but the Loan Balance on Period 180 (ROW 186) results in 0.0000000001559783... and the Payoff Months in 181, or #N/A after doing the last step. I'm checking all the formulas again, but I'm not sure why it isn't an exact 0.00.

[@Incomedigs]

Hey Edward....thanks for watching! Likely just a rounding issue. You can fix this by using the "Round" formula in column G. Try entering: =ROUND(G6+SUM(C7:F7),2) This formula rounds the result to the nearest 2 decimal places. Once entered...auto-fill it down throughout the table. Give that a try and let me know how it goes!

[@wow_chiledawg]

@@Incomedigs Excellent! It works. Thanks. Would you consider doing a training video for an advance schedule that works with adjustable-rate mortgages? i.e ARM-7, ARM-10, etc.

[@justindownstroke]

@@wow_chiledawg do you have the formula for this. I'm having the same problem, but not sure how to use the "round"

[@sethmorelock867]

@@Incomedigs I am glad I looked through the comments I had that issue too. It was driving me a little crazy not getting it to come out right.

[@gwilson9564]

@@Incomedigs I think the formula should be =ROUND(G6+SUM(D7:F7),2) - I changed C7 to D7 and it worked for me.

[@mckenziebee3826]

Beautiful tutorial, thank you. How would I create a cell for interest saved (due to an advanced principal payment) like you mention at the end of the video? Edit: Nevermind, quickly figured out a rudimentary way quickly after writing. If anybody else would like this information here you go: Create a cell for "Total Interest:" and have it be "=SUM(C7:C367)" Then create a cell somewhere random on the sheet with "Initial Interest:" equaling the very base 0 advanced payments interest total (the sum above with 0 added principal, no formula) Then create another cell below that with "Current Interest:" equaling to the cell for "Total Interest". Then create an "Interest Saved:" cell at the top of the sheet with your other information, and have it be a simple equation of adding the initial interest + current interest cells and that should output the "savings". I also recommend turning the "initial" and "current" interests to white text, so that they're invisible, just make sure you know where they are in case you want to make future changes.

[@Incomedigs]

Awesome! Thanks McKenzie for watching and for sharing your insight w/ others!

[@brianide3267]

A huge help, thanks!

[@DMin4147]

Thank you for the video. After countless of video tutorials, I have to say this one is the best one I have watched. The only thing I could not get was the =index(A5:G366,match(0,G5:G366,0),1) code for the pay off months. For some reason it just gives me a #N/A. Would you know by any chance why? Thank you for your help.

[@sethmorelock867]

Same here I found that if I leave it at 5% it was fine even 6% it has a problem if where you have 0 if after two places there are some additional digits, my theory because I do not know if I am right is that the formula is seeing those digits we are not seeing and not recognizing the 0. Like I said theory I am trying to figure this out myself still.

[@sethmorelock867]

Look in the comments the fix is in there.

[@noeahmed2263]

i guess im asking randomly but does anybody know a tool to get back into an Instagram account? I somehow forgot the login password. I would love any help you can give me!

[@andRwhoo]

put in -1 after the G366 in your code above

[@zcbeh8694]

this really help! issit possible to incl moratorium period and flexible interest rate for input?

[@emilygoelzer8016]

Thank you so much for this video! Very helpful. For the very last step of the video, how do you get those few zeros at the end of the last payment? My data stops at payment 180 with the only $0 in G186, so the payoff month and year have the #N/A symbol.

[@incomedigs8281]

Hi Emily...thanks for watching! Ahh...okay! We likely have a rounding issue that rears its head with your specific example. We could adjust the "balance" formula such that: if(G6-SUM(C7:F7)

[@bb_unplugged]

for some reason when I finished inputing the Payoff (Mos) formula I got a 181 instead of a 180 but the $0 on my G column was actually on the 180 row...

[@BrianFitzsimmonsnc]

I had this same problem and it I think it ended up being an extra ) somewhere. Those things can kill ya! PEMDAS!

[@bbowdenpdx]

Can you please put the blank template in your links? it's going to take me the entire length of your video to set that up.

[@Incomedigs]

Hi Brent...the video is 22 minutes long. I suggest following along and creating yourself. The 22 minute investment will surely be valuable should you need to make changes/ adjustments and/ or create anything else in google sheets/ excel. Thanks for watching!

[@summerwright7438]

I DON”T GET IT!

[@Incomedigs]

Hi Summer....that's a bummer! What, specifically are you not getting? Are you following along...where are you getting held up?

[@BrianFitzsimmonsnc]

This was awesome, really appreciate your work! I was wondering if you knew of a way to translate the "Payoff (Yrs)" field into a more coherent string. Something like "8 years, 3 Months". Is that possible? Thanks again!

[@Incomedigs]

Hey Brian...of course! You can add [&" Years"] (omit brackets) to your formula and you should be all set. Lets say you had a payoff in 39 months... 1st, you want to round the down the years....something like: ROUNDOWN([INDEX FORMULA],0) ....This would give you 3 years. then you could take the total months (e.g. 39)....subtract your rounded down years to get the "extra" months. 39-(12*3) = 3 You can combine all that into a formula. Is that what you had in mind?

[@margaretmeaker2830]

Hi.. im battling to see what you doing.. could you not have made it eg 120 percent .. a bit.like your first video where it was easy to see what you doing.. I would appreciate that..

[@adamromano7921]

I have a question. Let's say I have a loan of 102,000k, and I have a mortgage payment of $500 due. How would I marry those two in quick books? If I just credit the loan and-debt the account, it'll take 100% principle from my loan and give me a false number on my balance sheet.