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