How to combine a range and cell into a new range in a formula in Excel

[zazexcel]

Hello experts,

I am trying to create a worksheet where I can enter a series of cash flows for two projects as one list and be able to calculate the XIRR for both of them separately.
I tried to search around online but I am not able to understand or able to calculate the right answer to my formula. Hoping some of the experts here can help improve my knowledge in this area.

Column A to:C - show the dates and amount into the different project. I will keep on adding the rows here when there is a new investment.
There would be another column or different tab with the column E to F showing the symbol and current amount

I have used {=XIRR(IF($C$2:$C$10=F2,$B$2:$B$10,0),A2:A10)}.
What I would like to add in my XIRR formula is the current amount of "20000" referencing to Cell F2 in the "values" of XIRR formula and add a today() date in the "dates" of XIRR formula.
1. I would like to combine a range (B2:B10) outflow and cell (F2) current amount into a new range which can be used in a If formula
2. Add a today() date to represent the current amount that I would get if I stop this project.

I spend a lot of hours trying different formula but I keep getting a error. Sorry for asking a noob question but I would like appreciate your expert help here. Thanks.

 

[XOR LX]

Strange coincidence. Almost identical to the very recent post here.

Excel 2013 and later, array formula** in G2:

=XIRR(IF(INDEX(1-MUNIT(1+COUNTIF(C$2:C$10,E2)),,1+COUNTIF(C$2:C$10,E2)),INDEX(B$2:B$10,N(IF(1,MODE.MULT(IF(C$2:C$10=E2,ROW(C$2:C$10)-MIN(ROW(C$2:C$10))+{1,1}))))),F2),IF(INDEX(1-MUNIT(1+COUNTIF(C$2:C$10,E2)),,1+COUNTIF(C$2:C$10,E2)),INDEX(A$2:A$10,N(IF(1,MODE.MULT(IF(C$2:C$10=E2,ROW(C$2:C$10)-MIN(ROW(C$2:C$10))+{1,1}))))),TODAY()))

and copied down.

Regards

 

[Peter Bartholomew]

@XOR LX
I have mixed feelings when it comes to solutions such as the one you quote. On one hand, I cannot but admire the ingenuity and knowledge that has gone into building the solution; on the other, I loath the cryptic nature of spreadsheet cell references and the challenge of teasing out the syntax of nested formulas. The use of MUNIT to generate a Boolean array and MODE.MULT to filter a set of values are ingenious workarounds for functionality missing in traditional Excel.

Going to the other extreme of beta release versions of Office 365:

= LET(
  filteredDates, FILTER(Date, Symbol=@Sym),
  filteredOutflow, FILTER(Outflow, Symbol=@Sym),
  UBound, 1+COUNT(filteredDates),
  idx, SEQUENCE(UBound),
  DatesExtended, IF(idx<UBound, filteredDates, Today),
  OutflowExtended, IF(idx<UBound, filteredOutflow, @CurrentAmount),
  XIRR(OutflowExtended, DatesExtended) )

Buried within the formula is an answer to the OP question. To combine a range and a cell you need to create an index one longer than the range you wish to build on

 = IF(idx<UBound, Dates, Today)

 

[zazexcel]

Thanks @XOR LX for this. I dont really understand this formula.. seems complicated but when I tried to adjust the reference to point at the right date in this formula in my worksheet it works now! Maybe I should really brush up my excel skills..

 

[bosco_yip]

Another traditional formula option without using Excel 2013 MUNIT and MODE.MULT function.

In H2 array formula copied down :

=XIRR(IF({1,0},INDEX(B$2:B$10,MATCH(E2,C$2:C$10,0)):B$10*(INDEX(C$2:C$10,MATCH(E2,C$2:C$10,0)):C$10=E2),F2*(ROW(INDIRECT("1:"&(ROW(C$10)-MATCH(E2,C$2:C$10,0))))=ROW(C$10)-MATCH(E2,C$2:C$10,0))),IF({1,0},INDEX(A$2:A$10,MATCH(E2,C$2:C$10,0)):A$10*(INDEX(C$2:C$10,MATCH(E2,C$2:C$10,0)):C$10=E2),G2*(ROW(INDIRECT("1:"&(ROW(C$10)-MATCH(E2,C$2:C$10,0))))=ROW(C$10)-MATCH(E2,C$2:C$10,0))))

Remark : The criteria current date, I fixed it in 9/6/2020 and put in G2:G3, you may change it to =TODAY()