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


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

---

Output Needed:


[TABLE="width: 188"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="align: center"]Fund[/TD]
[TD="align: center"]Present NAV[/TD]
[TD="align: center"]CAGR[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]842.7[/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]44[/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]C[/TD]
[TD="align: center"]39.99[/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]D[/TD]
[TD="align: center"]492[/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]E[/TD]
[TD="align: center"]580.52[/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]F[/TD]
[TD="align: center"]63.7[/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]G[/TD]
[TD="align: center"]685.2[/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="colspan: 2, align: center"]Total[/TD]
[TD="align: center"] [/TD]
[/TR]
</tbody>[/TABLE]

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