Got a credit card? use our Credit Card & Finance Calculators
Thanks to Anonymous,bruncher,niord,gvonge,Shelford, for Donating to support the site
Yield to maturity vs. XIRR , help with maths!
-
- Lemon Half
- Posts: 6385
- Joined: November 4th, 2016, 11:35 am
- Has thanked: 1882 times
- Been thanked: 2026 times
Yield to maturity vs. XIRR , help with maths!
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.
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.
-
- Lemon Half
- Posts: 6134
- Joined: November 5th, 2016, 9:05 am
- Has thanked: 21 times
- Been thanked: 1428 times
Re: Yield to maturity vs. XIRR , help with maths!
AleisterCrowley wrote: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.
-
- Lemon Half
- Posts: 6385
- Joined: November 4th, 2016, 11:35 am
- Has thanked: 1882 times
- Been thanked: 2026 times
-
- Lemon Half
- Posts: 8023
- Joined: November 4th, 2016, 11:24 am
- Has thanked: 7 times
- Been thanked: 3094 times
Re: Yield to maturity vs. XIRR , help with maths!
AleisterCrowley wrote:Any ideas?
Try =YIELD(TODAY(),"07/03/2025",5/100,99.41,100,2,3)
-
- Lemon Half
- Posts: 6385
- Joined: November 4th, 2016, 11:35 am
- Has thanked: 1882 times
- Been thanked: 2026 times
Re: Yield to maturity vs. XIRR , help with maths!
mc2fool wrote:AleisterCrowley wrote: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?
-
- Lemon Half
- Posts: 8023
- Joined: November 4th, 2016, 11:24 am
- Has thanked: 7 times
- Been thanked: 3094 times
Re: Yield to maturity vs. XIRR , help with maths!
AleisterCrowley wrote:mc2fool wrote: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?
-
- Lemon Half
- Posts: 8407
- Joined: November 4th, 2016, 11:20 am
- Has thanked: 929 times
- Been thanked: 4229 times
Re: Yield to maturity vs. XIRR , help with maths!
Is this a situation where you should be using clean price instead of dirty price, or vice versa?
TJH
TJH
-
- Lemon Half
- Posts: 6385
- Joined: November 4th, 2016, 11:35 am
- Has thanked: 1882 times
- Been thanked: 2026 times
Re: Yield to maturity vs. XIRR , help with maths!
mc2fool wrote:AleisterCrowley wrote: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
-
- Lemon Half
- Posts: 8498
- Joined: January 7th, 2017, 9:56 am
- Has thanked: 1562 times
- Been thanked: 3460 times
Re: Yield to maturity vs. XIRR , help with maths!
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.
I noticed that they also supply a calculator but there's no harm in D.I.Y.
-
- Lemon Half
- Posts: 6385
- Joined: November 4th, 2016, 11:35 am
- Has thanked: 1882 times
- Been thanked: 2026 times
Re: Yield to maturity vs. XIRR , help with maths!
monabri wrote: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
-
- Lemon Slice
- Posts: 365
- Joined: November 5th, 2016, 4:02 pm
- Has thanked: 26 times
- Been thanked: 108 times
Re: Yield to maturity vs. XIRR , help with maths!
By golly that's a helpful site. Does anyone do the equivalent for Index-Linked Gilts?
-
- Lemon Half
- Posts: 6385
- Joined: November 4th, 2016, 11:35 am
- Has thanked: 1882 times
- Been thanked: 2026 times
Re: Yield to maturity vs. XIRR , help with maths!
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
Can't help with the IL stuff
-
- Lemon Half
- Posts: 8498
- Joined: January 7th, 2017, 9:56 am
- Has thanked: 1562 times
- Been thanked: 3460 times
Re: Yield to maturity vs. XIRR , help with maths!
Kantwebefriends wrote: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
-
- Lemon Quarter
- Posts: 4502
- Joined: November 8th, 2016, 11:14 pm
- Has thanked: 1635 times
- Been thanked: 1637 times
Re: Yield to maturity vs. XIRR , help with maths!
AleisterCrowley wrote: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
-
- Lemon Quarter
- Posts: 4502
- Joined: November 8th, 2016, 11:14 pm
- Has thanked: 1635 times
- Been thanked: 1637 times
Re: Yield to maturity vs. XIRR , help with maths!
GoSeigen wrote:AleisterCrowley wrote: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
Last edited by GoSeigen on July 12th, 2023, 9:16 am, edited 1 time in total.
-
- Lemon Half
- Posts: 6385
- Joined: November 4th, 2016, 11:35 am
- Has thanked: 1882 times
- Been thanked: 2026 times
Re: Yield to maturity vs. XIRR , help with maths!
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.
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.
-
- Lemon Quarter
- Posts: 4502
- Joined: November 8th, 2016, 11:14 pm
- Has thanked: 1635 times
- Been thanked: 1637 times
Re: Yield to maturity vs. XIRR , help with maths!
AleisterCrowley wrote: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
-
- Lemon Half
- Posts: 8023
- Joined: November 4th, 2016, 11:24 am
- Has thanked: 7 times
- Been thanked: 3094 times
Re: Yield to maturity vs. XIRR , help with maths!
AleisterCrowley wrote: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%).
-
- Lemon Half
- Posts: 6134
- Joined: November 5th, 2016, 9:05 am
- Has thanked: 21 times
- Been thanked: 1428 times
Re: Yield to maturity vs. XIRR , help with maths!
mc2fool wrote:
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.
-
- Lemon Half
- Posts: 8023
- Joined: November 4th, 2016, 11:24 am
- Has thanked: 7 times
- Been thanked: 3094 times
Re: Yield to maturity vs. XIRR , help with maths!
Alaric wrote:mc2fool wrote: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...
Who is online
Users browsing this forum: No registered users and 2 guests