Calculate running XIRR for specific account

[kapvg]

Hi,

Need help with the below issue.
I have a dataset with multiple investment accounts for which I want to calculate the XIRR on a recurring/ongoing basis. Sample data for 2 accounts below

Account 1

Book1
	B	C	D	E	F	G	H	I
2	Account	Date	Particulars	Employee Contribution ($)	Employer's Contribution ($)	Total ($)	Total Fund Value	XIRR
3	Account-1XY	18-Jan-2018	By Contribution	$840.40	$0.00	$840.40
4	Account-1XY	31-Mar-2018	Closing Balance				$804.16	-20.02%
5	Account-1XY	3-Apr-2018	By Contribution	$5,976.40	$0.00	$5,976.40
6	Account-1XY	29-Jun-2018	Closing Balance				$6,862.69	2.58%
7	Account-1XY	28-Sep-2018	Closing Balance				$6,944.00	3.67%
8	Account-1XY	31-Dec-2018	Closing Balance				$7,023.85	3.96%
9	Account-1XY	31-Mar-2019	Closing Balance				$7,341.50	7.56%
10	Account-1XY	28-Jun-2019	Closing Balance				$7,392.99	6.65%
11	Account-1XY	30-Sep-2019	Closing Balance				$7,288.55	4.50%
12	Account-1XY	31-Dec-2019	Closing Balance				$7,610.96	6.42%
13	Account-1XY	24-Feb-2020	By Contribution	$4,976.40	$0.00	$4,976.40
14	Account-1XY	18-Mar-2020	By Voluntary Contributions	$25,000.00	$0.00	$25,000.00
15	Account-1XY	31-Mar-2020	Closing Balance				$35,550.37	-8.51%
16	Account-1XY	30-Jun-2020	Closing Balance				$40,585.88	14.94%
17	Account-1XY	30-Sep-2020	Closing Balance				$43,211.20	18.12%
18	Account-1XY	31-Dec-2020	Closing Balance				$51,260.76	30.33%
19	Account-1XY	23-Mar-2021	By Voluntary Contributions	$1,500.00	$0.00	$1,500.00
20	Account-1XY	31-Mar-2021	Closing Balance				$54,652.75	27.95%
sample data

Cell Formulas
Range	Formula
I3:I20	I3	=IFERROR(XIRR(IF(ROW($C$3:C3)=ROW(C3), -1*($H$3:H3))+$G$3:G3, $C$3:C3),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Account 2

Book1
	K	L	M	N	O	P	Q	R
2	Account	Date	Particulars	Employee Contribution ($)	Employer's Contribution ($)	Total ($)	Total Fund Value	XIRR
3	Account-2AB	18-Jan-2018	Tier-2 Contribution	$2,076.40	$0.00	$2,076.40
4	Account-2AB	31-Mar-2018	Closing Balance				$1,970.11	-23.39%
5	Account-2AB	3-Apr-2018	Tier-2 Contribution	$4,976.40	$0.00	$4,976.40
6	Account-2AB	29-Jun-2018	Closing Balance				$6,926.56	-5.87%
7	Account-2AB	28-Sep-2018	Closing Balance				$7,085.45	0.85%
8	Account-2AB	31-Dec-2018	Closing Balance				$7,172.04	2.10%
9	Account-2AB	31-Mar-2019	Closing Balance				$7,579.57	7.08%
10	Account-2AB	28-Jun-2019	Closing Balance				$7,765.43	7.71%
11	Account-2AB	30-Sep-2019	Closing Balance				$7,751.81	6.27%
12	Account-2AB	31-Dec-2019	Closing Balance				$8,066.27	7.72%
13	Account-2AB	24-Feb-2020	Tier-2 Contribution	$4,976.40	$0.00	$4,976.40
14	Account-2AB	31-Mar-2020	Closing Balance				$10,336.73	-12.01%
15	Account-2AB	30-Jun-2020	Closing Balance				$11,917.06	-0.63%
16	Account-2AB	30-Sep-2020	Closing Balance				$12,680.75	3.04%
17	Account-2AB	31-Dec-2020	Closing Balance				$14,861.62	10.88%
18	Account-2AB	23-Mar-2021	Tier-2 Contribution	$1,500.00	$0.00	$1,500.00
19	Account-2AB	31-Mar-2021	Closing Balance				$17,054.77	11.83%
sample data

Cell Formulas
Range	Formula
R3:R19	R3	=IFERROR(XIRR(IF(ROW($L$3:L3)=ROW(L3), -1*($Q$3:Q3))+$P$3:P3, $L$3:L3),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

The XIRR formula used is based on help from XIRR function with 1 value from another cell and it works perfectly when used for a single account.

Without using VBA, I want to combine all the accounts into a single table that can be filtered/sorted as per the user's choice and I am wondering if the XIRR formula can be modified so as to return the correct XIRR for a specific account (i.e. Account1 or Account2) even when used in the combined dataset.
I saw the post: Calculate XIRR for a specific "item" from a table that's sorted by date but given my limited expertise I am unable to utilize/modify that formula for the above scenario

Any help would be greatly appreciated.

~kg

 

[joeu2004]

The XIRR formula used is based on help from XIRR function with 1 value from another cell and it works perfectly when used for a single account.

Looking back, I see that I over-complicated the formula. For your purposes, the following simplified formula can be array-entered:

=IF(H3="", "", XIRR(IF(ROW($C$3:C3)=ROW(C3), -H3)+$G$3:G3, $C$3:C3))

The point is: since ROW($C$3:C3)=ROW(C3) is true only for the last row of the term $C$3:C3, on the last row of the term $H$3:H3 is used, namely H3.

-----

I want to combine all the accounts into a single table that can be filtered/sorted as per the user's choice and I am wondering if the XIRR formula can be modified so as to return the correct XIRR for a specific account (i.e. Account1 or Account2) even when used in the combined dataset. I saw the post: Calculate XIRR for a specific "item" from a table that's sorted by date

First, are you interested in a solution based on DRSteele's suggestion in the cited earlier thread, or based on my suggestion?

-----

Second, when you say ``I want to combine [...] into a single table``, do you mean that you will __manually__ combine all data into a single table?

Or do you mean that you want the __formula__ to treat your separate tables as one combined table automagically?(!)

BTW, it is not necessary for the data to be sorted, except that for each account, in would be convenient if the first transaction appears before all of the other transactions for that account.

The XIRR function does not require data to be ordered by date, except that the first date is treated as the "present value" date. It must be earliest cash flow.

-----

Finally, please provide an example of the combined tables, __and__ how you will provide "a specific account", __and__ what you mean by "the" correct XIRR.

In the example in the cited earlier thread, the "specific account" was in a cell that is separate from the data table, and I relied on a parallel helper cell to simplify the XIRR formula.

Moreover, only one XIRR is calculated for each "specific account", namely the XIRR for all cash flows related to the "specific account".

In contrast, in your original examples, you are calculating a "running" XIRR for __each__ closing balance, on an "ongoing basis", as you write. That is, it is not "the" single correct XIRR; instead, it is __multiple__ XIRRs for each account.

Also, would all the data for each account be together in the combined table, effectively concatenating each separate table?

Or would cash flows for different accounts be interleaved, perhaps ordered by date, not by account?

 

[kapvg]

Looking back, I see that I over-complicated the formula. For your purposes, the following simplified formula can be array-entered:

=IF(H3="", "", XIRR(IF(ROW($C$3:C3)=ROW(C3), -H3)+$G$3:G3, $C$3:C3))

The point is: since ROW($C$3:C3)=ROW(C3) is true only for the last row of the term $C$3:C3, on the last row of the term $H$3:H3 is used, namely H3.

-----

Thanks @joeu2004 . The above makes sense and I believe it will help speed up the overall formula calculation ?

First, are you interested in a solution based on DRSteele's suggestion in the cited earlier thread, or based on my suggestion?

Apologies, I should have specified this in my original post. I am interested in expanding/using the solution that you had suggested in the post: Calculate XIRR for a specific "item" from a table that's sorted by date (i.e. post #3) as I don't want the solution to be limited only to Office365.

Second, when you say ``I want to combine [...] into a single table``, do you mean that you will __manually__ combine all data into a single table?

Or do you mean that you want the __formula__ to treat your separate tables as one combined table automagically?(!)

BTW, it is not necessary for the data to be sorted, except that for each account, in would be convenient if the first transaction appears before all of the other transactions for that account.

The XIRR function does not require data to be ordered by date, except that the first date is treated as the "present value" date. It must be earliest cash flow.

-----

I have the transactions data (not the XIRR) for each account separately and will manually combine them in a single table. The XIRR is to be calculated via a formula and I want the XIRR to be calculated for a specific account (kinda similar to your solution in post #3 but with the added complexity of having the final 'value' entry for XIRR calculation in a different column).

Finally, please provide an example of the combined tables, __and__ how you will provide "a specific account", __and__ what you mean by "the" correct XIRR.

In the example in the cited earlier thread, the "specific account" was in a cell that is separate from the data table, and I relied on a parallel helper cell to simplify the XIRR formula.

Moreover, only one XIRR is calculated for each "specific account", namely the XIRR for all cash flows related to the "specific account".

In contrast, in your original examples, you are calculating a "running" XIRR for __each__ closing balance, on an "ongoing basis", as you write. That is, it is not "the" single correct XIRR; instead, it is __multiple__ XIRRs for each account.

Also, would all the data for each account be together in the combined table, effectively concatenating each separate table?

Or would cash flows for different accounts be interleaved, perhaps ordered by date, not by account?

Book2
	B	C	D	E	F	G	H	I	J
2	Account	Date	Particulars	Employee Contribution ($)	Employer's Contribution ($)	Total ($)	Total Fund Value	XIRR	Correct XIRR
3	Account-1XY	18-Jan-2018	By Contribution	$840.40	$0.00	$840.40
4	Account-2AB	18-Jan-2018	Tier-2 Contribution	$2,076.40	$0.00	$2,076.40
5	Account-1XY	31-Mar-2018	Closing Balance				$804.16	0.00%	-20.02%
6	Account-2AB	31-Mar-2018	Closing Balance				$1,970.11	-86.32%	-23.39%
7	Account-1XY	3-Apr-2018	By Contribution	$5,976.40	$0.00	$5,976.40
8	Account-2AB	3-Apr-2018	Tier-2 Contribution	$4,976.40	$0.00	$4,976.40
9	Account-1XY	29-Jun-2018	Closing Balance				$6,862.69	0.00%	2.58%
10	Account-2AB	29-Jun-2018	Closing Balance				$6,926.56	0.00%	-5.87%
11	Account-1XY	28-Sep-2018	Closing Balance				$6,944.00	0.00%	3.67%
12	Account-2AB	28-Sep-2018	Closing Balance				$7,085.45	0.00%	0.85%
13	Account-1XY	31-Dec-2018	Closing Balance				$7,023.85	0.00%	3.96%
14	Account-2AB	31-Dec-2018	Closing Balance				$7,172.04	0.00%	2.10%
15	Account-1XY	31-Mar-2019	Closing Balance				$7,341.50	0.00%	7.56%
16	Account-2AB	31-Mar-2019	Closing Balance				$7,579.57	0.00%	7.08%
17	Account-1XY	28-Jun-2019	Closing Balance				$7,392.99	0.00%	6.65%
18	Account-2AB	28-Jun-2019	Closing Balance				$7,765.43	0.00%	7.71%
19	Account-1XY	30-Sep-2019	Closing Balance				$7,288.55	0.00%	4.50%
20	Account-2AB	30-Sep-2019	Closing Balance				$7,751.81	0.00%	6.27%
21	Account-1XY	31-Dec-2019	Closing Balance				$7,610.96	0.00%	6.42%
22	Account-2AB	31-Dec-2019	Closing Balance				$8,066.27	0.00%	7.72%
23	Account-1XY	24-Feb-2020	By Contribution	$4,976.40	$0.00	$4,976.40
24	Account-2AB	24-Feb-2020	Tier-2 Contribution	$4,976.40	$0.00	$4,976.40
25	Account-1XY	18-Mar-2020	By Voluntary Contributions	$25,000.00	$0.00	$25,000.00
26	Account-1XY	31-Mar-2020	Closing Balance				$35,550.37	0.00%	-8.51%
27	Account-2AB	31-Mar-2020	Closing Balance				$10,336.73	0.00%	-12.01%
28	Account-1XY	30-Jun-2020	Closing Balance				$40,585.88	0.00%	14.94%
29	Account-2AB	30-Jun-2020	Closing Balance				$11,917.06	0.00%	-0.63%
30	Account-1XY	30-Sep-2020	Closing Balance				$43,211.20	0.00%	18.12%
31	Account-2AB	30-Sep-2020	Closing Balance				$12,680.75	0.00%	3.04%
32	Account-1XY	31-Dec-2020	Closing Balance				$51,260.76	3.58%	30.33%
33	Account-2AB	31-Dec-2020	Closing Balance				$14,861.62	0.00%	10.88%
34	Account-1XY	23-Mar-2021	By Voluntary Contributions	$1,500.00	$0.00	$1,500.00
35	Account-2AB	23-Mar-2021	Tier-2 Contribution	$1,500.00	$0.00	$1,500.00
36	Account-1XY	31-Mar-2021	Closing Balance				$54,652.75	3.51%	27.95%
37	Account-2AB	31-Mar-2021	Closing Balance				$17,054.77	0.00%	11.83%
sample data (2)

Cell Formulas
Range	Formula
I3:I37	I3	=IFERROR(XIRR(IF(ROW($C$3:C3)=ROW(C3), -H3)+$G$3:G3, $C$3:C3),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

I hope the above example helps clarify what I am trying to achieve.
I am trying to get a 'running' XIRR specific to each account...I have added another column (column J) with the XIRR data pasted as values (from both source tables) before sorting the combined dataset by date. Essentially, Column J is what I am trying to achieve via a formula that I can drag down.

 

[joeu2004]

I hope the above example helps clarify what I am trying to achieve.

Perfectly! Must be brief; in a rush. Sorry.

Enter the following formula in K3 and copy down the column:

=MATCH(B3,B:B,0)

Array-enter the following formula into I3 and copy down the column:

=IF(H3="", "",
XIRR(IF(INDEX(B:B, K3):B3=B3, IF(ROW(INDEX(B:B, K3):B3)=ROW(B3), -H3) + INDEX(G:G, K3):G3, 0), INDEX(C:C, K3):C3))

If you do not want the "helper" values in column K, replace each instance of K3 with the MATCH(B3,B:B,0) in the second formula.

 

[kapvg]

Perfectly! Must be brief; in a rush. Sorry.

Enter the following formula in K3 and copy down the column:

=MATCH(B3,B:B,0)

Array-enter the following formula into I3 and copy down the column:

=IF(H3="", "",
XIRR(IF(INDEX(B:B, K3):B3=B3, IF(ROW(INDEX(B:B, K3):B3)=ROW(B3), -H3) + INDEX(G:G, K3):G3, 0), INDEX(C:C, K3):C3))

If you do not want the "helper" values in column K, replace each instance of K3 with the MATCH(B3,B:B,0) in the second formula.

You are awesome!!!?

Many thanks!!!, your solution works perfectly and I am even able to understand how it works ?

 

[MikeH2]

I am trying to do the same thing - calculate a rolling XIRR for an investment portfolio using 2 tables (one of transactions and the other consisting of monthly values for each stock in the portfolio).
I figure I can use Power Query to merge the two tables.
This solution is quite elegant, but I'm trying to figure out how to do the same thing only using Power Pivot/DAX instead.
Any help would be appreciated.
Thanks