CODEWITHSUNDEEP
excelvba
Dhruv ForWeb
Dhruv ForWeb

Reputation: 11

Excel VBA or Formula: XIRR for Multiple Account Multiple Years

My data table is:

My input is the left table consisting investment data of multiple account for multiple years. I want to calculate XIRR for each year for each account.

The rights table is the typical output that I want. However, I am not able to figure out how this can be done with the help of Excel Formula. Or by VBA. can anyone help?

Example:

Example Stackoverflow not allowing image to be uploaded due to not having reputation like 10 or something.

Upvotes: 1

Views: 705

Answers (1)

xidgel
xidgel

Reputation: 3145

Here's one approach. This approach requires that the data are sorted by account and by year. The strategy is (1) find the correct first row of data for each combination of Acc#+YEAR; (2) extract the data from that row to the end of the table; (3) filter out the data that is not associated with that Acc#+YEAR; and (4) calculate XIRR.

To determine the first row of data for each Account/Year combination, you can make a unique tag by concatenating the two together. I made a helper table that has the first rows:

enter image description here

using this formula (CTRL-SHIFT-ENTER):

=MATCH($F5&"_"&G$4,Account&"_"&Year,0)

where $F5 is a cell with an Acc#, G$4 is a cell with a YEAR##, Account is the column of account names, and Year is the column of years. I used an underscore to delimit account names and years; you can probably skip this if your account names and years do not collide with each other.

Now XIRR can be calculated. Here's the result:

enter image description here

Here's the formula (again CTRL-SHIFT-ENTER):

=XIRR(IF(OFFSET($A$2,G5,0,23-G5+1,1)=$K5,IF(OFFSET($B$2,G5,0,23-G5+1,1)=L$4,OFFSET($D$2,G5,0,23-G5+1,1),0),0),OFFSET($C$2,G5,0,23-G5+1,1))

I've used OFFSET to extract sub-arrays that start at the "first rows" (calculated above) and end at the bottom of the data table. (Note these sub-arrays still contain data from multiple accounts/years, so we'll filter in a later step.) In OFFSET($A$2,G5,0,23-G5+1,1)

  • $A$2 is the cell above the account names;
  • we offset G5 rows from the top of the date (G5 is one of the first rows from the helper table);
  • we offset 0 columns;
  • we grab 23-G5+1 rows (23 should be replaced by the total rows you have); and
  • we grab 1 column of data.

The other OFFSETs work the same, except on columns B (YEAR), C (Date) and D (Amount).

The next step is filtering out unwanted data from wrong Acc# or YEAR. This is done using the two nested IF statements. The basic logic is, replace Amount with 0 whenever we have the wrong Acc# or YEAR:

=XIRR(IF(Account=TheAcct,IF(YEAR=TheYear,Amount,0),0),Date)

This works because zero-cash flows don't affect the XIRR calculation.

Instead of the filtering step you could also calculate a last row for each Acc+YEAR. Then you would end up with a formula like:

=XIRR(OFFSET(Amount,FirstRow,0,LastRow-FirstRow+1,0),OFFSET(Date,FirstRow,0,LastRow-FirstRow+1,0))

Hope that helps.

Upvotes: 1

Related Questions