![]() If so, we increment the previous period by 1. First, we check to see if the previous period is less than the total periods for the entire loan (loanYears * 12). Namely, we need to stop the periods from incrementing when we reach the total number of periods (term * 12) and then suppress the other calculations after that point. We do this by incorporating some extra logic. In order to make the term in years variable, we need to do some extra work in the formulas. G6=PPMT(intAnnual/12,E6,loanYears*12,-loanAmt) // principal Then, in the range E6:I6 we use the following formulas: E6=1 // period 1 hardcodedį6=IPMT(intAnnual/12,E6,loanYears*12,-loanAmt) // interest This is the approach demonstrated on Sheet2 of the attached workbook. However, it is still possible to build out the mortgage payment schedule one formula at a time. In older versions of Excel (Excel 2019 and older) we can't create the payment schedule with a single formula because dynamic arrays are not supported. In this case, VSTACK combines the output from each separate HSTACK function vertically in the order shown above. Notice that HSTACK runs inside the VSTACK function, which combines ranges or arrays in a vertical fashion. Assemble the columns of data created earlier, including the periods, interest payments, principal payments, total payments, and the remaining balance for each period.Assemble the table header that appears in the range E5:I5.Assemble the values that appear in the total row in E4:I4.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. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |