Hi - Dave here.

Happy Friday!

In my last email, I shared a simple workbook to estimate a mortgage payment based on the loan amount, the term, and the annual interest rate.

A smart reader of this newsletter, Matt Hanchett, suggested I take this example further and generate an entire mortgage payment schedule with a single formula like this:

=LET(
loanAmt,C9,
intAnnual,C5,
loanYears,C6,
rate,intAnnual/12,
nper,loanYears*12,
pv,-loanAmt,
pmt,PMT(rate,nper,pv),
pers,SEQUENCE(nper),
ipmts,IPMT(rate,pers,nper,pv),
ppmts,PPMT(rate,pers,nper,pv),
bals,SCAN(loanAmt,ppmts,LAMBDA(x,r,x-r)),
tInterest,SUM(ipmts),
tPrincipal,SUM(ppmts),
tPaid,tInterest+tPrincipal,
VSTACK(
HSTACK(nper,tInterest,tPrincipal,tPaid,""),
HSTACK("Period","Interest","Principal","Total Pmt","Balance"),
HSTACK(pers,ipmts,ppmts,ipmts+ppmts,bals)
))

You can see the result in the worksheet below:



[Download the workbook and read the full explanation]

Matt's formula is such a great example of what can be done with Excel's new formula engine. Although the formula is longer than usual, it works in logical steps and eliminates many other formulas. On top of that, it's fully dynamic. If you change the loan term to 10 years, the entire table is recreated. This formula requires Excel 2021 or later. However, on Sheet2, I've created out a mortgage schedule that will work in older versions of Excel. As you will see, this requires many different formulas. Read the full explanation and download the workbook at the link above.

Excel formulas

We maintain a list of over 500 working formulas here.

If you need more structure, we also offer video training.

Have a great weekend!

Dave

Exceljet
P.O. Box 4804
Salt Lake City, UT 84110

Copyright © 2023 Exceljet, All rights reserved.
You received this email because you are subscribed to our newsletter.
To unsubscribe, click the link below.