CODEWITHSUNDEEP
functionvba
user1155299
user1155299

Reputation: 927

returning estimated parameters using VBA

I have the below function that I am using to enter 2 initial parameters, rho and V, and based on minimized sum of squared errors, I want it to return the estimated parameters, rho and V.

Public Function RhoAndV(params, MktStrike, MktVol, Vol, Fwd, Tau, Beta)
    Dim rho, V, Alpha As Double
    Dim i As Integer, L As Integer
    Dim sqdError() As Double, ModelVol() As Double
    rho = params(1)
    V = params(2)
    Alpha = AFn(Fwd, Fwd, Tau, Vol, Beta, rho, V)
    'MsgBox ("Alpha=" & Alpha)
    L = MktVol.Cells.Count
    ReDim ModelVol(L) As Double, sqdError(L) As Double
    For i = 1 To L
        ModelVol(i) = Vfn(Alpha, Beta, rho, V, Fwd, MktStrike(i), Tau)
        'MsgBox ("ModelVol(i)=" & ModelVol(i))
        sqdError(i) = (ModelVol(i) - MktVol(i)) ^ 2
    Next i
    RhoAndV = Application.SUM(sqdError)
    End Function

I have rho and V in cells A1:A2 with values 0.1 and 0.1, so I select A1:A2 as params. In cells C3:C6, I have MktStrike with values

30
31
32
33

In cells D3:D6, I have MktVol with values

0.23
0.24
0.25
0.26

This function now returns the sumof squared errors (I run it in Excel as =RhoAndV(A1:A3,C3:C6,D3:D6,0.25,10,1,0.5) and it returns .003. However, I want it to return the estimated parameters, rho and V. Can anyone please tell me how to fix this function to do so?

Upvotes: 0

Views: 103

Answers (1)

Tim Williams
Tim Williams

Reputation: 166755

You'd modify your original function to something like the example below. How you return the values depends on whether you want to return them to a row (eg D3:E3) or column (eg. D3:D4)

Function RhoAndV(params, MktStrike, MktVol, Vol, Fwd, Tau, Beta)

    'rest of your code

    'RhoAndV = Array(rho, V) 'returning to a row
    RhoAndV = Application.Transpose(Array(rho, V)) 'returning to a column
End Function

Upvotes: 1

Related Questions