Snasui Excel Forum

Code: Select all

=LET(
  cf, N(+B4:E9),
  year, --SUBSTITUTE(A4:A9,"Year ",""),
  rate, $H$2,
  HSTACK(
    TRANSPOSE(
      BYCOL(
        cf,
        LAMBDA(col,
          LET(
            acmcf, SCAN(0, col, LAMBDA(x,y,x+y)),
            idx, SUM(--(acmcf<=0)) + 1,
            IF(
              idx > ROWS(col),
              "No Payback",
              INDEX(year, idx) - INDEX(acmcf, idx)/INDEX(col, idx)
            )
          )
        )
      )
    ),
    TRANSPOSE(
      BYCOL(
        cf,
        LAMBDA(col,
          LET(
            disc, col/(1+rate)^year,
            acmdisc, SCAN(0, disc, LAMBDA(x,y,x+y)),
            idxd, SUM(--(acmdisc<=0)) + 1,
            IF(
              idxd > ROWS(col),
              "No DPB",
              INDEX(year, idxd) - INDEX(acmdisc, idxd)/INDEX(disc, idxd)
            )
          )
        )
      )
    )
  )
)