File Download: [ Ссылка ]
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 👉 [ Ссылка ] 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 [ Ссылка ]
Watch VLOOKUP Hacks Playlist here [ Ссылка ]
Watch VLOOKUP vs SUMIFS Playlist here [ Ссылка ]
Watch Dynamic Arrays playlist here [ Ссылка ]_
Watch Treasure Maps playlist here [ Ссылка ]
Watch Slow to Fast playlist here [ Ссылка ]
