Excel: Fully Dynamic Loan Amortization Schedule with the SCAN Function

File Download: www.excel-university.com/yt_7vc5ot6fu6s/
This video shows how to use the SCAN function, along with several additional functions, to create a fully dynamic loan amortization schedule. Excel will automatically generate the number of rows required when the user changes the number of periods. This solution does not require any conditional formatting, or filling formulas down into unused rows.
The formulas return the exact number of rows needed. It computes the monthly payment given the loan amount, annual interest rate, and number of months. It then automatically generates the amortization schedule, including columns for: period, beginning balance, interest, principal, ending balance, monthly payment, cumulative interest, and cumulative principal.
Note: NOT all versions of Excel support the SCAN function. At the time of this video, it is in the process of getting rolled out to Excel 365, and will not be retroactively added to perpetual license versions of Excel such as Excel for Windows 2016, 2019, and so on.
Functions used:
- SCAN
- SEQUENCE
- PMT
- IPMT
- PPMT
Chapters in this video:
00:00 - Introduction
00:12 - SEQUENCE SCAN
01:12 - PMT IPMT PPMT
04:38 - SEQUENCE SCAN
🚀 Unlock hidden Excel tricks and hacks, and become a time-saving Excel pro 👉 excel-university.com/challenge⚡️🕒 Say goodbye to late nights and spreadsheet struggles!
Subscribe here 👉 kzbin.info and turn on all notifications 🔔 for MS Excel time hacks that will make you more productive!
Also, check out these videos with time saving Excel Hacks:
Excel PivotTables for Beginners kzbin.info/www/bejne/jJubhIGkltllfKM
Watch VLOOKUP Hacks Playlist here kzbin.info/aero/PLg6394TGLU5EwVI-6MxpSZOVcoz9eEPVI
Watch VLOOKUP vs SUMIFS Playlist here kzbin.info/aero/PLg6394TGLU5EprSnwbRF8_jCVQKde7cjr
Watch Dynamic Arrays playlist here kzbin.info/aero/PLg6394TGLU5FmzOe_Pb-zapkM4cX6PDC_
Watch Treasure Maps playlist here kzbin.info/aero/PLg6394TGLU5FTN2vOk-J55wsCej9l-8Dn
Watch Slow to Fast playlist here kzbin.info/aero/PLg6394TGLU5HZ7zlZdmYrEUVAW9jYAWzE

[@GSC-zz8zt]

This is excellent and thank you very much for this video! Short, sweet and to the point.

[@ExcelU]

Glad you enjoyed it!

[@--SubZer0--]

Love it! How do you factor in extra monthly payments when using PPMT and IPMT while still keeping the spill ranges and dynamic arrays?

[@Abbtube]

I loved your approaches

[@josealvesferreira1683]

very good

[@johnstelma5461]

This is really cool! However unfortunately you can’t use it within a table, is there anyway set this up to where you can also use filter feature?

[@rnunez047]

Excellent. Maybe, if you have enough latin followers, in little corner of the video, you should write the function name in spanish. Actually, I understand, but you can increase the scope of the channel. i like a lot the fast explanation. thank so much 🙏👏👍

[@maryluw9915]

why am i coming back with a #NAME? for the running balance???

[@kumarmukesh8782]

show value error, please help

[@jongroeneweegen4160]

Tried to replicate your amortisation spreadsheet but my version of Excel does not support the SCAN function. Is the SCAN function still in beta mode?

[@ExcelU]

Yes ... in other words SCAN is not fully rolled out at this time to 365 subscribers.

[@85MA]

perfect, please create a dynamic amortization table with Interest only payment option. it would be great help. Thanks in advance Eg. Loan Amount 1,200,000 Loan Rate 12% Annually Loan Term 20 Interest Only Payment Period 10 Extra payment 3000 Following option are required in column 0 Payment No. 1 Opening Balance. 2 Principal. 3 Schudel Monthly Payment 4 Interest. 5 cumulative Interest. 6 Extra Payment. 7 Ending Balance.

[@jdonica6354]

I am doing the Scan formula exactly as shown (=SCAN(0,B10#,SUM)) and all I can get in the scan column is "#NAME?" I cannot figure out what I am doing incorrectly. After doing some internet research, I replaced the word SUM with LAMBDA(a,b,SUM(a+b) and then it worked perfectly. Does this mean that I have a different or older version of excel? Or really, what does this indicate? Thanks.

[@suzannecpa]

it happens to me too.

[@ciaucia156]

form of "eta reduced lambda" is used here (currently available only in beta chanell). btw, you could use just LAMBDA(a,b,a+b)

[@jdonica6354]

Thanks, yes, I replaced with the shortened formula and it still worked fine.@@ciaucia156

[@mikehodkinson9901]

@@ciaucia156 Same here - currently if you F1 on =SCAN it tells you to use =LAMBDA. Yet again this is still in Beta or Insiders only availability and it will be several months before this is in "Live / General" release Excel. Wish all experts would qualify such post with "This is available only in Beta / Insider Excel versions and may therefore not be available to you yet".

[@LauraParducci]

I have that same problem, but using LAMBDA just returns #REF. :(

[@thomasfox6876]

I can't get the scan function to work. I enter =SCAN(0,the array,sum) and I get #Name?

[@ExcelU]

At the time of this video, the SCAN function is in the process of being rolled out to Excel 365 users. It won't be retroactively added to perpetual license versions of Excel such as Excel for Windows 2016, 2019, and so on. So, the #Name? error most likely means your version of Excel doesn't have the SCAN function or doesn't support eta reduced lambdas. You may be able to replace the SUM argument with the LAMBDA equivalent.