Serving the Quantitative Finance Community

 
User avatar
equanimity
Topic Author
Posts: 27
Joined: April 30th, 2013, 2:54 am

Excel function to compute the yield of Italian Government Bonds

August 16th, 2017, 3:29 am

We can use the YIELD() function in Excel to compute the yield of most government bonds.  One exception is Italian Government Bonds (BTPs).  

I suspect the reason that the YIELD() function doesn't match Bloomberg is because Italian Government bonds have special pricing conventions.  In particular, yields are calculated using annual compounding even though coupons are paid are semi-annually.

Is there a way to work around this edge case?
 
User avatar
Hydraskull
Posts: 14
Joined: February 19th, 2016, 4:26 pm

Re: Excel function to compute the yield of Italian Government Bonds

August 16th, 2017, 1:02 pm

Can you convert the effective annual yield into a nominal bond yield and feed that into the excel yield formula?  For example, if the quoted yield on an annual effective basis is 5.0625%, use the formula:

2*{[(1+.050625)^.5]-1} = 5%

Then use 5% in Excel's yield formula.
 
User avatar
equanimity
Topic Author
Posts: 27
Joined: April 30th, 2013, 2:54 am

Re: Excel function to compute the yield of Italian Government Bonds

August 16th, 2017, 1:37 pm

Thanks, Hydraskull.  The YIELD() function in Excel takes the following arguments:

=YIELD(settlement_date, maturity_date, coupon, price, redemption, frequency, basis)

If we assume the following data:

Settlement Date:  11-September-2017
Maturity Date:  01-September-2028
Coupon:  4.75%
Price:  102.1277994
Redemption:  100
Frequency:  2
Basis:  1

If we pass the above-mentioned data to the YIELD() function, we get a yield of 4.5030678853 (which we know is off by about 5 basis points).

How are you suggesting that we use 2 * { [ (1 + rate) ^.5] -1 } = rate to solve for the outturn yield? 

Thanks! 
 
User avatar
DavidJN
Posts: 242
Joined: July 14th, 2002, 3:00 am

Re: Excel function to compute the yield of Italian Government Bonds

August 16th, 2017, 2:55 pm

Seeing as the bond pays semi-annual cash flows, off the top of my head I'd suggest solving for the semi-annual yield in the usual way, and then converting the result into an annual equivalent yield. 

This would be similar to the APR (annual percentage rate) calculations mandated by law in US consumer borrowing - regardless of the payment frequency of a consumer loan, the rate has to be quoted as an annual pay equivalent, presumably to illuminate the true cost and to facilitate comparison among alternatives.
 
User avatar
DavidJN
Posts: 242
Joined: July 14th, 2002, 3:00 am

Re: Excel function to compute the yield of Italian Government Bonds

August 16th, 2017, 3:03 pm

So, using the Excel function one gets 0.0450196154413037 as the semi-annual yield and the annual compound equivalent would be 0.0455263068849243.

Does that help?
 
User avatar
equanimity
Topic Author
Posts: 27
Joined: April 30th, 2013, 2:54 am

Re: Excel function to compute the yield of Italian Government Bonds

August 16th, 2017, 3:23 pm

DavidJN - yes, that helps.  I see that on my side as well.  Unfortunately, I'm trying to back into a rate of 4.5524% that I'm seeing on Bloomberg.  We're off slightly.  Perhaps it's the 1 day of accrued interest?
 
User avatar
equanimity
Topic Author
Posts: 27
Joined: April 30th, 2013, 2:54 am

Re: Excel function to compute the yield of Italian Government Bonds

August 16th, 2017, 4:09 pm

DavidJN - I don't believe it's the accrued interest.  When I set the settlement date to September 1, 2017 (which is a coupon payment date) and apply the following formula:

=((1+(YIELD(DATE(2017,9,1), DATE(2028,9,1), 0.0475, 102.1277994, 100, 2, 1) /2)) ^2) -1

I get a yield of 4.5532798%, whereas Bloomberg is showing a yield of 4.5531%.  So, we're still off about 1/100 of a basis point...
 
User avatar
outrun
Posts: 4573
Joined: January 1st, 1970, 12:00 am

Re: Excel function to compute the yield of Italian Government Bonds

August 16th, 2017, 6:05 pm

Is the daycount convention ACT/ACT?
 
User avatar
equanimity
Topic Author
Posts: 27
Joined: April 30th, 2013, 2:54 am

Re: Excel function to compute the yield of Italian Government Bonds

August 16th, 2017, 6:14 pm

Yes, the day count is Act/Act according to Bloomberg.
 
User avatar
DavidJN
Posts: 242
Joined: July 14th, 2002, 3:00 am

Re: Excel function to compute the yield of Italian Government Bonds

August 16th, 2017, 6:19 pm

A quick look using Google suggests that the BTP day count is Act/Act. For example see
http://help.derivativepricing.com/1298.htm

Is this a freshly issued bond? Could is be that the settlement date is within a short or long first coupon period? 

Is the Bloomberg number calculated using the Street method (the usual way as per Excel) or the Treasury method? The Treasury method (also called the Moosemuller method) uses simple interest for the first coupon period and compound interest thereafter. See http://help.cqg.com/cqgic/15/default.ht ... uller1.htm

Why anyone would want to do that is beyond me,

Also, matching Bloomberg numbers can at times be a mugs game. I used to in humor refer to that system as the industry standard error.
 
User avatar
DavidJN
Posts: 242
Joined: July 14th, 2002, 3:00 am

Re: Excel function to compute the yield of Italian Government Bonds

August 16th, 2017, 6:23 pm

Bond pricing is usually all about getting the dates correct. Ensure that your previous and next coupon dates match what you see in Bloomberg. Also, is the Bloomberg correcting for dates that fall on non-business days. Actual payments on BTPs use the Following date roll. Most bond yield calculation methods ignore date rolls but maybe the Bloomberg is adjusting for this.
 
User avatar
equanimity
Topic Author
Posts: 27
Joined: April 30th, 2013, 2:54 am

Re: Excel function to compute the yield of Italian Government Bonds

August 16th, 2017, 8:19 pm

Yes, it's the difference between the Street Yield and the True Yield.