Black-Scholes Option Pricing Model Using VBA

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

<p><span style="COLOR: rgb(0,153,0);">****************************************************************************</span></p><p><span style="COLOR: rgb(0,153,0);">'*&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; Cumulative Standard Normal Distribution&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; *</span><br style="COLOR: rgb(0,153,0);"/><span style="COLOR: rgb(0,153,0);">'*&nbsp;&nbsp; (This function provides similar result as NORMSDIST( ) on Excel)&nbsp; *</span><br style="COLOR: rgb(0,153,0);"/><span style="COLOR: rgb(0,153,0);">'****************************************************************************</span><br style="COLOR: rgb(51,51,255);"/><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">Function SNorm(z)</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; c1 = 2.506628</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; c2 = 0.3193815</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; c3 = -0.3565638</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; c4 = 1.7814779</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; c5 = -1.821256</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; c6 = 1.3302744</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; If z &gt; 0 Or z = 0 Then</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; w = 1</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; Else: w = -1</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; End If</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; y = 1 / (1 + 0.2316419 * w * z)</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; SNorm = 0.5 + w * (0.5 - (Exp(-z * z / 2) / c1) * _</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (y * (c2 + y * (c3 + y * (c4 + y * (c5 + y * c6))))))</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">End Function<br/><br style="COLOR: rgb(51,51,255);"/></span><span style="COLOR: rgb(0,153,0);">'**********************************************************************</span><br style="COLOR: rgb(0,153,0);"/><span style="COLOR: rgb(0,153,0);">'*&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Black-Scholes European Call Price Computation&nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; *</span><br style="COLOR: rgb(0,153,0);"/><span style="COLOR: rgb(0,153,0);">'**********************************************************************</span><br style="COLOR: rgb(51,51,255);"/><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">Function Call_Eur(s, x, t, r, sd)</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; Dim a As Single</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; Dim b As Single</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; Dim c As Single</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; Dim d1 As Single</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; Dim d2 As Single</span><br style="COLOR: rgb(51,51,255);"/><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; a = Log(s / x)</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; b = (r + 0.5 * sd ^ 2) * t</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; c = sd * (t ^ 0.5)</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; d1 = (a + b) / c</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; d2 = d1 - sd * (t ^ 0.5)</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; Call_Eur = s * SNorm(d1) - x * Exp(-r * t) * SNorm(d2)</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; </span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">End Function</span><br style="COLOR: rgb(51,51,255);"/><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(0,153,0);">'*********************************************************************</span><br style="COLOR: rgb(0,153,0);"/><span style="COLOR: rgb(0,153,0);">'*&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Black-Scholes European Put Price Computation&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp;&nbsp; *</span><br style="COLOR: rgb(0,153,0);"/><span style="COLOR: rgb(0,153,0);">'*********************************************************************</span><br style="COLOR: rgb(51,51,255);"/><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">Function Put_Eur(s, x, t, r, sd)</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; Dim a As Single</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; Dim b As Single</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; Dim c As Single</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; Dim d1 As Single</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; Dim d2 As Single</span><br style="COLOR: rgb(51,51,255);"/><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; a = Log(s / x)</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; b = (r + 0.5 * sd ^ 2) * t</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; c = sd * (t ^ 0.5)</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; d1 = (a + b) / c</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; d2 = d1 - sd * (t ^ 0.5)</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; CallEur = s * SNorm(d1) - x * Exp(-r * t) * SNorm(d2)</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; Put_Eur = x * Exp(-r * t) - s + CallEur</span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">&nbsp;&nbsp;&nbsp; </span><br style="COLOR: rgb(51,51,255);"/><span style="COLOR: rgb(51,51,255);">End Function</span><br style="COLOR: rgb(51,51,255);"/><br/><span style="COLOR: rgb(51,51,255);"><br/></span><span style="COLOR: rgb(51,51,255);"></span><br style="COLOR: rgb(51,51,255);"/><br/></p>

扫码加我 拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

分享0 收藏2 回帖

关键词：SCHOLES Pricing choles Pricin Option VBA Using Pricing Option model

<p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt;"><span lang="ZH-CN" style="FONT-FAMILY: SimSun; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;; mso-fareast-language: ZH-CN;">我以为是书</span><span lang="ZH-CN"><font face="Times New Roman">
                        </font></span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt;"><span lang="ZH-CN" style="FONT-FAMILY: SimSun; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;; mso-fareast-language: ZH-CN;">原来是简单的</span><span lang="EN-US" style="mso-fareast-language: ZH-CN; mso-fareast-font-family: SimSun;"><font face="Times New Roman">VBA</font></span><span lang="ZH-CN" style="FONT-FAMILY: SimSun; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;; mso-fareast-language: ZH-CN;">程序代码</span></p><span lang="ZH-CN" style="FONT-SIZE: 12pt; FONT-FAMILY: SimSun; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;; mso-fareast-language: ZH-CN; mso-bidi-font-family: &quot;Times New Roman&quot;; mso-font-kerning: 1.0pt; mso-ansi-language: EN-US; mso-bidi-language: AR-SA;">晕</span>

感謝樓主分享!