Matching Excel Bond Yields with QuantLib Conventions

# Excel YIELD function equivalent in python Quantlib


# Excel YIELD function equivalent in python Quantlib












I am struggling to get an equivalent of Excel's YIELD function using Quantlib in python. As you can see from the Excel documentation on YIELD here, only a few parameters are needed compared to this example using Quantlib http://gouthamanbalaraman.com/blog/quantlib-bond-modeling.html

UPDATE:

Also, if I use the function bondYield, I can't seem to get the same values as in Excel. Take for example this bond:

the YIELD above has the formula `=YIELD(B1,B2,B3/100,B4,100,2,1)*100`. The yield is `1.379848`.

If I try to set up similar parameters in Quantlib, as shown below

```
# ql.Schedule
calendar = ql.UnitedStates()
bussinessConvention = ql.ModifiedFollowing
dateGeneration = ql.DateGeneration.Backward
monthEnd = False
cpn_freq = 2
issueDate = ql.Date(30, 9, 2014)
maturityDate = ql.Date(30, 9, 2019)
tenor = ql.Period(cpn_freq)
schedule = ql.Schedule(issueDate, maturityDate, tenor, calendar, bussinessConvention,
                       bussinessConvention, dateGeneration, monthEnd)

# ql.FixedRateBond
dayCounter = ql.ActualActual() 
settlementDays = 1
faceValue = 100
couponRate = 1.75 / 100
coupons = [couponRate]
fixedRateBond = ql.FixedRateBond(settlementDays, faceValue, schedule, coupons, dayCounter)

# ql.FixedRateBond.bondYield
compounding = ql.Compounded
cleanPrice = 100.7421875
fixedRateBond.bondYield(cleanPrice, dayCounter, compounding, cpn_freq) * 100
```

This gives a yield of `1.3784187000852273`, which is close, but not the same as the one given by the excel function.

## Answer by Luigi Ballabio (score 5)

https://quant.stackexchange.com/a/36039

Your question is more or less answered in How to calculate bond yield in QuantLib - Python. Once you've built the fixed-rate bond object (as in the post you linked) you can call

```
fixedRateBond.bondYield(targetPrice, day_count, compounding, frequency)
```

Comparing the above to the Excel interface in your link, `targetPrice` is `pr`, `frequency` is the frequency as in Excel, and `day_count` is `basis`. The other parameters (maturity, settlement etc.) go in the definition of the bond.

This will let you skip the part in Goutham's post that deals with spot-curve definition and pricing engines. However, you won't have something as simple as Excel's formula. That's because the definition of the bond has to include quite a few real-life parameters (such as: should we adjust the start and end of coupons to a business day when they fall on a weekend or a holiday? If so, how? What calendar should we use to decide what days are holidays? Do you want the yield to be continuously compounded?)

If you want to avoid this, you can choose defaults that make sense for you and wrap the calculations in a simpler interface that you can call.

## Answer by user8948 (score 2)

https://quant.stackexchange.com/a/36033

You're getting downvoted for asking a bad question. I'll explain why the question is bad.

Your link to the Excel documentation has the full specification of what YIELD does.

> If there is one coupon period or less until redemption, YIELD is calculated as follows: (image here; look at your link) where: A = number of days from the beginning of the coupon period to the settlement date (accrued days). DSR = number of days from the settlement date to the redemption date. E = number of days in the coupon period. If there is more than one coupon period until redemption, YIELD is calculated through a hundred iterations. The resolution uses the Newton method, based on the formula used for the function PRICE. The yield is changed until the estimated price given the yield is close to price.

So: if there is one coupon period, you have an explicit formula.

If not, you have to implement the PRICE equation (Excel documentation) and obtain $yield$ such that

$$PRICE(yield,\theta) = yield $$

where $\theta$ is a vector with the other parameters. Now, you could do this two ways:

- Implement a custom numerical routine. Excel does this with Newton's method, but you could try fixed-point iteration (after doing some math and verifying that PRICE satisfies the conditions of the contraction mapping theorem.



So in summary: all you have to do is:

1) Implement YIELD for one coupon period or less, as detailed in the Excel documentation 2) Implement PRICE as detailed in the Excel documentation; and 3) Use Python for root-finding.

This might take you a good half hour of coding, but hey, you're learning.