buying an individual gilt question

aroominyork

Going back to the YTM calculations, @JohnWilder asks here about using the YIELD function. My calculation involved adding a cell for each coupon payment and was cumbersome. The YIELD function described on this video makes life much simpler. The results are slightly different - does any know (or care?) why? - my calc for TN25 at a price of £92.10 shows a YTM (XIRR) of 4.49% and the YIELD function shows 4.46%?

masonic

aroominyork said:

Going back to the YTM calculations, @JohnWilder asks here about using the YIELD function. My calculation involved adding a cell for each coupon payment and was cumbersome. The YIELD function described on this video makes life much simpler. The results are slightly different - does any know (or care?) why? - my calc for TN25 at a price of £92.10 shows a YTM (XIRR) of 4.49% and the YIELD function shows 4.46%?

It looks like that function is assuming the dates of the interest payments, which may differ from the actual dates. I don't know exactly which figures you are plugging into it, but it may not be (accurately) taking into account the first distribution and what you paid for accrued interest when you purchased.

Delburn

Another difference may be the frequency assumption/input into the yield formula.

XIRR will produce an annual equivalent rate (AER)

I think the convention for gilts and other bonds is to quote yields assuming interest is compounded in line with the coupon frequency (normally half yearly for gilts).  For example if the price of a gilt is exactly 100, and the coupon is say 4%, the yield is said to be 4% (payable half yearly), not the annual equivalent of (1+.04/2)^2-1=4.04%.

I think the excel YIELD function follows this convention, so for example a YIELD output 4.46% with a frequency input of 2, means an AER of 4.51%.  The yield function also has a day count convention input which may cause some minor differences.

NedS

aroominyork said:

Going back to the YTM calculations, @JohnWilder asks here about using the YIELD function. My calculation involved adding a cell for each coupon payment and was cumbersome. The YIELD function described on this video makes life much simpler. The results are slightly different - does any know (or care?) why? - my calc for TN25 at a price of £92.10 shows a YTM (XIRR) of 4.49% and the YIELD function shows 4.46%?

Really? My very crude spreadsheet is showing 3.91% YTM for TN25 at a price of £92.10. I could be wrong. Are you referring to the 0.25% coupon gilt?

aroominyork

NedS said:

aroominyork said:

Going back to the YTM calculations, @JohnWilder asks here about using the YIELD function. My calculation involved adding a cell for each coupon payment and was cumbersome. The YIELD function described on this video makes life much simpler. The results are slightly different - does any know (or care?) why? - my calc for TN25 at a price of £92.10 shows a YTM (XIRR) of 4.49% and the YIELD function shows 4.46%?

Really? My very crude spreadsheet is showing 3.91% YTM for TN25 at a price of £92.10. I could be wrong. Are you referring to the 0.25% coupon gilt?

Thanks for picking that up. I'd tapped in 91.02 instead of 92.10. With 92.10 I get 3.95% on XIRR and 3.93% on YIELD.

Delburn said:

Another difference may be the frequency assumption/input into the yield formula.

XIRR will produce an annual equivalent rate (AER)

I think the convention for gilts and other bonds is to quote yields assuming interest is compounded in line with the coupon frequency (normally half yearly for gilts).  For example if the price of a gilt is exactly 100, and the coupon is say 4%, the yield is said to be 4% (payable half yearly), not the annual equivalent of (1+.04/2)^2-1=4.04%.

I think the excel YIELD function follows this convention, so for example a YIELD output 4.46% with a frequency input of 2, means an AER of 4.51%.  The yield function also has a day count convention input which may cause some minor differences.

So while XIRR may be more accurate since you plug in the date of each coupon, YIELD is simpler to compute and gives a close enough figure for decision-making. Agreed?

NedS

aroominyork said:

NedS said:

aroominyork said:

Going back to the YTM calculations, @JohnWilder asks here about using the YIELD function. My calculation involved adding a cell for each coupon payment and was cumbersome. The YIELD function described on this video makes life much simpler. The results are slightly different - does any know (or care?) why? - my calc for TN25 at a price of £92.10 shows a YTM (XIRR) of 4.49% and the YIELD function shows 4.46%?

Really? My very crude spreadsheet is showing 3.91% YTM for TN25 at a price of £92.10. I could be wrong. Are you referring to the 0.25% coupon gilt?

Thanks for picking that up. I'd tapped in 91.02 instead of 92.10. With 92.10 I get 3.95% on XIRR and 3.93% on YIELD.

Cool, and confirms my calculations are in the right ballpark - mine are a little lower as they do not account for any reinvestment (compounding) of coupon payments.