How To Properly use QuantLib to Replicate Excel's MDURATION Function

Question

just wanted to ask how to use Quantlib on replicating Excel's MDURATION.

Given the following parameters, I was able to get a value of 4.478837 via MS Excel's MDURATION Function.

# GIVEN:
# settlement_date = "2021-05-15"
# maturity_date = "2025-11-30"
# coupon_rate = 0.00375
# yld_rate = 0.01
# frequency = 2 
# basis = 1
#             
# EXPECTED RESULT: 4.478837

I am trying to replicate this using Quantlib Python but I'm getting a different result of 4.32822. This is my current code

import QuantLib as ql
from datetime import datetime 

days_difference = (datetime(2025, 11, 30) - datetime(2021, 5, 15)).days

coupon = 0.00375
yld = 0.01

start = ql.Date(15,5,2021)
maturity = start + ql.Period(days_difference, ql.Days)

bond = ql.FixedRateBond(2, ql.TARGET(), 1000, start, maturity, ql.Period('1Y'), [coupon], ql.ActualActual())
rate = ql.InterestRate(yld, ql.ActualActual(), ql.Compounded, ql.Semiannual)
simple_duration = ql.BondFunctions.duration(bond, rate, ql.Duration.Simple)
mod_duration = ql.BondFunctions.duration(bond, rate, ql.Duration.Modified)
mac_duration = ql.BondFunctions.duration(bond, rate, ql.Duration.Macaulay)
print(mac_duration, mod_duration, )

# OUTPUT: (4.349865119875341 4.328223999875962)

So how to properly use Quantlib to be able to arrive at 4.478837?

Sorry if this is too basic as I am new to Quantlib and Im still confused on how to use it.

Answer 1

I think you are missing the eval date, and then there are some subleties in how Excel/QL setup discount factors etc.:

import QuantLib as ql

now, you forgot this step:

ql.Settings.instance().evaluationDate = ql.Date(15,5,2021) 

and then

coupon = 0.00375
yld = 0.01
start = ql.Date(15,5,2021)
maturity = ql.Date(30,11,2025)
schedule = ql.Schedule(start, 
                       maturity, 
                       ql.Period('6M'), 
                       ql.NullCalendar(), 
                       ql.Unadjusted, 
                       ql.Unadjusted, 
                       ql.DateGeneration.Backward, 
                       False)

bond = ql.FixedRateBond(0,  1000, schedule, [coupon], ql.ActualActual())
rate = ql.InterestRate(yld, ql.ActualActual(), ql.Compounded, ql.Annual)
mod_duration = ql.BondFunctions.duration(bond, rate, ql.Duration.Modified)
print(100*(mod_duration/4.478837-1))

resulting in a % error of:

-0.2944451687993066

Is that close enough for your applications? If not, you will most likely have to dig deeper into the mechanics of both QL and XL as the differences can result from date rolling, how Excel / ql create the schedule (forward, backwards), which Actual/Actual daycounter Excel is implementing etc. Then, there's also the question whether Excel (or ql) uses 1/(1+r)^t with $t$ a date fraction, 1/(1+r)^(0.5*n) or any other variant of the discount factor definition, e.g. exp(-rt).

NB: Alternatively, you can set the evaluation date when calling the modified duration:

ql.BondFunctions.duration(bond, rate, ql.Duration.Modified,ql.Date(15,5,2021))