ExcelUser18
New Member
- Joined
- May 3, 2017
- Messages
- 40
I have data set below where the first date is cell G33 and its a range from G33:X33. The first cashflow is cell G40 and the range is from G40:X40. Not shown below is a date of reference in Cell F42 that I need to change in order to hopefully make the formula dynamic but ultimately the date in cell F42 should be updated so as to reference it in range G33:X33 and calculate the XIRR with the corresponding cashflows. Note that while I'm showing 18 months here (F42 = 9/1/2027), if I changed the date to 12/1/2026, all the columns would hide (i.e. if cell is greater than 12/1/2026, "") and there would be positive cash flow in the same column which help calculate XIRR on its own.
I checked ChatGPT and my companies internal chatbot and was given 2 different formulas below, both which are not working. Can anyone give any insight as to why they might not be working? Note if I run a regular XIRR through 18 months, it works fine, calculating to a 25.3% result. But when folding in the two formulas below, it does not work. I'm not married to these formulas so I can use something else if its easier and does what I need.
ChatGPT:
Formula: XIRR(OFFSET(G40, 0, MATCH(F42, G33:X33, 0)-1, 1, COUNTA(G40:X40)-MATCH(F42, G33:X33, 0)+1), OFFSET(G33, 0, MATCH(F42, G33:X33, 0)-1, 1, COUNTA(G33:X33)-MATCH(F42, G33:X33, 0)+1))
Error: #N/A
Work Chatbot:
Formula: XIRR(OFFSET($G$40, 0, MATCH($F$42, $G$33:$X$33, 0)-1, 1, COUNT($G$33:$X$33)), OFFSET($G$40, 0, MATCH($F$42, $G$33:$X$33, 0)-1, 1, COUNT($G$33:$X$33)))
Error: #NUM!
I checked ChatGPT and my companies internal chatbot and was given 2 different formulas below, both which are not working. Can anyone give any insight as to why they might not be working? Note if I run a regular XIRR through 18 months, it works fine, calculating to a 25.3% result. But when folding in the two formulas below, it does not work. I'm not married to these formulas so I can use something else if its easier and does what I need.
ChatGPT:
Formula: XIRR(OFFSET(G40, 0, MATCH(F42, G33:X33, 0)-1, 1, COUNTA(G40:X40)-MATCH(F42, G33:X33, 0)+1), OFFSET(G33, 0, MATCH(F42, G33:X33, 0)-1, 1, COUNTA(G33:X33)-MATCH(F42, G33:X33, 0)+1))
Error: #N/A
Work Chatbot:
Formula: XIRR(OFFSET($G$40, 0, MATCH($F$42, $G$33:$X$33, 0)-1, 1, COUNT($G$33:$X$33)), OFFSET($G$40, 0, MATCH($F$42, $G$33:$X$33, 0)-1, 1, COUNT($G$33:$X$33)))
Error: #NUM!
| Date | 4/1/2026 | 5/1/2026 | 6/1/2026 | 7/1/2026 | 8/1/2026 | 9/1/2026 | 10/1/2026 | 11/1/2026 | 12/1/2026 | 1/1/2027 | 2/1/2027 | 3/1/2027 | 4/1/2027 | 5/1/2027 | 6/1/2027 | 7/1/2027 | 8/1/2027 | 9/1/2027 |
| Year | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 2 | 2 | 2 | 2 | 2 |
| Month | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 |
| Expense #1 | -$11,660,440 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 |
| Expense #2 | -$6,173,198 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 |
| Expense #3 | -$128,172 | -$128,172 | -$128,172 | -$128,172 | -$128,172 | -$128,172 | -$128,172 | -$128,172 | -$128,172 | -$128,172 | -$128,172 | -$128,172 | -$128,172 | -$128,172 | -$128,172 | -$128,172 | -$128,172 | -$128,172 |
| Income #1 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $27,283,506 |
| Total Cashflow | -$17,961,810 | -$128,172 | -$128,172 | -$128,172 | -$128,172 | -$128,172 | -$128,172 | -$128,172 | -$128,172 | -$128,172 | -$128,172 | -$128,172 | -$128,172 | -$128,172 | -$128,172 | -$128,172 | -$128,172 | $27,155,334 |
