5 minutes reading time (1051 words)

Over 30 Bond Risk Management Functions in Excel: Clean & Dirty Price, Yield, Duration, Convexity, BPS, DV01, Z-spread etc

Free and open source QuantLib is capable of calculating several risk measures associated with the pricing of bonds and allows you to get in Excel quantities like clean and dirty price, duration, convexity, BPS, DO01, Z-spread etc.

I have already showed you how to build a yield curve out of clean bond prices using either a parametric or non-parametric fit.

For demonstration purposes below, I will use Deriscope to create a fixed rate bond in Excel and then calculate its clean price, duration and convexity.

Recommended for Deriscope starters: The Overview and Quick Guide pages.

Get a free Deriscope activation code that enables you to run the accompanied spreadsheet!

Structure and Pricing of a Fixed Rate Bond

A Fixed Rate Bond is a very simple instrument that pays its holder a series of coupons based on a given notional and a fixed rate. It also pays the notional at maturity.

Diagrammatically the bond's cash flows look like this:

The exact amounts and the resulting NPV (Net Present Value) of the bond are as below:

Creating a fixed rate bond object in Excel

The Deriscope wizard can generate the spreadsheet formula that creates a fixed rate bond object in Excel as the following video demonstrates:

and this is how the pasted formula looks like, after I have shifted two blocks upwards so that they are all visible:

Understanding the formula

As you see, cell A1 contains the formula =ds(A2:B15), which takes one input argument and returns the text &FxdBnd_A1:1.1

The prefix & indicates that &FxdBnd_A1:1.1 is the handle name of some object. In fact it points to an object of type Fixed Rate Bond and can be used in any context where an object of type Bond is expected because the type Fixed Rate Bond inherits from Bond.

The input range A2:B15 contains 12 key-value pairs with obvious meanings.

Nevertheless I can read the information applying to each key-value pair by selecting the respective cell as this video shows:

Regarding the values – indicated with blue color – I can edit them by manually typing in the cells or by using the wizard as explained in previous articles, for example here

Available Bond Functions

As of the time of writing, QuantLib supports the following 37 functions related to bonds.

It is important to note that all these functions apply to all bond types supported by QuantLib that include fixed rate bonds, floating notes (both Libor and CMS) and inflation bonds.

Technical details and definitions are available at my Bond Functions documentation page.

AccrualDays

AccrualEndDate

AccrualPeriod

AccrualStartDate

AccruedAmount

AccruedDays

AccruedPeriod

ATMRate

BasisPointValue

BPS

CleanPrice

Convexity

DirtyPrice

Duration

KRD (The KRD algorithm makes use of QuantLib components but is implemented outside it)

MaturityDate

NextCashFlowAmount

NextCashFlowDate

NextCouponRate

PaymentCurrency

PreviousCashFlowAmount

PreviousCashFlowDate

PreviousCouponRate

ReferencePeriodEnd

ReferencePeriodStart

SettlementCalendar

SettlementDate

SettlementDateBump

SettlementDayCount

SettlementDays

SettlementPeriod

SettlementValue

StartDate

Yield

YieldValueBasisPoint

Calculating the clean price

QuantLib provides an overloaded function for calculating the clean price of a bond.

In programming jargon, overloaded simply means that the same function may be called with several different sets of input parameters.

Deriscope's way of getting along with overloaded functions is through check boxes in the wizard that the user can check or uncheck in order to choose the desired set of input parameters.

For example, Deriscope has one single function called Clean Price that can be configured through such check boxes to accept as input either a flat yield or a discounting yield curve or a Z-spread.

The following video shows how I can navigate through the various choices and finally generate the Clean Price function out of a flat yield input and a specified reference date.

Below is the spreadsheet image with the pasted function in cell D1, after I have made space by shifting elsewhere the blocks located there earlier:

Calculating the duration

Any type of bond is a stream of future cash flows, not necessarily known today.

QuantLib calculates the duration D of a stream of N cash flows according to three different definitions:

Simple:

D is defined as the weighted average of the cash flow payment times Ti, i = 1 to N, with the weights wi reflecting the present values Pi of the respective amounts rather than the amounts themselves.

This is the equation:

$D=\sum_{1}^{N}T_{i}w_{i}\;\;\;\;\;where\;\;\;\;\;w_{i}=\frac{P_{i}}{\sum_{1}^{N}P_{i}}$

Modified:

D is defined as the percentage change of the total present value P for a small change in the yield y, whereby P is calculated according to y.

This is the equation:

$D=-\frac{1}{P}\frac{\partial&space;P}{\partial&space;y}$

Macaulay: D is defined as follows

$D=\left&space;(&space;1+\frac{y}{f}&space;\right&space;)D_{mod}$

where f is the cash flow frequency, i.e. the number of cash flows per year and Dmod is the modified duration defined above.

The following video shows how I generate the Macaulay duration function out of a flat yield input and a specified reference date.

Below is the spreadsheet image with the pasted function in cell D7:

Calculating the convexity

Convexity C is a measure of how sensitive duration is on yield change.

It is defined as follows:

$C=\frac{1}{P}\frac{\partial^2&space;P}{\partial&space;y^2}$

The following video shows how I generate the Convexity function out of a flat yield input and a specified reference date.

Below is the spreadsheet image with the pasted function in cell G1: