I am trying to build a quick and dirty way of calculating the IRR for a series of share investments.

Data
A B C
1 Share1 -1000 1/1/2012 buy
2 Share1 50 1/6/2012 dividend
3 Share1 900 1/5/2014 current value
4 Share2 -2000 4/2/2012 buy
5 Share2 60 9/9/2012 div
6 Share2 70 1/1/1013 div
7 Share2 2500 1/5/2014 current value
8 Share3 …

I copy this table of data from a query in an access database
I paste it into excel
I want the IRR for investment in each share
I do not know how many cash movements will be involved with any share



This works
IRR = XIRR(B4:B7, C4:C7, 8)
I can manually retype the range for each share1, then share2, then ...


I would like to save some of this typing

I tried the following - but it gave an error

RowFirst = 4
RowLast = 7
IRR = XIRR(B[RowFirst]:B[RowLast], C[RowFirst]:C[RowLast], 8)

Is there an easy way for me to define the cashflow range and the date range for each company one after the other

Could I use visual basic code?
From within VB there seems to only be the IRR function which is for periodic cash movements
From the spreadsheet there is XIRR that handles a series of dates and cash movements

Using ms Excel 2007

You can use VBA, and it may work better if you do, but it's not absolutely necessary. In fact, after a little playing around I managed to make the formula solution work with just one helping column, which surprised me; I thought it would take more. See the attached.

For the cost of just one helping column I'd probably stick with that. But you can write your own worksheet function, if you'd prefer. Sometimes they're fun, but they do take longer to run so if you have more than a few hundred of these, you may decide to stick with the formula.

Attached Files

x.xlsx (9.8 KB, 9 views)

Thanks. That is brilliant.
I now feed in the "Order" of the cash movements within each investment: 1, 2, 3 etc
This generates the offsets that you need for your formula = 1 - Order = 0, -1, -2, etc
For some unknown reason this also fixed a problem I was having with XIRR
XIRR had been refusing to give negative internal rates of return. Now it is fine.
Thanks Problem solved.