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

Averages, Covariance and Correlation

Recall:

These are some of the important group statistics for portfolio optimization.

1. Averages - a $1$ x $n$ matrix whose inputs are the average of each stocks.
2. Covariance Matrix - the vector product of the mean returns transpose and the mean return itself
3. Correlation Matrix  - $ {cov_{ij}} / {\sqrt{cov_{ii}cov_{jj}}} $

Using functions in VBA

Sub cov()
    Dim str As String
    str = Range("D3")
    
    'calling returns
    ret = Range(str)

    'counting columns
    c = UBound(ret, 2)
    
    'Writing Output
    
    Range("B5") = "Averages"
        'writing answer to averages
        Range("B6").Select
        ActiveCell.Range(Cells(1, 1), Cells(1, c)) = AveM(ret) 'it calls for the function Ave and paste it to a

    Range("B8") = "Covariance Matrix"
        'writing answer to covariance
        Range("B9").Select
        ActiveCell.Range(Cells(1, 1), Cells(c, c)) = CovarianceM(ret)
    
    Range("B" & 10 + c) = "Correlation Matrix"
        'writing answer to correlation
        Range("B" & 11 + c).Select
        cv = CovarianceM(ret)
        ActiveCell.Range(Cells(1, 1), Cells(c, c)) = CorrelationM(cv)
End Sub

Function AveM(ret)
    'summing up all values in each stock and then dividing it by the total number of returns
    
    'counting rows and columns
    r = UBound(ret)
    c = UBound(ret, 2)
        
    'creates 1xc array
    a = Range(Cells(1, 1), Cells(1, c))
    
    'summing up all values in row, then divide it by the row
    For j = 1 To c
        s = 0
        For i = 1 To r
            s = ret(i, j) + s
        Next i
        a(1, j) = s / r
    Next j
        'returns a as the average
        AveM = a
End Function

Function CovarianceM(ret)
    'covariance matrix  = (mean returns transpose x mean returns)/(n-1)
    
    'count rows and columns
    r = UBound(ret)
    c = UBound(ret, 2)
    
    'create dummy range for mean returns
    mret = Range(Cells(1, 1), Cells(r, c))
    
    'create dummy range for average and run the function Ave
    a = Range(Cells(1, 1), Cells(1, c))
    a = AveM(ret)
    
    'solving for mean returns
    For i = 1 To r
        For j = 1 To c
            mret(i, j) = ret(i, j) - a(1, j)
        Next j
    Next i
    
    'solving for transpose and the covariance matrix
    trans = Application.WorksheetFunction.Transpose(mret)
    covm = Application.WorksheetFunction.MMult(trans, mret)
    For i = 1 To c
        For j = 1 To c
            covm(i, j) = covm(i, j) / (r - 1)
        Next j
    Next i
    
    'returning the covariance matrix
    CovarianceM = covm
    
End Function

Function CorrelationM(covm)
    'Correlation Matrix cor_ij = cov_ij / [sqrt(cov_ii)*sqrt(cov_jj)]
    
    'count rows only since covm is an nxn matrix
    r = UBound(covm)
    
    'creating dummy range for cor_ij
    cor = Range(Cells(1, 1), Cells(r, r))
    
    'solving for correlation matrix
    For i = 1 To r
        For j = 1 To r
            cor(i, j) = covm(i, j) / (covm(i, i) * covm(j, j)) ^ (1 / 2)
        Next j
    Next i
    
    CorrelationM = cor
End Function

VBA: Covariance Matrix 2

Here is the second attempt in programming the covariance matrix. I still have problems with getting the returns and dynamic averages.


Sub COV2M()

    Dim ws As Worksheet
    Set ws = Sheets.Add
        
    ws.Range("A1") = "Covariance Matrix"

    ws.Range("A3") = "Prices:"
    
    'prices
    p = Worksheets("Sheet1").Range("B2:K100")
    
    'counting rows and columns
    r = Worksheets("Sheet1").Range("B2:K100").Rows.Count
    c = Worksheets("Sheet1").Range("B2:K100").Columns.Count
    
    'pasting price to ws
    ws.Range("A5").Select
    Set ans = ActiveCell.Range(Cells(1, 1), Cells(r, c))
    ans.Value = p
    
    'Returns
    ws.Range("M3") = "Returns"
        
    'rows of return
    r = r - 1
    
    ret = Range("M6:V103")
    For i = 1 To r
        For j = 1 To c
            ret(i, j) = p(i + 1, j) / p(i, j) - 1
        Next j
    Next i
    Range("M6:V103") = ret
    
    'getting the averages
    ws.Range("X3") = "Average"
    ave = ws.Range("X6:AG6")
    For j = 1 To c
        s = 0
        For i = 1 To r
            s = ret(i, j) + s
        Next i
        ave(1, j) = s / r
    Next j
    ws.Range("X6:AG6") = ave
    
    'getting the mean Returns
    ws.Range("AI3") = "Mean Returns"
    mret = ws.Range("AI6:AR103")
    For i = 1 To r
        For j = 1 To c
            mret(i, j) = ret(i, j) - ave(1, j)
        Next j
    Next i
    ws.Range("AI6:AR103") = mret
    
    'getting the covariance matrix
    ws.Range("AT3") = "Covariance"
    trans = Application.WorksheetFunction.Transpose(mret)
    covm = Application.WorksheetFunction.MMult(trans, mret)
    For i = 1 To c
        For j = 1 To c
            covm(i, j) = covm(i, j) / (r - 1)
        Next j
    Next i
    
    Range("AT6").Select
    Set ans = ActiveCell.Range(Cells(1, 1), Cells(c, c))
    ans.Value = covm
    
End Sub

VB: Covariance Matrix

This is getting the Covariance Matrix using the Record Macro. It is to analyze how it is being done in VBA.

Sub CovMatrix()
'
' CovMatrix Macro
' Fixed, Relative References is not used
'
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "Covariance Matrix given Prices"
    
    'Prices
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "1. Get the Prices"
    Range("B6").Select
    Sheets("Sheet1").Select
    Range("K100").Select
    ActiveWindow.SmallScroll Down:=-24
    ActiveWindow.ScrollRow = 46
    ActiveWindow.ScrollRow = 45
    ActiveWindow.ScrollRow = 43
    ActiveWindow.ScrollRow = 41
    ActiveWindow.ScrollRow = 39
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 36
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 23
    ActiveWindow.ScrollRow = 21
    ActiveWindow.ScrollRow = 18
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    Range("B2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("VBA").Select
    ActiveSheet.Paste
    Range("N4").Select
    Application.CutCopyMode = False
    
    'Returns
    ActiveCell.FormulaR1C1 = "2. Change it into Returns"
    Range("N7").Select
    ActiveCell.FormulaR1C1 = "=RC[-12]/R[-1]C[-12]-1"
    Range("N7").Select
    Selection.Copy
    Range("N8:W104").Select
    ActiveSheet.Paste
    Range("O7:W7").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    'Averages
    Range("Y4").Select
    ActiveCell.FormulaR1C1 = "3.Get the Averages"
    Range("Y7").Select
    ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-11]:R[97]C[-11])"
    Range("Y7").Select
    Selection.Copy
    Range("Z7:AH7").Select
    ActiveSheet.Paste
    Range("Y7:AH7").Select
    Application.CutCopyMode = False
    
    'Mean adjusted Return
    Range("AJ4").Select
    ActiveCell.FormulaR1C1 = "4.Mean Adjusted Returns"
    Range("AJ7").Select
    ActiveCell.FormulaR1C1 = "=RC[-22]-R7C[-11]"
    Range("AJ7").Select
    Selection.Copy
    Range("AJ8:AJ104").Select
    ActiveSheet.Paste
    Range("AK7:AS104").Select
    ActiveSheet.Paste
    Range("AU5").Select
    Application.CutCopyMode = False
    
    'covariance Matrix
    Range("AU4").Select
    ActiveCell.FormulaR1C1 = "5. Get the Covariance matrix"
    Range("AU7:BD16").Select
    Selection.FormulaArray = _
        "=MMULT(TRANSPOSE(RC[-11]:R[97]C[-2]),RC[-11]:R[97]C[-2])/(COUNT(RC[-11]:R[97]C[-11])-1)"
    ActiveWindow.ScrollRow = 75
    ActiveWindow.ScrollRow = 74
    ActiveWindow.ScrollRow = 73
    ActiveWindow.ScrollRow = 72
    ActiveWindow.ScrollRow = 71
    ActiveWindow.ScrollRow = 70
    ActiveWindow.ScrollRow = 69
    ActiveWindow.ScrollRow = 67
    ActiveWindow.ScrollRow = 66
    ActiveWindow.ScrollRow = 65
    ActiveWindow.ScrollRow = 64
    ActiveWindow.ScrollRow = 62
    ActiveWindow.ScrollRow = 60
    ActiveWindow.ScrollRow = 58
    ActiveWindow.ScrollRow = 57
    ActiveWindow.ScrollRow = 55
    ActiveWindow.ScrollRow = 54
    ActiveWindow.ScrollRow = 53
    ActiveWindow.ScrollRow = 52
    ActiveWindow.ScrollRow = 50
    ActiveWindow.ScrollRow = 49
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 47
    ActiveWindow.ScrollRow = 46
    ActiveWindow.ScrollRow = 45
    ActiveWindow.ScrollRow = 44
    ActiveWindow.ScrollRow = 43
    ActiveWindow.ScrollRow = 42
    ActiveWindow.ScrollRow = 41
    ActiveWindow.ScrollRow = 40
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 37
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 26
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 23
    ActiveWindow.ScrollRow = 21
    ActiveWindow.ScrollRow = 19
    ActiveWindow.ScrollRow = 17
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 11
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    ActiveWindow.LargeScroll ToRight:=-1
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 21
End Sub

Thinking of a dynamic way to program this Covariance. Somehow copy and paste seems to be the easier way. Though when reading the code by itself and applying it using relative references, it doesn't make sense. So I have to go back to arrays and matrices.

Secant Method Exercise

This exercise is also on numericalmethods.xlsx

Given:  $ f(x) = x^2-10x+9$, find the roots using the secant method.

Computation:

1. We choose two points for $x_{-1}$ and $x_{0}$.
2. Solve for the next points using the formula: \[  x_n = x_{n-1} - f(x_{n-1}) \frac{x_{n-1}-x_{n-2}}{f(x_{n-1})-f(x_{n-1})}   \]

This formula is derived from getting the slope of the secant lines through the points $(x_n,f(x_n))$, $(x_{n-1},f(x_{n-1}))$ and  $(x_{n-2},f(x_{n-2}))$.

\[  \frac{f(x_n) - f(x_{n-1})}{x_n - x_{n-1}} = \frac{f(x_{n-1}) - f(x_{n-2})}{x_{n-1} - x_{n-2}} \]

Since $f(x_n)=0$, then
\[ \frac{0 - f(x_{n-1})}{x_n - x_{n-1}} = \frac{f(x_{n-1}) - f(x_{n-2})}{x_{n-1} - x_{n-2}} \]

Solve for $x_n.$

\[ \begin{split}
- f(x_{n-1})(x_{n-1} - x_{n-2})      &= (x_n - x_{n-1})( f(x_{n-1}) - f(x_{n-2})) \\
\frac{- f(x_{n-1})(x_{n-1} - x_{n-2})}{ f(x_{n-1}) - f(x_{n-2})}    &=  x_n - x_{n-1} \\
 x_{n-1} - \frac{ f(x_{n-1})(x_{n-1} - x_{n-2})}{ f(x_{n-1}) - f(x_{n-2})}    &=  x_n \\
 x_n &=  x_{n-1} - \frac{ f(x_{n-1})(x_{n-1} - x_{n-2})}{ f(x_{n-1}) - f(x_{n-2})} \end{split}\]

3. Continue to iterate until $f(x) = 0$ or we've applied the stopping rules.

This is my answer.

I also applied secant method on $ f(x) = cos(x) - x$ and arrived at this result.

So far in excel it works. How about applying these methods in Visual Basic? Will it work? What can be my possible problems?