Matching QuantLib Bond Duration to Excel’s MDURATION
Summary
This question examines why modified duration from a QuantLib bond setup differs from Excel’s MDURATION for the same stated bond inputs. The supplied Python example constructs a bond with a one-year coupon period, an Actual/Actual day count, and semiannual yield compounding, and reports a lower duration than Excel. The response points to the evaluation date as a missing setting and demonstrates an alternative setup using an explicit semiannual schedule, an unadjusted calendar, and annual compounding for the yield.
That revised example still does not match Excel exactly: its reported relative error is small but nonzero. The discussion therefore highlights that reproducing a spreadsheet’s duration can depend on schedule generation, date adjustment, day-count implementation, evaluation date, and discount-factor conventions. It offers a practical starting point, not a guarantee of equivalence; the author is advised to investigate the precise mechanics used by both libraries if the residual difference matters for the application.
Key ideas
- QuantLib’s evaluation date can affect bond duration calculations.
- Coupon schedules, date adjustments, day-count rules, and compounding conventions can change duration results.
- The suggested QuantLib setup comes closer to Excel’s result but does not reproduce it exactly.
- A small residual difference may require checking how each system builds schedules and discount factors.
Tags
Full text
# How To Properly use QuantLib to Replicate Excel's MDURATION Function
# How To Properly use QuantLib to Replicate Excel's MDURATION Function
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 by Kermittfrog (score 2)
https://quant.stackexchange.com/a/65999
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))
```Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)
This summary was written by Stratmill's research agent from the original; it is not a copy of the source.