Topics: 1. (00:00) Introduction. 2. (00:55) What is an amortization table? 3. (01:13) Period Rate formula. 4. (01:30) Total Period Formula. 5. (01:42) PMT period payment formula with the PMT function. 6. (03:05) First row in amortization table formula. 7. (03:27) SEQUENCE array function to generate period column. 8. (03:51) SEQUENCE array function to generate PMT column. 9. (04:40) IMPT and a function argument array operation to generate Interest Paid column. 10. (06:17) PMPT and a function argument array operation to generate Principal Paid column. 11. (06:33) SUBTOTAL and OFFSET to generate a spilled array of running totals for the Balance column 12. (10:13) Test results. 13. (10:28) Summary and Video Links.
Hi Mike, so cool and clever to use SUBTOTAL and OFFSET to spill the declining loan balance. You could also get the SPILLED declining loan balance with this formula: =IFERROR(-C8/IFERROR(-PMT(C5,SEQUENCE(C7,,C7-1,-1),1),0),0). Briefly, it relies on calculating the loan constant for each spilled period of remaining term (period rate divided by the remaining term) and dividing the monthly payment by that calculated loan constant. I had to use IFERROR to trap for the divide errors as of the end of the loan (0 periods of remaining term) and force in the appropriate $0's. It spills just like your SUBTOTAL / OFFSET formula which is arguably easier to set up. Fun to figure different ways to solve the problem. Thanks for the great learning and Monday amortization table fun :)) Thumbs up!!
@@ExcelWizard Excellent! I hashed it out in a couple of steps and then assembled it into one formula. I should have looked it over to eliminate the redundancies. Thanks for your insights! Thumbs up!!
Mike, honestly, I do not have words to express how good you are :) I learned spilled array trick for my work before but I just watched this and you are simply awesome!
Wow, Financial is Fun 😍 here other versions of non-volatile balance =C2+CUMPRINC(C5,C7,C2,1,SEQUENCE(C7),0) or =C2+MMULT(-(SEQUENCE(C7)>=SEQUENCE(,C7)),E12#) and Single cell formula =LET(se,SEQUENCE(C7),CHOOSE({1,2,3,4,5},se,-C8,-IPMT(C5,se,C7,C2),-PPMT(C5,se,C7,C2),C2+CUMPRINC(C5,C7,C2,1,se,0)))
Hello, Mr. Girvin. You can avoid the OffSet workaround (06:33) by using another Financial Function: CUMPRINC, but you'll have to use what Mr. Excel calls de 'one-ary plus' operator in the "End Period" argument of the function, which, in your workbook, would be +C12# (including both "plus" and "pound" operators) The CUMPRINC even avoids the use of the weird period 0 line
Hey Mike! Brilliant as usual! I have a request to you, I don't know if it's too much to ask! Umm I have gone through your bond videos and I was wondering if you could explore more on the advanced application on Bonds, like: duration, term structure and valuation! I know I am asking too much but tbh, I never had the chance to connect theory with practice, until I discovered your channel. So it would be great, since you already know a lot about these things! Thanks again!
Hi Mike Hope you are doing fine. Awesome lesson Mike Great u r rokstar very useful video and very practical need everyone for every project ......very big thanks to share with us. Very greatful for us...👌👌👌👌👌👌👌👌💐💐💐💐🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂
Right off the bat, there's a difference according to region: in my region the period rate is related to the yearly rate but not in a proportional fashion. Due to compounding, but in other regions the latter is forbidden by law. So in my region the (I/P)PMT functions are useless (same holds true for financial calculators). Now, on topic of EMT's: an offset inside a subtotal to get a running total inside of a spilled array... you don't see that every day! Now, it looks a little volatile, but it's really cool. Thanks for that super-cool trick! :-)
Yes, here is USA that relationship between annual rate and period rate is defined by law. Glad you like that Spilled Running Total. It is a good trick that I am sure that we will use here and there : )
Hi Mike. Hope you are doing well. Can you please share any alternative of the SUBTOTAL & OFFSET combination with the latest dynamic array functions in Office 365, such as REDUCE, MAP, SCAN, LAMBDA? Also, could you make a fully dynamic depreciation plan for vehicles and equipment? Thank you in advance.
Hi Mike, it's a great video you have done! Just wondering if you can do another video, applying this to Variable Rate as per your Trick 407. I have tried but just couldn't get the formulas to work. Many thanks.
That is great. Can you please also include dates and calculate interest payment based on actual number of days between two days using DA rather than assuming one rate.
Great financial lesson and great use of OFFSET!!, took the challenge, just for fun , find a way not to use OFFSET , (like I did also on the recent Bill's video with adding columns). So, if we have an clmn array with 3 rws 1 1 2 1+2 3 we need to get to 1+2+3 This can be done with MMULT between INDEX (array, 1 0 0 and 1 1 2 0 1 1 2 3 ) 1 1 2 3 1 2 3 1 2 3 is easy, we need to get rid of top right corner to replace it with 0. This I done it with a condition =sequence(3)*sequence(,3)
Excellent! I would of liked to see avoid using volatile OFFSET, consider additional monthly payments, and the years and months spilled dynamically too....a full blown and efficient AMT table...however this helps get me started on the right path using Dynamic Arrays for this problem.
@@excelisfun Yes, thank you. I can see volatileless formulas :) I'll have to add the spilled year, month, and additional monthly payment...which I think shouldn't be a problem.
Would be good to know how to make adjustment halfway through the lease schedule. Due to COVID, there are incentive / rebate provided to tenants which was not in the original lease agreement.
Great Video! But I am missing something! I've never worked with excel or spreadsheet in general for that matter. As I try to follow along when you put the period rate formula in i end up with $0.00 every time, am I not entering it correctly?
I think we can't stop learning Excel unless it wil Disappears, you start working with it like software and a long the road it becomes like game...it's funny 😄
Hi mike great video as always... I have a question, if you renew your mortgage regularly to get the best interest rates available how can we build that in (more complex)... Ie rate 1 for first 2 years then after remortgaging at year 3 with a new rate from a different bank... Would need an input table to the side of each new rate and which period the change happened
I don't have time to do a spilled array formula video right now, but the amortization playlist has a video with changing rates: ruclips.net/video/NjWDp-tw6y0/видео.html , Excel Magic Trick 407: Amortization Table W Variable Rate But I would probably just build a new amortization table each time I refinance, at least that is what I do.
Thank you for your videos. I love this Amortization Table using Dynamic Arrays. What would you do if someone wanted to pay $100 additional principle each month? Can you make that Dynamic as well?
I don't have time to do a spilled array formula video right now, but the amortization playlist has a video with extra payment column: ruclips.net/video/U1wTeVXbA8M/видео.html , Excel Magic Trick 515: Amortization Table Pay Off Early & Trouble Shoot Formula Creation
hello i have been following and watching your tutorials.. I would like to ask some help me and my friend is working on a lending sheet. Wherein we cant come up with the right formula and we have to enter every details manually. We would like a sheet wherein when client doesn't pay interest in full for the month its still calculate the whole amount, sothig that is flexible or adaptable. thank you, hope u read my comment :)
Topics:
1. (00:00) Introduction.
2. (00:55) What is an amortization table?
3. (01:13) Period Rate formula.
4. (01:30) Total Period Formula.
5. (01:42) PMT period payment formula with the PMT function.
6. (03:05) First row in amortization table formula.
7. (03:27) SEQUENCE array function to generate period column.
8. (03:51) SEQUENCE array function to generate PMT column.
9. (04:40) IMPT and a function argument array operation to generate Interest Paid column.
10. (06:17) PMPT and a function argument array operation to generate Principal Paid column.
11. (06:33) SUBTOTAL and OFFSET to generate a spilled array of running totals for the Balance column
12. (10:13) Test results.
13. (10:28) Summary and Video Links.
This video deserves way more views.
Google consistent ranks my videos very low, even though there is rarely better free technical content out there. I don't know why...
Hi Mike, so cool and clever to use SUBTOTAL and OFFSET to spill the declining loan balance. You could also get the SPILLED declining loan balance with this formula: =IFERROR(-C8/IFERROR(-PMT(C5,SEQUENCE(C7,,C7-1,-1),1),0),0). Briefly, it relies on calculating the loan constant for each spilled period of remaining term (period rate divided by the remaining term) and dividing the monthly payment by that calculated loan constant. I had to use IFERROR to trap for the divide errors as of the end of the loan (0 periods of remaining term) and force in the appropriate $0's. It spills just like your SUBTOTAL / OFFSET formula which is arguably easier to set up. Fun to figure different ways to solve the problem. Thanks for the great learning and Monday amortization table fun :)) Thumbs up!!
That is mathematically wizardry, Wayne : ) : ) : ) : ) Very clever. I added it to the download workbook. Thanks : ) Go Team!!!
@@excelisfun Cool! Thanks Mike!!
👍👍👍 Awesome formula, Wayne.
I make a shortened version of your formula
=IFERROR(C8/PMT(C5,C7-SEQUENCE(C7),1),0)
@@ExcelWizard Excellent! I hashed it out in a couple of steps and then assembled it into one formula. I should have looked it over to eliminate the redundancies. Thanks for your insights! Thumbs up!!
Oh!, There is a CUMPRINC function.
=C2+CUMPRINC(C5,C7,C2,1,SEQUENCE(C7),0)
So clever! Thank you!
Excellent. I learned something today. I started with Lotus 1-2-3 years ago. Thanks for teaching an old dog a new trick.
Cool!!! You go way back with Lotus 123. Lotus had nothing like this.
Mike, honestly, I do not have words to express how good you are :) I learned spilled array trick for my work before but I just watched this and you are simply awesome!
Glad to help, cheerguy80!!!!
Great video! Putting Array formulas into practice!!
Yes, Dynamic Spilled Array Formulas make everything so much easier : ) Glad you like the video.
Brilliant Mike, thanks!
Glad you like it!
You are a God for uploading this content and sharing this knowledge
Just a giy having fun with Excel : ) Glad you enjoy the videos, Javan : )
Thanks Mike. The Offset part was Awesome!!! Always Learning : ) : )
Always learning is fun and good : )
Thanks Mike! My week begins with your wonderful video.
Glad it starts the week off well : )
Boom!I've Never Heard Of An Amortization Table But I Just Learned How To Create One Using Spilled Arrays Wicked...Thank You Mike :)
You are Boom Welcome, darryl : ) : ) : )
Nice application of dynamic arrays.
Thanks, Vida : )
Thanks Mike for sharing all of these treasures.
My pleasure!
Mike, I don't know how you continue to jam 10lbs of potatoes in a five pound bag. That was awesome. Thamks for sharing...
Wow - jamming 10 lbs of potatoes into a 5 lbs bag. That sounds so cool : )
Great video. Thanks for posting. Always enjoy learning new Excel tricks.
You are welcome, Tim!!!
Like always, an EXCELLENT video by Amazing // Syed
Awesome video Mike!
Thanks, Chris : ) : ) : ) : )
Wow, Financial is Fun 😍 here other versions of non-volatile balance
=C2+CUMPRINC(C5,C7,C2,1,SEQUENCE(C7),0) or
=C2+MMULT(-(SEQUENCE(C7)>=SEQUENCE(,C7)),E12#)
and Single cell formula
=LET(se,SEQUENCE(C7),CHOOSE({1,2,3,4,5},se,-C8,-IPMT(C5,se,C7,C2),-PPMT(C5,se,C7,C2),C2+CUMPRINC(C5,C7,C2,1,se,0)))
I tried the CUMPRINC function but did not get it to work. Thanks for that one : ) I added it to download workbook. Go Team!
I added all your formulas : ) : ) : ) : )
Such an honor, Thanks a lot 😍
@@ExcelWizard You are welcome a lot!!! Go Team : ) : ) : )
Hello, Mr. Girvin. You can avoid the OffSet workaround (06:33) by using another Financial Function: CUMPRINC, but you'll have to use what Mr. Excel calls de 'one-ary plus' operator in the "End Period" argument of the function, which, in your workbook, would be +C12# (including both "plus" and "pound" operators)
The CUMPRINC even avoids the use of the weird period 0 line
Thanks! You and a few others suggested this and I added it to the download workbook!
Like always, an EXCELlent video by Amazing Mike. Thank you very much.
You are welcome very much, Syed MM : ) : ) : )
Hey Mike! Brilliant as usual! I have a request to you, I don't know if it's too much to ask! Umm I have gone through your bond videos and I was wondering if you could explore more on the advanced application on Bonds, like: duration, term structure and valuation! I know I am asking too much but tbh, I never had the chance to connect theory with practice, until I discovered your channel. So it would be great, since you already know a lot about these things! Thanks again!
Excellent you are great mike and excel magician
Always glad to help, kishor!!!
Always something new! Thank you, Mike!
You are welcome, Malina : ) :)
Hi Mike
Hope you are doing fine.
Awesome lesson Mike
Great u r rokstar
very useful video and very practical need everyone for every project ......very big thanks to share with us.
Very greatful for us...👌👌👌👌👌👌👌👌💐💐💐💐🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂
You are sooooooooooooooo welcome, ashish!!!! Thanks for the okay hand signals, flowers and cakes : ) : ) : ) : )
Beautiful and amazing
Thank you! Cheers!
Awesome video, enjoyed watching. Thank you and bravo!
Glad you like it, Orca : ) : )
Right off the bat, there's a difference according to region: in my region the period rate is related to the yearly rate but not in a proportional fashion.
Due to compounding, but in other regions the latter is forbidden by law.
So in my region the (I/P)PMT functions are useless (same holds true for financial calculators).
Now, on topic of EMT's: an offset inside a subtotal to get a running total inside of a spilled array... you don't see that every day!
Now, it looks a little volatile, but it's really cool. Thanks for that super-cool trick! :-)
Yes, here is USA that relationship between annual rate and period rate is defined by law.
Glad you like that Spilled Running Total. It is a good trick that I am sure that we will use here and there : )
Mike, have you ever pressed CTRL+A or CTRL+SHIFT+A when writing a formula (just after writing the name of a function)?
Try it... :-)
@@GeertDelmulle Yes, I saw Mr Excel's video today : ) : )
Hi Mike. Hope you are doing well.
Can you please share any alternative of the SUBTOTAL & OFFSET combination with the latest dynamic array functions in Office 365, such as REDUCE, MAP, SCAN, LAMBDA?
Also, could you make a fully dynamic depreciation plan for vehicles and equipment?
Thank you in advance.
Hi Mike, it's a great video you have done! Just wondering if you can do another video, applying this to Variable Rate as per your Trick 407. I have tried but just couldn't get the formulas to work. Many thanks.
That is great. Can you please also include dates and calculate interest payment based on actual number of days between two days using DA rather than assuming one rate.
I do not have a spilled array video on that or time to make one. But I will keep it in mind for a future video because it is a great topic.
Amazing video.
Glad you enjoyed it!
You are amaizing 🥳
Glad you like it, Gilberto!!!!
Thanks Mike, This is great. But what i would request is, why you can't make this kind of schedules in excel 2016 or 2019.
Microsoft has not added spilled arrays in those versions. The link to my amortization playlist with how to do it in other versions is below video.
Great financial lesson and great use of OFFSET!!, took the challenge, just for fun , find a way not to use OFFSET , (like I did also on the recent Bill's video with adding columns).
So, if we have an clmn array with 3 rws
1 1
2 1+2
3 we need to get to 1+2+3
This can be done with MMULT between INDEX (array, 1 0 0 and 1
1 2 0 1
1 2 3 ) 1
1 2 3
1 2 3
1 2 3 is easy, we need to get rid of top right corner to replace it with 0.
This I done it with a condition =sequence(3)*sequence(,3)
You are so smart!!!! Thanks for helping the team in so many ways : )
@@excelisfun Dnt mention, it's only Excel gymnastics.😊
Excellent! I would of liked to see avoid using volatile OFFSET, consider additional monthly payments, and the years and months spilled dynamically too....a full blown and efficient AMT table...however this helps get me started on the right path using Dynamic Arrays for this problem.
In download workbook there is a way : )
Did you download and see ?
@@excelisfun Yes, thank you. I can see volatileless formulas :) I'll have to add the spilled year, month, and additional monthly payment...which I think shouldn't be a problem.
@@Sal_A Cool : ) : ) Go Team!!!!!
That's Amazing ... Thanks Mike
You are welcome, Hussein!!!
Awesome.
Would be good to know how to make adjustment halfway through the lease schedule. Due to COVID, there are incentive / rebate provided to tenants which was not in the original lease agreement.
Great Video! But I am missing something! I've never worked with excel or spreadsheet in general for that matter.
As I try to follow along when you put the period rate formula in i end up with $0.00 every time, am I not entering it correctly?
Awesome
Finance is fun!!!!
I tried to involve early pay offs and variable interest rates in a dynamical way, but unfortunately it seems not possible.. did you manage it Mike?
I think we can't stop learning Excel unless it wil Disappears, you start working with it like software and a long the road it becomes like game...it's funny 😄
That is funny : ) : )
Great
Glad it is great for you : )
Hi mike great video as always... I have a question, if you renew your mortgage regularly to get the best interest rates available how can we build that in (more complex)... Ie rate 1 for first 2 years then after remortgaging at year 3 with a new rate from a different bank... Would need an input table to the side of each new rate and which period the change happened
I don't have time to do a spilled array formula video right now, but the amortization playlist has a video with changing rates: ruclips.net/video/NjWDp-tw6y0/видео.html , Excel Magic Trick 407: Amortization Table W Variable Rate
But I would probably just build a new amortization table each time I refinance, at least that is what I do.
how about you use a cumprinc function to calculate the accumulative of the principal paid?
From Excel Wizard's comment below: =C2+CUMPRINC(C5,C7,C2,1,SEQUENCE(C7),0)
how does the table automatically generates the format when it is spilled?
Nice
Glad it is nice for you Sachin!!!
Thank you for your videos. I love this Amortization Table using Dynamic Arrays. What would you do if someone wanted to pay $100 additional principle each month? Can you make that Dynamic as well?
I don't have time to do a spilled array formula video right now, but the amortization playlist has a video with extra payment column: ruclips.net/video/U1wTeVXbA8M/видео.html , Excel Magic Trick 515: Amortization Table Pay Off Early & Trouble Shoot Formula Creation
@@excelisfun Thank you for taking the time to answer my question.
Hi Mike, is there a non-volitale option to OFFSET? Love the SEQUENCE to spill.
Yes,
cr gr0912 and Wayne Edmondson comments below. I have added their non-OFFSET option to the download file.
Thanks for this information Mike..
Pls help..
I get (#SPILL!) message after (=SEQUENCE(Table 1[total periods]) then hit enter
#SPILL! means there is a cell in the spill path that has something in it.
Do you have any videos about google sheet?
Just one: ruclips.net/video/o9B6HCFv0x0/видео.html sorry : (
I am unable to get same format after spill....
hello i have been following and watching your tutorials.. I would like to ask some help me and my friend is working on a lending sheet. Wherein we cant come up with the right formula and we have to enter every details manually. We would like a sheet wherein when client doesn't pay interest in full for the month its still calculate the whole amount, sothig that is flexible or adaptable. thank you, hope u read my comment :)
I am not sure. But you can try this Excel question site: mrexcel.com/forum
great work, no seq option in ms word 2019
No. Microsoft says dynamic spilled arrays and new Excel calculation Engine is only in Microsoft 365.
@@excelisfun That sucks
hi Mike did you see SQLBI channel, there is some new stuff about DAX
Yes, I did see that channel - it is amazing!
I just created this
Cool : )