CODEWITHSUNDEEP
excelvba
af1nn
af1nn

Reputation: 1

Rearrange equation to solve for a different variable

I am looking at VBA code (function) written by someone else.

Here is the code:

Function EuropeanDelta(StrikePrice, MarketPrice, Volatility, InterestRate As Double, PC As String, ValueDate, ExpiryDate As Date, Optional PriceOrYield As String = "P") As Double

Rem Declare our working variables
  Dim r As Double
  Dim d1 As Double
  Dim d2 As Double
  Dim t As Double
  Dim SqT As Double
Rem End of variable declaration

  If PriceOrYield = "Y" Then
    MarketPrice = 100 - MarketPrice
    StrikePrice = 100 - StrikePrice
    If PC = "C" Then
      PC = "P"
    Else
      PC = "C"
    End If
  End If

Rem Initiase our working variables

  t = (ExpiryDate - ValueDate) / 365
  SqT = Sqr(t)
  r = Application.WorksheetFunction.Ln(1 + InterestRate)

  d1 = (Application.WorksheetFunction.Ln(MarketPrice / StrikePrice) + (Volatility * Volatility * 0.5) * t) / (Volatility * SqT)

Rem Quick logic to deal with Calls or Puts

  If PC = "C" Then
    EuropeanDelta = Exp(-r * t) * Application.WorksheetFunction.NormSDist(d1)
  Else
    EuropeanDelta = -Exp(-r * t) * Application.WorksheetFunction.NormSDist(-d1)
  End If

  If PriceOrYield = "Y" Then
    EuropeanDelta = EuropeanDelta * -1
  End If

End Function

The whole problem is based around the line for "d1". I would like to re-organise to solve for "StrikePrice". I have tried writing it out mathematically and then re-arranging, then swapping back to VBA.

Upvotes: 0

Views: 205

Answers (2)

duffymo
duffymo

Reputation: 308968

This is just algebra - high school math.

Take it in steps. Make sure you do the same operation to both sides to make sure that equality still holds.

Here's your starting equation:

d = {ln(m/s) + v*v*t/2}/(v*sqrt(t))

Multiply both sides by the denominator of the RHS:

d*v*sqrt(t) = ln(m/s) + v*v*t/2

Subtract v*v*t/2 from both sides:

(d*v*sqrt(t) - v*v*t/2) = ln(m/s)

Apply the exponential function to both sides, noting that exp(ln(x)) = x:

exp(d*v*sqrt(t) - v*v*t/2) = m/s

Multiply both sides by s:

s*exp(d*v*sqrt(t) - v*v*t/2) = m

Divide both sides by exp(d*v*sqrt(t) - v*v*t/2) to get the desired result:

s = m/exp(d*v*sqrt(t) - v*v*t/2)

Let's see if this function makes sense.

At t = 0 the denominator exp(0) = 1, so the strike price is equal to the market price.

As t -> infinity, we hope that the denominator gets large so s -> zero. L'Hospital's Rule will help here.

Upvotes: 1

John Alexiou
John Alexiou

Reputation: 29264

@duffymo is correct, but am giving the answer directly in terms of VBA code

' d1 = (Log(MarketPrice / StrikePrice) + (Volatility * Volatility * 0.5) * t) / (Volatility * Sqr(t))
'
' Volatility * Sqr(t) * d1 = Log(MarketPrice / StrikePrice) + Volatility^2 * t/2
'
' Log(MarketPrice / StrikePrice) = Volatility * Sqr(t) * d1 - Volatility^2 * t/2
'
' MarketPrice / StrikePrice = Exp(Volatility * Sqr(t) * d1 - Volatility^2 * t/2)
'
StrikePrice = MarketPrice / Exp(Volatility * Sqr(t) * d1 - Volatility^2 * t/2)

Other Notes :

  • For brevity replace Application.WorksheetFunction.Ln() with Log()
  • There is no need cache SqT = Sqr(t) since it is only used once.
  • For clarity replace Volatility*Volatility with Volatility^2 as internally it does the same thing.

Upvotes: 1

Related Questions