SOLVED

# Can Excel 365 newspeak simplify the solution?

Bronze Contributor

# 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.

3 Replies
best response confirmed by JoeUser2004 (Bronze Contributor)
Solution

# Re: Can Excel 365 newspeak simplify the solution?

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

# Re: Can Excel 365 newspeak simplify the solution?

For testing in Excel Online:

# Re: Can Excel 365 newspeak simplify the solution?

Thanks a bunch.  I need to study it.  But it looks straight-forward.

1 best response

Accepted Solutions
best response confirmed by JoeUser2004 (Bronze Contributor)
Solution

# Re: Can Excel 365 newspeak simplify the solution?

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