Option Valuation

[tshah1992]

Hi,

I am trying to build binomial option model in excel. right now I have a macro in place that values American put model & also construct a binomial tree upto 200 nodes. I am trying to make it more dynamic by making it value american call as well as europian options.

I have formulas but I am not able to make my macro run. To give a proper view first I will post the formulas to value bith american & europian options. Below that I will write the macro I am using. Please have a look and help me out if you can.

Function Max(A, B)
If A > B Then Max = A Else Max = B
End Function

Function option_price(A, B, C, K, P, rate, T, N)
option_price = Max((P * A + (1 - P) * B) * Exp(-rate * T / N), K - C)
End Function

Sub Macro1()
N = Cells(3, 2).Value
Striking_Price = Cells(8, 2).Value
Volatility = Cells(6, 2).Value
Current_Stock = Cells(7, 2).Value
r = Cells(5, 2).Value
Maturity = Cells(4, 2).Value
U = Exp(Volatility * (Maturity / N) ^ (0.5))
D = 1 / U
P = (Exp(r * (Maturity / N)) - D) / (U - D)
Flag = True
Counter = 11

Cells(3, 5).Value = U
Cells(4, 5).Value = D
Cells(5, 5) = P

Do While (Flag = True)
Counter = Counter + 1
If Cells(Counter, 1).Value <> Empty Then Flag = False
Loop

For i = 10 To (Counter - 11) + Counter + 1
For j = 1 To (Counter - 11) / 2 + 1
Cells(i, j).Select
Selection.Clear
Next
Next

For i = 0 To N
Cells(10, N + 1).Value = N
Cells(11 + i * 4, N + 1).Value = Current_Stock * (U ^ (N - i)) * (D ^ i)
Cells(11 + i * 4, N + 1).Interior.ColorIndex = 6
Cells(11 + i * 4 + 1, N + 1).Value = Max(Striking_Price - Current_Stock * (U ^ (N - i)) * (D ^ i), 0)
Cells(11 + i * 4 + 1, N + 1).Interior.ColorIndex = 12
Next

For j = 1 To N
For i = 0 To N - j
Cells(10, N - j + 1).Value = N - j
Cells(11 + 2 * j + i * 4, N - j + 1).Value = Current_Stock * (U ^ (N - j - i)) * (D ^ i)
Cells(11 + 2 * j + i * 4, N - j + 1).Interior.ColorIndex = 6
Cells(11 + 2 * j + i * 4 + 1, N - j + 1).Value = option_price(Cells(11 + 2 * j + i * 4 + 1 - 2, N - j + 2), Cells(11 + 2 * j + i * 4 + 1 + 2, N - j + 2), Cells(11 + 2 * j + i * 4, N - j + 1), Striking_Price, P, r, Maturity, N)
Cells(11 + 2 * j + i * 4 + 1, N - j + 1).Interior.ColorIndex = 12
Next
Next

Cells(6, 5).Value = Cells(11 + 2 * N + 1, 1).Value
Cells(6, 5).NumberFormat = "0.000"
Range("B3:B8").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Cells(6, 5).Select
MsgBox "The price is " & Format(Cells(6, 5).Value, "0.000") & ".", vbInformation, "American Put"
End Sub

 

[bobsan42]

I cannot say I understand the problem - what exactly is it? Macro won't run is quite vague a statement.
Please clarify.
At first glance - do NOT use names of built in functions for user-defined ones e.g. MAX().

 

[RoryA]

Welcome to the forum.

Please take a minute to read the forum rules, especially regarding cross-posting, and then update your post accordingly (just like on the other site you posted to ) Thanks.