Lompat ke konten
Anda disini: M Jurnal » Skripsi » Cara Menghitung Portofolio Optimal Menggunakan Excel – Tutorial Lengkap

Cara Menghitung Portofolio Optimal Menggunakan Excel – Tutorial Lengkap

Portofolio Optimal

Analisis Portofolio Optimal merupakan salah satu strategi investasi yang cukup populer untuk menghasilkan return yang tinggi dengan risk yang cukup rendah.

Dalam dunia pendidikan, banyak peneliti yang mengembangkan teori-teori asset pricing ini. Salah satu teori yang cukup populer ialah Portofolio Optimal menggunakan Single Index Model.

Pada kesempatan kali ini, Saya akan memberikan tutorial cara menghitung portofolio optimal menggunakan excel.

Sebelum masuk ke pembasannya, bagi Anda yang masih bingung dalam menentukan judul penelitian, silahkan lihat daftar contoh judul penelitian / skripsi manajemen keuangan yang dapat Anda download file pdf nya sebagai referensi dalam penelitian.

Download File Portofolio Optimal Excel

Sebelum Anda mempelajari panduan ini sampai habis, silahkan download terlebih dahulu File Excel yang Saya gunakan atau gunakan File Excel Online Berikut:

Note:
Klik Icon FullScreen pada sudut kanan bawah file excel online untuk menggunakan semua fitur.
Klik icon download pada baris sudut kanan bawah layar untuk download file.

Data yang dibutuhkan Untuk Analisis Portofolio Optimal

Silahkan persiapkan data-data berikut untuk di olah.

  1. Closing Price, yaitu harga penutupan saham sesuai periode penelitian
  2. Indeks Harga Saham, yaitu indeks yang dapat menggambarkan objek penelitian secara umum. Jika Anda melakukan penelitian dengan objek saham-saham syariah, maka dapat menggunakan Jakarta Islamic Index (JII) dan sebagainya.
  3. Risk Free, Anda bisa menggunakan suku bunga yang ditetapkan bank central. Jika objek penelitian adalah perusahaan go-public IDX, maka Anda dapat menggunakan suku bunga acuan dari Bank Indonesia yaitu BI Rate (sekarang menjadi BI 7 Days Repo Rate).

Sebagai contoh, Saya akan menggunakan 11 perusahaan yang konsisten masuk kedalam daftar 50 perusahaan terbesar (memiliki kapitalisasi pasar terbesar) di IDX dengan periode dari 2005 – 2015.

Saya menggunakan Ms Excel 2013 dalam memproses data. Mungkin akan terdapat sedikit perbedaan jika Anda menggunakan versi lama atau terbarunya. Jika Anda masih belum tahu cara menggunakan Excel, silahkan pelajari di Pusat Panduan Excel M Jurnal.

Tabulasi Data Portofolio Optimal di Excel

Tabulasi data ini bertujuan agar mempermudah kita dalam mengolah data. Saya sarankan, Anda menyusun data seperti berikut ini:

Portofolio Optimal
Image: M Jurnal
  1. Untuk  memudahkan perhitungan selanjutnya, susun Data Emiten yang menjadi sampel penelitian secara horizontal.
  2. IHSG merupakan indeks pasar. Sebelum melakukan analisis portofolio optimal, Anda harus memilih indeks pasar yang paling tepat menggambarkan data sample secara keseluruhan.
    Contoh pertama, Saya menggunakan sample 11 perusahaan terbesar yang terdaftar IDX. Menurut Hartono (2008:98), perusahaan berkapitalisasi besar lebih mendominasi fluktuasi IHSG. Maka Saya menggunakan IHSG sebagai index pasar.
    Contoh kedua, jika Anda menggunakan sampel emiten-emiten syari’ah, maka indeks pasar yang paling tepat adalah indeks syari’ah seperti JII atau ISSI.
  3. Rf (risk free) adalah tingkat keuntungan (return) bebas risiko. Pada contoh ini, Saya menggunakan BI Rate.

Selanjutnya kita mulai mengolah data. Silahkan gunakan pintasan panduan berikut untuk membuka setiap Sub Bab Panduan. Di akhir setiap Sub Bab, Anda akan menemukan Pintasan Panduan yang sama.

Pintasan Panduan Olah Data Skripsi Portofolio Optimal

BAB Sebelumnya: Proposal Skirpsi

  1. Data & Tabulasi: (Anda Disini)
  2. Return: Menghitung Actual Return dan Expected Return masing-masing emiten, market dan risk free.
  3. Risk: Menghitung Variance (σ2), Alpha (α), Beta (β), dan Unsystematic Risk (ei) masing-masing emiten dan market
  4. Risk Free: Menghitung Excess Return dan Expected Excess Return dari Risk Free
  5. Ai, Bi, dan Ci: Menghitung nilai Ai, Bi, Ci dan Excess Return to Beta (ERB)
  6. Optimal: Mengurutkan Emiten berdasarkan ERB, Menghitung Cut-off Point dan Menentukan Emiten yang masuk kedalam Portofolio Optimal
  7. Pembobotan: Menghitung Zi, Wi, Alpha Portofolio, Beta Portofolio, dan Unsystematic Risk Portofolio Optimal.
  8. SIM or CAPM ?: Menghitung Single Index Model (SIM) dan Capital Asset Pricing Model (CAPM) lalu memahami hasilnya

61 tanggapan pada “Cara Menghitung Portofolio Optimal Menggunakan Excel – Tutorial Lengkap”

  1. Hallo mas Adi.
    Kami juga sering mendengar keluhan dari teman-teman yang pake ms 2010. Kami belum tahu apa penyebab yang pasti ms 2010 banyak kendala. Namun sebetulnya fungsi slope dan intercept udah didukung pada Ms. 2007 sampai 2016.
    Ada baiknya di coba kembali. Pastikan format formulanya udah benar. Misalnya penggunaan tanda “titik koma” (;) atau tanda “koma” (,) sebagai pemisah range data.
    Jika masalah masih berlanjut, tolong mas kirimkan ss nya ke kami ya.

  2. Christian Adhi Natanegara

    Rumus Excel Variance Residual Errornya masih salah, Pak. Tidak sesuai dengan ilustrasi Excel di bawahnya. Mohon bantuannya ditinjau kembali. Terima kasih

  3. Rumus Intercept saya sudah blok actual return tetapi kenapa tetap tidak bisa ya? thx

  4. Saya mau tanya, ada beberapa sumber memakai suku bunga SBI, kenapa disini memakai BI rate?
    Lalu bagaimana cara mencari nilai IHSG? Terima kasih

  5. selamat sore pak, saya mau bertanya jika mencari return riskfree harian dalam srtahun sebaiknya dibagi 360 atau 365 ya terima kasih

  6. BI Rate itu suku bunga acuan bagi seluruh bank di Indonesia mbak. Jd, rata2 bank akan memberikan bunga sekitar besaran BI Rate. Sumber nya bisa baca buku suad husnan atau jogiyanto hartono mbak.
    Selagi ada sumber yg jelas, kita bisa pertahankan penggunaan BI Rate.

    Untuk mencari nilai IHSG, bisa di download di situs IDX mbak.. atau bisa juga download melalui finance.yahoo.com.

  7. Iryani Wulandari

    Mohon maaf bapak, bolehkah membagi ilmu bapak untuk metode diganti menjadi metode Markowitz yaitu tidak menggunakan alpha dan Beta melainkan koefisien korelasi. Juga mohon untuk cara atau tahap tahap secara excel. mohon bantuan. Terimakasih

  8. Dwi Septina Sari

    selamat siang, saya mau menanyakan rumus Varians (σ2) excel jika dihitung menggunakan rumus manual hasilnya berbeda dengan menggunakan excel.
    misal hasil varians (σ2) AALI di excel anda hasilnya 0,0158
    namun setelah saya hitung menggunakan rumus manual hasilnya 0,05448

    mohon penjelasannya terimakasih

  9. Halo om, mau tanya kenapa Rf per bulan harus dibagi 12 sedangkan BI Rate nya sudah ada per bulan ?

  10. Maaf.. saya baru aktif kembali untuk kelola web ini. Mungkin jawaban saya saat ini telat. Tapi mudah2an bisa membantu teman2 yang lain bila membaca komentar ini.
    Rf memang di bagi 12. 1 tahun = 12 bulan. BI rate akan berubah terus setiap bulannya. contoh. BI Rate Bulan 11. Ini adalah suku bunga acuan untuk 12 bulan kedepan. Selanjutnya, Bulan 12 akan ada BI Rate lagi. BI Rate bulan 12 ini adalah suku bunga acuan untuk 12 bulan kedepan. terhitung tanggal di terbitkannya BI Rate. Dan begitu seterusnya bila ada terbitan suku bunga acuan selanjutnya di bulan2 berikutnya.

  11. Fatmawati Lestari

    permisi kak, saya mau menanyakan kenapa ang dipakai itu data bulanan yah? apakah ada alasannya data bulanan itu lebih baik digunakan saat melakukan perhitungan terhadap portofolio karna seperti yang kita tau harga saham saja setiap setiknya bisa berubah. terimakasih kak

  12. Itu semua tergantung si pengguna data mbak. Melihat tujuan dari si pengguna. Apakah untuk analisa jangka Panjang / pendek. Kalau portofolio untuk investasi akan lebih baik menggunakan data dengan time frame besar, missal Daily, Weekly, Monthly. Kalau jangka pendek (trader), lebih baik menggunakan time frame kecil.

    Jika dihubungkan dengan judul artikel ini. “Portofolio optimal”. Model asset pricing yang digunakan CAPM. Dalam pembentukan portofolio optimal menggunakan CAPM memerlukan data Risk Free (dalam hal ini BI Rate).
    Pertanyaannya:
    Apakah BI Rate bisa berubah setiap detiknya seperti harga saham ? Tentu tidak. BI membutuhkan waktu untuk menganalisa makro ekonomi untuk menentukan arah kebijakan BI Rate.
    Dalam penelitian statistik tentunya kita tidak bisa meneliti data yang menggunakan time frame berbeda. Kita harus menyamakan time frame nya. Maka dari itu menggunakan data Bulanan.

  13. Kalo perusahaan dalam kategori optimal tapi bobotnya negatif itu bagaimana cara mengatasi nya ya?, terus kalo data mingguan rf nya di bagi 50 atau 52?

  14. Seleksi lagi perusahaan yang masuk portofolio optimal. Perusahaan yg memiliki ERB dan beta negatif, tidak masuk dalam portofolio optimal. Bobot itu harus antara 0 sampai 1 mbak. Jika ada bobot yang negatif, artinya ada kesalahan dalam menentukan perusahaan yg masuk ke dalam portofolio optimal
    Untuk rf mingguan di bagi 52 aja mbak.

  15. Apa alasan mengeliminasi perusahaan yang konsisten berada dalam 50 big cap karena stock averse atau stock split?

  16. Stock Split membagi harga saham sesuai rasio stock split sehingga jumlah saham beredar menjadi lebih banyak. Stock Averse kebalikannya. Karena Perhitungan Portofolio optimal menggunakan data (mentah) harga saham, maka perubahan data harga saham akibat stock split dan/atau stock averse akan merubah hasil perhitungan return dll menjadi keliru.
    Yang ke 2, mengeliminasi perusahaan yang konsisten berada dalam 50 big cap, itu karena target penelitian adalah emiten2 yang konsisten masuk dalam list 50 big cap. Yang tidak konsisten, tentu tidak masuk dalam kriteria penelitian.

  17. Apakah untuk menghitung expected return dengan menggunakan model pasar, langkah-langkahnya sama? Hanya, pada langkah perhitungan terakhirnya saja yang berbeda, yaitu dengan menggunakan rumus model pasar. Mohon penderahannya. Terima kasih sebelumnya

  18. Eniya alfinawati putri

    Assalammualaikum wr wb, pak saya mau nanya untuk menghitung varian residual itu apa emng rumus nya seperti itu? Soalnya saya liat dibuku jogiyanto tidak seperti itu rumusnya , mohon bantuannya pak

  19. Wa’alaikumsalam kak Eniya. Iya kak. Itu penyederhanaan rumus agar sesuai dengan tabulasi data yang Saya pake pada contoh ini. Coba baca buku Karangan Suad Husnan.

  20. Eniya alfinawati putri

    Buku suad husnan judul nya tentang apa pak?
    Dan tahun berapa pak? Soalnya saya liat banyak buku suad husnan

  21. Maaf ya kak Eniya, baru bisa Saya bales.
    Judul nya ini kak.
    Husnan, Suad. 2015. Dasar-dasar Teori Portofolio & Analisis Sekuritas. Edisi Kelima. Yogyakarta: UPP STIM YKPN
    Bisa juga baca2 buku karangan Tandelilin

  22. Bisa kok kak.
    Klik icon download (selembar kertas dengan tanda panah ke bawah) di Excel online di atas ya..
    Barusan saya coba, bisa kok

  23. Halo kak, mau tanya. Kalau misalnya setelah perhintungan hanya 1 saham yang masuk kategori optimal dan memiliki proporsi 1 artinya kan 100%. Nah, 100% itu maksudnya apa ya kak?

  24. artinya, 100% risk/return di hasilkan dari saham tsb..
    Arti lainnya, bisa juga 100% dana yang dimiliki di investasikan ke saham tsb untuk mendapatkan keuntungan sesuai perhitungan Expected Return CAPM atau SIM atau model asset pricing lainnya

  25. makasih sebeulmnya kak. maaf kak, saya mau tanya lgi, model SIM kan merupakan salah satu model yg digunakan investor dalam pengambilan keputusan investasi. jadi bagaimana jika misalkan investor ingin berinvestasi sepanjang tahun 2021-2023 tapi belum mendapatkan datanya untuk menghitung model SIM tersebut, jdi pengambilan keputusannya gimana ya kak? mohon infonya kak, terima kasih

  26. Ya pake hasil dari data historis yg udah ada kak (sebelum 2021).
    Misalnya seperti contoh di Panduan ini, hasil perhitungan dari model SIM kan berupa Expected Return Portofolio perbulan.
    Hasil Expected Return Portofolio itu yang jadi gambaran seberapa besar keuntungan yang bisa di dapatkan dari kombinasi saham yg di hasilkan untuk menentukan keputusan investasi berikut nya.
    Note: Expected Return = Return yg diharapkan

  27. mohon infonya, bukankah rumus untuk mencari excess return yaitu ekspektasi return dikurang risk free? tp kok yang kk hitung return?

  28. Maaf kak, data 50 saham biggest caps nya didapat dari statistik bulanan IDX bukan? Atau ada sumber lain kak? Saya sangat membutuhkan data 50 saham biggest cap tahun 2010-2020 per bulan kak. Data yang saya temukan belum lengkap. Boleh mohon bantuannya, Kak. Terima kasih. Jazakallah Khoir.

  29. Note : Untuk mendapatkan data bulanan 50 saham biggest cap nya bagaimana ya Kak?

  30. Kalo mau liat emiten apa saja yg masuk daftar 50 big cap, liat di factbook IDX kak.
    Seingat Saya, BEI hanya publikasikan factbook idx 5 thn terakhir..
    Kalo butuh factbook lebih dari 5 thn terakhir, cek di artikel Factbook IDX
    Terus kalo mau nyari data harga saham perbulan.. ambil di https://finance.yahoo.com aja kak.

  31. Saya mulai cek dari tabel penentuan emiten yang masuk ke dalam portofolio optimal ya kak.
    Untuk perhitungan sebelum itu, Saya anggap ga ada yang salah (termasuk rumus rumus Excel nya).
    Dari sini, Saya cuma lihat 1 rumus yang salah. Rumus IF pada Kolom “Keputusan” Optimal atau Tidak Optimal.
    Rumus yang kakak pake ini: =IF(F31>G31,”Optimal”,”Tidak Optimal”). Itu bakal ngebandingin ERB vs Ci
    Sementara untuk penentuan Emiten yang masuk ke dalam portofolio optimal pake ERB vs C*
    Rumus Penentuan Optimal sudah Saya jelaskan sebelumnya. Klik disini
    Kalo mau cepat, coba ganti aja rumus nya dengan ini: =IF(F31>H31,”Optimal”,”Tidak Optimal”)
    Dari rumus itu, ada 4 emiten yang masuk portofolio optimal (termasuk 2 Emiten yang memiliki Beta Negatif yaitu PGAS dan PWON).
    Eliminasi PGAS dan PWON. Jadi hanya ada 2 Emiten yang akan lanjut ke perhitungan berikutnya (Menghitung Zi, Wi, dan seterusnya.
    Dari 2 Emiten tersebut, Saya dapet Expected Return Portofolio Optimal menggunakan SIM = 0.078686317834566 atau 7.87%
    Lumayan gede itu return nya dibanding Risk Free. Saya rasa buat kesimpulannya jadi makin gampang.
    Oh ya…
    Nanti jangan lupa share ya, berapa hasil yang kakak dapet.

  32. Dewi Aprilia Maharani

    kak, iya benar untuk sheet “S2”, ternyata saya keliru, malah erb vc ci, untuk hasil sama kak sim sebesar 0,78686, oh iya kak emngnya knp beta minus tdk masuk ke perhitungan selanjutnya… soalnya saya pada sheet “S5” semester 5, beta nya minus semua untuk yg masuk kategori optimal… itu bagaimana ya kak kira2?, sebelumnya terima kasih banyak kak

  33. Saya belum nemu buku / teori yg bilang gitu..
    Ini pure pendapat Saya. Kalo kakak pikir ini masuk akal, silahkan digunakan.. kalo di tanya referensi dari mana, ya bilang aja dari website Saya.
    Saya memposisikan diri Saya sebagai Investor nih.
    Tentu Saya akan berhati2 memilih saham.
    Pertama, Penentuan portofolio optimal saham ini tujuannya apa ?
    Tentu untuk menentukan kombinasi saham2 mana yg dapat mengoptimalkan keuntungan. Jd akan ada beberapa saham yg berpotensi mengurangi keuntungan atau meningkatkan risiko dan dieliminasi.
    Kedua, Beta saham itu buat ngukur apa ? RISIKO bukan ?
    Beta saham yg bagus itu = 1. Itu beta market (misal seperti IHSG).
    Konsep beta dalam mengukur risiko adalah, apabila nilai beta semakin menjauhi 1 (baik positif atau negatif), maka semakin besar risiko nya.
    Kalo beta negatif, tentu risiko nya juga tinggi.

    Bagi Saya sebagai investor, saya tidak memilih Saham yg berisiko tinggi karna berpotensi menurunkan keuntungan.

    Itu point pertama kak.

    Point kedua…
    Saham yg memiliki beta negatif, akan memiliki Excess Return Negatif.
    Excess Return ini apa ? Kelebihan keuntungan saham dibanding risk free bukan ?
    Kalo Excess Return negatif, tentu keuntungan saham tsb dibawah Risk Free..
    Apa layak masuk ke dalam portofolio optimal ?

    Terus kenapa Saham yg punya beta negatif bisa masuk ke dalam portofolio optimal ?
    Liat rumus ngitung ERB = Expected Excess Return dibagi Beta.
    Negatif dibagi negatif menjadi positif.
    Otomatis memiliki ERB Positif.
    Terus, buat nentuin emiten yg masuk portofolio optimal ERB vs C*
    Nah disini lah biasa nya dia bakal lolos..

  34. Halo kak, saya lagi penelitian untuk mencari portofolio optimal dengan metode mean gini, apakah kakak bisa membantu saya, karena saya masih bingung untuk mencari bobot nya kak referensi untuk metode saya juga sedikit kak, terima kasih

  35. Panduan Portofolio Optimal ini ada 8 SUB-BAB.
    Coba Scroll ke atas, ke bagian Pintasan Panduan.
    Pelajari setiap SUB-BAB nya. Itu udah panduan step by step sampe penelitiannya selesai.
    Terus kalo ada yang bingung… coba jelaskan… Kakak ragu di perhitungan yang mana…

  36. Siang kak,
    Ini cara menghitung diatas itu bisa dipake buat tahapan sebelum menghitung sharpe ratio gak kak?
    Sama saya mau tanya kalo return market dan risk free pada Crypto makenya apa ya kak?

  37. Ambil perhitungan yang sama dengan yang dibutuhkan share ratio aja…
    Beberapa contoh perhitungan di atas juga bisa…
    Seperti Ngitung Return..

    Soal Crypto Saya belum paham.

  38. Halo mau tanya, Ai Bi Ci itu apa dan buat apa yah karena sepertinya tidak dijelaskan itu apa. Lalu untuk alpha beta dan juga Zi artinya apa yah? maaf saya bertanya agar bisa paham artinya. Terima kasih

  39. Halo kak mau tanya, saya liat di web BI, sejak 19 Agustus 2016 BI rate diganti dengan BI-7 Day Reverse Repo Rate. Jadi kalau memakai BI7DRR apakah tetap dibagi 12 untuk Rf perbulannya?

  40. Assalamualaikum

    Kak mohon maaf ingin bertanya, untuk model CAPM bagaimana cara menentukan saham yang optimal atau tidak?

  41. Ikuti aja kak semua part tutorial di atas. Ada 8 Sub-BAB. Di Sub-BAB terakhir sebagai pembeda SIM dan CAPM

  42. Maksudnya, apakah saham optimal untuk metode SIM dan CAPM pasti sama? Soalnya lihat dibeberapa jurnal berbeda, tapi diexcel ini hanya dijelaskan untuk yang SIM, seperti nilai Erb untuk metode SIM dan CAPM dibeberapa jurnal penelitian menghasilkan hasil yang berbeda, mohon pencerahannya

  43. Hallo min, saya sedang skripsi dan melakukan analisis portofolio menggunakan capm dan sim
    Dalam capm kan ada nama nya sahan efisien, dimana Ri > ERI = Efisien
    Sedangkan pada yang admin bagikan hanya ada ERI, dimana dihitung dengan Average(range Ri)
    Dan yang saya tau, rumus ERi capm bukan Average begitu. Mohon bantuannya min segera.

  44. Hallo admin, saya sedang skripsi dan melakukan analisis portofolio optimal menggunakan capm dan sim.
    Ada situasi dimana mencari sahan efisien pada capm adalah dengan melihat Ri>ERi = efisien.
    Pada artikel yang admin share, hanya terdapat nilai ERi yang dihitung dengan rumus Average(range Ri). Lalu jika ingin dibandingkan antara Ri dan ERi bagaimana ya? Sedangkan misal data Ri saya adalah selama 5 tahun.

    Dan yang saya tau, rumus ERi capm bukan begitu, lalu saya lihat-lihat artikel yang admin share menggunakan SIM bukan CAPM. Dimana saat saya sudah mencari ERi menggunakan rumus capm yang saya tau yaitu : ERi = Rf + Bi (ERm – Rf)
    Hasil nya menjadi beda

    Mohon bantuannya karena saya stuck disana, tolong segera, Terima kasih.

Komentar Anda:

Alamat email Anda tidak akan dipublikasikan. Ruas yang wajib ditandai *