How to Calculate Duration and Convexity of a Bond with Excel

Duration and convexity are important numbers in bond portfolio management, and duration is pretty simple in Excel because there are built-in functions. Convexity, though, is another matter. Of course, there are formulas that you can type in (see table below), but they aren’t easy for most people to remember and are tedious to enter. In this article I will show you how you can use a very accurate approximation method that is easy to use in Excel. In fact, the accuracy of these simple approximations are more than good enough. They will generally be the same as the exact solution to two or more decimal places.

The usual formulas for calculating Macaulay duration, modified duration, and convexity are given in the table below. Obviously, the summations make the calculations tedious. There are closed-form solutions for these equations, but they are fairly long and ugly. In other words, they aren’t really suited for a spreadsheet solution without resorting to VBA.

Formula for	Formula
Macaulay Duration	$$\frac{1}{P}\sum\limits_{t = 1}^N {\frac{{C{F_t}}}{{{{\left( {1 + i} \right)}^t}}}}  \times t$$
Modified Duration	$$ {\frac{1}{{P \times \left( {1 + i} \right)}}\sum\limits_{t = 1}^N {\frac{{C{F_t}}}{{{{\left( {1 + i} \right)}^t}}}}  \times t} $$
Convexity	$$\frac{1}{{P \times {{\left( {1 + i} \right)}^2}}}\sum\limits_{t = 1}^N {\frac{{C{F_t}}}{{{{\left( {1 + i} \right)}^t}}}}  \times \left( {{t^2} + t} \right)$$

In the equations, $P$ is the bond price, $CF_t$ is the cash flow in period $t$, and $i$ is the per period yield to maturity. It is important to note that these equations work only on an interest payment date. The approximation technique that we will show will work on any date as long as you use the Price function.

In this article we will see how to calculate modified duration, Macaulay duration, and convexity three different ways: using the PV function, the Price function, and the built-in MDuration and Duration functions. As noted above, there is no built-in function for convexity, likely because it measures second-order effects and isn’t quite as important as the duration measures.

Modified Duration

Most investments textbooks start their discussion of duration with Macaulay duration, but we will start with modified duration because it is a bit more straightforward. Modified duration is the first derivative of the bond pricing equation with respect to the yield ($i$), divided by the price ($P$) of the bond:

$${\rm{Modified\, Duration}} = \frac{{\partial P}}{{\partial i}} \times \frac{1}{P}$$

We know how to calculate the price of a bond using Excel, so the problem here is how to calculate that partial derivative. Recall that a partial derivative tells you how much a function changes when one of its variables changes by a small amount. More specifically, this partial derivative will tell us how much the bond price will change when the yield changes by a small amount (say from 5% to 5.001%). When we take this change and divide it by the current price, we are simply converting the dollar change into a percentage change. So you can see that this derivative will tell us how sensitive a bond’s price will be to changes in interest rates.

We can approximate a first partial derivative by using a technique called “centered differencing.” Simply perturb the yield by a tiny amount and recalculate the price. Do this twice (above and below the actual yield) and then divide by two times the change in yield:

$$\frac{{\partial P}}{{\partial i}} \cong \frac{{{P_{i – h}} \,- {P_{i + h}}}}{{2h}}$$

In the equation, $h$ is the small change in the yield, $P_{i+h}$ is the bond price at a slightly higher yield, and $P_{i−h}$ is the price at a slightly lower yield. We will set $h=0.00001$, though you could use a smaller change. Now, we can approximate the modified duration with the formula:

$${\rm{Modified\, Duration}} \cong \frac{{{P_{i – h}} \,- {P_{i + h}}}}{{2h}} \times \frac{1}{P}$$

Let’s look at an example:

Imagine that you are considering the purchase of a bond with a face value of $1,000 and a coupon rate of 8% paid semiannually. The bond matures in 20 years and the yield to maturity is 6%. What is the modified duration of this bond?

The image below shows how we will set up the worksheet to calculate modified duration using the approximation technique that was just outlined.

We need to find the bond price three times to use our approximations for the duration and convexity measures. We will calculate these prices using the PV function or the Price Function. The PV function is useful if you don’t have exact dates and you are on a coupon payment date. The Price function should be used if you have actual dates. Using the Price function also means that you don’t have to be on a payment date.

Using the PV Function

The PV function should only be used when you do not have dates. This would be typical in a finance course, for example, and matches the information that we have in the image above. In B8, enter the formula to calculate the initial value of the bond at a yield of 6%:

=-PV(B5/B2, B1*B2, B3*B4/B2, B4)

You should get a value of \$1,231.15. Next, we will use the same function, except that we will add or subtract 0.001% from the yield. The table below shows the formulas for B8:B10 that will be used in the approximations:

Cell	Formula	Result
B8	=-PV(B5/B2, B1*B2, B3*B4/B2, B4)	1,231.15
B9	=-PV(B5/B2-B6, B1*B2, B3*B4/B2, B4)	1,231.42
B10	=-PV(B5/B2+B6, B1*B2, B3*B4/B2, B4)	1,230.88

Using the Price Function

Outside of the classroom in the real world, you will always know the exact dates for settlement and maturity, so the Price function should be used. This is because it correctly calculates the number of days between dates, handles the different day count conventions, and can be used on days that are not coupon payment dates. So, this method of finding the prices is vastly superior to using the PV function. We have previously demonstrated the Price function in the article “Bond Valuation Using Microsoft Excel,” so be sure to check that article if you aren’t familiar with the Price function.

Make a copy of the worksheet used above and then make the changes shown in the image below:

Note that we have added the settlement and maturity dates in rows 2 and 3. Also, the maturity date was determined by using the Date function, where we simply added 20 years to the settlement date:

=DATE(YEAR(B2)+B1, MONTH(B2), DAY(B2))

Now replace the formulas in B8:B10 with the formulas in the table below:

Cell	Formula	Result
B8	=PRICE(B2,B3,B5,B7,B6/10,B4,0)*10	1,231.15
B9	=PRICE(B2, B3, B5, B7-2*B8, B6/10, B4, 0)*10	1,231.42
B10	=PRICE(B2, B3, B5, B7+2*B8, B6/10, B4, 0)*10	1,230.88

Note that the values in the Result column are the same as we got using the PV function. This is because we assumed that we are on a coupon payment date. However, using the Price function means that we can change the dates and still get correct answers.

We are now ready to find the approximate modified duration by using our approximation formula from above. You can use either worksheet and will get the same answers because the approximation formula only cares about the prices, not the method of calculating them.

$${\rm{Modified\, Duration}} \cong \frac{{1231.416 \,- 1230.879}}{{2 \times 0.00001}} \times \frac{1}{{1231.148}} = 21.809$$

Realize that this result is the modified duration in semiannual periods, so divide by 2 to get 10.905 years. The image below shows the worksheet with the modified duration calculated:

The formula in B14 to calculate modified duration is:

=(B11-B12)/(2*B8)*(1/B10)

and the result matches the previous result.

Macaulay Duration

Once you have found the modified duration, Macaulay duration is easy because we simply multiply by 1 plus the per period yield:

$${\rm{Macaulay\, Duration}} = {\rm{Modified\, Duration}} \times \left( {1 + i} \right) \cong \frac{{{P_{i – h}} \,- {P_{i + h}}}}{{2h}} \times \frac{{\left( {1 + i} \right)}}{P}$$

Using the example problem from above, recall that we got 10.905 years for the modified duration. Therefore, the Macaulay duration is $10.905\times 1.03=11.232$ years.

Since we already have the three prices that we need, doing this calculation in the worksheet is very simple. In B17:B18 add the following formulas:

In cell B17: =B14*(1+B7/B4)

In cell B18: =B17/B4

Convexity

To calculate convexity in a spreadsheet is only slightly more complicated, but the idea is the same. That is, approximate the derivative and then divide by the current price. The convexity of a bond measures the amount of curvature in the price/yield relationship and its formula is:

$${\rm{Convexity}} = \frac{{{\partial ^2}P}}{{\partial {i^2}}} \times \frac{1}{P}$$

In other words, convexity is the second derivative of the price formula with respect to the yield divided by the price of the bond.

As before, our first task is to approximate the derivative. In this case, we will use the central difference approximation:

$${\rm{Convexity}} \cong \frac{{{P_{i – h}} + {P_{i + h}} \,- 2P}}{{{h^2}}} \times \frac{1}{P}$$

Notice that, as before, we need three prices (current price and two with the yield changed slightly). Since we still have those prices in the worksheet, simply enter them into the above equation:

$${\rm{Convexity}} \cong \frac{{1,231.416 + 1,230.879 \,- 2 \times 1,231.148}}{{{{0.00001}^2}}} \times \frac{1}{{1,231.148}} = 681.072$$

If we wish to annualize the convexity, we divide by the payment frequency squared ($2^2$ here) to get 170.268.

In the worksheet, enter the following formulas in B20:B21:

In cell B20: =(B11+B12-2*B10)/B8^2*(1/B10)

In cell B21: =B20/B4^2

Using the Built-in MDuration and Duration Functions

As noted previously, Excel has built-in functions that can be used to calculate both modified and Macaulay duration on any date. However, note that these two functions require dates, so if you don’t have them, then you will need to use the approximation technique shown above. Also, there is no function to calculate convexity, so you have to use the method shown in the previous section.

Both the MDuration (modified) and Duration (Macaulay) functions work exactly like the Price function, so they are easy to use. The functions are defined as:

Purpose	Function
Modified Duration	MDURATION(Settlement, Maturity, Coupon, Yld, Frequency, Basis)
Macaulay Duration	DURATION(Settlement, Maturity, Coupon, Yld, Frequency, Basis)

Using these functions does not require the three prices that we needed previously. They are entirely self-contained. Make a copy of your worksheet containing the dates, and then enter the following formulas in B14 and B17:

In cell B14: =MDURATION(B2, B3, B5, B7, B4)

In cell B17: =DURATION(B2, B3, B5, B7, B4)

Note that these functions return the modified duration and Macaulay duration in years, so there is no need to divide by the payment frequency. You will get 10.90 years for modified duration, and 11.23 years for Macaulay duration. These are exactly the same answers that we got using the approximation techniques.

Conclusion and One Caveat

We have seen here that we can easily calculate modified duration, Macaulay duration, and convexity using a spreadsheet. This is easy because we calculate the price three times: at the existing yield, at a slightly lower yield, and at a slightly higher yield. The approximations are very accurate as the table below shows:

Annualized Measure	Exact Solution	Approximate Solution	Same to
Modified Duration	10.90499629	10.90499664	6 decimal places
Macaulay Duration	11.23214617	11.23214638	6 decimal places
Convexity	170.25762159	170.25761639	4 decimal places

This degree of accuracy is good enough for all practical purposes. So, there is really no need to memorize the complicated exact formulas for these bond risk measures.

One caveat to this article is that I have only shown how to do these calculations on a coupon payment date. Extending the approximations to work on any date is quite straightforward: Simply use the dirty prices of the bond in place of payment date prices that I have used in the examples above. I show how to calculate the price of a bond on any date elsewhere on TVMCalcs.com. If you have any questions or comments about this article, please feel free to contact me.