XIRR Formula That Recognizes Change of Group

**Chove** · 03-20-2023, 04:48 PM

Hello,

I have a series of investments that I'm trying to quickly calculate IRRs for. I'm wondering if there's a way to write a formula that will calculate based on a change in Column A.

I need it to calculate XIRR for all 'A' values, then 'B', then 'C', etc. Right now I have to do this manually for hundreds of lines.

A	1/1/20	-300
A	2/3/21	-400
A	12/31/22	500
B	1/1/20	-300
B	2/3/21	-400
B	3/5/21	-500
B	6/20/21	-700
B	1/15/22	-50
B	6/15/22	-150
B	12/31/22	2000
C	1/1/20	-300
C	12/31/22	700
D	1/1/20	-300
D	3/5/21	-60
D	12/31/22	1500
E	1/1/20	-300
E	2/3/21	-800
E	3/5/21	-900
E	6/20/21	-150
E	1/15/22	-200
E	6/15/22	-70
E	8/3/22	-200
E	12/31/22	800
F	1/1/20	-300
F	12/31/22	500

Thank you!

**FlameRetired** · 03-20-2023, 05:51 PM

Welcome to the forum Chove.

Financial functions aren't my long suit but I believe this is correct. Copy and paste this formula into D1 and copy down.

Formula:

Please Login or Register  to view this content.

There is a shorter way to do this but it is volatile. If the "hundreds of lines" exceeds 1000 this might cause your calculations to slow down.

Formula:

Please Login or Register  to view this content.

Also I am not familiar with Mac product numbers and functions available, but check and see if the FILTER function will work on your Mac. If it will this can be simpler still.

Formula:

Please Login or Register  to view this content.

**FlameRetired** · 03-23-2023, 07:45 PM

Another way:
With unique column A labels in H this in I1 and copied down.

Formula:

Please Login or Register  to view this content.

XIRR Formula That Recognizes Change of Group

LinkBack

Thread Tools

Rate This Thread

Display

XIRR Formula That Recognizes Change of Group

Re: XIRR Formula That Recognizes Change of Group

Re: XIRR Formula That Recognizes Change of Group

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Formula that recognizes another cell

Formula that recognizes change in field

[SOLVED] If formula that recognizes #N/A

Formula that recognizes duplicates

Formula that recognizes duplicates

Formula that recognizes duplicates

[SOLVED] Formula that recognizes duplicates

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions