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.
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:
Recommended for Deriscope starters: The Overview and Quick Guide pages.
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 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:
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 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:
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
