VBA Valuation of a barrier option with Black-Scholes formula

[JohnDenim]

[FONT=&quot]Hello fellow members, [/FONT]

[FONT=&quot]I am new to this forum and to VBA and am seeking help in finding the error for my makro in VBA. I am trying to value a down-and-out put barrier option with the adjusted BS formula from Haug (I left out F because there is no Rebate). I inserted the formula I want to use and my VBA code plus sheet. [/FONT]
[FONT=&quot]Any help is highly appreciated and karma will be given to you! [/FONT]

[FONT=&quot]Thanks in advance!![/FONT]

[FONT=&quot]John

Formula from BS, modified by Rubinstein and later by Haug:

[/FONT]


pdo(K>B) = A – B + C – D + F
Φ = -1 η = 1
A = ΦSe(cc-rf)TN(Φx1) - Φ Ke- rfTN(Φx1-Φσ√T)
B = ΦSe(cc- rf)TN(Φx2) - Φ Ke- rfTN(Φx2-Φσ√T)
C = ΦSe(cc- rf)T(B/S)2(μ+1)N(ηy1)-Φ Ke- rfT(B/S)2μN(ηy1-ησ√T)D = ΦSe(cc- rf)T(B/S)2(μ+1)N(ηy2)-Φ Ke- rfT(B/S)2μN(ηy2-ησ√T)F = PDCP*[(B/S)μ+λN(ηz)+(B/S)μ-λN(ηz-2ηλσ√T)]


x1 = [ln(S/K) / σ√T] + (1 + μ)σ√Ty1 = [ln(B2 / SK) / σ√T] + (1+μ)σ√Tz = [ln (B / S) / σ√T] + λσ√T

x2 = [ln(S/B) / σ√T]+(1+μ)σ√T
y2 = [ln(B/S) / σ√T] + (1+μ)σ√Tμ = (cc - σ2 / 2) /σ2 λ = [(μ2 + (2rf/σ2))]1⁄2






[FONT=Open Sans, Helvetica, Arial, sans-serif]My VBA makro:

[/FONT]Function DOP_BS(ByVal S0 As Double, _
ByVal X As Double, _
ByVal B As Double, _
ByVal T As Double, _
ByVal rf As Double, _
ByVal Vola As Double, _
ByVal m As Long, _
Optional ByVal q As Double)

Dim x_1 As Double, _
x_2 As Double, _
y_1 As Double, _
y_2 As Double, _
z As Double, _
u As Double, _
lambda As Double

If IsMissing(q) Then q = 0#


x_1 = (Log(S / X)) / Vola * Sqr(T) + (1 + u) * Vola * Sqr(T)
x_2 = (Log(S / B)) / Vola * Sqr(T) + (1 + u) * Vola * Sqr(T)
y_1 = (Log(B ^ 2 / S * X)) / Vola * Sqr(T) + (1 + u) * Vola * Sqr(T)
y_2 = (Log(B / S)) / Vola * Sqr(T) + (1 + u) * Vola * Sqr(T)
z = (Log(B / S)) / Vola * Sqr(T) + lambda * Vola * Sqr(T)
u = (rf - (Vola ^ 2) / 2) / Vola ^ 2
lambda = Sqr(u ^ 2 + ((2 * r) / Vola ^ 2))


Dim A As Double, _
B As Double, _
C As Double, _
D As Double, _
E As Double

A = -1 * S0 * Exp(-rf * T) * SNV(-x_1) + X * Exp(-rf * T) * SNV(-x_1 + Vola * Sqr(T))
B = -1 * S0 * Exp(-rf * T) * SNV(-x_2) + X * Exp(-rf * T) * SNV(-x_2 + Vola * Sqr(T))
C = -1 * S0 * Exp(-rf * T) * (B / S) * Exp(2 * (u + 1)) * SNV(y_1) + X * Exp(-rf * T) * (B / S) * Exp(2 * u) * SNV(y_1 - Vola * Sqr(T))
D = -1 * S0 * Exp(-rf * T) * (B / S) * Exp(2 * (u + 1)) * SNV(y_2) + X * Exp(-rf * T) * (B / S) * Exp(2 * u) * SNV(y_2 - Vola * Sqr(T))


DOP_BS = A - B + C - D

End Function


THANK YOU!

 

[StephenCrump]

Welcome to the Forum!

Just based on a quick look at your code, there are a few places where you have translated a/bc --> a/b*c, which is not the same. You have also translated ln into log, which is again not the same. For example:

Required result: x1 = [ln(S/K) / σ√T] + (1 + μ)σ√T

You have:

x_1 = (Log(S / X)) / Vola * Sqr(T) + (1 + u) * Vola * Sqr(T)

which you need to change to:

x_1 = Ln(S / X) / (Vola * Sqr(T)) + (1 + u) * Vola * Sqr(T)

You need to do similar for σ√T in x2, y1, y2 and z.

It would help in debugging if your variable names were consistent with the formulae you're using, e.g. use K rather than X.

Can we assume your SNV function is working OK? Is this an add-in, or something you have written?

I haven't been through all your calculations, but there may be other coding discrepancies, e.g. I did note:

Required result: A = Φ*S*exp((cc-r_f)T) *N(Φx ) – Φ*K* exp(- r_fT) *N(Φx1-Φσ√T)
where Φ = -1

You have:

A = -1 * S0 * Exp(-rf * T) * SNV(-x_1) + X * Exp(-rf * T) * SNV(-x_1 + Vola * Sqr(T))

What happened to the cost of carry cc?

 

[JohnDenim]

Thank you for your response, I btw love Australia! Was there at the East Coast for travelling. Our teacher told us that log() in VBA is the natural logarithm which I need to use here in this formula. I also added some brackets f.e. for a/b*c to a/(b*c). I get a result now, but it is definitely wrong as it goes into the millions if i set the Vola very low. The makro looks now like this:

Function DAOP_BS(ByVal S0 As Double, _
ByVal X As Double, _
ByVal B As Double, _
ByVal T As Double, _
ByVal rf As Double, _
ByVal Vola As Double, _
ByVal m As Long, _
Optional ByVal q As Double) As Double

Dim x_1 As Double, _
x_2 As Double, _
y_1 As Double, _
y_2 As Double, _
z As Double, _
u As Double, _
lambda As Double, _
S_x1 As Double, _
S_x1_2 As Double, _
S_x2 As Double, _
S_x2_2 As Double, _
S_y1 As Double, _
S_y1_2 As Double, _
S_y2 As Double, _
S_y2_2 As Double

If IsMissing(q) Then q = 0#


x_1 = (Log(S0 / X)) / (Vola * Sqr(T)) + (1 + u) * Vola * Sqr(T)
x_2 = (Log(S0 / B)) / (Vola * Sqr(T)) + (1 + u) * Vola * Sqr(T)
y_1 = (Log(B ^ 2 / (S0 * X))) / (Vola * Sqr(T)) + (1 + u) * Vola * Sqr(T)
y_2 = (Log(B / S0)) / (Vola * Sqr(T)) + (1 + u) * Vola * Sqr(T)
z = (Log(B / S0)) / (Vola * Sqr(T)) + lambda * Vola * Sqr(T)
u = (rf - (Vola ^ 2) / 2) / (Vola ^ 2)
lambda = Sqr(u ^ 2 + ((2 * rf) / (Vola ^ 2)))


S_x1 = SNV(-x_1)
S_x1_2 = -x_1 - -1 * Vola * Sqr(T)
S_x2 = SNV(-x_2)
S_x2_2 = -x_2 - -1 * Vola * Sqr(T)
S_y1 = SNV(y_1)
S_y1_2 = SNV(y_1 - Vola * Sqr(T))
S_y2 = SNV(y_2)
S_y2_2 = SNV(y_2 - Vola * Sqr(T))

Dim A As Double, _
L As Double, _
C As Double, _
D As Double, _
E As Double

A = -1 * S0 * Exp(-rf * T) * S_x1 - -1 * X * Exp(-rf * T) * S_x1_2
L = -1 * S0 * Exp(-rf * T) * S_x2 - -1 * X * Exp(-rf * T) * S_x2_2
C = -1 * S0 * Exp(-rf * T) * (B / S0) * Exp(2 * (u + 1)) * S_y1 - -1 * X * Exp(-rf * T) * (B / S0) * Exp(2 * u) * S_y1_2
D = -1 * S0 * Exp(-rf * T) * (B / S0) * Exp(2 * (u + 1)) * S_y2 - -1 * X * Exp(-rf * T) * (B / S0) * Exp(2 * u) * S_y2_2




DAOP_BS = A - L + C - D

End Function

Again, I would be very thankful if someone could help me out here and sees anymore mistakes!

 

[StephenCrump]

Again, just based on a quick look, you still don't seem to be treating cc consistently in your formulae ..

Required results:

1. μ = (cc - σ² / 2) /σ²
2. A, B, C and D all include a term (cc-r_f)T

You are coding:

1. μ = (r_f - σ² / 2) /σ², implying cc=rf
2. For A, B, C and D, -r_fT, implying cc=0?

 

[StephenCrump]

Our teacher told us that log() in VBA is the natural logarithm which I need to use here in this formula.

Quite rightly. My silly mistake, sorry.

 

[JohnDenim]

Thanks again for your reply, but I assume a non-dividend paying stock so that cc=rf.

 

[StephenCrump]

I assume a non-dividend paying stock so that cc=rf.

OK, but the results A, B, C and D each include a term: exp((cc-rf)T) which would then simplify to 1.

But instead, you have rendered each of these as exp(-rf*T)?

 

[JohnDenim]

Thanks, I got it through another formula. I really appreciate your time!

 

[JohnDenim]

@Jacek, i cannot reply to you, because your mail is full.

 

[jck_jck]

@JohnDenim - Would you mind trying now? - I deleted messages. If not possible maybe you could help out on this thread? Thank you very much