How to calculate IRR of multiple projects

[sky31579]

I would like to calculate the IRR of multiple different projects, I try to take array of cashflow and dates of different projects, then using XIRR, but XIRR can only calculate continuous data. Below is a simplify sample, I can manually put all the data of one project together, but I can't do that if there are hundreds project. Is there any method to calculate the IRR of different projects? Thanks for help!

[TABLE="width: 502"]
<tbody>[TR]
[TD="width: 71"][/TD]
[TD="width: 76"]A[/TD]
[TD="width: 71"]B[/TD]
[TD="width: 71"]C[/TD]
[TD="width: 71"]D[/TD]
[TD="width: 71"]E[/TD]
[TD="width: 71"]F[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl63, align: center"]Dates[/TD]
[TD="class: xl63, align: center"]Projects[/TD]
[TD="class: xl63, align: center"]Cashflow[/TD]
[TD][/TD]
[TD="class: xl63, align: center"]Projects[/TD]
[TD="class: xl63, align: center"]IRR[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="class: xl64, align: center"]1/1/18[/TD]
[TD="class: xl63, align: center"]A[/TD]
[TD="class: xl63, align: center"]-100[/TD]
[TD][/TD]
[TD="class: xl63, align: center"]A[/TD]
[TD="class: xl63, align: center"]?[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="class: xl64, align: center"]1/2/18[/TD]
[TD="class: xl63, align: center"]B[/TD]
[TD="class: xl63, align: center"]-120[/TD]
[TD][/TD]
[TD="class: xl63, align: center"]B[/TD]
[TD="class: xl63, align: center"]?[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="class: xl64, align: center"]1/3/18[/TD]
[TD="class: xl63, align: center"]C[/TD]
[TD="class: xl63, align: center"]-50[/TD]
[TD][/TD]
[TD="class: xl63, align: center"]C[/TD]
[TD="class: xl63, align: center"]?[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="class: xl64, align: center"]1/4/18[/TD]
[TD="class: xl63, align: center"]D[/TD]
[TD="class: xl63, align: center"]2[/TD]
[TD][/TD]
[TD="class: xl63, align: center"]D[/TD]
[TD="class: xl63, align: center"]?[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="class: xl64, align: center"]1/5/18[/TD]
[TD="class: xl63, align: center"]B[/TD]
[TD="class: xl63, align: center"]24[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="class: xl64, align: center"]1/6/18[/TD]
[TD="class: xl63, align: center"]C[/TD]
[TD="class: xl63, align: center"]29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="class: xl64, align: center"]1/7/18[/TD]
[TD="class: xl63, align: center"]A[/TD]
[TD="class: xl63, align: center"]21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="class: xl64, align: center"]1/8/18[/TD]
[TD="class: xl63, align: center"]D[/TD]
[TD="class: xl63, align: center"]29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="class: xl64, align: center"]1/9/18[/TD]
[TD="class: xl63, align: center"]B[/TD]
[TD="class: xl63, align: center"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="class: xl64, align: center"]1/10/18[/TD]
[TD="class: xl63, align: center"]A[/TD]
[TD="class: xl63, align: center"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="class: xl64, align: center"]1/11/18[/TD]
[TD="class: xl63, align: center"]C[/TD]
[TD="class: xl63, align: center"]23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="class: xl64, align: center"]1/12/18[/TD]
[TD="class: xl63, align: center"]D[/TD]
[TD="class: xl63, align: center"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="class: xl64, align: center"]1/13/18[/TD]
[TD="class: xl63, align: center"]B[/TD]
[TD="class: xl63, align: center"]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[admiral100]

Hi,

TRy this :

An array formula , Ctrl+Shift+Enter NOT just Enter

F2 =IRR(IF(($B$2:$B$14=E2),$C$2:$C$14,""))

[TABLE="width: 560"]
<colgroup><col width="70" span="8" style="width:52pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 70"][/TD]
[TD="class: xl63, width: 70"]A[/TD]
[TD="class: xl63, width: 70"]B[/TD]
[TD="class: xl63, width: 70"]C[/TD]
[TD="class: xl63, width: 70"]D[/TD]
[TD="class: xl63, width: 70"]E[/TD]
[TD="class: xl63, width: 70"]F[/TD]
[TD="class: xl63, width: 70"][/TD]
[/TR]
[TR]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]Dates[/TD]
[TD="class: xl63"]Projects[/TD]
[TD="class: xl63"]Cashflow[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]Projects[/TD]
[TD="class: xl63"]IRR[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]2[/TD]
[TD="class: xl64"]01/01/18[/TD]
[TD="class: xl63"]A[/TD]
[TD="class: xl63"]-100[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]A[/TD]
[TD="class: xl65"]74%[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3[/TD]
[TD="class: xl64"]01/02/18[/TD]
[TD="class: xl63"]B[/TD]
[TD="class: xl63"]-120[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]B[/TD]
[TD="class: xl65"]-29%[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]4[/TD]
[TD="class: xl64"]01/03/18[/TD]
[TD="class: xl63"]C[/TD]
[TD="class: xl63"]-50[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]C[/TD]
[TD="class: xl65"]3%[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]5[/TD]
[TD="class: xl64"]01/04/18[/TD]
[TD="class: xl63"]D[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]D[/TD]
[TD="class: xl63"]#NUM![/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]6[/TD]
[TD="class: xl64"]01/05/18[/TD]
[TD="class: xl63"]B[/TD]
[TD="class: xl63"]24[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]7[/TD]
[TD="class: xl64"]01/06/18[/TD]
[TD="class: xl63"]C[/TD]
[TD="class: xl63"]29[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]8[/TD]
[TD="class: xl64"]01/07/18[/TD]
[TD="class: xl63"]A[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]9[/TD]
[TD="class: xl64"]01/08/18[/TD]
[TD="class: xl63"]D[/TD]
[TD="class: xl63"]29[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]10[/TD]
[TD="class: xl64"]01/09/18[/TD]
[TD="class: xl63"]B[/TD]
[TD="class: xl63"]9[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]11[/TD]
[TD="class: xl64"]01/10/18[/TD]
[TD="class: xl63"]A[/TD]
[TD="class: xl63"]300[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]12[/TD]
[TD="class: xl64"]01/11/18[/TD]
[TD="class: xl63"]C[/TD]
[TD="class: xl63"]23[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]13[/TD]
[TD="class: xl64"]01/12/18[/TD]
[TD="class: xl63"]D[/TD]
[TD="class: xl63"]20[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]14[/TD]
[TD="class: xl63"]1/13/18[/TD]
[TD="class: xl63"]B[/TD]
[TD="class: xl63"]25[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
</tbody>[/TABLE]

 

[joeu2004]

(I assume you have a typo: the value in C5 should be -2 for project D. Right?)

We cannot use Excel IRR because the periods are not consecutive and equal for the cash flows of each project. For example, for project A, the cash flows occur on 1/1, 1/7 and 1/10.

Ostensibly, array-enter (press ctrl+shift+Enter, not just Enter) formulas of the following form into column F (F2, e.g.):

=XIRR(IF(INDEX($B$2:$B$14,G2,1):$B$14=E2, INDEX($C$2:$C$14,G2,1):$C$14, 0), INDEX($A$2:$A$14,G2,1):$A$14)

That assumes that we array-enter formulas of the following form into column G (G2, e.g.):

=MATCH(TRUE, IF($B$2:$B$14=E2, ISNUMBER($A$2:$A$14)), 0)

The MATCH formula determines the first row number that corresponds to the project. Excel XIRR does not behave correctly when the first value/date rows are zero, FALSE or otherwise.

The formulas work if we assume that the dates are in the form d/m/yyyy.

That assumption seems to be incorrect, considering the date associated with the last cash flow: 1/13/2018. But if the dates are of the form m/d/yyyy, Excel XIRR returns #NUM for all but project C, even if we extract the cash flows for each project and use Excel XIRR straight-forwardly. That exacerbates the concerns expressed below.

But I wonder if the cash flows for each project are correct. Be sure that the last cash flow includes the "nominal value"; that is, the remaining value (investment) of the project.

Also, it is risky to use Excel XIRR in this manner. Excel XIRR can easily fail because it requires a "guess" rate (3rd parameter) to help its internal algorithm. Moreover, the Excel XIRR implementation seems to fail unexpectedly quite often, especially with "unusual" cash flows, due to a poor internal design, IMHO.

 

[joeu2004]

Some errata (too late to edit)....

The formulas work if we assume that the dates are in the form d/m/yyyy.
That assumption seems to be incorrect, considering the date associated with the last cash flow: 1/13/2018.

Well, hopefully 1/13/2018 was another of sky31579's typos, and it should be 1/1/2019.

Be sure that the last cash flow includes the "nominal value"; that is, the remaining value (investment) of the project.

That's the "notional" value.

 

[sky31579]

Hi,

Thanks so much for the help!

 

[sky31579]

Hi,

Thanks so much for the help and sorry for the typo&#55357;&#56911;