Mapping Swap Coupon Value Dates to Required Fixing Dates
Article Quant Q&A · Author: osullivj
Summary
The document explains why a live interest rate swap valuation can request historical index fixings on dates that differ from the schedule dates shown in QuantLibXL. The schedule dates correspond to coupon value dates, while the floating coupon needs the index fixing date associated with each value date. A missing historical fixing therefore causes the net present value calculation to fail even when the schedule dates have been collected.
For a quick resolution in the example, the answer suggests moving back two business days from the value date. A more general approach is to use QuantLibXL’s interest rate index fixing date function, which accounts for the index’s fixing lag and calendar conventions. The two-day adjustment applies only to the stated case; other indices may use different conventions, so supplying fixings should be based on the specific index and value date.
Key ideas
- Floating coupons use index fixing dates, which can differ from coupon value dates.
- The dates returned by a schedule function may not be the dates required for historical fixings.
- A two-day backward adjustment works in the example but depends on the index conventions.
- Use the index fixing date function to derive the fixing date from the coupon value date.
Tags
Full text
# QuantLibXL swap valuation fixing dates
# QuantLibXL swap valuation fixing dates
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...
IRDValServer.xls
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?
## Answer by Luigi Ballabio (score 4)
https://quant.stackexchange.com/a/35791
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.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.