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

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.


In a non-mathematical way, and before our next lecture on actually calculating convexity, we try to explain why convexity has to be taken into account, particularly for large interest rate moves in either direction.

At the limit, interest rates moving down have a proportionally larger and larger effect on bond prices. Similarly, interest rates moving up have a proportionally smaller and smaller effect on bond prices.

Taken together at the limit of infinitely small interest rate moves, these two effects create convexity in the bond price, when measured against changing interest rates.

The flat-sloped tangential line of Modified Duration will then tend to underestimate price moves up, when interest rates move down, and will tend to overestimate price moves down, when interest rates move up.

This is the calculus principle behind convexity.

In our next lecture, we will actually calculate convexity.

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

Please read our disclaimer.


Modified Duration, Lecture 023, Securities Investment 101

December 5, 2013

We conclude our lectures on ‘Duration’, before we move on to ‘Convexity’, with an explanation of ‘Modified Duration’ and its importance in predicting how individual bond prices will change on specific interest rate changes. In passing, we cover the ‘MDURATION’ Excel function, and also dabble a little in different coupon payment frequencies, such as Annual, […]

Read the full article →

The Importance of Macaulay’s Duration, Lecture 022, Securities Investment 101

November 29, 2013

In this lecture we discuss the importance of Macaulay’s Duration, particularly to anyone holding large bond portfolios. In passing, we show how and why zero coupon bonds have a duration identical to their maturities, and why the anticipated directional moves of interest rates are so critical to bond traders and pension fund managers. We also […]

Read the full article →

Calculating Duration, Lecture 021, Securities Investment 101

November 25, 2013

Before we delve deeper into the mysteries of duration and convexity, now is a good opportunity to actually calculate duration, at least in its form as an average weighted cashflow period, known as Macaulay’s Duration. We do this both the long way, from first principles, and by using Excel’s built-in ‘DURATION’ command. Previous video, Securities […]

Read the full article →

Introducing Duration, Lecture 020, Securities Investment 101

November 22, 2013

In the first of a series of videos, we begin to delve into the complex subjects of bond duration and bond convexity. This first lecture begins to introduce the topic of duration and why we need it to anticipate bond price changes after expected (or unexpected) changes in required market interest rates. Previous video, Money […]

Read the full article →

Inter Bank Offer Rates, Lecture 018, Securities Investment 101

July 1, 2013

In this lecture we discuss what inter-bank offer rates are, where they originated, and how they are typically calculated on a daily business-day basis. Although providing a ‘generic’ description of how they work, and mentioning several of the major international alternatives, we also provide information on one of the major inter-bank offer rates (as of […]

Read the full article →