Search code examples
excelquantlib

Compare QuantLib bond pricing with Excel functions YIELD and PRICE when doing stress testing

I calculated bond price and stressed bond price (shocking up yield) in both Excel and Python Quantlib. As the following table shows, weird results were generated: base bond price matches well between Excel and Quantlib but the stressed bond prices have more gaps (1.5% relative difference). Yields also show some gaps. Can you please provide some comments? enter image description here

Excel Code:

    Base Bond Price=PRICE("2021-8-19","2025-8-19",4.5%,YIELD("2021-8-19","2025-8- 
   19",4.5%,95,100,4,0),100,4,0)

    Stress Bond Price=PRICE("2021-8-19","2025-8-19",4.5%,YIELD("2021-8-19","2025-8- 
   19",4.5%,95,100,4,0)+662/10000,100,4,0)

Python code:

    import datetime
    import QuantLib as ql
    settlement_date = ql.Date(19,8,2021)
    valuation_date = ql.Date(19,8,2021)
    issue_date = ql.Date(19,8,2021)
    maturity_date = ql.Date(19,8,2025)
    tenor = ql.Period(4)
    calendar = ql.UnitedStates()
    business_convention = ql.Following
    date_generation = ql.DateGeneration.Backward
    end_month = False
    face_value = 100
    coupon_rate = 450/10000
    day_count = ql.Thirty360(ql.Thirty360.USA)
    redemption_value = 100
    schedule = ql.Schedule(issue_date, maturity_date, tenor, calendar, business_convention, business_convention, date_generation, end_month)
    bond = ql.FixedRateBond(settlement_date-valuation_date, face_value, schedule, [coupon_rate], day_count, business_convention, redemption_value, issue_date)
    target_price = 95
    bond_yield = bond.bondYield(target_price, day_count, ql.Compounded, 4, ql.Date(), 1.0e-8,1000)
    bond_price = bond.cleanPrice(bond_yield, day_count, ql.Compounded, 4)
    STRESS = 662
    stress_bond_yield = bond_yield+STRESS/10000
    
    stress_bond_price = bond.cleanPrice(stress_bond_yield, day_count, ql.Compounded, 4)
    excel_base_bond_price = 99.50
    excel_stress_bond_price = 75.02971569
    print('Base bond price from excel is', excel_base_bond_price )
    print('Base bond price from Quantlib is', bond_price)
    print('Stressed bond price from excel is',excel_stress_bond_price)
    print('Stressed bond price from Quantlib is',stress_bond_price)
Solution

  • You need to add

    ql.Settings.instance().evaluationDate = valuation_date

    before the calculation. If you don't do so, you'll be calculating the yield and price as of today instead of as of the valuation date. Once you do that, the yields and prices match a lot better.