Cara Menggunakan Excel untuk Mensimulasikan Harga Saham

Beberapa investor aktif memodelkan variasi saham atau aset lain untuk mensimulasikan harganya dan instrumen yang didasarkan padanya, seperti derivatif. Mensimulasikan nilai aset pada spreadsheet Excel dapat memberikan representasi penilaian yang lebih intuitif untuk portofolio.

Takeaways Kunci

• Pedagang yang ingin menguji kembali model atau strategi dapat menggunakan harga simulasi untuk memvalidasi keefektifannya.
• Excel dapat membantu pengujian balik Anda menggunakan simulasi monte carlo untuk menghasilkan pergerakan harga acak.
• Excel juga dapat digunakan untuk menghitung volatilitas historis untuk dihubungkan ke model Anda untuk akurasi yang lebih baik.

Membuat Simulasi Model Harga

Apakah kita sedang mempertimbangkan untuk membeli atau menjual instrumen keuangan, keputusan dapat dibantu dengan mempelajarinya baik secara numerik maupun grafis. Data ini dapat membantu kami menilai kemungkinan pergerakan berikutnya yang mungkin dilakukan aset dan pergerakan yang kemungkinannya kecil.

Pertama-tama, model membutuhkan beberapa hipotesis sebelumnya. Kami berasumsi, Misalnya, bahwa setiap hari kembali, atau "r(t), Dari aset-aset ini terdistribusi secara normal dengan rata-rata, "(μ), dan sigma simpangan baku, "(σ)." Ini adalah asumsi standar yang akan kita gunakan di sini, meskipun ada banyak hal lain yang dapat digunakan untuk meningkatkan akurasi model.

R ( T ) = S ( T ) - S ( T - 1 ) S ( T - 1 ) ~ n ( μ , σ ) di mana: S ( T ) = Menutup T S ( T - 1 ) = Menutup T - 1 \begin{sejajar} &r ( t ) =\frac { S ( t ) - S ( t - 1 ) }{ S ( t - 1 ) } \sim N ( \mu, \sigma ) \\ &\textbf{where:} \\ &S ( t ) =\text{close}_t \\ &S ( t - 1 ) =\text{close}_{t - 1} \\ \end{ sejajar} ​r(t)=S(t−1)S(t)−S(t−1)​∼N(μ, )dimana:S(t)=lemari​S(t−1)=lemari−1​​

Yang memberikan:

R ( T ) = S ( T ) - S ( T - 1 ) S ( T - 1 ) = μ δ T + σ φ δ T di mana: δ T = 1 hari = 1 3 6 5 dari satu tahun μ = berarti φ ≅ n ( 0 , 1 ) σ = volatilitas tahunan \begin{aligned} &r ( t ) =\frac { S ( t ) - S ( t - 1 ) }{ S ( t - 1 ) } =\mu \delta t + \sigma \phi \sqrt { \delta t } \\ &\textbf{di mana:} \\ &\delta t =1 \ \text{hari} =\frac { 1 }{ 365 } \ \text{setahun} \\ &\mu =\text{ mean} \\ &\phi \cong N ( 0, 1 ) \\ &\sigma =\text{volatilitas tahunan} \\ \end{selaras} ​r(t)=S(t−1)S(t)−S(t−1)​=μδt+σϕδt​dengan:δt=1 hari=3651​ dalam setahunμ=meanϕ≅N(0, 1)σ=volatilitas tahunan​

Yang mengakibatkan:

S ( T ) - S ( T - 1 ) S ( T - 1 ) = μ δ T + σ φ δ T \begin{aligned} &\frac { S ( t ) - S ( t - 1 ) }{ S ( t - 1 ) } =\mu \delta t + \sigma \phi \sqrt { \delta t } \\ \ akhir{selaras} ​S(t−1)S(t)−S(t−1)​=μδt+σϕδt​​

Akhirnya:

S ( T ) - S ( T - 1 ) = S ( T - 1 ) μ δ T + S ( T - 1 ) σ φ δ T S ( T ) = S ( T - 1 ) + S ( T - 1 ) μ δ T + S ( T - 1 ) σ φ δ T S ( T ) = S ( T - 1 ) ( 1 + μ δ T + σ φ δ T ) \begin{aligned} S ( t ) - S ( t - 1 ) =&\ S ( t - 1 ) \mu \delta t + S ( t - 1 ) \sigma \phi \sqrt { \delta t } \\ S ( t ) =&\ S ( t - 1 ) + S ( t - 1 ) \mu \delta t \ + \\ &\ S ( t - 1 ) \sigma \phi \sqrt { \delta t } \\ S ( t ) =&\ S ( t - 1 ) ( 1 + \mu \delta t + \sigma \phi \sqrt { \delta t } ) \\ \end{aligned} S(t)−S(t−1)=S(t)=S(t)=​ S(t−1)μδt+S(t−1)σϕδt​ S(t−1)+S(t− 1)μδt + S(t−1)σϕδt​ S(t−1)(1+μδt+σϕδt​)​

Dan sekarang kita dapat mengungkapkan nilai harga penutupan hari ini menggunakan penutupan hari sebelumnya.

• Perhitungan :

Untuk menghitung , yang merupakan rata-rata pengembalian harian, kami mengambil n harga penutupan terakhir berturut-turut dan menerapkan, yang merupakan rata-rata dari jumlah n harga masa lalu:

μ = 1 n Σ T = 1 n R ( T ) \begin{sejajar} &\mu =\frac { 1 }{ n } \sum_{ t =1 } ^ { n } r ( t ) \\ \end{selaras} ​μ=n1​t=1∑n​r(t)​

• Perhitungan volatilitas - volatilitas

adalah volatilitas dengan rata-rata variabel acak nol dan standar deviasi satu.

Menghitung Volatilitas Historis di Excel

Untuk contoh ini, kita akan menggunakan fungsi Excel "=NORMSINV (RAND ())." Dengan dasar dari distribusi normal, fungsi ini menghitung bilangan acak dengan rata-rata nol dan simpangan baku satu. Untuk menghitung , cukup rata-ratakan hasil menggunakan fungsi Ln (.):distribusi log-normal.

Di sel F4, masukkan "Ln(P(t) / P(t-1)"

Dalam pencarian sel F19 "=RATA-RATA (F3:F17)"

Di sel H20, masukkan “=RATA-RATA(G4:G17)

Di sel H22, masukkan "=365*H20" untuk menghitung varians tahunan

Di sel H22, masukkan "=SQRT(H21) " untuk menghitung simpangan baku tahunan

Jadi kita sekarang memiliki "tren" pengembalian harian masa lalu dan standar deviasi (volatilitas). Kami dapat menerapkan rumus kami yang ditemukan di atas:

S ( T ) - S ( T - 1 ) = S ( T - 1 ) μ δ T + S ( T - 1 ) σ φ δ T S ( T ) = S ( T - 1 ) + S ( T - 1 ) μ δ T + S ( T - 1 ) σ φ δ T S ( T ) = S ( T - 1 ) ( 1 + μ δ T + σ φ δ T ) \begin{aligned} S ( t ) - S ( t - 1 ) =&\ S ( t - 1 ) \mu \delta t + S ( t - 1 ) \sigma \phi \sqrt { \delta t } \\ S ( t ) =&\ S ( t - 1 ) + S ( t - 1 ) \mu \delta t \ + \\ &\ S ( t - 1 ) \sigma \phi \sqrt { \delta t } \\ S ( t ) =&\ S ( t - 1 ) ( 1 + \mu \delta t + \sigma \phi \sqrt { \delta t } ) \\ \end{aligned} S(t)−S(t−1)=S(t)=S(t)=​ S(t−1)μδt+S(t−1)σϕδt​ S(t−1)+S(t− 1)μδt + S(t−1)σϕδt​ S(t−1)(1+μδt+σϕδt​)​

Kami akan melakukan simulasi selama 29 hari, maka dt =1/29. Titik awal kami adalah harga penutupan terakhir:95.

• Di sel K2, masukkan "0."
• Di sel L2, masukkan "95".
• Di sel K3, masukkan "1."
• Di sel L3, masukkan "=L2 * (1 + $F$19 * (1/29) + $H$22 *SQRT(1/29)*NORMSINV (RAND ()))."

Lanjut, kami menyeret rumus ke bawah kolom untuk menyelesaikan seluruh rangkaian harga simulasi.

Model ini memungkinkan kita untuk menemukan simulasi aset hingga 29 tanggal yang diberikan, dengan volatilitas yang sama dengan 15 harga sebelumnya yang kami pilih dan dengan tren yang sama.

Akhirnya, kita dapat mengklik "F9" untuk memulai simulasi lain karena kita memiliki fungsi rand sebagai bagian dari model.