I'm trying to build a Excel spreadsheet with QuantLib 1.4 for swap valuation of "live" swaps; IRSs with a start date in the past, and an end date in the future. This is what I've got so far...


Basically it's a cut down version of the InterestRateDerivatives.xls example sheet, with Cap, Floor, FRA and Swaptions removed. In the DateCalc tab I've added a call to qlScheduleDates in column D so I know which past dates require fixings. However, the dates supplied by qlScheduleDates are not the ones that qlInstrumentNPV needs. ohRangeRetrieveError gives this error message: "qlInstrumentNPV - 2nd leg: Missing Euribor6M Actual/360 fixing for August 10th, 2017"

I tried launching Excel from inside Visual Studio, so I could breakpoint the code throwing that error. This was the stack trace to the error.

QuantLibXL-vc90-mt-sgd-1_4_0.xll!QuantLib::Error::Error(const std::basic_string<char,std::char_traits<char>,std::allocator<char> > & file, long line, const std::basic_string<char,std::char_traits<char>,std::allocator<char> > & function, const std::basic_string<char,std::char_traits<char>,std::allocator<char> > & message)  Line 97 C++
QuantLibXL-vc90-mt-sgd-1_4_0.xll!QuantLib::IborCoupon::indexFixing()  Line 102 + 0x146 bytes    C++
    // fixingDate_ = 42957 (10 Aug 2017), today = 42962 (15 Aug 2017)
    // fixingValueDate_ = 42961 (14 Aug 2017)
    // this is BlackIborCouponPricer::coupon_
QuantLibXL-vc90-mt-sgd-1_4_0.xll!QuantLib::BlackIborCouponPricer::adjustedFixing(double fixing)  Line 101 + 0x17 bytes  C++
QuantLibXL-vc90-mt-sgd-1_4_0.xll!QuantLib::BlackIborCouponPricer::swapletPrice()  Line 148 + 0x24 bytes C++
QuantLibXL-vc90-mt-sgd-1_4_0.xll!QuantLib::BlackIborCouponPricer::swapletRate()  Line 153 + 0xe bytes   C++
QuantLibXL-vc90-mt-sgd-1_4_0.xll!QuantLib::FloatingRateCoupon::rate()  Line 93 + 0x29 bytes C++
QuantLibXL-vc90-mt-sgd-1_4_0.xll!QuantLib::FloatingRateCoupon::amount()  Line 62 + 0x2d bytes   C++
QuantLibXL-vc90-mt-sgd-1_4_0.xll!QuantLib::CashFlows::npvbps(const std::vector<boost::shared_ptr<QuantLib::CashFlow>,std::allocator<boost::shared_ptr<QuantLib::CashFlow> > > & leg, const QuantLib::YieldTermStructure & discountCurve, bool includeSettlementDateFlows, QuantLib::Date settlementDate, QuantLib::Date npvDate, double & npv, double & bps)  Line 515 + 0xf bytes  C++
QuantLibXL-vc90-mt-sgd-1_4_0.xll!QuantLib::DiscountingSwapEngine::calculate()  Line 86 + 0x88 bytes C++
QuantLibXL-vc90-mt-sgd-1_4_0.xll!QuantLib::Instrument::performCalculations()  Line 168 + 0x26 bytes C++
QuantLibXL-vc90-mt-sgd-1_4_0.xll!QuantLib::LazyObject::calculate()  Line 150 + 0xf bytes    C++
QuantLibXL-vc90-mt-sgd-1_4_0.xll!QuantLib::Instrument::calculate()  Line 155    C++
QuantLibXL-vc90-mt-sgd-1_4_0.xll!QuantLib::Instrument::NPV()  Line 187 + 0xe bytes  C++
QuantLibXL-vc90-mt-sgd-1_4_0.xll!qlInstrumentNPV(char * ObjectId, xloper * Trigger)  Line 155 + 0x19 bytes  C++

By breakpointing in IborCoupon::indexFixing I found that the fixingValueDate_ in IborCoupon lined up with the dates supplied by qlScheduleDates, but the fixingDate_ did not. In the debugger I captured these values on different visits into IborCoupon::indexFixing( )

fixingDate      2017-08-10  2015-08-12  2016-02-11  2016-08-11  2017-02-10  2017-08-10
fixingValueDate 2017-08-14  2015-08-14  2016-02-15  2016-08-15  2017-02-14  2017-08-14
fixingEndDate   2018-02-14  2016-02-15  2016-08-15  2017-02-14  2017-08-14  2018-02-14

How can I resolve this date mismatch so I can supply the correct fixings? Can I get qlScheduleDates to show me the fixingDate values rather thatn the fixingValueDates? Or can I change the floating rate coupon pricing logic to take the dates qlScheduleDates reports?

  • $\begingroup$ I've just read this: quant.stackexchange.com/questions/16862/… Following @atkins advice I applied a qlCalendarAdvance(..., "-2D", ...) to the dates supplied by qlScheduledDates before passing them into qlIndexAddFixings, and that seems to have fixed the problem. $\endgroup$
    – osullivj
    Aug 20, 2017 at 17:24

1 Answer 1


Going backwards 2 days with qlCalendarAdvance(..., "-2D", ...) works in this particular case. To be more robust, and to take into account the number of fixing days and the conventions of the index you're using, you can use the qlInterestRateIndexFixingDate function. It takes the interest-rate index and a value date (that is, the start of your coupon) and returns the corresponding fixing date.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.