In the bond market, convexity refers to the relationship between price and yield. When graphed, this relationship is non-linear and forms a long-sloping U-shaped curve. A bond with a high-degree of convexity will experience relatively dramatic fluctuations when interest rates move.
While there is no bond convexity function in Microsoft Excel, it can be approximated through a multi-variable formula.
Simplifying Convexity Formulas
The standard convexity formula involves a time series of cash flows and rather complicated calculus. This cannot be easily replicated in Excel, so a simpler formula is necessary:
Convexity = ((P+) + (P-) - (2Po)) / (2 x ((Po)(change in Y)²)))
Where (P+) is the bond price when the interest rate is decremented, (P-) is the bond price when the interest rate is incremented, (Po) is the current bond price and the "change in Y" is the change in interest rate represented in decimal form. The "change in Y" can also be described as the bond's effective duration.
This may not seem simple on the surface, but this is the easiest formula to use in Excel.
How to Calculate Convexity in Excel
To calculate convexity in Excel, begin by designating a different pair of cells for each of the variables identified in the formula. The first cell acts as the title (P+, P-, Po and Effective Duration), and the second carries the price, which is information you have to gather or calculate from another source.
Suppose that (Po) value is in cell C2, (P+) is in C3 and (P-) is in C4. The effective duration is in cell B5.
In a separate cell, enter the following formula: =(C3 + C4 - 2*C2) / (2*C2*(B5^2))
This should provide the effective convexity for the bond. A higher result means that the price is more sensitive to changes in interest rates.