Calculating CAGR of Funds

[puneet1011]

Hi,
I'm looking to calculate the CAGR of funds. The base sheet has multiple funds and I need an output which gives the CAGR of all invidually as well as cumulatively.

---

Base File

Date	FundName	Number of Units	NAV	Total Investment	Present NAV	Present Value
22-Apr-12	D	13.24	528.90	7,000.00	492.00	6,511.63
26-Apr-12	F	12.93	77.35	1,000.00	63.70	823.53
28-Apr-12	A	6.91	723.45	5,000.00	842.70	5,824.18
15-May-12	C	61.74	48.59	3,000.00	39.99	2,469.03
12-Jun-12	E	16.17	618.38	10,000.00	580.52	9,387.76
07-Aug-12	G	8.08	742.30	6,000.00	685.20	5,538.46
09-Aug-12	B	18.94	52.80	1,000.00	44.00	833.33
25-Aug-12	D	12.44	401.80	5,000.00	492.00	6,122.45
25-Sep-12	F	100.06	79.95	8,000.00	63.70	6,373.98
27-Sep-12	A	6.99	858.60	6,000.00	842.70	5,888.89
21-Oct-12	C	183.12	54.61	10,000.00	39.99	7,322.83
24-Oct-12	E	9.09	769.82	7,000.00	580.52	5,278.69
25-Oct-12	G	15.30	588.13	9,000.00	685.20	10,485.44
04-Nov-12	B	21.44	46.64	1,000.00	44.00	943.40
26-Nov-12	D	2.22	451.00	1,000.00	492.00	1,090.91

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

---

Output Needed:

Fund	Present NAV	CAGR
A	842.7
B	44
C	39.99
D	492
E	580.52
F	63.7
G	685.2
Total

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Can I use XIRR? How can I use it conditionally?

Thanks,
Puneet

 

[sergioMabres]

Hi Puneet
Yes you can use XIRR but you need to
- Order by Fund and date
- Insert rows for totals for every fund
- Have en investment date end to use in the XIRR range, I used 31-Dec-2012 as an example
- Then have the formulas for XIRR point to every range

Formulas in L14=XIRR(I11:I14,E11:E14,2%)
And L24=XIRR(I2:I23,E2:E23,2%)

I hope it helps it is just one way of the many to do this

Cheers
Sergio