Excel

In this lecture we calculate the convexity for an annual-paying bond.

We do it first by building up an Excel grid, showing how each element of convexity is calculated.

Because Excel (rather strangely) does not have its own built-in CONVEXITY function (similar to DURATION or MDURATION), we also cover how to build your own personal convexity function within Excel’s VBA development environment.

The code for that demonstration function, for you to copy and paste, can be found below:

Public Function AndysAnnualConvexity(dYTM As Double, _
                                     dCouponRate As Double, _
                                     iBondMaturity As Integer, _
                                     dFaceValue As Double) As Double
Dim dConvexity As Double
Dim dAnnualPayment As Double
Dim dPresentValue As Double
Dim dTotalPVs As Double
Dim dTotalConvexity As Double

dConvexity = 0
dAnnualPayment = 0
dPresentValue = 0
dTotalPVs = 0
dTotalConvexity = 0

Dim dCouponPayment As Double

dCouponPayment = dCouponRate * dFaceValue

Dim dYearCounter As Double

dYearCounter = 1

Do While dYearCounter <= iBondMaturity

    dAnnualPayment = dCouponPayment
    
    If dYearCounter = iBondMaturity Then
    
        dAnnualPayment = dAnnualPayment + dFaceValue
        
    End If
    
    dPresentValue = dAnnualPayment / _
                    (Application.WorksheetFunction.Power(1 + dYTM, dYearCounter))
    
    dTotalPVs = dTotalPVs + dPresentValue
    
    dConvexity = (1 / (Application.WorksheetFunction.Power(1 + dYTM, 2))) * _
                 (dPresentValue) * _
                 (Application.WorksheetFunction.Power(dYearCounter, 2) + dYearCounter)
                 
    dTotalConvexity = dTotalConvexity + dConvexity
    
    dYearCounter = dYearCounter + 1
    
Loop

AndysAnnualConvexity = (dTotalConvexity / dTotalPVs)

End Function

In our next lecture, we will implement convexity to predict future bond prices.

The full YouTube playlist of Securities Investment 101 lecture videos can be found by clicking here.

Please read our disclaimer.

{ 0 comments }

In this Excel Library video, we take a limited amount of bond yield information, and then extrapolate and interpolate from this a good-fitting yield curve which covers all the ‘potential’ rates in-between.

We do this using the Nelson-Siegel-Svensson method, via the Excel data tool, Solver, and minimise residual error squares to create a believable yield curve, despite a lack of complete information.

The main Nelson-Siegel-Svensson block of code used in this video can be copied from the section below:

=(Beta1)+
(Beta2*((1-EXP(-A2/Lambda1))/(A2/Lambda1)))+
(Beta3*((((1-EXP(-A2/Lambda1))/(A2/Lambda1)))-
(EXP(-A2/Lambda1))))+
(Beta4*((((1-EXP(-A2/Lambda2))/(A2/Lambda2)))-
(EXP(-A2/Lambda2))))

The full YouTube playlist of Excel Library lecture videos can be found by clicking here.

Please read our disclaimer.

{ 0 comments }

Calculating Yield To Maturity from a Bond Price, Lecture 014, Securities Investment 101

June 14, 2013

We examine the theory behind how to calculate a required interest rate yield to maturity from a given bond price, then use three different methods in Excel to achieve the calculation. The methods used in Excel are the use of a scroller tied to an interest rate field, the built-in RATE() function, and the GoalSeek […]

Read the full article →

Commenting Excel Cells with VBA, Excel Library

May 24, 2013

In this MithrilMoney Excel Library video, we explain how to use Excel’s built-in VBA programming environment to create a tool which can comment all the formulas in Excel spreadsheets. No knowledge of VBA programming is required. The code segment mentioned in the video is as follows: The full YouTube playlist of Excel Library lecture videos […]

Read the full article →