# 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.

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.

• thanks @luigi-ballabio, that's useful, although I try to set this up and I see differences in values for the same bond parameters as per mi updated post above - can you please take a look at my update and let me know if you can spot something to help me match the excel result? The excel result is the same as the 'street convention' result in Bloomberg... thanks! – tsando Sep 15 '17 at 10:00
• This is starting to become a different question. Anyway, the two things that come to mind are: (1) there are three different act/act conventions; check that you're using the correct one; (2) print out fixedRateBond.settlementDate() to check that it's the one you expect; if not, you might have to adjust the evaluation date or the number of settlement days. If neither help, please post to the quantlib mailing list. I'm afraid this is not the right place for debugging scripts... – Luigi Ballabio Sep 15 '17 at 10:18
• thanks @luigi-ballabio, setting the settlementDays value so that settlementDate matches the excel value, seems to almost match it spot on! – tsando Sep 15 '17 at 11:18

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:

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.

• Or, he could find the corresponding function in QuantLib, which is a library for financial calculations. Which is what he is asking :) – Luigi Ballabio Sep 15 '17 at 7:20
• Your comment on doing this for learning holds water, though. – Luigi Ballabio Sep 15 '17 at 7:21
• @user8948 thanks for your explanation, but my question was how to implement this function in Quantlib, not in excel. I know how to do this in excel, but I am having some differences in the quantlib result vs the excel result, and this is probably due to conventions and default values used by YIELD in excel versus Quantlib parameters and default values – tsando Sep 15 '17 at 8:33
• @user8948 i have updated my questions, and hopefully that's now clearer what i'm trying to achieve, so would appreciate if you consider upvoting or removing a bad vote, thanks – tsando Sep 15 '17 at 10:12
• I hadn't downvoted. :) Best of luck! – user8948 Oct 9 '17 at 19:28