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

dcf.xlsx11 KB

excel

HansVogelaar

Aug 26, 2024

JoeUser2004

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

Forum Discussion

Can Excel 365 newspeak simplify the solution?

Resources