Help with calculating implied yield from Bund futures

Dec 8, 2011
4
0
11
#1
Hi,

I am working on a spreadsheet to back out the implied yield from the Bund price. I have my textbooks and examples, but cant seem to come up with yields that match what I see on Frankfurt Stock Exchange page for the deliverable bonds.

An example is the best way to show my working, so I attached a screenshot of my spreadsheet.

My calculated fwd price (FutInvPrice column) is the current futures price (143.7) * CF (from Eurex site) + accrued interest (from the last coupon to delivery date of futures contract). I then plug this into the Excel YIELD function, using the delivery date of the futures contract as the settlement, and the maturity of the bond in question.

You can see that the market yield is way off, especially for the first two.

Is anyone able to spot the error I am making here?

Thanks.
 

Attachments

Dec 8, 2011
4
0
11
#3
You've got the CTD spot on, why are you expecting the implied yields of the other deliverables to match the actual yields?
Hi,

I now have the lightbulb moment that the implied forward yield of the futures contract should only track the CTD (sounds obvious in retrospect)..

I was under the impression the CTD was the May 2023 contract which was throwing me off. I was assuming this due to the MTS website showing that bond as the current benchmark (MTS Indices - European Bond Spreads). I haven't looked too closely at the CTD logic yet, but is it always the lowest implied yield?

Thanks.
 

Martinghoul

Well-known member
Feb 3, 2009
2,691
276
93
#4
Hi,

I am working on a spreadsheet to back out the implied yield from the Bund price. I have my textbooks and examples, but cant seem to come up with yields that match what I see on Frankfurt Stock Exchange page for the deliverable bonds.

An example is the best way to show my working, so I attached a screenshot of my spreadsheet.

My calculated fwd price (FutInvPrice column) is the current futures price (143.7) * CF (from Eurex site) + accrued interest (from the last coupon to delivery date of futures contract). I then plug this into the Excel YIELD function, using the delivery date of the futures contract as the settlement, and the maturity of the bond in question.

You can see that the market yield is way off, especially for the first two.

Is anyone able to spot the error I am making here?

Thanks.
Why on Earth are you adding accrued to the futures px * CF? If you do that, you're going to get some sort of a funny dirty px. Excel YIELD function uses a clean px to calculate the yield, which is probably why you're getting all these weird results.

The CTD is, quite literally, the cheapest to deliver bond, so to figure out which one it is, you need to do some additional calcs.
 
Dec 8, 2011
4
0
11
#5
Why on Earth are you adding accrued to the futures px * CF? If you do that, you're going to get some sort of a funny dirty px. Excel YIELD function uses a clean px to calculate the yield, which is probably why you're getting all these weird results.

The CTD is, quite literally, the cheapest to deliver bond, so to figure out which one it is, you need to do some additional calcs.
I'm trying to work out examples based on a number of sources.

I re-did some calcs, and can see the CTD is in fact the Jul-22 bond. When I calculate the implied dirty yield of that bond, it matches the quotes from the Frankfurt exchange. So can I assume the implied yield of the futures price is via the CLEAN price of the CTD * CF, and the quoted price from the exchange is dirty, or is the calculated yield via the dirty price just throwing me off completely? I'm using the exchange prices to cross-check my calculations.

I apologise if these questions appear basic - I dont have a finance background and I am seeing various sources describe the process in slightly different ways, which is why I am confused.
 

Attachments

Martinghoul

Well-known member
Feb 3, 2009
2,691
276
93
#6
I'm trying to work out examples based on a number of sources.

I re-did some calcs, and can see the CTD is in fact the Jul-22 bond. When I calculate the implied dirty yield of that bond, it matches the quotes from the Frankfurt exchange. So can I assume the implied yield of the futures price is via the CLEAN price of the CTD * CF, and the quoted price from the exchange is dirty, or is the calculated yield via the dirty price just throwing me off completely? I'm using the exchange prices to cross-check my calculations.

I apologise if these questions appear basic - I dont have a finance background and I am seeing various sources describe the process in slightly different ways, which is why I am confused.
Forget the dirty yield/px, it's throwing you off... Use the quoted px of the futures * CF and apply the YIELD function to that. That's all there is to it.