Explanation
In this example, the goal is to generate a basic mortgage payment schedule.
The schedule provides a clear picture of how the loan will progress over time.
A key goal is to create a dynamic schedule that automatically updates when the loan term changes.
Both approaches build on theexample here for estimating a mortgage payment.
Single formula
The single formula option requires Excel 365.
This makes the formula more readable and eliminates the need to repeat calculations.
Each of these operations returns an entire column of data for the final payment schedule.
In this case, VSTACK combines the output from each separate HSTACK functionverticallyin the order shown above.
However, it is still possible to build out the mortgage payment schedule one formula at a time.
This is the approach demonstrated on Sheet2 of the attached workbook.
We do this by incorporating some extra logic.
If so, we increment the previous period by 1.
To study these formulas in detail, download the workbook and have a look at Sheet2.
The array can be one dimensional, or two-dimensional, determined byrowsandcolumnsarguments.
HSTACK Function
The Excel HSTACK function combines arrays horizontally into a single array.
Each subsequent array is appended to the bottom of the previous array….
