# 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.

### Table Of Contents

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

IsTradable

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

ZSpread

###
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 **T _{i}, i = 1** to

**N**, with the weights

**w**reflecting the present values

_{i}**P**of the respective amounts rather than the amounts themselves.

_{i}This is the equation:

**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:

**Macaulay:** **D** is defined as follows

where **f** is the cash flow frequency, i.e. the number of cash flows per year and **D _{mod}** 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:

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**:

You may download the spreadsheet produced with the above steps here

Feel free to contact me if you want to share any thoughts with regard to this product or if you want to request any particular features. Contact info and social media links are available at my web site https://www.deriscope.com