Calculating CAGR of a fund with annual investments

[yashgt]

I invested 10000 in a fund on 20th Dec 2010.
The following year on 20th Dec 2011 I invested another 10000.
Since then I have been investing 10000 every year on 20th Dec.

The fund website shows me the total fund value as 300000.

This is a case of periodic investments. How do I calculate the CAGR. CAGR is the annual rate of interest I am earning considering I had invested in a Fixed Deposit every year.

 

[alansidman]

Book1
	A	B	C	D
1	12/20/2010	10000	10000	12%
2	12/20/2011	10000	20000
3	12/20/2012	10000	30000
4	12/20/2013	10000	40000
5	12/20/2014	10000	50000
6	12/20/2015	10000	60000
7	12/20/2016	10000	70000
8	12/20/2017	10000	80000
9	12/20/2018	10000	90000
10	12/20/2019	10000	100000
11	12/20/2020	10000	110000
12	12/20/2021	10000	120000
13	12/20/2022	10000	130000
14	12/20/2023	10000	140000
15	12/20/2024	10000	150000
16	12/20/2025	10000	160000
17	12/20/2026	10000	170000
18	12/20/2027	10000	180000
19	12/20/2028	10000	190000
20	12/20/2029	10000	200000
21	12/20/2030	10000	210000
22	12/20/2031	10000	220000
23	12/20/2032	10000	230000
24	12/20/2033	10000	240000
25	12/20/2034	10000	250000
26	12/20/2035	10000	260000
27	12/20/2036	10000	270000
28	12/20/2037	10000	280000
29	12/20/2038	10000	290000
30	12/20/2039	10000	300000
Sheet2

Cell Formulas
Range	Formula
D1	D1	=RATE(30,,-C1,C30)
C1:C30	C1	=SUM($B$1:B1)

 

[yashgt]

Please not that the rate of interest is a compounding rate. C2 is 10000 + 10000 + R% of 10000. Hence I think the formula for rate needs to be better.

 

[shg]

	A​	B​	C​	D​
1​			23.59%​	C1: =RATE(9, -10000, 0, 300000, 1)
2​
3​	Date​	Deposit​	Balance​	​
4​	20 Dec 2010​	10,000​	10,000​	C4: =B4
5​	20 Dec 2011​	10,000​	22,359​	C5: =C4 * (1 + $C$1) + B5
6​	20 Dec 2012​	10,000​	37,632​
7​	20 Dec 2013​	10,000​	56,508​
8​	20 Dec 2014​	10,000​	79,836​
9​	20 Dec 2015​	10,000​	108,665​
10​	20 Dec 2016​	10,000​	144,295​
11​	20 Dec 2017​	10,000​	188,328​
12​	20 Dec 2018​	10,000​	242,746​
13​	20 Dec 2019​		300,000​