VBA: Bond Valuation as Function

Given:  Data from automation macro Data.xls

On that excel sheet, we are given: term, credit rating, coupon rate and par value. Compute for the PV Cash flow/Market Value of the bond of the using the spot yield curve with the credit rating add on.

How the code works:

The Market Value is solved using the user defined function MV(term, creditrate, coupon, pvalue) with dynamic spot yield and credit rating, by letting the user define such.

1. We define the discounting factor depending on the requirements by using Lookup() of Excel.

2. We then use the discount factor in getting the cash flows. We sum up the PV of every year for the results. 

The AutoCompute() Sub automatically computes for the MV given number n of records.

New to the code:

• Defining Dimensions
• Application.WorksheetFunction.Lookup(i, spotYC)
• r = Range("A2").End(xlDown).Row - 1

Other learnings:

• one dimensional array: http://www.vbtutor.net/VBA/vba_chp21.htm
• Dim rate(10) as double

Code:

Function MV(term As Integer, CreditRate As String, Coupon As Double, pv As Double)
    
    Dim sumMV As Double
    Dim drate As Double
    Dim s As String
    Dim a As String
    
    s = Range("o6") 'range of spotyield
    spotYC = Range(s)
    a = Range("o7") 'range of credit rating addon
    CreditReq = Range(a)
    
    'determining individual credit rate Add on
    
    addon = Application.WorksheetFunction.Lookup(CreditRate, CreditReq)
    
    'determining cashflows
    
    sumMV = 0 'summing cashflows along each term
    For i = 1 To term
        srate = Application.WorksheetFunction.Lookup(i, spotYC) 'determine the spot yield rate per year
        drate = srate + addon
        sumMV = (Coupon * pv) / (1 + drate) ^ i + sumMV 'for cashflows with coup*pv
    Next i
    sumMV = sumMV + pv / (1 + drate) ^ term 'for the last cashflow


    If term = 0 Then
        sumMV = 0
    End If

    'throwing back to excel
    MV = sumMV

End Function


Sub AutoCompute()
    
    Dim term As Integer, CreditRate As String, Coupon As Double, pv As Double
    
    'first row serves as Titles so start with
    r = Range("A2").End(xlDown).Row - 1
      
    For i = 1 To r
        term = Range("b" & 1 + i)
        CreditRate = Range("c" & 1 + i)
        Coupon = Range("d" & 1 + i)
        pv = Range("e" & 1 + i)
        Range("f" & 1 + i) = MV(term, CreditRate, Coupon, pv)
    Next i
    
End Sub

VBA: Short Lecture Notes

Steps/Pointers for doing your code:

• Outline what you need to do in little modules.
• For each major procedure make an outline
• Try out the procedures on the spreadsheet 
•  Automate using the macro 
• Clean up the macro and do your editing. (Doing back engineering)
• Put your procedures together
•   Put your favourite codes together
• Stress test your code
• When in doubt, Use Help.

Some Clarifications

• Procedure: This is a basic set of codes that gets executed: Sub and End sub
•  Functions: creates a dynamic output: Function and End Function
• Modules: see all of your code

Variables: Common Data Type

• It must really be defined.
• Common Data Types
• Integers
• Long
• Single
• Double
• Decimal
• Date
• Object
• String - text
• Variant – any kind
• String x integer = error, so define the variables beforehand
• It entails dimensioning
• Data type Summary on VBA Help for more information

Declaring Variables 

• Code: Dim a as Integer, b as integer
• Declaring arrays
• Dim histdata(1 to 100) as double - it has 100 items
• Dim matrix(100,100) – it has 101 x 101 items

Looping

• For next
• For each next -
• Do while Loop – continue running until true
• Do until 
• Do – can endlessly run

If then Statements

     If a<b 

       Variable = 0

     Else
       Variable = 1
     End if
  

 Go to statement

• Changes the flow of the procedure
• BEST used for error trapping
• Abruptly change the code..
• Example: if variable = 1000, go to 170 (which is a statement outside the loop)
• Best used for error trapping

Case Constructions

• Works like if then statement, very handy for multiple cases

      Select case statement
         Case is <.5
            Procedure for case
         Case else
            Procedure

      End Select
                           
Visual programming - Forms

•  Codes are built around Forms
• In forms event are activated
• Left mouse click
• Click
• Enter range of values
• Working around the form: example: where you can capture the data at outputs the regression
• Form contain objects such as references, command button etc.
• Build form on arrays

Making Add-ins in excel

VBA: Intro to Userforms

Tips and Pointers:

1. Creating a new form: On the Insert Menu, Click Userform.
2. You can change name or label of userform in its Properties section. 
3. A form that choose a matrix or data from excel sheet: RefEdit in Toolbox. If there is no RefEdit, Right click on ToolBox--> Additional Controls --> Check refedit.ctrl --> Ok.
4. Double click on the object itself to view Code. OR On the Project Box, Forms folder, Right click on the form and select View Code.
5. To get the range in refedit: NewName = Range(RefeditName.Text)
6. To run the userform:  FormName.show
7. To end the userform: Unload Me

Additional New Codes:

1. Sheets.Add => adding new Sheets
2. Range("A5").Activate => activating the range

Example Process:

Given two matrices of unknown dimensions, we want to multiply them. 

1. Using the Button "Multiply!" in Excel, it opens up the Userform for Matrix Multiplication.

2. Select the two Matrix to multiply.

3. Click Ok, and then the answers will appear in a new sheet.

4. If wrong dimensions, a msgBox will appear.

5.Clicking the Cancel Button will end the Userform.

Code: userform mmult.xlsm

Sub Macro1()

    'For Button Multiply!

    MatrixMult.Show

End Sub

Private Sub CancelBut1_Click()

    Unload Me

End Sub

Private Sub OkBut1_Click()

    MatrixA = Range(mA.Text)

    MatrixB = Range(mB.Text)

    

    'Checker

    If UBound(MatrixA) = UBound(MatrixB) And UBound(MatrixA, 2) = UBound(MatrixB, 2) Then

        'Start of MMult

        r = UBound(MatrixA)

        c = UBound(MatrixA, 2)

    

        Sheets.Add

        ActiveCell.Range("A1").Select

        ActiveCell.Range(Cells(1, 1), Cells(r, c)) = Application.WorksheetFunction.MMult(MatrixA, MatrixB)

    Else

        MsgBox "Wrong dimensions of matrices"

    End If

    

End Sub

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