Cara Membuat Rumus CAGR di Excel

Pelajari bagaimana Anda bisa menghitung Tingkat Pertumbuhan Tahunan Majemuk (CAGR) di Excel menggunakan operator atau fungsi Excel yang berbeda.

CAGR adalah singkatan dari Compound Annual Growth Rate, yang mengukur tingkat pertumbuhan tahunan (tingkat kelancaran) investasi setiap tahun selama interval tertentu. Misalnya, Anda membeli saham senilai $100 pada tahun 2011 dan bernilai $400 pada tahun 2021, CAGR akan menjadi tingkat pertumbuhan investasi Anda pada saham tersebut setiap tahun.

Pasar saham bergejolak, pertumbuhan investasi dapat bervariasi dari tahun ke tahun. Pengembalian investasi dapat meningkat atau menurun. CAGR membantu kelancaran pengembalian investasi seperti yang telah tumbuh pada tingkat yang seimbang setiap tahun.

Meskipun tidak ada fungsi CAGR di Excel, ada beberapa cara Anda dapat menghitung CAGR di Excel. Artikel ini menunjukkan cara membuat rumus CAGR yang jelas dan mudah dipahami menggunakan fungsi Excel yang berbeda.

Cara Membuat Tingkat Pertumbuhan Tahunan Majemuk (CAGR) rumus di Excel

Compound Annual Growth Rate (CAGR) sangat membantu untuk bisnis, perencanaan keuangan, pemodelan keuangan, dan analisis investasi. Rumus CAGR menghitung pertumbuhan tahunan suatu investasi yang dapat digunakan untuk membandingkan dengan investasi lainnya.

Untuk menghitung CAGR, Anda memerlukan tiga input utama: nilai awal investasi, nilai akhir, dan jumlah periode (tahun).

rumus CAGR

Sintaks rumus CARG adalah:

CAGR =(Nilai akhir/Nilai awal)1/n - 1

di mana:

  • Nilai akhir – Saldo akhir investasi pada akhir periode investasi.
  • Nilai awal – Saldo awal investasi pada awal periode investasi.
  • n – Jumlah tahun Anda telah berinvestasi.

Menghitung CAGR di Excel

Sekarang setelah Anda mempelajari aritmatika di balik bunga majemuk, mari kita lihat bagaimana Anda dapat menghitung CAGR di Excel. Ada 5 cara membuat rumus Excel untuk menghitung CAGR:

  • Menggunakan Operator Aritmatika
  • Menggunakan fungsi RRI
  • Menggunakan fungsi POWER.
  • Menggunakan fungsi RATE.
  • Menggunakan fungsi IRR.

Menghitung CAGR di Excel Menggunakan Operator

Cara langsung untuk menghitung CAGR adalah dengan menggunakan operator. Gunakan rumus umum di atas untuk menghitung CAGR.

Mari kita asumsikan kita memiliki data penjualan untuk perusahaan tertentu dalam spreadsheet di bawah ini.

Kolom A memiliki tahun-tahun di mana pendapatan diperoleh. Kolom B memiliki pendapatan perusahaan pada tahun yang bersangkutan. Dengan rumus CAGR di excel, Anda dapat menghitung tingkat pertumbuhan tahunan untuk pendapatan.

Masukkan rumus di bawah ini untuk menghitung CAGR dengan metode langsung:

=(B11/B2)^(1/9)-1

Pada contoh di bawah, nilai awal investasi ada di sel B2 dan nilai akhir di sel B11. Jumlah tahun (periode) antara awal dan akhir periode investasi adalah 9. Biasanya setiap periode siklus investasi dimulai satu tahun dan berakhir pada tahun berikutnya, maka periode pertama siklus dalam hal ini adalah tahun 2011. -2012 dan siklus terakhir adalah 2019-2020. Jadi jumlah total tahun yang diinvestasikan adalah '9'

Hasilnya akan dalam angka desimal tidak dalam persentase seperti yang ditunjukkan di atas. Untuk mengubahnya menjadi persentase, buka tab 'Beranda', klik tarik-turun yang bertuliskan 'Umum' di grup Angka, dan pilih opsi '% Persentase'.

Sekarang, kami mendapatkan tingkat pertumbuhan tahunan gabungan yaitu '10,77%' di sel B13. Ini adalah tingkat pertumbuhan tunggal yang dihaluskan untuk seluruh periode waktu.

Menghitung CAGR di Excel Menggunakan Fungsi RRI

Fungsi RRI mengukur tingkat bunga ekuivalen periodik untuk pengembalian investasi atau pinjaman selama periode waktu tertentu.

Tingkat bunga dihitung berdasarkan nilai investasi sekarang dan masa depan, dan durasi.

Sintaksnya:

=RRI(nper,pv,fv)
  • tidak ada – jumlah periode (tahun)
  • pv – Ini menentukan nilai sekarang dari investasi atau pinjaman (sama dengan nilai awal)
  • fv – Ini menentukan nilai masa depan dari investasi atau pinjaman (sama dengan nilai akhir)

Kami memiliki nilai untuk nper di sel A11, pv di sel C2, dan fv di sel C11. Gunakan rumus ini:

=RRI(A11,C2,C11)

CAGR adalah '10,77%', yang ada di sel B13.

Menghitung CAGR di Excel Menggunakan Fungsi POWER

Metode lain yang mudah untuk menghitung tingkat pertumbuhan tahunan gabungan (CAGR) di Excel adalah dengan menggunakan fungsi POWER. Fungsi POWER menggantikan ^ operator dalam fungsi CAGR.

Sintaks fungsi POWER:

= POWER (angka, daya)

Argumen dari fungsi POWER:

  • nomor – Ini adalah angka dasar yang ditemukan dengan membagi nilai akhir (EV) dengan nilai awal (BV) (EV/BV).
  • kekuatan – Ini adalah untuk menaikkan hasil ke eksponen satu dibagi dengan durasi (1/jumlah periode (n)).

Sekarang, argumen didefinisikan dengan cara ini untuk menemukan nilai CAGR:

=POWER(EV/BV,1/n)-1

Mari kita terapkan rumus ke sebuah contoh:

=DAYA(C11/C2,1/A11)-1

Hasil:

Menghitung CAGR di Excel Menggunakan Fungsi RATE

Fungsi rate adalah fungsi lain yang dapat Anda gunakan untuk menemukan CAGR di Excel. Saat Anda melihat sintaks fungsi RATE, mungkin terlihat sedikit rumit dengan 6 argumen tidak kurang, tetapi setelah memahami fungsinya, Anda mungkin lebih suka metode ini untuk menemukan nilai CAGR.

Sintaks fungsi RATE:

=RATE(nper,pmt,pv,[fv],[type],[tebak])

Di mana:

  • tidak ada – Jumlah total periode pembayaran (jangka waktu pinjaman).
  • malam (opsional) – Jumlah pembayaran yang dilakukan di setiap periode.
  • Pv – Ini menentukan nilai sekarang dari pinjaman/investasi (nilai awal (BV))
  • [Fv]– Ini menentukan nilai masa depan dari pinjaman/investasi, pada pembayaran terakhir (nilai akhir (EV))
  • [Jenis] – Ini menentukan kapan pembayaran untuk pinjaman/investasi jatuh tempo, baik 0 atau 1. Argumen 0 berarti pembayaran jatuh tempo pada awal periode dan 1 berarti pembayaran jatuh tempo pada akhir periode (default adalah 0).
  • [Tebakan] - Tebakan Anda tentang tingkat. Jika dihilangkan, defaultnya adalah 10%.

Alasan fungsi RATE memiliki enam argumen adalah karena dapat digunakan untuk banyak perhitungan keuangan lainnya. Tapi kita bisa mengonversi fungsi RATE ke rumus CAGR, dengan hanya tiga argumen ke-1 (nper), ke-3 (pv), dan ke-4 (fv):

=RATE(nper,,-BV,EV)

Karena kami tidak melakukan pembayaran rutin (bulanan, triwulanan, tahunan), kami membiarkan argumen kedua kosong.

Untuk menghitung tingkat pertumbuhan tahunan majemuk menggunakan fungsi RATE, gunakan rumus ini:

=RATE(A11,,-C2,C11)

Jika Anda tidak ingin menghitung jumlah periode secara manual, gunakan fungsi ROW sebagai argumen pertama dari rumus RATE. Ini akan menghitung NPR untukmu.

=RATE(ROW(A11)-ROW(A2),,-C2,C11)

Menghitung CAGR di Excel menggunakan Fungsi IRR

IRR kependekan dari 'Internal Rate Return' adalah fungsi Excel yang menghitung IRR untuk pembayaran dan pendapatan yang terjadi secara berkala (yaitu bulanan, tahunan).

Metode IRR sangat membantu ketika Anda perlu menghitung nilai CAGR untuk arus kas periodik.

Sintaksnya adalah:

=IRR(nilai,[tebakan])

Di mana:

  • nilai-nilai - berbagai pembayaran. Rentang pembayaran harus memiliki setidaknya satu arus kas negatif dan satu arus kas positif.
  • [Tebak] (Opsional) – Ini mewakili tebakan nilai tarif Anda. Jika diabaikan, defaultnya adalah 10%.

Fungsi Excel IRR mengharuskan Anda untuk menyesuaikan kembali kumpulan data dengan cara ini:

Nilai awal harus dimasukkan sebagai angka negatif, nilai akhir adalah angka positif, dan semua nilai lainnya sebagai nol.

Berikut adalah rumus untuk contoh:

=IRR(C2:C11)

Nah, itulah beberapa cara menghitung compound annual growth rate (CGAR) di Excel.