I’m trying to help someone in another forum.  Can we use Excel 365 newspeak to simplify the implementation?

Unfortunately, I don’t speak Excel 365. I thought I might use this problem to learn by example. And when I say “Excel 365”, I mean to include recent versions of Excel that have the same features — Excel 2019 and later?

The following image demonstrates how to calculate the discounted cash flow of increasing cash flows.

Formulas:
B4: 100000
B5 (copy down): =B4 * (1 + LOOKUP(A5, $F$4:$H$4, $F$5:$H$5))
C4 (copy down): =B4 / (1 + $B$1)^A4
C26: =SUM(C4:C24)
C27: =B4 + NPV(B1, B5:B24)

Can we eschew the DCF table and calculate sum(DCF) and/or npv(CF) using Excel 365 newspeak?

In pseudo-code, the formulas might take the following forms:

sum(DCF):
=let(y=0, cf0=100000, cf=cf0,
cf0 + sum(arrayof(lambda(y=y+1, cf=cf*(1+lookup(y, $F$4:$H$4, $F$5:$H$5)), cf / (1+$B$1)^y))))

npv(CF):
=let(y=0, cf0=100000, cf=cf0,
cf0 + npv($B$1, arrayof(lambda(y=y+1, cf=cf*(1+lookup(y, $F$4:$H$4, $F$5:$G$5), cf))))

The pseudo-LAMBDA expressions are intended to be recursive.

​I’m trying to help someone in another forum.  Can we use Excel 365 newspeak to simplify the implementation? Unfortunately, I don’t speak Excel 365. I thought I might use this problem to learn by example. And when I say “Excel 365”, I mean to include recent versions of Excel that have the same features — Excel 2019 and later? The following image demonstrates how to calculate the discounted cash flow of increasing cash flows.  Formulas:
B4: 100000
B5 (copy down): =B4 * (1 + LOOKUP(A5, $F$4:$H$4, $F$5:$H$5))
C4 (copy down): =B4 / (1 + $B$1)^A4
C26: =SUM(C4:C24)
C27: =B4 + NPV(B1, B5:B24)  Can we eschew the DCF table and calculate sum(DCF) and/or npv(CF) using Excel 365 newspeak? In pseudo-code, the formulas might take the following forms: sum(DCF):
=let(y=0, cf0=100000, cf=cf0,
cf0 + sum(arrayof(lambda(y=y+1, cf=cf*(1+lookup(y, $F$4:$H$4, $F$5:$H$5)), cf / (1+$B$1)^y))))

npv(CF):
=let(y=0, cf0=100000, cf=cf0,
cf0 + npv($B$1, arrayof(lambda(y=y+1, cf=cf*(1+lookup(y, $F$4:$H$4, $F$5:$G$5), cf))))  The pseudo-LAMBDA expressions are intended to be recursive.  Read More

​