Pengiraan IRR (kadar pulangan dalaman) dalam Excel

Tutorial menunjukkan cara mengira IRR sesuatu projek dalam Excel dengan formula dan ciri mencari matlamat. Anda juga akan belajar bagaimana untuk membuat kadar templat pulangan dalaman untuk melakukan semua pengiraan IRR secara automatik.

Apabila anda mengetahui kadar pulangan dalaman pelaburan yang dicadangkan, anda mungkin menganggap anda mempunyai semua yang anda perlukan untuk menilai - semakin besar IRR yang lebih baik. Dalam praktiknya, ia tidak begitu mudah. Microsoft Excel menyediakan tiga fungsi yang berbeza untuk mencari kadar pulangan dalaman, dan benar -benar memahami apa yang sebenarnya anda hitung dengan IRR akan sangat membantu.

• Apakah kadar pulangan dalaman (IRR)?
• Pengiraan IRR Manual dalam Excel
• Cara Mengira IRR dengan Formula Excel
  • Formula IRR untuk mengira kadar pulangan dalaman
  • Formula XIRR untuk mencari IRR untuk aliran tunai dengan masa yang tidak sama rata
  • Formula Mirr untuk Modified IRR
• Kadar Kalkulator / Templat Pulangan Dalaman
• Bagaimana melakukan IRR dengan mencari matlamat

Apa itu IRR?

Kadar pulangan dalaman (IRR) adalah metrik yang biasa digunakan untuk menganggarkan keuntungan pelaburan yang berpotensi. Kadang -kadang, ia juga dirujuk sebagai kadar aliran tunai yang diskaun pulangan atau kadar pulangan ekonomi .

Secara teknikal, IRR adalah kadar diskaun yang menjadikan nilai sekarang bersih bagi semua aliran tunai (kedua -dua aliran dan aliran keluar) dari pelaburan tertentu sama dengan sifar.

Istilah "dalaman" menunjukkan bahawa IRR mengambil kira hanya faktor dalaman; Faktor luaran seperti inflasi, kos modal dan pelbagai risiko kewangan dikecualikan daripada pengiraan.

Apa yang dinyatakan oleh IRR?

Dalam belanjawan modal, IRR digunakan secara meluas untuk menilai keuntungan pelaburan prospektif dan berpangkat pelbagai projek. Prinsip umum adalah semudah ini: semakin tinggi kadar pulangan dalaman, semakin menarik projek ini.

Apabila menganggarkan satu projek, penganalisis kewangan biasanya membandingkan IRR kepada kos purata modal atau kadar halangan yang ditimbang oleh syarikat, yang merupakan kadar pulangan minimum ke atas pelaburan yang boleh diterima oleh syarikat. Dalam situasi hipotetikal, apabila IRR adalah satu -satunya kriteria untuk membuat keputusan, projek dianggap sebagai pelaburan yang baik jika IRR lebih besar daripada kadar halangan. Jika IRR lebih rendah daripada kos modal, projek itu harus ditolak. Dalam praktiknya, terdapat banyak faktor lain yang mempengaruhi keputusan seperti nilai sekarang bersih (NPV), tempoh bayaran balik, nilai pulangan mutlak, dll.

Batasan IRR

Walaupun IRR adalah kaedah yang sangat popular untuk menilai projek modal, ia mempunyai beberapa kelemahan yang boleh menyebabkan keputusan suboptimal. Masalah utama dengan IRR adalah:

• Ukuran relatif . IRR menganggap peratusan tetapi bukan nilai mutlak, sebagai hasilnya, ia boleh memihak kepada projek dengan kadar pulangan yang tinggi tetapi nilai dolar yang sangat kecil. Dalam amalan, syarikat mungkin lebih suka projek besar dengan IRR yang lebih rendah lebih kecil dengan IRR yang lebih tinggi. Dalam hal ini, NPV adalah metrik yang lebih baik kerana ia menganggap jumlah sebenar yang diperoleh atau hilang dengan menjalankan projek.
• Kadar pelaburan semula yang sama . IRR mengandaikan bahawa semua aliran tunai yang dihasilkan oleh sesuatu projek dilaburkan semula pada kadar yang sama dengan IRR itu sendiri, yang merupakan senario yang sangat tidak realistik. Masalah ini diselesaikan oleh MIRR yang membolehkan menentukan kadar kewangan yang berbeza dan melabur semula.
• Keputusan Pelbagai . Bagi projek -projek dengan aliran tunai positif dan negatif, lebih daripada satu IRR boleh didapati. Isu ini juga diselesaikan dalam MIRR, yang direka untuk menghasilkan hanya satu kadar.

Walaupun kekurangan ini, IRR terus menjadi ukuran penting belanjawan modal dan, sekurang -kurangnya, anda harus membuat pandangan yang ragu -ragu sebelum membuat keputusan pelaburan.

Pengiraan IRR dalam Excel

Oleh kerana kadar pulangan dalaman adalah kadar diskaun di mana nilai sekarang bersih siri aliran tunai yang diberikan adalah sama dengan sifar, pengiraan IRR didasarkan pada formula NPV tradisional:

Sekiranya anda tidak begitu akrab dengan notasi penjumlahan, bentuk formula IRR yang dilanjutkan mungkin lebih mudah difahami:

Di mana:

• Cf ₀ - pelaburan awal (diwakili oleh nombor negatif)
• Cf ₁ , cf ₂ ... cf _n - aliran tunai
• Saya - Nombor Tempoh
• N - Jumlah Tempoh
• IRR - kadar pulangan dalaman

Sifat formula adalah sedemikian rupa sehingga tidak ada cara analisis untuk mengira IRR. Kami perlu menggunakan pendekatan "Guess and Check" untuk mencarinya. Untuk lebih memahami konsep kadar pulangan dalaman, mari kita lakukan pengiraan IRR pada contoh yang sangat mudah.

Contoh : Anda melabur $ 1000 sekarang dan dapat kembali $ 500 dan $ 660 dalam 2 tahun akan datang. Kadar diskaun apa yang menjadikan nilai sekarang bersih sifar?

Sebagai tekaan pertama kami, mari kita cuba kadar 8%:

• Sekarang: PV = -$ 1,000
• Tahun 1: PV = $ 500 / (1 0.08) ¹ = $ 462.96
• Tahun 2: PV = $ 660 / (1 0.08) ² = $ 565.84

Menambah mereka, kami mendapat NPV sama dengan $ 28.81:

Oh, tidak dekat dengan 0. Mungkin tekaan yang lebih baik, katakan 10%, boleh mengubah sesuatu?

• Sekarang: PV = -$ 1,000
• Tahun 1: PV = $ 500 / (1 0.1) ¹ = $ 454.55
• Tahun 2: PV = $ 660 / (1 0.1) ² = $ 545.45
• NPV: -1000 $ 454.55 $ 545.45 = $ 0.00

Itu sahaja! Pada kadar diskaun 10%, NPV adalah tepat 0. Jadi, IRR untuk pelaburan ini adalah 10%:

Begitulah cara anda mengira kadar pulangan dalaman secara manual. Microsoft Excel, program perisian lain dan pelbagai kalkulator IRR dalam talian juga bergantung pada kaedah percubaan dan ralat ini. Tetapi tidak seperti manusia, komputer boleh melakukan beberapa lelaran dengan cepat.

Cara Mengira IRR dalam Excel dengan Formula

Microsoft Excel menyediakan 3 fungsi untuk mencari kadar pulangan dalaman:

• IRR - Fungsi yang paling biasa digunakan untuk mengira kadar pulangan dalaman untuk satu siri aliran tunai yang berlaku pada selang masa yang tetap .
• XIRR - Menemukan IRR untuk satu siri aliran tunai yang berlaku pada selang masa yang tidak teratur . Kerana ia mengambil kira tarikh pembayaran yang tepat, fungsi ini memberikan ketepatan pengiraan yang lebih baik.
• MIRR - Mengembalikan kadar pulangan dalaman yang diubahsuai , yang merupakan variasi IRR yang menganggap kedua -dua kos pinjaman dan faedah yang diperolehi yang diterima pada pelaburan semula aliran tunai positif.

Di bawah ini anda akan dapati contoh semua fungsi ini. Demi konsistensi, kami akan menggunakan set data yang sama dalam semua formula.

Formula IRR untuk mengira kadar pulangan dalaman

Katakan anda sedang mempertimbangkan pelaburan 5 tahun dengan aliran tunai dalam B2: B7. Untuk mengerjakan IRR, gunakan formula mudah ini:

=IRR(B2:B7)

Nota. Untuk formula IRR berfungsi dengan betul, sila pastikan aliran tunai anda mempunyai sekurang -kurangnya satu negatif (aliran keluar) dan satu nilai positif (aliran masuk), dan semua nilai disenaraikan pada susunan kronologi .

Untuk maklumat lanjut, sila lihat fungsi Excel IRR.

Formula XIRR untuk mencari IRR untuk aliran tunai yang tidak teratur

Sekiranya aliran tunai dengan masa yang tidak sama rata, menggunakan fungsi IRR boleh berisiko, kerana ia mengandaikan bahawa semua pembayaran berlaku pada akhir tempoh dan semua tempoh masa adalah sama. Dalam kes ini, XIRR akan menjadi pilihan yang lebih bijak.

Dengan aliran tunai dalam B2: B7 dan tarikh mereka di C2: C7, formula akan pergi seperti berikut:

=XIRR(B2:B7,C2:C7)

Nota:

• Walaupun fungsi XIRR tidak semestinya memerlukan tarikh dalam susunan kronologi, tarikh aliran tunai pertama (pelaburan awal) harus pertama dalam array.
• Tarikh mesti disediakan sebagai tarikh Excel yang sah ; Membekalkan tarikh dalam format teks meletakkan Excel berisiko menafsirkannya.
• Fungsi Excel XIRR menggunakan formula yang berbeza untuk mencapai hasilnya. Formula XIRR diskaun pembayaran berikutnya berdasarkan tahun 365 hari, sebagai hasilnya, XIRR sentiasa mengembalikan kadar pulangan dalaman tahunan .

Untuk maklumat lanjut, sila lihat fungsi Excel XIRR.

Formula Mirr untuk menyelesaikan IRR yang diubahsuai

Untuk mengendalikan keadaan yang lebih realistik apabila dana projek dilaburkan semula pada kadar yang lebih dekat dengan kos modal syarikat, anda boleh mengira kadar pulangan dalaman yang diubahsuai dengan menggunakan formula MIRR:

=MIRR(B2:B7,E1,E2)

Di mana B2: B7 adalah aliran tunai, E1 adalah kadar kewangan (kos meminjam wang) dan E2 adalah kadar pelaburan semula (faedah yang diterima pada pelaburan semula pendapatan).

Nota. Kerana fungsi MIRR Excel mengira kepentingan kompaun pada keuntungan, hasilnya mungkin jauh berbeza daripada fungsi IRR dan XIRR.

IRR, XIRR dan MIRR - Mana yang lebih baik?

Saya percaya tiada siapa yang boleh memberikan jawapan generik kepada soalan ini kerana asas teoritis, kelebihan dan kelemahan ketiga -tiga kaedah ini masih dipertikaikan di kalangan ahli akademik kewangan. Mungkin, pendekatan terbaik adalah melakukan ketiga -tiga pengiraan dan membandingkan hasilnya:

Umumnya, dianggap bahawa:

• XIRR menyediakan ketepatan pengiraan yang lebih baik daripada IRR kerana ia mengambil kira tarikh tepat aliran tunai.
• IRR sering memberikan penilaian yang terlalu optimis mengenai keuntungan projek, sementara MIRR memberikan gambaran yang lebih realistik.

Kalkulator IRR - Templat Excel

Sekiranya anda perlu melakukan pengiraan IRR dalam Excel secara teratur, menubuhkan kadar templat pulangan dalaman boleh menjadikan hidup anda lebih mudah.

Kalkulator kami akan merangkumi ketiga -tiga formula (IRR, XIRR, dan MIRR) supaya anda tidak perlu bimbang yang hasilnya lebih sah tetapi boleh menganggap mereka semua.

1. Masukkan aliran tunai dan tarikh dalam dua lajur (a dan b dalam kes kami).
2. Masukkan kadar kewangan dan kadar pelaburan semula dalam 2 sel berasingan. Secara pilihan, namakan ini menjual kewangan dan reinvest_rate , masing -masing.
3. Buat dua julat dinamik yang dinamik, bernama cash_flows dan tarikh .

   Dengan mengandaikan lembaran kerja anda dinamakan Sheet1 , aliran tunai pertama (pelaburan awal) berada dalam sel A2, dan tarikh aliran tunai pertama adalah dalam sel B2, membuat julat yang dinamakan berdasarkan formula ini:

   Cash_flows:

   =OFFSET(Sheet1!$A$2,0,0,COUNT(Sheet1!$A:$A),1)

   Tarikh:

   =OFFSET(Sheet1!$B$2,0,0,COUNT(Sheet1!$B:$B),1)

   Langkah -langkah terperinci boleh didapati dalam cara membuat julat bernama dinamik dalam Excel.

4. Gunakan nama yang baru anda buat sebagai argumen formula berikut. Sila ambil perhatian bahawa formula boleh dimasukkan dalam mana -mana lajur selain A dan B, yang dikhaskan secara eksklusif untuk aliran dan tarikh tunai.

   =IRR(Cash_flows)

   =XIRR(Cash_flows, Dates)

   =MIRR(Cash_flows, Finance_rate, Reinvest_rate)

Selesai! Anda kini boleh memasukkan bilangan aliran tunai dalam lajur A, dan kadar formula pulangan dalaman dinamik anda akan dikira semula dengan sewajarnya:

Sebagai langkah berjaga -jaga terhadap pengguna yang cuai yang mungkin lupa untuk mengisi semua sel input yang diperlukan, anda boleh membungkus formula anda dalam fungsi Iferror untuk mencegah kesilapan:

=IFERROR(IRR(Cash_flows), "")

=IFERROR(XIRR(Cash_flows, Dates), "")

=IFERROR(MIRR(Cash_flows, Finance_rate, Reinvest_rate), "")

Perlu diingat bahawa jika sel -sel kewangan

Cara melakukan IRR dalam Excel dengan mencari matlamat

Fungsi Excel IRR hanya melakukan 20 lelaran untuk mencapai kadar dan XIRR melakukan 100 lelaran. Jika selepas itu banyak lelaran hasil yang tepat dalam 0.00001% tidak dijumpai, #num! ralat dikembalikan.

Jika anda mencari lebih ketepatan untuk pengiraan IRR anda, anda boleh memaksa Excel untuk melakukan lebih daripada 32,000 lelaran dengan menggunakan ciri mencari matlamat, yang merupakan sebahagian daripada analisis apa-apa.

Idea ini adalah untuk mendapatkan matlamat untuk mencari kadar peratusan yang menjadikan NPV sama dengan 0. Inilah caranya:

1. Sediakan data sumber dengan cara ini:
   • Masukkan aliran tunai dalam lajur (B2: B7 dalam contoh ini).
   • Letakkan IRR yang dijangkakan dalam beberapa sel (B9). Nilai yang anda masukkan sebenarnya tidak penting, anda hanya perlu "memberi" sesuatu kepada formula NPV, jadi letakkan apa -apa peratusan yang masuk ke dalam fikiran, katakan 10%.
   • Masukkan formula NPV berikut dalam sel lain (B10): =NPV(B9,B3:B7) B2

   

2. Pada tab Data , dalam kumpulan ramalan , klik bagaimana jika analisis > mencari matlamat ...
3. Dalam kotak dialog mencari matlamat , tentukan sel dan nilai untuk menguji:
   • Tetapkan sel - rujukan kepada sel NPV (B10).
   • Untuk nilai - jenis 0, yang merupakan nilai yang dikehendaki untuk sel set.
   • Dengan menukar sel - rujukan kepada sel IRR (B9).

   Apabila selesai, klik OK .

   

4. Kotak dialog Status mencari matlamat akan muncul dan memberitahu anda jika penyelesaian telah dijumpai. Jika berjaya, nilai dalam sel IRR akan digantikan dengan yang baru yang menjadikan NPV sifar.

   Klik OK untuk menerima nilai baru atau membatalkan untuk mendapatkan kembali yang asal.

   

Dengan cara yang sama, anda boleh menggunakan ciri mencari matlamat untuk mencari XIRR. Satu -satunya perbezaan ialah anda perlu menggunakan formula XNPV dan bukannya NPV.

Nota. Nilai IRR yang ditemui dengan pencarian matlamat adalah statik , ia tidak mengira semula secara dinamik seperti formula. Selepas setiap perubahan dalam data asal, anda perlu mengulangi langkah -langkah di atas untuk mendapatkan IRR baru.

Itulah cara melakukan pengiraan IRR dalam Excel. Untuk melihat dengan lebih dekat pada formula yang dibincangkan dalam tutorial ini, anda dialu -alukan untuk memuat turun buku kerja sampel kami di bawah. Saya mengucapkan terima kasih kerana membaca dan berharap dapat melihat anda di blog kami minggu depan!

Buku Kerja Amalan untuk Muat turun

Kalkulator Excel IRR - Contoh (fail .xlsx)

Atas ialah kandungan terperinci Pengiraan IRR (kadar pulangan dalaman) dalam Excel. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!