Amortization Schedule with Spill Ranges

This is the second in a series on dynamic array formulas and spill ranges. In this video, we'll create a dynamic amortization schedule ... and it is actually pretty fun!

Written tutorial and sample file here:

Before we had spill ranges, it was a hassle to update an amortization schedule when the number of periods changed. That is, you may want to see it for a loan with 360 months, and then update it for a loan with 180 months, and so on. As the number of periods change, so does the number of lines we want displayed. So, we'd have to fiddle with the formula rows or use some clever technique like conditional formatting or dynamic print ranges. But, now we can use dynamic array formulas and spill ranges.

[matthewtschetter]

Outstanding! Thank you so much. I love this backdoor method.

[scotolivera]

Thanks, i spent the whole trying to find this way. Extremely helpful. like and subscribe DONE

[--SubZer--]

Love it! How do you factor in extra monthly payments using IPMT and PPMT while still keeping everything as a spill range?