Sub to put generated values in workbook...

hi there,


Im having some trouble here. The code is not doing what i want it to do. The code again:

If verti = "Option price" And hori = "Time to exercise" And asset = "Equity" And choice = "call" Then

For i = 1 To 10
'j = 2
t_m = t + i

Worksheets("workings").Cells(j + 1, j).Value = t_m

a = Log(s / x)
b = (r - q + (0.5 * (sd ^ 2))) * t_m
c = sd * (t ^ 0.5)
d1 = (a + b) / c
d2 = d1 - sd * (t_m ^ 0.5)

Call_Eur = (s * Exp(-q * t_m) * Hcumnorm(d1)) - (x * Exp(-r * t_m) * Hcumnorm(d2))
Worksheets("workings").Cells(j, j + 1) = Call_Eur

Next i

'Worksheets("workings").Cells(j + 1, j).Value = t_m
'Worksheets("workings").Cells(j + 1, j + 1) = Call_Eur

End If

This code is supposed to generate t_m and each time, store it in a cell in my worksheet "workings". Then, it should use the generated value of t_m and calculate Call_Eur, and each time, put the value generated in my worksheet alongside the generated values of t_m.

But it's not!...I don't know why either. I have made a lot changes but it is still not working.

Would you have any idea why?

Alvin

You are not setting j anywhere, nor are you incrementing it during the loop. You have a lot of variables in there which are not declared, although you may have done this elsewhere. What is s? q? Hcumnorm? You need to check that all these are working, because I can't tell you just by looking at it. Why do you keep writing to the cells, rather than just twice at the end?

zaza

Hi zaza,

Thanks for the reply. I did define all the variables, but did not include them as I thought that they were not necessary. It is the loop that is not working properly, and i think that i will need to increment j (which is has been defined and is equal to 2).

And Hcumnorm is the normal distn that ive written and using, as the one in Excel is flawed and does not produce accurate results.

i will let u know if it works and then put up the working version.

Thanks again.

AJ

Im putting up thw whole code, which is still not working:

Sub opt_pr_vs_t2m() '(1) Plotting option price against time to maturity

Worksheets("workings").Range("b3:c102").ClearContents

' Getting the title on the vertical and horizontal axis right

asset = Worksheets("options").Range("e23").Value
choice = Worksheets("options").Range("e42").Value

verti = Worksheets("options").Range("k23").Value
hori = Worksheets("options").Range("k25").Value

Worksheets("workings").Range("b2").Value = verti
Worksheets("workings").Range("c2").Value = hori

'getting the values for the vertical and horizontal axes

s = Worksheets("options").Range("e30").Value ' s = Stock Price
sd = Worksheets("options").Range("e25").Value ' sd = standard deviation
r = Worksheets("options").Range("e26").Value ' r = Risk-free rate
f = Worksheets("options").Range("e27").Value ' f = foreign risk free rate
q = Worksheets("options").Range("e28").Value ' q = Continuous dividend yield
t = Worksheets("options").Range("e38").Value ' t = Time to exercise
x = Worksheets("options").Range("e40").Value ' x = Strike Price

z = Worksheets("options").Range("k30").Value ' z = number of generated x values - here time

Dim a As Double
Dim b() As Double
Dim c() As Double
Dim d1 As Double
Dim d2() As Double
Dim t_m() As Double
Dim Call_Eur() As Double
Dim i As Integer
Dim j As Integer
Dim m As Integer

ReDim b(1 To z)
ReDim c(1 To z)
ReDim d2(1 To z)
ReDim t_m(1 To z)
ReDim Call_Eur(1 To z)

If verti = "Option price" And hori = "Time to exercise" And asset = "Equity" And choice = "call" Then

sum1 = 0

Application.StatusBar = False

t = 0

j = 2

For i = 1 To z

t_m(i) = t + i
sum1 = sum1 + t_m(i)
m = j + 1
Worksheets("workings").Cells(m, m).Value = sum1

a = Log(s / x)
b(i) = (r - q + (0.5 * (sd ^ 2))) * sum1 't_m(i)
c(i) = sd * (sum1 ^ 0.5)
d1 = (a + b(i)) / c(i)
d2(i) = d1 - sd * (sum1 ^ 0.5)

sum2 = 0
Call_Eur(i) = (s * Exp(-q * sum1) * Hcumnorm(d1)) - (x * Exp(-r * sum1) * Hcumnorm(d2(i)))
sum2 = sum2 + Call_Eur(i)
Worksheets("workings").Cells(m, m - 1) = sum2

Next i

End If

End Sub

So there we are. I have been able to generate only two values so far.

help please!!!

AJ

As I said earlier, you are not incrementing j anywhere. Put:

j = j+1

before your Next i.

Although I don't really see why you need m and j.

zaza

Thanks..the code is working now...still some refinements need to be brought to it though...i will ppost the final version ASAP...