XIRR Non Contiguous Data

[EasterGreen]

I have cash flows in a table for a variety of contracts but initial investment values are separate and the investment dates are also separate, I have tried OFFSET etc but am struggling to make any sense of it.
Example:

B14..Z14 has the initial investment dates
B15..Z15 has the initial investment values

A20..A99 has the cash flow dates
B20..Z99 has the cash flows

Guess would be 10%.

a very simplified way of showing what I am trying to get in cell B10 could be (but doesn't work)
=XIRR(b$15 then b$20..b$99, b$14 then $a$20..$a$99, 10%)
and C10 in the same manner
=XIRR(c$15 then c$20..c$99, c$14 then $a$20..$a$99, 10%)

Thanks!

 

[joeu2004]

I wasn't entirely certain whether a reply to your suggestions should have been a new posting

I participate in several forums, and I get their rules mixed up. In any case, it is not my place to enforce the rules, even if this forum deprecated "hijacking" -- which it now seems that __this__ forum does not.

I used the first of the 2 solutions you suggested and it worked perfectly. That said I don't know why it works.

I extended the range of "normal" cashflows by adding a pseudo-row before and after to the original range B20:B99 and A20:A99.

I treat the pair B19/A19 as a placeholder for the initial balance, and the pair B100/A100 as a placeholder for the ending balance. But we don't actually use B19/A19 and B100/A100. So it does not matter what is in those cells, if anything at all.

When the row number is 19, we use B15 (value) and B14 (date) instead of B19/A19. Likewise, when the row number is 100, we use B17 (value) and B16 (date) instead of B100/A100. For all other rows, we use B20:B99 (value) and A20:A99 (date).

The key is: B20:B99 must be zero or truly empty (no value), which XIRR treats as zero. In contrast (and I should have shown in my example), A20:A99 can have dates that might be irrelevant to the cashflows in any particular column (B:Z). Since the corresponding values in rows B20:B99 are zero, the corresponding dates in column A do not affect the calculation.

For example, we might have the date 5/13/2021 in A31 because it is relevant for the cashflows in column C. But it does not affect the IRR of column B because B31 is empty or zero.

FYI, my use of partial-absolute references (B$19, B$100, etc) was overkill.

The important thing is the absolute references to column A ($A$19, $A$100, etc). That allows us to copy the formula in column B (B13) into columns C:Z (B13:Z13), and still reference the correct dates for "normal" cashflows in A20:A99.

If you have specific questions that I have not addressed, feel free to ask them here.

 

[john53]

I participate in several forums, and I get their rules mixed up. In any case, it is not my place to enforce the rules, even if this forum deprecated "hijacking" -- which it now seems that __this__ forum does not.



I extended the range of "normal" cashflows by adding a pseudo-row before and after to the original range B20:B99 and A20:A99.

I treat the pair B19/A19 as a placeholder for the initial balance, and the pair B100/A100 as a placeholder for the ending balance. But we don't actually use B19/A19 and B100/A100. So it does not matter what is in those cells, if anything at all.

When the row number is 19, we use B15 (value) and B14 (date) instead of B19/A19. Likewise, when the row number is 100, we use B17 (value) and B16 (date) instead of B100/A100. For all other rows, we use B20:B99 (value) and A20:A99 (date).

The key is: B20:B99 must be zero or truly empty (no value), which XIRR treats as zero. In contrast (and I should have shown in my example), A20:A99 can have dates that might be irrelevant to the cashflows in any particular column (B:Z). Since the corresponding values in rows B20:B99 are zero, the corresponding dates in column A do not affect the calculation.

For example, we might have the date 5/13/2021 in A31 because it is relevant for the cashflows in column C. But it does not affect the IRR of column B because B31 is empty or zero.

FYI, my use of partial-absolute references (B$19, B$100, etc) was overkill.

The important thing is the absolute references to column A ($A$19, $A$100, etc). That allows us to copy the formula in column B (B13) into columns C:Z (B13:Z13), and still reference the correct dates for "normal" cashflows in A20:A99.

If you have specific questions that I have not addressed, feel free to ask them here.

Thank you for the additional information and for the opportunity to ask another question.

Going with the first of the two solutions you provided, I looked at the function as having 2 nested if statements. Using the solution offered I would have expected the nested if statements to return the values in B$15 and B$14 and then simply stop. Clearly I'm wrong and I suspect that I need to develop a deeper understanding of how array functions work. Your solution is excellent.

As an aside, I compared the result of the function that you proposed and which I will be using to the result I get by stacking things in the "more traditional" way (i.e. all dates stacked in one column and values in another column) I get a small difference but it is in the 6th or 7th decimal place which doesn't concern me. Out of interest I might check it for all of the calculations in my spreadsheet to ensure that the result is is similar.

Again ... thanks.

 

[joeu2004]

I would have expected the nested if statements to return the values in B$15 and B$14 and then simply stop. Clearly I'm wrong and I suspect that I need to develop a deeper understanding of how array functions work.

Yeah, I realized later that I omitted that "little point" from my explanation. I thought I'd wait to see what you might say.

Each nested-IF expression returns an array of 83 elements, ostensibly selecting from rows 19:101.

The middle 81 elements have the values from rows 20:99 (B20:B99 and A20:A99). The first and last elements are the values from the specified cells. For example: B15 and B14 for the first elements of the first and second parameters; and B17 and B16 for the second elements.

Stylistically, you might think of the first parameter as an array of formulas (highlighting the TRUE value):

{ IF(ROW(B19)=ROW(B19), B15, IF(ROW(B19)=ROW(B100), B17, B19)),
  IF(ROW(B20)=ROW(B19), B15, IF(ROW(B20)=ROW(B100), B17, B20)),
  [....]
  IF(ROW(B99)=ROW(B19), B15, IF(ROW(B99)=ROW(B100), B17, B99)),
  IF(ROW(B100)=ROW(B19), B15, IF(ROW(B100)=ROW(B100), B17, B100)) }

To demonstrate, copy the following example into a new worksheet (click on the Copy icon under "f(x)"):

XIRR discontig last CF.xlsx
	A	B	C	D
12				date #s
13	IRR	10.82%	8.84%
14	Beg bal	5/13/2021	5/13/2021	44329
15		-100,000	-100,000
16	End Bal	5/13/2022	8/13/2021	44421
17		50,000	90,000
18
19	Cash Flows
20	6/13/2021	9,000	10,000	44360
21	7/13/2021	1,200	2,000	44390
22	8/13/2021	1,800		44421
23	9/13/2021	8,200		44452
Sheet3 (2)

Formulas:
C13: { =XIRR(IF(ROW(C19:C100)=ROW(C19),C15,IF(ROW(C19:C100)=ROW(C100),C17,C19:C100)),
IF(ROW($A$19:$A$100)=ROW($A$19),C14,IF(ROW($A$19:$A$100)=ROW($A$100),C16,$A$19:$A$100))) }
D14: =C14
Format D14 as General, and copy D14 into D16 and D20:D23

Now, select C13 and carefully do the following in the Formula Bar:

1. Change 100 to 23, temporarily changing this to arrays of 5 elements just for demonstration purposes. The result:

=XIRR(IF(ROW(C19:C23)=ROW(C19),C15,IF(ROW(C19:C23)=ROW(C23),C17,C19:C23)),
IF(ROW($A$19:$A$23)=ROW($A$19),C14,IF(ROW($A$19:$A$23)=ROW($A$23),C16,$A$19:$A$23)))

2. For each parameter, select the nested IF expression IF(ROW(...)), and press function key f9 to replace it with the constant array. The result:

=XIRR( { -100000; 10000; 2000; 0; 90000 }, { 44329; 44360; 44390; 44421; 44421 } )

The values 44xxx in the second parameter are the internal "serial numbers" for the dates; visually compare with column D.

3. Be sure to press Esc to restore the original formula.

-----

I compared the result of the function that you proposed and which I will be using to the result I get by stacking things in the "more traditional" way (i.e. all dates stacked in one column and values in another column) I get a small difference but it is in the 6th or 7th decimal place

As you say, any small difference should not be a concern.

But there should be no difference, if done correctly, as I demonstrated in G26 and H26 in my first example.

I suspect that when you constructed the "stacked" data, you entered values as they are displayed instead of referencing to the original "unstacked" data with formulas of the form =B20.

The differences might be apparent if you format the original "unstacked" data to display 15 significant digits. (16 decimal places should be more than enough.)

But even then, the infinitesimal differences might not be apparent because Excel formats only up to 15 significant digits, and with calculated values, there might be additional invisible decimal places to the right.

(Caveat: The following might be TMI.)

If you're really curious, you can see the "invisible" differences by entering formulas of the form =SUM(B20, -(B20&"")) into a parallel column, formatted as Scientific.

FYI, we must use SUM instead of =B20-(B20&"") in order to avoid a dubious feature of Excel that might obscure the infinitesimal difference.

The expression B20&"" returns B20 as a string with up to 15 significant digits.

If that does not explain the difference and you are still curious, upload an example Excel file that demonstrates the difference to a file-sharing website (e.g. box.net/files or dropbox.com), and post the public shared URL in a response here.

If this forum does not permit you to post URLs yet, type the URL in the form "app dot box dot com slash s slash 1u2pg3abcyrwvcck4z5eynxc6hxwcge".