Duration

Duration is a measure of the sensitivity of the price of a bond to the change of interest rate. It's widely used as a risk measure for bonds. The higher a bond's duration, the more risky it is.


Excel provides a formula to calculate Duration (also refers to Macauley Duration) and MDuration (somewhat inaccurately termed by Excel as Macauley Duration), using the same syntax:
Duration(settlement, maturity, coupon, yield, frequency, basis)
MDuration(settlement, maturity, coupon, yield, frequency, basis)

The MDuration can be used to calculate the volatility of a bond. The difference between Duration and MDuration is as follow:
MDuration = Duration / [ 1 + (yield / number of coupon payment per year) ]

The effects of maturity and coupon on duration are showed as follow:

* Download the Excel file - Duration, Effects of Maturity and Coupon *

VBA for Variance-Covariance Matrix

Function VarCov(rng As Range) As Variant
   
    Dim i As Integer
    Dim j As Integer
    Dim colnum As Integer
    Dim matrix() As Double
   
    colnum = rng.Columns.Count
    ReDim matrix(colnum - 1, colnum - 1)
       
    For i = 1 To colnum
        For j = 1 To colnum
            matrix(i - 1, j - 1) = Application.WorksheetFunction.Covar(rng.Columns(i), rng.Columns(j))
        Next j
    Next i

    VarCov = matrix

End Function

 

* Download the excel file *