How to calculate IRR of multiple projects

sky31579

New Member
Joined
Sep 18, 2018
Messages
3
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!

ABCDEF
1DatesProjectsCashflowProjectsIRR
21/1/18A-100A?
31/2/18B-120B?
41/3/18C-50C?
51/4/18D2D?
61/5/18B24
71/6/18C29
81/7/18A21
91/8/18D29
101/9/18B9
111/10/18A1
121/11/18C23
131/12/18D20
141/13/18B25

<tbody>
</tbody>
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Hi,

TRy this :

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

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

ABCDEF
1DatesProjectsCashflowProjectsIRR
201/01/18A-100A74%
301/02/18B-120B-29%
401/03/18C-50C3%
501/04/18D2D#NUM!
601/05/18B24
701/06/18C29
801/07/18A1
901/08/18D29
1001/09/18B9
1101/10/18A300
1201/11/18C23
1301/12/18D20
141/13/18B25

<colgroup><col width="70" span="8" style="width:52pt"> </colgroup><tbody>
</tbody>
 
Upvote 0
(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.
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top