2

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.

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?

vicgil
  • 21
  • 2
  • Please trim your code to make it easier to find your problem. Follow these guidelines to create a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example). – Community Apr 27 '22 at 13:20

0 Answers0