The following sheet contains multiple items and their respective cash flows in one table. All entries are sorted by item in order to make the XIRR function work.
There is just one problem. The layout isn't very user friendly. A row needs to be inserted for every future entry. This can be problematic in a big table with many cash flows. As shown below, the ideal solution would be to sort by date. For new entries, just find the bottom.
And this is where I'm hitting a wall. Please help with the XIRR formula for this particular scenario. Many thanks.
| XIRR.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Item | Date | Amount | Item | XIRR | ||||
| 2 | X | 5/6/12 | 825 | X | 0.0843 | ||||
| 3 | X | 7/22/12 | -100 | Y | 0.0856 | ||||
| 4 | X | 12/18/15 | 675 | Z | 0.0801 | ||||
| 5 | X | 11/14/19 | 475 | ||||||
| 6 | X | 12/31/20 | -3,000 | ||||||
| 7 | Y | 6/13/13 | 775 | ||||||
| 8 | Y | 2/1/14 | -50 | ||||||
| 9 | Y | 3/28/14 | 600 | ||||||
| 10 | Y | 10/8/18 | 500 | ||||||
| 11 | Y | 12/31/20 | -3,000 | ||||||
| 12 | Z | 8/2/13 | 575 | ||||||
| 13 | Z | 1/1/15 | 850 | ||||||
| 14 | Z | 4/13/16 | -100 | ||||||
| 15 | Z | 9/5/17 | 600 | ||||||
| 16 | Z | 12/31/20 | -3,000 | ||||||
| 17 | |||||||||
| 18 | |||||||||
| 19 | |||||||||
| 20 | |||||||||
Sheet1 | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F2:F4 | F2 | =XIRR(OFFSET(C$2,MATCH(E2,A$2:A$18,0)-1,0,COUNTIF(A$2:A$18,E2)),OFFSET(B$2,MATCH(E2,A$2:A$18,0)-1,0,COUNTIF(A$2:A$18,E2))) |
There is just one problem. The layout isn't very user friendly. A row needs to be inserted for every future entry. This can be problematic in a big table with many cash flows. As shown below, the ideal solution would be to sort by date. For new entries, just find the bottom.
| XIRR.xlsx | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 1 | Item | Date | Amount | Item | Date | Amount | XIRR | ||||
| 2 | X | 5/6/12 | 825 | X | 12/31/20 | -3,000 | ? | ||||
| 3 | X | 7/22/12 | -100 | Y | 12/31/20 | -3,000 | ? | ||||
| 4 | Y | 6/13/13 | 775 | Z | 12/31/20 | -3,000 | ? | ||||
| 5 | Z | 8/2/13 | 575 | Overall | 12/31/20 | -9,000 | ? | ||||
| 6 | Y | 2/1/14 | -50 | ||||||||
| 7 | Y | 3/28/14 | 600 | ||||||||
| 8 | Z | 1/1/15 | 850 | ||||||||
| 9 | X | 12/18/15 | 675 | ||||||||
| 10 | Z | 4/13/16 | -100 | ||||||||
| 11 | Z | 9/5/17 | 600 | ||||||||
| 12 | Y | 10/8/18 | 500 | ||||||||
| 13 | X | 11/14/19 | 475 | ||||||||
| 14 | |||||||||||
| 15 | |||||||||||
| 16 | |||||||||||
| 17 | |||||||||||
| 18 | |||||||||||
| 19 | |||||||||||
| 20 | |||||||||||
Sheet2 | |||||||||||
And this is where I'm hitting a wall. Please help with the XIRR formula for this particular scenario. Many thanks.
