The #VALUE error arises because the ranges $A1:$A$4 and $B1:$B$4
reference row 1, which contains text that Excel cannot interpret as a number.
Ostensibly, change them to $A
2:$A$4 and $A
2:$A$4
But that just exposes another instance of the #NUM error.
-----
The #NUM errors arise for a different reason for each formula.
With the correction above, the formula in E3 becomes:
=IF($A3="", " - ", XIRR(IF($A2:$A$4=$A3, -($C3-$B3), $B2:$B$4), $A2:$A$4, 0.1))
If we highlight the first parameter and press f9 in the Formula Bar, it becomes:
=IF($A3="", " - ", XIRR(
{ 20; 20; 3390.56 }, $A2:$A$4, 0.1))
(Be sure to press Esc to restore the original formula.)
Note that all of the values are positive. (I'll discuss the reason why below.)
XIRR requires at least one negative and one positive value.
-----
Similarly for E4 and E5, if we highlight the first parameter and press f9 in the Formula Bar, the formulas become:
E4: =IF($A4="", " - ", XIRR(
{ 0; 0; 3390.56 }, $A2:$A$4, 0.1))
E5: =IF($A5="", " - ", XIRR(
{ 0; 3390.56 }, $A3:$A$4, 0.1))
Note there is only one non-zero value.
XIRR requires at least two non-zero values.
And FYI, another error is:
the first cash flow is zero. XIRR does not allow that (for no good reason, IMHO).
But instead of returns a bona fide Excel error,
XIRR returns a bogus numerical result (typically plus or minus 2.98E-9, which is usually displayed misleadingly as zero because of how the XIRR is typically formatted (Percentage, not Scientific).
-----
Finally, the #N/A error arises because the
ranges are comprised of only one row, to wit:
=IF($A6="", " - ", XIRR(IF(
$A4:$A$4=$A6, -($C6-$B6),
$B4:$B$4),
$A4:$A$4, 0.1))
Ostensibly, this is no different from the error in E4 and E5 (only one non-zero value).
But apparently XIRR makes a special case of this situation (undocumented !).
-----
The root cause of some of these errors is:
your formula is fundamentally wrong.
I demonstrated the correct form in your crossposted thread at
https://www.excelforum.com/excel-formulas-and-functions/1392271-xirr-calculation-error.html.
One of the key corrections, in the formula in E3, is to use the conditional expression
ROW($A$2:A3)=
ROW(A3), not $A2:$A$4=$A3, to calculate the final valuation "to date" (i.e. to the current row).
There are two important changes.
1. The use of the ROW function instead of comparing dates (or any value). This is especially important in your design, where you have multiple rows for the same date.
2. "Anchoring" the first row ($A$2), not the last row ($A$4).
-----
Another important change is the calculation of the final valuation "to date" when the condition above is met.
It should be: minus "value @ end month" minus "withdrawal @ end month" plus "deposit @ end month"
assuming that withdrawals are negative cash flows, and deposits and end-of-month values are positive cash flows.
Thus, ostensibly, in the formula in E3, the expression should be
-B3-C3
+D3, not -($C3-$B3), which is effective B3-C3.
-----
But there are other flaws in your design that complicates things.
1. Apparently, your intent is to enter "value @ end month" in a row where the date is the first of the next month. That is confusing. And it complicates the design of the XIRR formula. And you did not accommodate that dubious design correctly.
2. Furthermore, you are not consistent about assigning "the date is the first of the next month" in a row that corresponds to the "value @ end month".
3. Any withdrawal and deposit "@ end month" might not in the same row that corresponds to the "value @ end month".
-----
And finally, I believe there is a typo in row 16. I presume that the date should be Dec 30 or 31, not Dec 20.
-----
I am not willing to completely redesign your application.
If I did, I would make the following suggestions, at a minimum:
1. Ensure that the row that corresponds to the "value @ end month" have the month-end date.
2. Ensure that the sum of all deposits and sum of all withdrawals on the month-end date are included in the one row with the "value @ end month", not in separate rows, which are permissible for all other dates.
3. Calculate the XIRR only in the row for the month-end date.
-----
But even if it were designed correctly (or "more reasonably, IMHO"), you can still expect #NUM errors, at least for some of the earliest end-of-month XIRRs.
(And perhaps plus or minus 2.98E-9 instead of #NUM -- a design flaw, IMHO.)
The reason is: the guess of 10% might not be sufficient for some months close to the date of the first non-zero cash flow.
There really is no good solution for that.
At a minimum, I would suggestion embedding the XIRR calculation in an IFERROR function. That will catch any #NUM error.
But of course, that will not correct it, if that is even possible. And it will not correct any "bogus" return from XIRR, like 2.98E-9.
"The solution is left as an exercise for the student". (wink)