[Python] QuantLib FixedRateBond versus Excel Yield()

I'm currently trying to calculate the Yield of Treasury Bonds in Python using the QuantLib library. As a reference I used the Excel Yield function, but I'm getting different results for the same input-values.

I don't understand why I'm getting different results and I don't understand where this difference comes from.

I have two columns using the Yield function, a YTM column and an Actual Yield column. I'll start with YTM.

I know that similar questions already exists, like the next two links, but it doesn't resolve my issue.

• Excel YIELD function equivalent in python Quantlib
• QuantLib Bond Yield

YTM

In Excel I use the following values:

=YIELD(valuedate, maturitydate, rate, price, redemption, coupons, 3)

• valuedate = 26/11/2021
• maturitydate = 26/01/2026
• rate = 0.000%
• price = 45.340
• redemption = 100
• coupons = 1
• dayCount = 3 = Actual365

With these values I get a YTM of 20.903%.

To replicate this in Python I used this code:.

#Dates
start = 26/11/2021
maturity = 26/01/2026

#Yield Params
redemption = 100
coup = 1
price = 45.340
couponRate = 0.000%
rate = [couponRate]
calendar = ql.NullCalendar()
settlementDays = 0

tenor = ql.Period(ql.Annual)
fixedRateBond = ql.FixedRateBond(settlementDays, calendar, redemption, start, maturity, tenor, rate, ql.Actual365Fixed())
fixedRateBond.bondYield(price,
ql.Actual365Fixed(),
ql.SimpleThenCompounded, #SimpleThenCompounded gives best result yet
ql.Annual,
start,
1.0e-16,
100
)


With QuantLib, using the same values as Excel Yield, I get a Yield of 20.827% which is a small difference of 0.075%.

But, when looking at Actual Yield, I get bigger differences using the same parameters as with YTM.

So, with the same mindset of trial-and-error, I've used slightly different parameters which returned the best results I could obtain.

Actual Yield

Excel values:

=YIELD(quotationdate+3, maturitydate, rate, quotationprice, redemption, coupons, 3)


(note: that we do +3 because the accrued interest is calculated on the settlementDate)

• quotationdate = 2/3/2022
• maturitydate = 16/10/2049
• rate = 3.897%
• quotationprice = 15
• redemption = 100
• coupons = 1
• dayCount = 3 = Actual365

which returns a yield of 26.044%

QuantLib:

ql.Settings.instance().evaluationDate = 2/3/2022
start = 2/3/2022
maturity = 16/10/2049

coup = 1
price = 15
couponRate = 3.897%
rate = [couponRate]
settlementDays = 3

calendar = ql.NullCalendar()
businessConvention = ql.Unadjusted
dateGeneration = ql.DateGeneration.Backward
monthEnd = False
redemption = 100
tenor = ql.Period(ql.Annual)

schedule = ql.Schedule(start,
maturity,
tenor,
calendar,
businessConvention,
businessConvention,
dateGeneration,
monthEnd)

bond = ql.FixedRateBond(settlementDays, redemption, schedule, rate, ql.Actual365Fixed())
bond.bondYield(price,
ql.Actual365Fixed(),
ql.Compounded,
ql.Annual)


Resulting in a Yield of 26.368% which is a difference of 0.324%

I want to stress that I don't have a financial background. I got these parameters via other similar StackOverflow questions and through a lot of trial-and-error.

Using the same parameters in QuantLib for Actual Yield as used in YTM gave worse results than these parameters.

Could someone explain to me which parameters I should use to replicate the Excel Yield() function and why I'm getting differences between QuantLib and Excel?

Also, is the Excel function granted to be 100% correct?

Continue reading...