Working from the inside out, the HSTACK function stacks arrays or ranges side by side horizontally. The variables defined above are assembled into a final layout with the VSTACK function and the HSTACK function: VSTACK( Each of these operations returns an entire column of data for the final payment schedule. In other words, this is the core of the dynamic formula.
The SCAN function returns a running balance with 360 entries. The PPMT function returns 360 principal payments. The IPMT function returns 360 interest payments. The SEQUENCE function returns 360 periods. Most of the calculations above are straightforward, but it's worth pointing out that because nper is 360 (30 years * 12 months per year), and because nper is provided to SEQUENCE: tPaid: The total of all payments made over the term. tPrincipal: The total principal paid over the term. tInterest: The total interest paid over the term. bals: Remaining balances for each period, calculated with the SCAN function and the LAMBDA function to sequentially subtract the principal payment from the initial loan amount. ppmts: Principal payments for each period, calculated with the PPMT function. ipmts: Interest payments for each period, calculated with the IPMT function. pers: All periods, a dynamic array of numbers from 1 to nper using the SEQUENCE function. pmt: The monthly payment, which is calculated with the PMT function. pv: Present value of the loan, which is the negative of the loan amount. nper: Total number of payment periods (loan term in years multiplied by 12). rate: Monthly interest rate (annual interest rate divided by 12). 
loanYears: Total years of the loan (C6). The LET function defines the variables used in the formula as follows: This makes the formula more readable and eliminates the need to repeat calculations. The LET function is used to define named variables that can be used in subsequent calculations. 
HSTACK(pers,ipmts,ppmts,ipmts+ppmts,bals)Īt a high level, this formula calculates and displays a mortgage payment schedule, detailing the number of periods (months), interest payment, principal payment, total payment, and remaining balance for each period based on the given loan details.
HSTACK("Period","Interest","Principal","Total Pmt","Balance"), HSTACK(nper,tInterest,tPrincipal,tPaid,""), In the worksheet shown above, we are generating the entire mortgage schedule with a single dynamic array formula in cell E4 that looks like this: =LET(īals,SCAN(loanAmt,ppmts,LAMBDA(x,r,x-r)), The single formula option requires Excel 365. This simplified example ignores property taxes, homeowner's insurance, mortgage insurance, and other fees.
0 kommentar(er)
