XIRR function with 1 value from another cell

[kapvg]

Hi,

I am using Excel 2016 and need help using the XIRR formula for the sample data below:

In the above sample data, I want to calculate XIRR in column D. It would be great if I could build the formula like =XIRR ((B$2:B3), C3), (A$2:A3), A3)) but unfortunately excel does not work as per my whims

I am unable to figure out a proper way to join/concatenate two ranges to use in the formula and the best I was able to build up after searching around for a couple of hours is:

=XIRR(CHOOSE({1,2},OFFSET($B$2,0,0,MATCH($A3,A:A,FALSE)-1,1),VLOOKUP($A3,$A:$C,3,FALSE)),CHOOSE({1,2},OFFSET($A$2,0,0,MATCH($A3,A:A,FALSE)-1,1),VLOOKUP($A3,$A:$A,1,FALSE)))

but this does not give the correct result as it ends up taking the value $315 multiple times and same with the date 30-Jun-2017 (takes it 3 times instead of twice)

Without using VBA, is it possible to have a formula created that would work for all cells D3 to D12 (drag down) ??

 

[joeu2004]

I think you want something like the following:

Book1
	A	B	C	D
1	date	invest	end value	IRR
2	31-Mar-2017	-$100.00	$105.00
3	30-Jun-2017	-$200.00	$315.00	75.17%
4	30-Sep-2017	-$300.00	$620.00	21.14%
5	31-Dec-2017	-$400.00	$861.67	-47.34%
6	31-Mar-2018	-$50.00	$1,119.17	14.23%
7	30-Jun-2018	-$60.00	$1,376.67	36.05%
8	30-Sep-2018	-$350.00	$1,634.17	16.69%
9	31-Dec-2018	-$160.00	$1,891.67	18.93%
10	31-Mar-2019	-$390.00	$2,149.17	7.59%
11	30-Apr-2019	-$250.00	$2,406.67	7.32%
12	31-May-2019	-$90.00	$2,664.17	14.06%
Sheet1

Array-enter (press ctrl+shift+Enter instead of just Enter) the following formula into D3:

=XIRR(IF(ROW($A$2:A3)=ROW(A3), $C$2:C3)+$B$2:B3, $A$2:A3)

Then copy D3 and paste into D4:D12. Format D3:D12 as Percentage.

Excel surrounds the formulas with curly braces in the Formula Bar to indicate that they were array-entered. Do not type the curly braces yourself.

The following demonstrate the correctness as I interpret the cash flows:

Book1
	F	G	H
1		cash flow
2	31-Mar-2017	-$100.00
3	30-Jun-2017	-$200.00
4	30-Sep-2017	-$300.00
5	31-Dec-2017	-$400.00
6	31-Mar-2018	-$50.00
7	30-Jun-2018	-$60.00
8	30-Sep-2018	-$350.00
9	31-Dec-2018	-$160.00
10	31-Mar-2019	-$390.00
11	30-Apr-2019	-$250.00
12	31-May-2019	-$90.00
13	31-May-2019	$2,664.17
14		14.06%	IRR
15		TRUE	G=D?
Sheet1

The formula in G14 is (normally-entered; just press Enter as usual):

=XIRR(G2:G13, F2:F13)

 

[joeu2004]

PS.... I had mean to include the following as another demonstrate of the correctness as I interpret the cash flows. Refer to the first XL2BB snippet in response #2.

xirr multicol vals.xlsx
	I	J	K
1		end value
2	31-Mar-2017	$100.00
3	30-Jun-2017	$303.33
4	30-Sep-2017	$613.56
5	31-Dec-2017	$1,034.25
6	31-Mar-2018	$1,118.36
7	30-Jun-2018	$1,215.66
8	30-Sep-2018	$1,606.65
9	31-Dec-2018	$1,820.83
10	31-Mar-2019	$2,270.87
11	30-Apr-2019	$2,545.56
12	31-May-2019	$2,664.17
13		5.84E-06	J-C
Sheet1

Formulas:
J2: =-B2
J3: =J2*(1+$D$12)^((I3-I2)/365) - B3
Copy J3 into J4:J12

Note that for the purpose of applying the XIRR rate, interest is compounded daily.

 

[kapvg]

Array-enter (press ctrl+shift+Enter instead of just Enter) the following formula into D3:

=XIRR(IF(ROW($A$2:A3)=ROW(A3), $C$2:C3)+$B$2:B3, $A$2:A3)

Many thanks joeu2004 !!!
Your formula works perfectly even with the larger data set that I have. ??

I am able to use the above formula for my data set but, unfortunately as I am a noob at this, I am unable to understand how it works (even after going through the 'Evaluate Formula' multiple times).

Can you please kindly explain how this formula works??

 

[kapvg]

Array-enter (press ctrl+shift+Enter instead of just Enter) the following formula into D3:

=XIRR(IF(ROW($A$2:A3)=ROW(A3), $C$2:C3)+$B$2:B3, $A$2:A3)

To revalidate the above, I have confirmed the results with the ArrayUnion UDF and they work perfectly. But as I am unable to use VBA in this excel file, I will go with the formula from joeu2004 and hope someone can help me understand how the formula works?

 

[joeu2004]

Can you please kindly explain how this formula works??

For any sequence of cash flows starting on the date in $A$2 and ending on a subsequent date (in A3, A4 etc), the cash flows (inflows) are the corresponding values in column B. But for the end date, we treat the ending value in column C as an additional cash flow (outflow, oppositely signed). So for the sequence of cash flows for the dates in $A$2:A12, for example, the last net cash flow is C12+B12.

The syntax $A$2:A3 keeps $A$2 as the date of the first cash flow, while changing the date of the last cash flow to A3, A4 etc as we copy the formula down the column. Similarly for the ranges $B$2:B3 and $C$2:C3.

The IF expression creates an array of the form {FALSE, FALSE,..., C12} (stylistically; not valid Excel syntax) for the sequence of cash flows for the dates in $A$2:A12.

The condition of the IF expression is an array of the form (again, stylistically) {ROW($A$2)=ROW(A12), ROW(A3)=ROW(A12),..., ROW(A12)=ROW(A12)}, which is TRUE only for the last row.

Thus, the first parameter of the XIRR expression is an array of the form (stylistically) {FALSE+$B$2, FALSE+B3,..., C12+B12}. And in that context, Excel treats FALSE as 0. Thus, the first parameter is an array of the form {$B$2, B3,..., C12+B12)}.

The second XIRR parameter can simply be the corresponding dates, $A$2:A12. No special treatment is needed.

So, stylistically, the XIRR expression becomes XIRR({$B$2, B3,..., C12+B12}, $A$2:A12).

-----

The Excel IRR model assumes that cash flows occur at the end of the period (on the corresponding date), and that interest, compounded daily, is calculated based on the previous balance at the end of the previous period (on the previous date).

This is demonstrated by the amortization schedule in response #3.

In J3, the previous balance plus interest compounded daily is calculated by the expression J2*(1+$D$12)^((I3-I2)/365).

And the end-of-the-period cash flow is added by the expression -B3. We negate the inflow because you choose to represent inflow as negative values, and in the amortization schedule, I choose to represent the current balance as a positive value.

Thus, the ending balance for the sequence of cash flows for the dates in $A$2:A12 is the value in J12, which matches the ending value in C12 in the IRR model in response #2.

-----

You might note that the current balances for the intermediate dates (J3:J11) do not match the ending values in C3:C11 in the IRR model.

That is because the IRR is an average rate, which might (and does) differ from the actual growth rate for any particular sub-period.

In that case, we only expect the first and last balances to match the IRR model.

 

[kapvg]

For any sequence of cash flows starting on the date in $A$2 and ending on a subsequent date (in A3, A4 etc), the cash flows (inflows) are the corresponding values in column B. But for the end date, we treat the ending value in column C as an additional cash flow (outflow, oppositely signed). So for the sequence of cash flows for the dates in $A$2:A12, for example, the last net cash flow is C12+B12.

The syntax $A$2:A3 keeps $A$2 as the date of the first cash flow, while changing the date of the last cash flow to A3, A4 etc as we copy the formula down the column. Similarly for the ranges $B$2:B3 and $C$2:C3.

The IF expression creates an array of the form {FALSE, FALSE,..., C12} (stylistically; not valid Excel syntax) for the sequence of cash flows for the dates in $A$2:A12.

The condition of the IF expression is an array of the form (again, stylistically) {ROW($A$2)=ROW(A12), ROW(A3)=ROW(A12),..., ROW(A12)=ROW(A12)}, which is TRUE only for the last row.

Thus, the first parameter of the XIRR expression is an array of the form (stylistically) {FALSE+$B$2, FALSE+B3,..., C12+B12}. And in that context, Excel treats FALSE as 0. Thus, the first parameter is an array of the form {$B$2, B3,..., C12+B12)}.

The second XIRR parameter can simply be the corresponding dates, $A$2:A12. No special treatment is needed.

So, stylistically, the XIRR expression becomes XIRR({$B$2, B3,..., C12+B12}, $A$2:A12).

-----

The Excel IRR model assumes that cash flows occur at the end of the period (on the corresponding date), and that interest, compounded daily, is calculated based on the previous balance at the end of the previous period (on the previous date).

This is demonstrated by the amortization schedule in response #3.

In J3, the previous balance plus interest compounded daily is calculated by the expression J2*(1+$D$12)^((I3-I2)/365).

And the end-of-the-period cash flow is added by the expression -B3. We negate the inflow because you choose to represent inflow as negative values, and in the amortization schedule, I choose to represent the current balance as a positive value.

Thus, the ending balance for the sequence of cash flows for the dates in $A$2:A12 is the value in J12, which matches the ending value in C12 in the IRR model in response #2.

-----

You might note that the current balances for the intermediate dates (J3:J11) do not match the ending values in C3:C11 in the IRR model.

That is because the IRR is an average rate, which might (and does) differ from the actual growth rate for any particular sub-period.

In that case, we only expect the first and last balances to match the IRR model.

Many thanks for this detailed explanation.

I was not aware that we can use the IF expression in this manner, was a nice learning and hopefully will be able to use it in future scenarios.

With XIRR, I was thinking about XIRR in its traditional sense and was thus expecting to have (number of rows +1) values for both the XIRR (values, dates) segments. I did not realize that as the final/last date is the same, one can easily add the last amount in the 'Invested' column with the amount in the 'Value' column to get a net amount for the last date.?

I have tried and can confirm that as long as an amount in the 'Value' column is populated for the final date, this formula works perfectly even if there is no amount in the 'Invested' column.?

 

[joeu2004]

You're welcome.

 

[joeu2004]

=XIRR(IF(ROW($A$2:A3)=ROW(A3), $C$2:C3)+$B$2:B3, $A$2:A3)

Although that is not wrong, it is unnecessarily complicated. It is sufficient to array-enter:

=XIRR(IF(ROW($A$2:A3)=ROW(A3), C3)+$B$2:B3, $A$2:A3)

The point is: since ROW($A$2:A3)=ROW(A3) is true only for the last row of the term $A$2:A3, only the last row of the term $C$2:C3 is used, namely C3.