Excel - XIRR

Asked By Marc

20-Nov-09 08:11 PM

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?

21-Nov-09 01:59 AM

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 do not know about Excel 2007.)

So....



Set up the following:

D1:  =B1
D2:  =C6

E1:  =A1
E2:  =A6

=XIRR(D1:D2, E1:D2)

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.



Set up the following:

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

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

=XIRR(D3:D5, E3:E5)

Format the XIRR cell as Percentage.


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