![](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)
Microsoft 365 and Office | Excel | For home | Windows
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Errata: This replaces a previous incorrect response, which I deleted, but you might have seen.
Andrew wrote:
Column F is where I'm having trouble and should show for just one year. I've extracted the numbers relevant to cell F16 on the right and calculated IRR. The array formula does derive these figures and present them to the XIRR function but it gives the answer of 0%. It must be something to do witht the starting valuation as F14 & 15 work fine. F16 is the first valuation where there is a starting balance.
I will explain the problems you encountered below.
But first, I suggest that you array-enter (press ctrl+shift+Enter instead of just Enter) the following formula into F14, then copy F14 down through F29:
=XIRR(IF(ROW($A$2:A14)=ROW($A$2),
IF(EDATE(A14,-12)<=$A$2, $C$2,
-INDEX($D$14:$D$29,MATCH(EDATE(A14,-12),$A$14:$A$29))),
IF(ROW($A$2:A14)=ROW(A14), D14,
IF((EDATE(A14,-12)<=$A$2:A14)*($A$2:A14<=A14),
$C$2:C14, 0))),
IF(ROW($A$2:A14)=ROW($A$2), MAX($A$2,EDATE(A14,-12)),
$A$2:A14))
Note that there is no need for the helper values in column J.
See the detailed explanation below.
Also, download the file "XIRR calc CORRECTED.xlsx" (click here) [1], and look at F14:F29 in the worksheet "SIA corrected".
[1] https://app.box.com/s/zsaxat0tou570ogyctfqmlkti9yhdiyf
The suggested formula corrects two problems caused by your formula. They can be seen by using the Evaluate Formula to step until the last step before calculation.
- In F19, the formula is effectively (omitting some irrelevant entries):
=XIRR({0;-7200;0;0;-7463;12003},
{"6/29/2007";"5/20/2008";"5/21/2009";0;"10/5/2007";
"10/5/2008"})
The key problem is: the first value is zero. Consequently, even if XIRR worked properly (see #2), the IRR would be calculated from 6/29/2007, not 10/5/2007, the valuation for the beginning of the 12-month window.
Instead, the first value and date should be -7463 and 10/5/2007, the negated valuation corresponding to the beginning of the 12-month window.
- If the first value is zero, XIRR returns the constant 2.98E-9 (approx). There is no good reason for that. It is a defect.
However, if we fix problem #1, we avoid problem #2. The suggested formula does just that.
The suggesed formula passes the following parameters to XIRR.
For the value array:
- For the first row, use the initial investment if it is after 12 months prior to the valuation date; otherwise, use the valuation negated.
- For the last row (valuation date), use the valuation.
- For all other rows, use the investment if the date is between the valuation date and 12 months prior; otherwise, use zero.
For the date array:
- For the first row, use the later of initial investment date or 12 months prior to the valuation date.
- For all other rows, use the investment or valuation date.
Note that if the corresponding value is zero, the date has no impact on the IRR calculation, even if it is outside the 12-month window. Also note that if the cell is empty, its "date" and value are zero.
The formula assumes:
- There are no valuations before the initial investment.
- The first valuation is after the initial investment; or if is on the same date, it is equal to the negated initial investment.
- There is a valuation corresponding to 12 months prior to every valuation except when the 12-month window begins before the initial investment.
That is, MATCH always finds an exact match. I use an "approximate" MATCH -- last parameter is zero implicitly -- only for efficiency.