Excel YIELD function equivalent in python Quantlib

Question

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 1

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 2

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.

• If this sounds hard: use scipy.optimize.root (which you could have discovered googling for "python function root". Note that this function solves the equation $f(x)=0$, so you want to feed it $PRICE(yield,\theta)-yield$

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.