This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I am getting #NUM error on one XIRR calculation for a series of dates (formatted as dates) and dollars invested and returned (formatted as currency). I'm using the same methodology for a few other sets of dates and dollars and XIRR is working.
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Comments have been turned off. Learn more
This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Comments have been turned off. Learn more
Hi. I believe the nature of this particular "bad investment" problem has no solution. It's just the way the mathematics works out.
Here are the numbers:
| Amount invested | 8-Jul-14 | ($150,360) | |
|---|---|---|---|
| Capital call | 15-Feb-18 | ($216) | |
| 12-Nov-18 | ($749) | ||
| #1 | 8-Jul-15 | $768 | |
| #2 | 6-Oct-15 | $3,166.80 | |
| #3 | 1-Dec-15 | $1,804.32 | |
| #4 | 9-Feb-16 | $1,023.36 | |
| #5 | 10-Feb-16 | $870.00 | |
| #6 | 23-Mar-16 | $964.32 | |
| #7 | 21-Apr-16 | $1,020.00 | |
| #8 | 18-May-16 | $876.00 | |
| #9 | 21-Jun-16 | $1,023.24 | |
| #10 | 14-Jul-16 | $1,020.00 | |
| #11 | 16-Aug-16 | $1,020.00 | |
| #12 | 20-Aug-16 | $1,020.00 | |
| #13 | 24-Oct-16 | $1,020.00 | |
| #14 | 6-Dec-16 | $1,020.00 | |
| #15 | 28-Dec-16 | $1,032.00 | |
| #16 | 6-Feb-17 | $1,032.00 | |
| Total distribution | $18,680 | #NUM! |
Formula is
=XIRR(E3:E21,D3:D21)
Date cells are formatted as dates and the dollar amount cells are formatted as currency. The answer should be something like -80%. I tried inputting a guess (-0.8) and that did not work.
We need to see the data and the formula. Also, explain the numbers; use terms like initial balance, deposits, withdrawals, and final balance.
You might get #NUM because you need to provide a "guess" (3rd parameter). But if your data are real-world numbers, that is unlikely.
More likely: you are getting #NUM because your cash flow model is incorrect. Some common mistakes: (1) failing to use signed cash flows; (2) using periodic balances instead of cash flows per se (except for the first and last values); and (3) failing to include the final balance in the last value.