期权定价

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

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

立即领取

感谢您参与论坛问题回答

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

+2 论坛币

求各位大神教我用excel做期权定价，单期多起二叉树，灰常感谢

扫码加我 拉你入群

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

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

分享0 收藏0 回帖

关键词：期权定价 用excel EXCEL xcel exce excel 二叉树

Hi, buddy,
are you talking about Black-Scholes Option Valuation model?

If so, let me know please,
I will send one to you.

I assume you are referring to Binomial Tree model.
Here is the customized function code.

-------------------------------------------------------------------------------
Binomial Tree Excel VBA Code
-------------------------------------------------------------------------------

Function binomial_tree(S, K, t, r, sigma, N)

Dim delta_t As Double
Dim u As Double
Dim d As Double
Dim j As Integer 'number of up movements on the tree
Dim i As Integer 'number of time intervals that have passed
Dim a As Double
Dim call_price() As Double
Dim put_price() As Double
Dim p As Double

delta_t = t / N
u = Exp(sigma * Sqr(delta_t))
d = -Exp(sigma * Sqr(delta_t))
a = Exp(r * delta_t)
p = (a - d) / (u - d)

ReDim call_price(1 To N, 1 To N)
ReDim put_price(1 To N, 1 To N)


For j = N To 1 Step -1

call_price(N, j) = WorksheetFunction.Max(S * u ^ j * d ^ (N - j) - K, 0)
put_price(N, j) = WorksheetFunction.Max(K - S * u ^ j * d ^ (N - j), 0)

For i = N - 1 To 1 Step -1
For j = N - 1 To 1 Step -1

call_price(i, j) = Exp(-r * delta_t) * (p * call_price(i + 1, j + 1) + (1 - p) *
call_price(i + 1, j))
put_price(i, j) = Exp(-r * delta_t) * (p * put_price(i + 1, j + 1) + (1 - p) *
put_price(i + 1, j))

Next j
Next i

binomial_tree = Array(call_price, put_price)


End Function

=======================================================

Copy the code in your Excel developer VBA interface.
It should solve your problem.
Good luck.