Apa formula untuk menghitung tingkat pertumbuhan tahunan majemuk (CAGR) di Excel?

Tingkat pertumbuhan tahunan gabungan (CAGR) menunjukkan tingkat pengembalian investasi selama periode waktu tertentu, yang dinyatakan dalam persentase tahunan. Di bawah ini adalah ikhtisar tentang cara menghitungnya dengan tangan dan dengan menggunakan Microsoft Excel.

Apa ?

Tapi pertama-tama, mari kita definisikan istilah kita. Cara termudah untuk memikirkan CAGR adalah dengan menyadari bahwa selama beberapa tahun, nilai dari sesuatu dapat berubah – mudah-mudahan menjadi lebih baik – tetapi seringkali dengan kecepatan yang tidak merata. CAGR memberikan satu nilai yang menentukan pengembalian untuk seluruh periode pengukuran. Misalnya, jika kami disajikan dengan harga akhir tahun untuk saham seperti: 

  • 2015: $ 100
  • 2016: $ 120
  • 2017: $ 125

Dari akhir tahun 2015 hingga akhir tahun 2016, harga dihargai 20% (dari $ 100 menjadi 120). Dari akhir tahun 2016 hingga akhir tahun 2017, harga terapresiasi sebesar 4,17% (dari $ 120 menjadi $ 125). Berdasarkan tahun ke tahun, tingkat pertumbuhan ini berbeda, tetapi kita dapat menggunakan rumus di bawah ini untuk menemukan tingkat pertumbuhan tunggal untuk seluruh periode waktu.

CAGR membutuhkan tiga input: investasi ini nilai awal, yang nilai berakhir dan periode waktu (dinyatakan dalam tahun). Alat online, termasuk kalkulator CAGR Investopedia  , akan memberikan CAGR saat memasukkan ketiga nilai ini. Rumusnya adalah:

CSEBUAHGR=(EBBB)1n-1where:EB=Ending balanceBB=Beginning balancen=Number of years\ begin {aligned} & CAGR = \ left (\ frac {EB} {BB} \ right) ^ {\ frac {1} {n}} – 1 \\ & \ textbf {di mana:} \\ & EB = \ text { Saldo akhir} \\ & BB = \ text {Saldo awal} \\ & n = \ text {Jumlah tahun} \ end {rata} orang CAGR=(BB

Memasukkan nilai di atas kita mendapatkan [(125/100) ^ (1/2) – 1] untuk CAGR 11,8%. Terlepas dari kenyataan bahwa harga saham naik pada tingkat yang berbeda setiap tahun, tingkat pertumbuhan keseluruhannya dapat didefinisikan sebagai 11,8%.

Tips & Trik Menghitung CAGR

Salah satu kesalahan yang mudah dilakukan dalam menghitung CAGR adalah salah menghitung jangka waktunya. Misalnya, pada contoh di atas, ada tiga tahun kalender. Tetapi karena data disajikan sebagai harga akhir tahun , kami sebenarnya hanya memiliki dua tahun yang lengkap. Itulah mengapa persamaannya menjadi 1/2, bukan 1/3.

Sekarang, katakanlah kita memiliki saham yang data harga tahunannya disajikan dalam persentase, bukan dolar, istilah:

  • 2015: 10%
  • 2016: 15%
  • 2017: -4%

Dalam hal ini, data ditampilkan dari awal tahun, seperti, pengembalian tahunan keseluruhan pada tahun 2015 (10%), pengembalian tahunan seluruhnya pada tahun 2016 (15%), dan seluruh pengembalian tahunan pada tahun 2017 (- 4%). Jadi saat menghitung CAGR, sebenarnya kami akan bekerja dengan jangka waktu tiga tahun.

Kita perlu mengubah persentase ini menjadi nilai awal dan akhir yang sebenarnya. Ini adalah kesempatan yang baik untuk menggunakan spreadsheet, karena mudah untuk menambahkan kolom pembantu untuk mengubah persentase menjadi nilai. 

Menghitung CAGR di Excel

Rumus matematika sama seperti di atas: Anda membutuhkan nilai akhir, nilai awal dan panjang yang diukur dalam tahun. Meskipun Excel memiliki rumus built-in, itu jauh dari ideal, jadi kami akan menjelaskan yang terakhir.

Praktik terbaik pemodelan keuangan memerlukan penghitungan agar transparan dan dapat diaudit. Masalah dengan menumpuk semua kalkulasi ke dalam rumus adalah Anda tidak dapat dengan mudah melihat angka mana yang pergi ke mana, atau angka apa yang dimasukkan pengguna atau di-hardcode. 

Cara untuk menyiapkannya di Excel adalah dengan memiliki semua data dalam satu tabel, lalu memecah perhitungan baris demi baris. Sebagai contoh, mari kita dapatkan tingkat pertumbuhan tahunan gabungan dari penjualan perusahaan selama 10 tahun:

CAGR penjualan untuk dekade tersebut adalah 5,43%.

Situasi yang lebih kompleks muncul ketika periode pengukuran bukan dalam tahun genap. Ini hampir pasti ketika berbicara tentang pengembalian investasi , dibandingkan dengan angka penjualan tahunan. Solusinya adalah mencari tahu total tahun yang diselesaikan, dan menambahkannya ke tahun parsial (disebut tahun rintisan). 

Mari kita ambil angka yang sama, tetapi minta mereka menjadi harga saham:

Pro dan Kontra CAGR

CAGR lebih unggul dari perhitungan lain, seperti pengembalian ratarata , karena memperhitungkan fakta bahwa nilai-nilai majemuk dari waktu ke waktu.

Di sisi bawah, CAGR meredam persepsi volatilitas. Misalnya, Anda memiliki investasi yang mencatat perubahan ini selama tiga tahun:

  • 2015: keuntungan 25%
  • 2016: Kerugian 40%
  • 2017: 54% keuntungan

Itu sebenarnya CAGR 5%, tetapi volatilitas tahun ke tahun dalam pengembalian tersebut sangat besar. Kenyataannya adalah banyak investasi mengalami pasang surut jangka pendek yang signifikan, dan dengan merapikannya, bisa dikatakan, CAGR mungkin memberikan kesan kinerja yang akurat secara numerik, tetapi menyesatkan secara emosional. Ini seperti peta yang menginformasikan dengan benar bahwa tujuan Anda hanya berjarak lima mil, tanpa menunjukkan kondisi jalan yang bergelombang.

CAGR juga dapat dimanipulasi tergantung pada periode pengukuran, yang pada akhirnya (dan seringkali sewenang-wenang) dipilih. CAGR dapat diubah untuk menghindari tahun negatif di pasar saham (seperti 2008), atau untuk memasukkan tahun dengan kinerja yang kuat (seperti 2013).  

Garis bawah

CAGR membantu mengidentifikasi tingkat pengembalian investasi yang stabil selama periode waktu tertentu. Ini mengasumsikan senyawa investasi selama periode waktu yang ditentukan, dan berguna untuk membandingkan investasi dengan karakteristik volatilitas yang serupa.