XIRR

[Marc]

10-Oct-07 10000 Goog
20-Nov-08 2000 intc
29-Nov-08 4000 intc


21-Nov-09 goog -20000
21-Nov-09 intc -10000

I have the above data. I want to do a xirr for b1 and b6 with a1 and a6
I also want to the XIRR for b2, b3, c7 and the corresponding dates.

I am trying to calculate my gain on those stocks. How do I do that?

 

[Joe User]

I am trying to calculate my gain on those stocks. How do I do that?

If you want to use XIRR, it would be nice if you could write the formula
=XIRR((B2,B3,C7),(A2,A3,A7)), a form that the IRR function supports.

But the Excel 2003 XIRR does not. XIRR requires contiguous (adjacent) cells
in each range. (I don't know about Excel 2007.)

So....

I want to do a xirr for b1 and b6 with a1 and a6

Set up the following:

D1: =B1
D2: =C6

E1: =A1
E2: =A6

=XIRR(D12, E12)

FYI, for this simple investment, you can get about the same result by the
following:

=(1+RATE(A6-A1, 0, B1, C6))^365 - 1

Format the XIRR and RATE cells as Percentage.

I also want to the XIRR for b2, b3, c7 and the corresponding dates

Set up the following:

D3: =B2
D4: =B3
D5: =C7

E3: =A2
E4: =A3
E5: =A7

=XIRR(D35, E3:E5)

Format the XIRR cell as Percentage.


----- original message -----