Xirr - Calculation with Criteria

Good Afternoon,

I need to calculate the XIRR at specific points in time, but there are other criteria to consider. Please see the attached spreadsheet.

The calculation needs to include: ALL Dates, ALL Drawdowns up to each Valuation point AND only the corresponding Valuation. I've highlighted the relevant cells Yellow for this example.

I have included actual data and a working example. If you require further info, please let me know.

XIRR() does not tolerate non-numeric values in its input ranges very well, if at all, so I would usually solve this kind of problem by completely removing the undesired values from the input range. I think a lot will depend on how much automation is required.

1) If this is a one time or rare task, I would probably do it manually exactly like you have done it here -- make a copy of the list and delete the appropriate rows.
2) If I want a little bit more automation, I might set up an advanced filter (https://www.contextures.com/xladvfilter01.html ) that will filter the list for all drawdown entries before a specified date and the one valuation entry for that date, and have advanced filter copy the filtered list to another range. My XIRR() function is then set up to use the output ranges for the advanced filter. I would still need to manually call advanced filter for each set, but that should be minimal effort.
3) If I decide that I am doing this frequently enough to justify the programming investment, then I work on either a VBA procedure or formulas that perform the same tasks as 2 without user input. It might look something like this solution I proposed to another user: https://www.excelforum.com/excel-for...uous-data.html

How do you want to proceed?

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

My apologies. Thread title updated on original post.

Hi,

Perhaps adding a few more manually calculated expected results to your workbook would be useful.

Regards

@carlbrianhadi.... The following works by coincidence (explained below) with your example.

Array-enter (press ctrl+shift+Enter instead of just Enter) the following formula into D4, then copy D4 and paste into D5:D23:

Please Login or Register  to view this content.

It works only for your example and only by coincidence because your example requires a "guess" parameter (-1%) in order to coerce XIRR to return a reasonable result. For some other examples, a "guess" of -1% might not work.

Also, we use IFERROR to mask a #NUM error in D5. That #NUM error is reasonable since a mathematical calculation of the IRR is (0/126000)^35 - 1 = 0^35 - 1 = -100%. Apparently, Excel XIRR does not recognize the special case. And in general, the IRR cannot be -100%, since NPV = 126000/(1-100%)^0 + 0/(1-100%)^1 is not computable.

But the use of IFERROR might mask other #NUM and #DIV/0 errors, which normally requires a (different) "guess" parameter to remedy the error, if that is even possible.

It should also be noted that Excel XIRR sometimes returns bogus numerical results, which would go undetected. In particular, XIRR might return about +/-2.98E-09, which appears to be zero unless you format the cell as Scientific. In my experience, that particular result should be interpreted the same as #NUM or #DIV/0, namely: we should provide a (different) "guess" parameter.

The best way to detect bogus numerical results is calculate the NPV and ensure that it is "relatively close to zero". That is purposely vague, to imply that the test is fraught with error. Moreover, Excel XNPV does not allow negative discount rates; a defect, IMHO. We would need to use a SUMPRODUCT expression to calculate the (X)NPV ourselves. Ostensibly, that formula would be (X1 = result of XIRR):

=SUMPRODUCT(C4:C23/(1+X1)^((A4:A23-A4)/365))

But of course, that needs to be embellished and array-entered in a manner similar to the XIRR formula above.