Bond pricing in C# with QLNet and Excel-DNA

I had to perform some research on bond curves fitting methods recently, thus looking for a decent library to do some bond pricing (vanilla stuff: fixed coupon bonds …), and calibrate an OLS of some model prices against market contributions.

I think that QuantLib stands as the open source reference for that kind of calculations, but since I was keener to do some quick implementation in C#, and expose the results in Excel (with Excel-DNA), I turned to QLNet, which is a direct conversion of the famous C++ library into C#.Net. I read on Wilmott forums that QLNet was somewhat outdated, but there are still some recent commits on their GitHub project actually and it is, I think, a good alternative to doing C++ to C++/CLI to C# plumbing (if find the courage I’ll do it or maybe just use XLW to expose the C++ to Excel directly).

In this post I share some C# code useful to compute some bond analytics out of a discount curve, which seems badly covered in Quantlib and QLNet examples. Indeed, in the examples, and test suite, bonds are often evaluated from yields, or flat rate models. Here I wanted to calibrate a Nelson Siegel Svensson so I needed to take into account the slope of my discount curve.

Installation of QLNet and Excel-DNA in our C# class library is straightforward using Nugget package manager:

QLNet Nugget Installation

A very convenient aspect of having the Excel-DNA dependency installed locally at project level is that it’s naturally referenced, and at compile time, it automatically generates the 32 and 64-bit xll.

The function below is quite rough, and doesn’t enable much customization of cashflows, but it’s a good basis to move forward for further adjustments (additional inputs, outputs …).

 [ExcelFunction(
            Category = "Smile of Thales",
            Name = "BondPrice",
            Description = "Calculate a bond price from a zero curve")]
        public static object GetBondPrice(
            int tradedate,
            int maturity,
            double coupon,
            int frequency,
            string businessdayconvention,
            double[] zcdates,
            double[] discountfactors,
            int issuedate,
            int settledays,
            double faceamt,
            string expectedres)
        {
            var settle = new Date(tradedate);
            var issue = new Date(issuedate);
            Settings.setEvaluationDate(new Date(tradedate));
            var freq = (Frequency) frequency;
            BusinessDayConvention dayconv;
            Enum.TryParse(businessdayconvention, out dayconv);
            var discountCurve = CreateZcTermstructure(tradedate, zcdates, discountfactors);
            IPricingEngine bondEngine = new DiscountingBondEngine(discountCurve);
            var bondSchedule = new Schedule(settle, new Date(maturity), new Period(freq), new NullCalendar(), dayconv, dayconv, DateGeneration.Rule.Backward, false);
            var fixedRateBond = new FixedRateBond(settledays, 1, bondSchedule, new List<double> { coupon }, new ActualActual(), dayconv, faceamt, issue);

            fixedRateBond.setPricingEngine(bondEngine);

            if (expectedres.ToUpper() == "CLEAN")
                return fixedRateBond.cleanPrice();
            if (expectedres.ToUpper() == "DIRTY")
                return fixedRateBond.dirtyPrice();
            if (expectedres.ToUpper() == "CF")
                return fixedRateBond.cashflows();
            if (expectedres.ToUpper() == "ACCRUED")
                return fixedRateBond.accruedAmount();
            
            var res = new object[3,2];

            res[0, 0] = "Clean Price";
            res[1, 0] = "Dirty Price";
            res[2, 0] = "Accrued";
            res[0, 1] = fixedRateBond.cleanPrice();
            res[1, 1] = fixedRateBond.dirtyPrice();
            res[2, 1] = fixedRateBond.accruedAmount();

            return res;
        }

[ExcelFunction(

Category = "Smile of Thales",

Name = "BondPrice",

Description = "Calculate a bond price from a zero curve")]

public static object GetBondPrice(

int tradedate,

int maturity,

double coupon,

int frequency,

string businessdayconvention,

double[] zcdates,

double[] discountfactors,

int issuedate,

int settledays,

double faceamt,

string expectedres)

{

var settle = new Date(tradedate);

var issue = new Date(issuedate);

Settings.setEvaluationDate(new Date(tradedate));

var freq = (Frequency) frequency;

BusinessDayConvention dayconv;

Enum.TryParse(businessdayconvention, out dayconv);

var discountCurve = CreateZcTermstructure(tradedate, zcdates, discountfactors);

IPricingEngine bondEngine = new DiscountingBondEngine(discountCurve);

var bondSchedule = new Schedule(settle, new Date(maturity), new Period(freq), new NullCalendar(), dayconv, dayconv, DateGeneration.Rule.Backward, false);

var fixedRateBond = new FixedRateBond(settledays, 1, bondSchedule, new List<double> { coupon }, new ActualActual(), dayconv, faceamt, issue);

fixedRateBond.setPricingEngine(bondEngine);

if (expectedres.ToUpper() == "CLEAN")

return fixedRateBond.cleanPrice();

if (expectedres.ToUpper() == "DIRTY")

return fixedRateBond.dirtyPrice();

if (expectedres.ToUpper() == "CF")

return fixedRateBond.cashflows();

if (expectedres.ToUpper() == "ACCRUED")

return fixedRateBond.accruedAmount();

var res = new object[3,2];

res[0, 0] = "Clean Price";

res[1, 0] = "Dirty Price";

res[2, 0] = "Accrued";

res[0, 1] = fixedRateBond.cleanPrice();

res[1, 1] = fixedRateBond.dirtyPrice();

res[2, 1] = fixedRateBond.accruedAmount();

return res;

}

The logic to create a piecewise zero coupon rate model is encapsulated in the following method, for more clarity:

private static Handle<YieldTermStructure> CreateZcTermstructure(
            int settleDate,
            double[] dates,
            double[] discountfactors)
        {
            // This code returns a Zero Coupons YieldTermStructure
            var settle = new Date(settleDate);
            var bondInstruments = new List<RateHelper>();
            for (var i = 0; i < discountfactors.GetLength(0); i++)
            {
                // Troubles if evaluation date >= first discount factor date
                if (dates[i] <= settleDate) continue;
                var df = new Handle<Quote>(new SimpleQuote(discountfactors[i]));
                var matu = new Date((int)dates[i]);
                var schedule = new Schedule(settle, matu, new Period(Frequency.Once), new NullCalendar(),
                                            BusinessDayConvention.Unadjusted, BusinessDayConvention.Unadjusted,
                                            DateGeneration.Rule.Backward, true);
                var coupons = new List<double> { 0 };
                var helper = new FixedRateBondHelper(df, 0, 1.0, schedule, coupons,
                                                     new ActualActual(), BusinessDayConvention.Unadjusted, 1.0, settle);
                bondInstruments.Add(helper);
            }
            //I opt for a cubic interpolation of discount factors
            YieldTermStructure bondDiscountingTermStructure =
                new PiecewiseYieldCurve<Discount, Cubic>(settle, bondInstruments, new ActualActual());

            return new Handle<YieldTermStructure>(bondDiscountingTermStructure);
        }

private static Handle<YieldTermStructure> CreateZcTermstructure(

int settleDate,

double[] dates,

double[] discountfactors)

{

// This code returns a Zero Coupons YieldTermStructure

var settle = new Date(settleDate);

var bondInstruments = new List<RateHelper>();

for (var i = 0; i < discountfactors.GetLength(0); i++)

{

// Troubles if evaluation date >= first discount factor date

if (dates[i] <= settleDate) continue;

var df = new Handle<Quote>(new SimpleQuote(discountfactors[i]));

var matu = new Date((int)dates[i]);

var schedule = new Schedule(settle, matu, new Period(Frequency.Once), new NullCalendar(),

BusinessDayConvention.Unadjusted, BusinessDayConvention.Unadjusted,

DateGeneration.Rule.Backward, true);

var coupons = new List<double> { 0 };

var helper = new FixedRateBondHelper(df, 0, 1.0, schedule, coupons,

new ActualActual(), BusinessDayConvention.Unadjusted, 1.0, settle);

bondInstruments.Add(helper);

}

//I opt for a cubic interpolation of discount factors

YieldTermStructure bondDiscountingTermStructure =

new PiecewiseYieldCurve<Discount, Cubic>(settle, bondInstruments, new ActualActual());

return new Handle<YieldTermStructure>(bondDiscountingTermStructure);

}

The result:

Attached an excel spreadsheet (NSS.xlsm) with a test that reconciles the calculated zero coupon and the original discount curve. In addition, you will find the project source files, .csproj, and the generated XLL for 32 and 64-bit Excel.

Enjoy

Sources and Excel file: BondPricing

The smile of Thales

A journey into Finance and Computation

Recent posts

Archives

Bond pricing in C# with QLNet and Excel-DNA

Leave a Reply Cancel reply