Can Excel 365 newspeak simplify the solution?

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))))

Of course, I need some syntax to limit y=1 to 20.  No idea what that might look like.

The pseudo-LAMBDA expressions are intended to be recursive.

To make it a bit easier, I added a fake CF growth rate of 0% for year 0:

Formula for sum(DCF):

=LET(
  initial, 100000, 
  dcfrate, B1, 
  years, SEQUENCE(21, , 0), 
  lookupyear, F4:I4, 
  lookuprate, F5:I5, 
  cf, SCAN(initial, years, 
    LAMBDA(amount, yr, amount*(1+LOOKUP(yr, lookupyear, lookuprate)))), 
  dcf, cf/(1+dcfrate)^years, 
  SUM(dcf))

And the NPV formula:

=LET(initial, 100000, 
  dcfrate, B1, 
  years, SEQUENCE(20), 
  lookupyear, F4:I4, 
  lookuprate, F5:I5, 
  cf, SCAN(initial, years, 
    LAMBDA(amount, yr, amount*(1+LOOKUP(yr, lookupyear, lookuprate)))), 
  initial+NPV(dcfrate, cf))

=LET( initial, 100000, dcfrate, B1, years, SEQUENCE(21, , 0), lookupyear, F4:I4, lookuprate, F5:I5, cf, SCAN(initial, years, LAMBDA(amount, yr, amount*(1+LOOKUP(yr, lookupyear, lookuprate)))), dcf, cf/(1+dcfrate)^years, SUM(dcf))

=LET(initial, 100000, dcfrate, B1, years, SEQUENCE(20), lookupyear, F4:I4, lookuprate, F5:I5, cf, SCAN(initial, years, LAMBDA(amount, yr, amount*(1+LOOKUP(yr, lookupyear, lookuprate)))), initial+NPV(dcfrate, cf))

Forum Discussion

Can Excel 365 newspeak simplify the solution?

Share

Resources