Interest Rate Models

The class groups discussed two of term structure models: The Vacisek Model and the CIR term model.  These models are used to model interest rates.

What about the models:

• equilibrium type of model
• uses the Mean Reversion 
• offer benefits since they are numerically quite simple and easy to solve with computers
• very helpful in finding the important factors. 
• interesting and widely tested empirically since they offer closed form solutions of their conditional and steady state density functions.
• possible to get negative interest rates

Given the following:

• $\Delta t = T-t$ 
• $a$ as the speed of mean reversion
• $\mu$ as the long run average rate
• $r$ as the current state
• $\sigma$ as the volatility

We can get the models:

CIR Model:
\[ P(t,T) = A(T-t)e^{-B(T-t)r}\]
where: \[ A(T-t) = \left [   \frac{2 \gamma e^{(a+ \gamma)(T-t)/2}} {(\gamma +a)(e^{\gamma (T-t)}-1} \right ]^{2ab/ \sigma^2} \]
and
\[ B(T-t) = \frac {2(e^{\gamma (T-t)}-1)}{(\gamma+ a)(e^{\gamma (T-t)}-1)+2 \gamma}\]


Vacisek Model:
\[ P(t,T) = A(T-t)e^{-B(T-t)r}\]

where: \[ A(T-t) = \text{exp} \left [ \frac{ ( B(T-t) - T+t ) (a^2b-\sigma^2/2)}{a^2} -\frac{\sigma^2 B(T-t)^2}{4a}\right ] \]
and
\[ B(T-t) = \frac {1-e^{-a(T-t)}}{a}\]

Comparing these two models, they have the same process only that the parameters $ A(T-t)$ and  $B(T-t)$ are different.


Sources: 

• Hull book
• Groups ppt. They simplified the process.

Monte Carlo: Antithetic and QuasiRandom

Please see: "Antithetic and QuasiRandom.xls"

As I have came to understand, the Antithetic and QuasiRandom is used to reduce the variances.

Antithetic technique:

• Each time random variable r is drawn, its complement 1-r calculated and used to drive a parallel run of the simulation. This tends to lead to negatively correlated output values; hence lower variance
• Example: this is from Monte Carlo - Antithetic and Quasi-Random.xls and it's quite direct to the point.
• Table: The rand generates the random variable, and the randns generate the distribution. S1 solves $S_T$ of the simulation. S2 solves for antithesis of $S_T$ of the simulation. Then we get the payoffs for each S1 and S2. Then get the average payoff. Then we do it many many times. 
• MC Value: Get the average of the avg pay and discount it to get the present value of the option. 

QuasiRandom technique:

• We use Low Discrepancy Sequences to determine the random variables
• Faure Sequence
• Halton Sequence
• Sobol Sequence
• Inverse normal distribution
• Box-Muller transform
• Example: This is from Monte Carlo - Antithetic and Quasi-Random.xls 
• Table: it uses Faure Sequence to determine the random variable, then Box-Miller transformation is used to determine the inverse normal distribution. Then we get $S_T$ for the share price and get the payoff.
• MC Value: Get the average of the payoff and discount it to get the present value of the option. 

As can be seen in the graph below, Monte Carlo simulation, both Quasi and Antithetic Variate, converges to Black Scholes formula.

Source: Monte Carlo - Antithetic and Quasi-Random.xls 

Monte Carlo Option Pricing

Our group reported on Monte Carlo Option Pricing:

Monte carlo option pricing final v3 from naojan

Notes:

Monte Carlo

• important is how to generate the random distribution.
• Do not rely on this as a black box solution..
• We can look at the historical data.
• We can do this as a project – straight forward models from hull or wilmott to generate the price setting model..to value our payoff.

Slide 3 - The Entire slides focused on Option Pricing. But for Portfolio Statistics we have 3 important concepts:

• Find an algorithm for how the most basic investments evolve randomly. 
• Equities: 
• often the lognormal random walk
• can be represented on a spreadsheet or in code as how a stock price changes from one period to the next by adding on a random return. 
• Fixed-income
• BGM model in modeling how interest rates of various maturities evolve
• Credit 
• A model that models the random bankruptcy of a company. 
• Can represent any interrelationships between investments  which can achieved through correlations.
• Understand the derivatives theory for after performing simulations of the basic investments, there is a need to have models for more complicated contracts that depend on them such as options/derivatives/contingent claims. 
• May be able to use the results in the simulation of thousands future scenarios to examine portfolio statistics 
• Ie. how classical Value at Risk can be estimated

Slide 4

Risk-neutrality assumption – We make the assumption that investors are risk neutral, i.e., investors do not increase the expected return they require from an investment to compensate for increased risk.

Cox and Ross (1976) have shown that the assumption of risk neutrality can be used to obtain solutions of option valuation problems.’ This implies that the expected return on the underlying asset is the risk-free rate and that the discount rate used for the expected payoff on an option  is the risk-free rate.

Slide 6 - Stages from Watsham book and the original Boyle paper

Slide 7 - 14 - Option pricing_monte carlo example.xls

Slide 15 -Example from Wilmott: 

Its difference from watsham is that the watsham example creates random variables with empirical data's probability distribution. Wilmotts random variables are with uniform probability distribution.

***Note: have to understand the "Antithetic and QuasiRandom.xls"

VBA: CRR European, BS, and American Options

How the code works:

This is what I have done so far. It contains the basic codes for BS Merton, CRR European and American Options. It also includes the greeks, delta, gamma and theta. I coded it the way I understood options. In a visual way. Though it's not yet tested for other numbers.

Sub Test() just contains the input and output of what is happening on the code since I'm just halfway done. The "user" part and the "output" part isn't completed yet. It is just presentable.

GenEurAm() contains the code in solving the option value and generating the values involved in the tree for Amerucan and European options.

Greeks() computes for the greeks and has an output table of the greeks.

GenBS() is the Black-scholes Merton way for computing european options

DrawTree() generates a designed tree complete with asset price and option prices. Though it is not as efficient as I would like. I computed for 100 steps and it took 9 secs to generate the tree alone.

DrawTree2() generates a tree in a more efficient manner. I have a choice whether what tree to generate. Either the price tree or the option value tree.

The functions are just a way to make the code flexible and more user-friendly. Though I don't know what to do with them right now at my codes..maybe later it will be handy.

Will do the user part next. and then the output sheets.

New to the code:

• Learned: Declaring variables outside the sub or function
• ActiveCell.Offset(0, 0).Value 
• Range(starter).Offset(0, 0).Value
• On properties of the range
• With ... End With 
• .Interior.ColorIndex = 34
• .Borders.Weight = xlThin
• .HorizontalAlignment = xlCenter
• .Value = OptionValue(lp, j)

Code:

Public OptionValue() As Double

Public Price() As Double
Public bsOV As Double

Public kind As String
Public z As Integer

Public So As Double
Public K As Double
Public q As Double
Public r As Double
Public T As Double
Public n As Integer

Sub Test()
    
    So = 100
    K = 95
    q = 0
    r = 0.08
    vol = 0.3
    T = 0.5
    n = 5
    'kind = "a" 'american = a, european = e
    z = -1 'for call z= -1, if for put z= -1
    
    starter = "C11"
    
    Range(starter).Offset(0, 0).Value = "European:BS"
    Range(starter).Offset(0, 1).Value = GenBS(z, So, K, q, r, vol, T, n)
    
    Call GenEurAm("e", z, So, K, q, r, vol, T, n)
    Range(starter).Offset(1, 0).Value = "European:CRR"
    Range(starter).Offset(1, 1).Value = OptionValue(0, 0)
    
    Range(starter).Offset(3, 0).Value = "Asset Prices:"
    Range(starter).Offset(4, 1).Select
    Call DrawTree2(n, Price)
    Range(starter).Offset(n + 5, 0) = "European Option Prices:"
    Range(starter).Offset(n + 6, 1).Select
    Call DrawTree2(n, OptionValue)
    
    Call GenEurAm("a", z, So, K, q, r, vol, T, n)
    Range(starter).Offset(1, 3).Value = "American:CRR"
    Range(starter).Offset(1, 4).Value = OptionValue(0, 0)
    Range(starter).Offset(2 * n + 7, 0) = "American Option Prices:"
    Range(starter).Offset(2 * n + 8, 1).Select
    Call DrawTree2(n, OptionValue)
    
    Range(starter).Offset(3 * n + 9, 0).Value = "Greeks:"
    Range(starter).Offset(3 * n + 10, 1).Select
    Call Greeks
    
End Sub

Private Sub GenEurAm(kind, z, So, K, q, r, vol, T, n)
    
    'computation of the formulas
    u = Exp(vol * Sqr(T / n))
    d = 1 / u
    b = Exp((r - q) * T / n)
    p = (b - d) / (u - d)
    df = Exp(r * T / n)
    
    'Price tree
    ReDim Price(0 To n, 0 To n)
    
    'American and European Option tree
    ReDim OptionValue(0 To n, 0 To n)
    
    For j = n To 0 Step -1
        For lp = 0 To j
            
            'Price tree
            Price(lp, j) = So * u ^ lp * d ^ (j - lp)
            whenX = z * (Price(lp, j) - K)
            
            'option tree
            'at the end nodes
            If j = n Then
                OptionValue(lp, j) = Application.WorksheetFunction.Max(whenX, 0)
            'at other nodes
            Else
                whenNotX = (p * OptionValue(lp + 1, j + 1) + (1 - p) * OptionValue(lp, j + 1)) / df
                'for american option
                If kind = "a" Then
                    OptionValue(lp, j) = Application.WorksheetFunction.Max(whenX, whenNotX)
                'for european option
                ElseIf kind = "e" Then
                    OptionValue(lp, j) = whenNotX
                End If
            End If
        Next lp
    Next j

End Sub

Private Sub Greeks()
    'after calculating the prices and the option values
    'brute force
    
    Dim del(0 To 2) As Double
    Dim gam As Double
    Dim the As Double
        
    del(0) = (OptionValue(0, 1) - OptionValue(1, 1)) / (Price(0, 1) - Price(1, 1))
    
    del(1) = (OptionValue(0, 2) - OptionValue(1, 2)) / (Price(0, 2) - Price(1, 2))
    del(2) = (OptionValue(1, 2) - OptionValue(2, 2)) / (Price(1, 2) - Price(2, 2))
    gamma = (del(1) - del(2)) / (Price(0, 1) - Price(1, 1))
    
    theta = (OptionValue(1, 2) - OptionValue(0, 0)) / (2 * T / n)
    
    ActiveCell.Offset(0, 0).Value = "Delta"
    ActiveCell.Offset(0, 1).Value = del(0)
    ActiveCell.Offset(1, 0).Value = "Gamma"
    ActiveCell.Offset(1, 1).Value = gamma
    ActiveCell.Offset(2, 0).Value = "Theta"
    ActiveCell.Offset(2, 1).Value = theta
    
End Sub

Private Function GenBS(z, So, K, q, r, vol, T, n)
    'Black Scholes Merton
    
    'defining the parts
    d1 = Application.WorksheetFunction.Ln(So / K) + (r - q + vol ^ 2 / 2) * T / (vol * Sqr(T))
    d2 = Application.WorksheetFunction.Ln(So / K) + (r - q - vol ^ 2 / 2) * T / (vol * Sqr(T))
    
    If z = 1 Then
        nd1 = Application.WorksheetFunction.NormSDist(d1)
        nd2 = Application.WorksheetFunction.NormSDist(d2)
    ElseIf z = -1 Then
        nd1 = Application.WorksheetFunction.NormSDist(-d1)
        nd2 = Application.WorksheetFunction.NormSDist(-d2)
    End If
    
    'getting black scholes
    GenBS = z * (So * Exp(-q * T) * nd1 - K * Exp(-r * T) * nd2)
    
End Function

Private Sub DrawTree(n, starter)
    'with design, not efficient
    
    r = 2 * (2 * n + 1)
    For j = 0 To n
        nrow = r / 2 - 2 * j
        For lp = j To 0 Step -1
            With Range(starter).Offset(nrow, j)
                .Interior.ColorIndex = 42
                .Borders.Weight = xlThin
                .HorizontalAlignment = xlCenter
                .Value = Price(lp, j)
            End With
            With Range(starter).Offset(nrow + 1, j)
                .Interior.ColorIndex = 34
                .Borders.Weight = xlThin
                .HorizontalAlignment = xlCenter
                .Value = OptionValue(lp, j)
            End With
            nrow = nrow + 4
        Next lp
    Next j
End Sub

Private Sub DrawTree2(n, ranger)
    'after calculating for the prices and the optionvalue
    'simple lng
    
    For col = n To 0 Step -1
        For nrow = n - col To n
            ActiveCell.Offset(nrow, col).Value = ranger(n - nrow, col)
        Next nrow
    Next col
    
End Sub

Public Function EuroCall(So, K, q, r, vol, T, n)
    Call GenEurAm("e", "1", So, K, q, r, vol, T, n)
    EuroCall = OptionValue(0, 0)
End Function

Public Function EuroPut(So, K, q, r, vol, T, n)
    Call GenEurAm("e", "-1", So, K, q, r, vol, T, n)
    EuroPut = OptionValue(0, 0)
End Function

Public Function AmCall(So, K, q, r, vol, T, n)
    Call GenEurAm("a", "1", So, K, q, r, vol, T, n)
    AmCall = OptionValue(0, 0)
End Function

Public Function AmPut(So, K, q, r, vol, T, n)
    Call GenEurAm("a", "-1", So, K, q, r, vol, T, n)
    AmPut = OptionValue(0, 0)
End Function

Public Function BSCall(So, K, q, r, vol, T, n)
    BSCall = GenBS("1", So, K, q, r, vol, T, n)
End Function

Public Function BSPut(So, K, q, r, vol, T, n)
    BSPut = GenBS("-1", So, K, q, r, vol, T, n)
End Function

Sources:


http://www.cpearson.com/excel/classes.aspx

http://dmcritchie.mvps.org/excel/colors.htm

http://www.global-derivatives.com/index.php/component/content/52?task=view

Black, F. & Scholes, M. "The Pricing of Options & Corporate Liabilities" The Journal of Political Economy (May '73)

Hull, J. "Options, Futures & Other Derivatives" 5th Edition 2002 - Chapter 12

Merton, R. "Theory of Rational Option Pricing" Bell Journal of Economics & Management (June '73)


Mathlab Example, European or American options
          - http://www.goddardconsulting.ca/matlab-binomial-crr.html

American Call Option, Greeks, Python, getting data from google finance, etc., Concepts and formulas included
          - http://www.quantandfinancial.com/2012/11/cox-ross-rubinstein-option-pricing-model.html

Option Pricing Formulas book, Complete with greeks and American Options p279-288
          - http://r2-d2.bu.edu/AT__The_Complete_Guide_to_Option_Pricing_Formulas__2nd_ed_.pdf

Option Calculator
         - http://www.volatilitytrading.net/cox_ross_rubenstein_calculator.htm

CRR: Option Pricing

This post dwells on CRR tree with the application of Option Pricing.

I admit the first method is the simpler one but only used for European Option Pricing. However if I wanted to generalize European and American Option Pricing, it is better explained in the second method.

Inputs:

• Share Price (S) in dollars
• Exercise Price (K) in dollars
• Dividend yield (q) in percent (continuously paying)
• Risk-free rate (r ) in percent and p.a.
• Volatility ( σ ) in percent
• Time of maturity (T) in years
• Number of periods/ steps (n)  

Output: European Call and Put Option

Method 1: Direct Approach (similar to Haug book and  Jackson and Staunton book)

First we get the necessary $u$, $d$ and $p$ by using the following formulas:

• $u = e^{\sigma \Delta{t}}$
• $d = e^{-\sigma \Delta{t}}$  or simply $d = \frac{1}{u}$
• $p = \frac{ e^{(r-q)\Delta{t}} - d }{u-d} $

where $\Delta{t} = \frac{T}{n}$.

European Options are only exercised at the end of the period. That means we are just looking at the end nodes of the tree. In this case, we solve for the probability at each of the end nodes, multiply it by each payoff, sum it up and then get its present value. The formula for call and put option are as follows:

\[ c= e^{-rT} \sum_{i=0}^{n}  {_n}C_{i}  p^i  (1-p)^{n-i} \text{max}[Su^i d^{n-i} - K, 0] \]
\[ p= e^{-rT} \sum_{i=0}^{n}  {_n}C_{i}  p^i  (1-p)^{n-i} \text{max}[K-Su^i d^{n-i}, 0] \]
where

• $e^{-rT}$ is the discounting factor
• ${_n}C_{i}  p^i  (1-p)^{n-i}$ is the probabilities at each final node $n$ at step $i$
• $Su^i d^{n-i}$ is the prices at each final node $n$ at step $i$
• $\text{max}[Su^i d^{n-i} - K, 0]$ is the payoff for call option.
• $\text{max}[K-Su^i d^{n-i}, 0]$ is the payoff for put option.

Notice that the only difference for put and call options are their payoffs.

Example: same as in Haug approach sheet in CRRforProj.xlsm 

Given the following values, we want to compute for the European Call and Put Option.

Solution:

Method 2: Using Trees (also similar to Haug book, Jackson and Staunton book and the presentation found in earlier post.)


First we get the necessary $u$, $d$ and $p$ by using the following formulas:

• $u = e^{\sigma \Delta{t}}$
• $d = e^{-\sigma \Delta{t}}$  or simply $d = \frac{1}{u}$
• $p = \frac{ e^{(r-q)\Delta{t}} - d }{u-d} $

where $\Delta{t} = \frac{T}{n}$.

Then we create the tree for price. The formula is  $Su^i d^{n-i}$ at each node $j$ at step $i$.

Then we create the tree for the option price. The end nodes, we compute for the payoff. It is the same whether it is European or American options.

• $\text{max}[Su^i d^{n-i} - K, 0]$ is the payoff for call option.
• $\text{max}[K-Su^i d^{n-i}, 0]$ is the payoff for put option.

At the remaining nodes, European and American Option Prices differ even if the process is the same. It is because European option only exercises the end of the period while American options can be exercised earlier. So the formula for the remaining nodes $j$ at step {i} are as follows:

• European Call and Put: $O_{i,j}= e^{-r \Delta{t}} (p O_{i+1,j+1}+(1-p)O_{i,j+1} ) $
• American Call: $OV_{i,j} = \text{max}[Su^i d^{n-i} - K, e^{-r \Delta{t}} (pO_{i+1,j+1}+(1-p)O_{i,j+1} )]$
• American Put: $OV_{i,j} = \text{max}[K-Su^i d^{n-i},e^{-r \Delta{t}} (pO_{i+1,j+1}+(1-p)O_{i,j+1} )]$

Example:

for European Option Example: please see earlier post.

For American Option Example: same as in American Tree sheet in CRRforProj.xlsm 

Given the following values, we want to get the American Put Option Price.

Solution:

Source: 

• http://r2-d2.bu.edu/AT__The_Complete_Guide_to_Option_Pricing_Formulas__2nd_ed_.pdf
• Advance Modelling in Finance using Excel and VBA by Jackson and Staunton

Black Scholes: Option Pricing

I thank God for blogging. It forces me to put my thoughts into words, helping me to think clearly.

Inputs:

• Share Price (S) in dollars
• Exercise Price (K) in dollars
• Dividend yield (q) in percent (continuously paying)
• Risk-free rate (r ) in percent and p.a.
• Volatility ( vol ) in percent
• Time of maturity (T) in years or Option Life (T-t) (it doesnt matter

Output:

• European Call and Put Option

Method: Black-Scholes Merton

• With or without dividends, we can use the same formula.
• d1 = Ln(So / K) + (r - q + vol ^ 2 / 2) * T / (vol * Sqr(T)
• d2 = Ln(So / K) + (r - q - vol ^ 2 / 2) * T / (vol * Sqr(T))
• z  * (So * Exp(-q * T) * nd1 - K * Exp(-r * T) * nd2)
• if z = 1, we have a call option 
• if z = -1, we have a put option
• nd1 and nd2 are standard normal distribution of d1 and d2 respectively.

Sources:

• http://www.global-derivatives.com/index.php/component/content/52?task=view
• Black, F. & Scholes, M. "The Pricing of Options & Corporate Liabilities" The Journal of Political Economy (May '73)
• Hull, J. "Options, Futures & Other Derivatives" 5th Edition 2002 - Chapter 12

VBA: Autoregression and OLS in functions

The code calls for the range of the original series in one cell and the number of lags to the other cell.  It is assumed that the range of the original series is correct.(Meaning there is no checker for the input of the orig series, but for the lag, there is..) Then we wanted to do autoregression only with this given data.

The AR Function only calls for the original prices and the number of lags we wanted. Inside that function, the y and the x values are created. And then we throw those values to the OLS function where the regression is made.

The function OLS  y and x values as inputs. Since y is now a matrix, we are left with creating the x matrix in such a way that the first columns are 1's. The output would be the betas and coefficient of the model.

It was done as such, so that the OLS(y,x) can function independently. AR(orig,lag) however relies to the OLS function as to how I understood the concept.

The code is written below. It can also be seen in AR functions.xlsm


Sub test()

 'calling for the range and lag values
    Dim ran As String
    ran = Range("J3")
    lag = Range("J4")
    
    If lag < 1 Or lag Like "[A-Z,a-z]" Then MsgBox "Wrong Lag"

    orig = Range(ran)
    
    'output in the excel
    Range("H6") = "b="
    Range("I6").Select
    ActiveCell.Range(Cells(1, 1), Cells(lag + 1, 1)) = AR(orig, lag)

End Sub

Function AR(orig, lag)
    'given original series and number of lag, we apply AR
   
    'creating y values
    r = UBound(orig) - lag
    y = Range(Cells(1, 1), Cells(r, 1))
    For i = 1 To r
        y(i, 1) = orig(i + lag, 1)
    Next i
    
    'creating x values
    x1 = Range(Cells(1, 1), Cells(r, lag))
    
    For j = 1 To lag
        For i = 1 To r
              x1(i, j) = orig(i + lag - j, 1)
        Next i
    Next j

    AR = OLS(y, x1)
    
End Function

Function OLS(y, x1)
 'regression, getting betas and coefficient
 'given values y and x (not the matrix x)

    'adding ones to for coefficients in x matrix
    x = Range(Cells(1, 1), Cells(UBound(x1), UBound(x1, 2) + 1))
    For j = 1 To UBound(x, 2)
        For i = 1 To UBound(x)
            If j = 1 Then
                x(i, j) = 1
            Else
                x(i, j) = x1(i, j - 1)
            End If
        Next i
    Next j
    
    xtrans = Application.WorksheetFunction.Transpose(x)
    xtx = Application.WorksheetFunction.MMult(xtrans, x)
    xtxinv = Application.WorksheetFunction.MInverse(xtx)
    xtxinvxt = Application.WorksheetFunction.MMult(xtxinv, xtrans)
    bsol = Application.WorksheetFunction.MMult(xtxinvxt, y)
    OLS = bsol
    
End Function

VBA: Autoregression

The code calls for the range of the original series, and the number of lags we wanted for the autoregression.  then OLS in matrix form is performed with the output of the betas and coefficient of the model. Again this is not dynamic

And oh..I'm getting the used to adding watches and stops.

Sub regression()

    'calling for fixed range and lag values
    orig = Range("B3:B100")
    lag = 2
    
    r = Range("B3:B100").Rows.Count - lag
    y = Range(Cells(1, 1), Cells(r, 1))
    For i = 1 To r
        y(i, 1) = orig(i + lag, 1)
    Next i
    
    'creating x range
    c = lag + 1
    x = Range(Cells(1, 1), Cells(r, c))
    
    'individual, not dynamic
    'For i1 = 1 To r
    '    x(i1, 1) = 1
    'Next i1
    
    'For i2 = 1 To r
    '    x(i2, 2) = orig(i2 + 1, 1)
    'Next i2
    
    'For i3 = 1 To r
    '    x(i3, 3) = orig(i3 + 0, 1)
    'Next i3
    
    For j = 1 To c
        For i = 1 To r
              x(i, j) = orig(i + c - j, 1)
              If j = 1 Then x(i, j) = 1
        Next i
    Next j
    
    'Range("i7").Select
    'ActiveCell.Range(Cells(1, 1), Cells(r, 1)) = y
    'Range("j7").Select
    'ActiveCell.Range(Cells(1, 1), Cells(r, c)) = x
    
    'regression, getting b solution
    xtrans = Application.WorksheetFunction.Transpose(x)
    xtx = Application.WorksheetFunction.MMult(xtrans, x)
    xtxinv = Application.WorksheetFunction.MInverse(xtx)
    xtxinvxt = Application.WorksheetFunction.MMult(xtxinv, xtrans)
    bsol = Application.WorksheetFunction.MMult(xtxinvxt, y)

    'output in the excel
    Range("H6") = "b="
    Range("I6").Select
    ActiveCell.Range(Cells(1, 1), Cells(c, 1)) = bsol
    
End Sub

Autoregression Sample

Sample Auto regression of Stock returns in Excel

Recall: 
\[AR(2): y_t = b_0+b_1y_{t-1}+b_2y_{t-2}+ \text{error}\]
Process in Excel. (pls see autoregression.xlsm)

1. Since we want an AR(2), we first get the lag1 and lag2 of the original stock return as shown below.

2. From the Data Ribbon, click Data Analysis on the left side of the Ribbon.

3. Choose Regression, then click OK.

4. For the Input Y Range, choose the original series.

5. For the Input X Range, choose the lag series.

6. Remember to only choose the number of observations that both Y and X have. Unfilled Cells are disregarded. That means for the Y series, we chose B5:B100 and for the X series we chose C5:D100.

7. Click OK.

Results and Interpretation:

With 96 observations, the model resulted to an AR(2) with the linear combination of
\[ y_t = 0.00679131-0.0066908y_{t-1}-0.0486791y_{t-2}+ \text{error}\]
However, The F Statistics shows us that the value is greater than alpha of 0.05. It implies that the model does not fit to the data given. Moreover, the Adjusted $R^2=-0.01$ which implies that only -0.1% of the variance was explained.  Furthermore, the betas and the coefficient are not significant as their p-values are greater than alpha of 0.05. These leads us to the analysis that even though we got the model, it is not a good model to begin with. At least we know now how to do regression in Excel. :-)

Time Series Analysis

Trend Line - OLS

•  It has four major assumptions
• line
• normal distribution
• equal variance
• serial correlation (looking at the area of volatility)
• if not linear - transformation should occur. and anything transformed should revert it back to the original.

Autoregression

• regressing data against itself  - lags (for an example of how to in excel, see autoregression.xlsm)
• Sample model: $AR(2): y_t = b_0+b_1y_{t-1}+b_2y_{t-2}+ \text{error}$

Moving Average

• Sample model: $MA(2): y_t = e_0 +a_1e_{t-1}+a_2e_{t-2}$
• the $e$'s are the errors of an internal model.

ARMA

• Combination of both AR and MA Model
• Sample model: $ARMA(2,2): y_t =b_0+b_1y_{t-1}+b_2y_{t-2}+ e_0 +a_1e_{t-1}+a_2e_{t-2}$
• $AR(1):  y_t =b_0+b_1y_{t-1}+\text{error} $
• $b_1 <1$ - no problem. It stays stationary even if there are shocks.
• $b_1=1$ - shocks has permanent effect in the time series model.
• $b_1>1$ - not stationary. It explodes or decays.
• Covariance stationary
• mean is constant over time
• variance and covariance is constant over time
• $MA(1): y_t = e_0 +a_1e_{t-1}$
• the errors are artifacts of another model and its always brand new.
• Box-Jenkins
• AR
• ACF - decay to 0
• PACF -drops to 0
• MA
• ACF - drops to 0
• ACF - decay to 0
• Parsimony - important, how good is the forecast?
• AIC and BIC - choosing the model with the lowest value
• Random walk
• Model: $y_t = y_{t-1} + e$
• if the first differences is a white noise process.

Integrated Model

• If we difference it twice, we must go back twice after.
• ARIMA(p, d, q) model - AR(p), integration at d, MA(q)

Business Modelling

• MA - Kathang isip lamang because of the errors
• AR - is based on observable values
• Rule of thumb - 50 observations

Random walk with Drift

• An AR model:  $AR(2): y_t = b_0+b_1y_{t-1}+b_2y_{t-2}+ \text{error}$
• it is a random walk with trend

Mean Reversion

• MPT Application: Variance = $w^T \sigma w$ (sensitivity analysis on variancesensitivity.xlsx)
• must be homoscedastic
• not work on negatives

GARCH

• Conditional Heteroscedasticity applies
• Model:  $GARCH(1,1): h_t = \alpha_0+\alpha_1r_{t-1}^2+\beta_1h_{t-1}$

EWMA

CRR Tree

Since we are assigned to report on the CRR tree, our group made a powerpoint presentation on this. It includes the basics of a CRR tree.

Crr presentation from naojan

Notes: CRR presentation.pptx, CRRBOPMv2.xlsx

Jarrow-Rudd Tree (JR) tree is assumes 50% probability in the u and d. CRR don't.

Slide 6 

Step 1. Binomial model acts similarly to the asset that exists in a risk neutral world.

\[ pu+qd = e^{i * \Delta t} = r\]

where

\[\begin{split}
\Delta t &= \frac{t}{n} \\
t &= \text{term of the option} \\
n &= \text{number of periods}
\end{split}\]

Its variance: 

 \[ pu^2 + qd^2 – e^{(i* \Delta t)^2} = \sigma^2 \Delta t \]

Slide 9 - Notice that the lattice is symmetrical, that is due to the assumption that d=1/u (ud=1). Thus , it is easier to program since it involves fewer steps.

References:

• Cox, J.C., Ross S.A, Rubinstein, M., Option Pricing : A Simplified Approach. (1979). Published in Journal of Finance and Economics
• Watsham, Terry J., and Parramore, Keith. Quantitative Methods in Finance. (1997)
• http://investexcel.net/736/binomial-option-pricing-excel/
• http://www.sitmo.com/article/binomial-and-trinomial-trees/
• http://en.wikipedia.org/wiki/Binomial_options_pricing_model
• http://sfb649.wiwi.hu-berlin.de/fedc_homepage/xplore/tutorials/xlghtmlnode63.html#bin-fig2
• http://www.terry.uga.edu/~mayhew/Old/chapter9.pdf
• Other books:
• Hull book 
• Paul Wilmott
• Advance Modelling in Finance Using Excel and VBA