Yield to maturity vs. XIRR , help with maths!

[AleisterCrowley]

Evening all
I'm investigating Gilts at the moment - looking at this site;
https://www.dividenddata.co.uk/uk-gilts ... -yields.py
Now, I thought I'd be able to work out the YTM easily to check the numbers. I've tried it two ways with XIRR in Excel: with coupons lumped together with redemption, and as positive cashflows twice yearly. Neither seem to give the same answer as the site linked.
As an example
TR25, 5% coupon
(click on the small box on the far right to bring up the detail )
Yield to Maturity: 5.372%
Coupon Payment Dates: 7 March,7 September
Total Accrued Interest: £1.712 per £100
Dirty Price: £101.12 = £99.41 + £1.712

XIRR all coupons lumped together with final redemption;
-101.12 11-Jul-23
110 07-Mar-25
5.209%

XIRR with coupons as positive cashflows

-101.12 11-Jul-23
2.5 07-Sep-23
2.5 07-Mar-24
2.5 07-Sep-24
102.5 07-Mar-25
5.445%

So the XIRR methods are coming out either side of the published YTM
Any ideas? Clearly I'm missing something obvious here
Thanks.

[Alaric]

TR25, 5% coupon
Yield to Maturity: 5.372%

..

XIRR with coupons as positive cashflows

-101.12 11-Jul-23
2.5 07-Sep-23
2.5 07-Mar-24
2.5 07-Sep-24
102.5 07-Mar-25
5.445%

The IRR calculation is correct, but for reasons lost in antiquity, Gilts yield are quoted as twice the half-yearly. It's a long tradition well predating computing. It was eminently practical when they only have tables to solve for the yield based on 6 month periods.

The relationship is that (1+quotedyield/2) ^2 -1 = IRR and the values quoted satisfy this relationship. IRR invariably being measured as a yearly quantity.

[AleisterCrowley]

Crikey! Let me go back to the spreadsheet

thanks

[mc2fool]

Any ideas?

Try =YIELD(TODAY(),"07/03/2025",5/100,99.41,100,2,3)

[AleisterCrowley]

Any ideas?

Try =YIELD(TODAY(),"07/03/2025",5/100,99.41,100,2,3)

That looks handy, and appears to give the answer from the site
Is it fair to say this understates the 'true' yield- by about 7bps in my example?

[mc2fool]

Try =YIELD(TODAY(),"07/03/2025",5/100,99.41,100,2,3)

That looks handy, and appears to give the answer from the site
Is it fair to say this understates the 'true' yield- by about 7bps in my example?

Uh?

[tjh290633]

Is this a situation where you should be using clean price instead of dirty price, or vice versa?

TJH

[AleisterCrowley]

That looks handy, and appears to give the answer from the site
Is it fair to say this understates the 'true' yield- by about 7bps in my example?

Uh?

Well XIRR says 5.445% compared with 5.372% for the half yearly gubbins

[monabri]

I never noticed that dividend data had this info...is it new (I usually look at the individual share or IT information) as bonds haven't really interested me until a tentative glance recently in that direction ?

I noticed that they also supply a calculator but there's no harm in D.I.Y.

[AleisterCrowley]

I never noticed that dividend data had this info...is it new (I usually look at the individual share or IT information) as bonds haven't really interested me until a tentative glance recently in that direction ?

...

Yes, same here. I only noticed it today, despite looking at the FTSE divi dates several times a month

[Kantwebefriends]

By golly that's a helpful site. Does anyone do the equivalent for Index-Linked Gilts?

[AleisterCrowley]

It is: I was looking for something with the Gilts in issue nicely laid out - found purely by accident today when I was looking at dividend dates and spotted the (new?) tab..
Can't help with the IL stuff

[monabri]

By golly that's a helpful site. Does anyone do the equivalent for Index-Linked Gilts?

https://www.yieldgimp.com/index-linked-gilt-yields


https://www.yieldgimp.com/gilt-yields

From a post here.

viewtopic.php?p=599897#p599897

[GoSeigen]

XIRR with coupons as positive cashflows

-101.12 11-Jul-23
2.5 07-Sep-23
2.5 07-Mar-24
2.5 07-Sep-24
102.5 07-Mar-25
5.445%

So the XIRR methods are coming out either side of the published YTM
Any ideas? Clearly I'm missing something obvious here
Thanks.

This looks correct on my brief look.

Is it not just the case that the linked site is using the wrong value to calculate YTM: should be dirty price but they are using clean?

Sorry, I haven't actually checked, this is my first impression.


GS

[GoSeigen]

XIRR with coupons as positive cashflows

-101.12 11-Jul-23
2.5 07-Sep-23
2.5 07-Mar-24
2.5 07-Sep-24
102.5 07-Mar-25
5.445%

So the XIRR methods are coming out either side of the published YTM
Any ideas? Clearly I'm missing something obvious here
Thanks.

This looks correct on my brief look.

Is it not just the case that the linked site is using the wrong value to calculate YTM: should be dirty price but they are using clean?

Sorry, I haven't actually checked, this is my first impression.

Oops previous post was nonsense. I should have checked before posting. The site's value looks correct by my calculations.

I'd now say that XIRR does not apply the same discounting for the calculation as used by the site. I'd have to investigate the differences (no longer stored in memory! [EDIT: Alaric's suggestion looks right, and he's a former actuary IIRC]) to identify exactly what the problem is, could be an exercise for the OP. Like mc2fool suggested, I'd just use the Yield function in your spreadsheet which will produce the value published by most sites.

GS

[AleisterCrowley]

I'm having another look - could be the compounding (discounting?) rules used by XIRR as you suggest ?
Taking a 1 year 5% bond at par, obv a £2.50 return in 6 months and again in 1 year is worth more now than £5 in a year.

[GoSeigen]

I'm having another look - could be the compounding (discounting?) rules used by XIRR as you suggest ?
Taking a 1 year 5% bond at par, obv a £2.50 return in 6 months and again in 1 year is worth more now than £5 in a year.

I think it's Alaric's suggestion, per values in OP: (SQR(1+5.445%)-1)*2 = 5.373%

which seems close enough.


GS

[mc2fool]

I'm having another look - could be the compounding (discounting?) rules used by XIRR as you suggest ?
Taking a 1 year 5% bond at par, obv a £2.50 return in 6 months and again in 1 year is worth more now than £5 in a year.

Hmmm, yes, that appears to be the difference:

=XIRR({-100,105},{0,365}) returns (unsurprisingly!) 5.000000% whereas
=XIRR({-100,2.5,102.5},{0,182,365}) returns 5.062676%

However =YIELD(TODAY(), TODAY()+365,5/100,100,100,frequency,3) returns (almost) the same for frequency (the number of coupon payments per year) set to either 1 or 2 (4.999317% vs 4.999826%).

[Alaric]

However =YIELD(TODAY(), TODAY()+365,5/100,100,100,frequency,3) returns (almost) the same for frequency (the number of coupon payments per year) set to either 1 or 2 (4.999317% vs 4.999826%).

The YIELD function is giving you the annual yield when the payment frequency is yearly and the twice the half yearly yield when the payment frequency is half yearly. The answers for comparing yields between bonds when they have differing frquencies of payment can be misleading.

[mc2fool]

However =YIELD(TODAY(), TODAY()+365,5/100,100,100,frequency,3) returns (almost) the same for frequency (the number of coupon payments per year) set to either 1 or 2 (4.999317% vs 4.999826%).

The YIELD function is giving you the annual yield when the payment frequency is yearly and the twice the half yearly yield when the payment frequency is half yearly. The answers for comparing yields between bonds when they have differing frquencies of payment can be misleading.

Yes, that's clear. I guess it's a matter of perspective as to whether that or the XIRR method gives the "correct" yield. I think personally I'd prefer the latter but as the former seems to be the industry standard we have to accept that.

And after all, when historical yields on shares are quoted it's simply on the sum of the dividends issued in the previous 12 months, without any accounting as to their timing...