Download Excel File: [ Ссылка ]
Learn how to create an amortization table to break apart each period loan payment to show interested paid and principal reduction. We use Microsoft 365 Excel Spilled Array Formulas to allow user to change years and total periods and have the amortization table dynamically adjust. Learn about the financial functions: PMT, IPMT and PPMT. Learn about the dynamic Range function OFFSET. Learn about the SUBTOTAL function, Learn about the SEQUENCE Dynamic Spilled Array Function.

Excel Amortization Table Excel Videos
[ Ссылка ]


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.

свернуть