Serving the Quantitative Finance Community

 
nicre977
Topic Author
Posts: 1
Joined: November 7th, 2016, 11:40 am

Implementation of the Heston model in Excel

February 21st, 2017, 1:36 pm

I´m trying to write code for the heston model in VBA, I follow the example given by the book Option pricing models & volatility (Using excel & VBA), I´m using the closed form approach, so no monte carlo simulation is needed. First I have to write the code for two complex intergrals and that does not seem to work, below is the code for the first integral, can anybody see what´s wrong? The error message in excel is "Compile error: Sub or Function not defined" 

CODE:
Function HestonP1(phi, kappa, theta, lambda, rho, sigma, tau, K, S, r, v)
mu1 = 0.5
b1 = set_cNum(kappa + lambda - rho * sigma, 0)
d1 = cNumSqrt(cNumSub(cNumSq(cNumSub(set_cNum(0, rho * sigma * phi), b1)), cNumSub(set_cNum(0, sigma ^ 2 * 2 * mu1 * phi), set_cNum(sigma ^ 2 * phi ^ 2, 0))))
g1 = cNumDiv(cNumAdd(cNumSub(b1, set_cNum(0, rho * sigma * phi)), d1), cNumSub(cNumSub(b1, set_cNum(0, rho * sigma * phi)), d1))
DD1_1 = cNumDiv(cNumAdd(cNumSub(b1, set_cNum(0, rho * sigma * phi)), d1), set_cNum(sigma ^ 2, 0))
DD1_2 = cNumSub(set_cNum(1, 0), cNumExp(cNumProd(d1, set_cNum(tau, 0))))
DD1_3 = cNumSub(set_cNum(1, 0), cNumProd(g1, cNumExp(cNumProd(d1, set_cNum(tau, 0)))))
DD1 = cNumProd(DD1_1, cNumDiv(DD1_2, DD1_3))
CC1_1 = set_cNum(0, r * phi * tau)
CC1_2 = set_cNum((kappa * theta) / (sigma ^ 2), 0)
CC1_3 = cNumProd(cNumAdd(cNumSub(b1, set_cNum(0, rho * sigma * phi)), d1), set_cNum(tau, 0))
CC1_4 = cNumProd(set_cNum(2, 0), cNumLn(cNumDiv(cNumSub(set_cNum(1, 0), cNumProd(g1, cNumExp(cNumProd(d1, set_cNum(tau, 0))))), cNumSub(set_cNum(1, 0), g1))))
cc1 = cNumAdd(CC1_1, cNumProd(CC1_2, cNumSub(CC1_3, CC1_4)))
f1 = cNumExp(cNumAdd(cNumAdd(cc1, cNumProd(DD1, set_cNum(v, 0))), set_cNum(0, phi * Application.Ln(S))))
HestonP1 = cNumReal(cNumDiv(cNumProd(cNumExp(set_cNum(0, -phi * Application.Ln(K))), f1), set_cNum(0, phi)))
End Function
 
User avatar
Cuchulainn
Posts: 20254
Joined: July 16th, 2004, 7:38 am
Location: 20, 000

Re: Implementation of the Heston model in Excel

February 21st, 2017, 2:25 pm

Looks like a noobie qiuestion.

Nick Webber in his book on VBA discusses the Heston Model.
 
User avatar
outrun
Posts: 4573
Joined: January 1st, 1970, 12:00 am

Re: Implementation of the Heston model in Excel

February 21st, 2017, 2:32 pm

Amazing that you waste your time to post questions about "sub or function not defined"
 
User avatar
Cuchulainn
Posts: 20254
Joined: July 16th, 2004, 7:38 am
Location: 20, 000

Re: Implementation of the Heston model in Excel

February 21st, 2017, 2:59 pm

Amazing that you waste your time to post questions about "sub or function not defined"
Indeed. Which leads one to thinks that OP did not write the code but has copied it from a CD.
 
User avatar
outrun
Posts: 4573
Joined: January 1st, 1970, 12:00 am

Re: Implementation of the Heston model in Excel

February 21st, 2017, 4:32 pm

I copy paste code all the time, but I take the responsibility to exactly know what I'm doing.

OP doesn't know VBA, but even if he did, would he spot a typo, a + that should be a -?

This looks all very unresponsible. Human error risk here is much bigger than the gain of using Heston over B&S or even just the intrinsic value of the call !