Index linked bond ladder spreadsheet - need some critics/testers!

[Accumulator]

You've done a grand job. Don't think there's anything else like it for the UK. The US ones seem to be much less suitable for conversion than I realised.

I definitely prefer Tradeweb but the Base RPIs are what they are. That said, your estimated ones are very close.

I think I've worked out how to make 8-monthers work - in terms of building a ladder anyway, not managing cashflow.

Been playing with a version that aligns Target Real Income per rung by the length of time between maturity dates.

e.g. there's 20 months between redemption of UKGI 0.125 03/26 and UKGI 1.25 11/27. But only 4 months between UKGI 0.75 11/33 and UKGI 0.75 03/34.

[Accumulator]

Was going to ask you how much damage spreads do on the cost side? Looks like they could add quite a bit to the expense. Does your £40 transaction cost aim to capture spreads?

[helfordpirate]

I definitely prefer Tradeweb but the Base RPIs are what they are. That said, your estimated ones are very close.

They in fact exactly match - IF you work out exactly or find the Reference RPI for the day for which the Tradeweb data is the closing prices. If you just put in the Reference RPI from the notes in the YieldGimp data then it is typically a day out as YieldGimp is real-time and TradeWeb is yesterday (or worse).

I have updated the spreadsheet with more current data. I also noticed that YieldGimp doesnt give prices if there was no trade in the day and so sometimes the prices were 0. I have changed it so the sheet uses the last non-zero price it had in that case.

To be honest, I did not really explore the reality of the cost side - just put it in the spreadsheet. I think my platform (ii) is only £3.99. But I havent really explored it properly.

Good luck with your target income work. It's a bit of a rabbit hole!

[Accumulator]

I definitely prefer Tradeweb but the Base RPIs are what they are. That said, your estimated ones are very close.

They in fact exactly match - IF you work out exactly or find the Reference RPI for the day for which the Tradeweb data is the closing prices. If you just put in the Reference RPI from the notes in the YieldGimp data then it is typically a day out as YieldGimp is real-time and TradeWeb is yesterday (or worse).

Hi Helford, I think I must be missing something. I thought the Base RPI's don't change - they're set in stone. That seems to be how they appear on YieldGimp - they don't change from day to day. From the DMO doc on calculating cashflows: [I'm not allowed to post the link but it appears with a 'How to calculate cash flows on index-linked gilts' search.]

"In order to calculate interest payments on three-month lag index-linked gilts,
an ‘Index Ratio’ is applied, using particular Reference RPIs. The Index Ratio
is the ratio of the Reference RPI for the date of the cash flow divided by the
Reference RPI at the date of first issue..."

"The Reference RPI for the first issue date for a given bond remains constant
over its life. However, different index-linked gilts should have different values
for it, depending on when they are first issued."

So when I'm talking about Base RPIs, I mean the "The Reference RPI for the first issue date"

Maybe I'm misunderstanding, or this may well be trivial because your spreadsheet calculates a very similar number.

You're spot on about the rabbit hole

[helfordpirate]

So when I'm talking about Base RPIs, I mean the "The Reference RPI for the first issue date"

Maybe I'm misunderstanding, or this may well be trivial because your spreadsheet calculates a very similar number.

No you are exactly right. The Base RPI is Per Bond and is fixed for its lifetime.

But if you have a dataset that does not tell you the Base RPI or indeed the Index Ratio, you can calculate it using these identities:

DirtyPrice = CleanPrice * IndexRatio(Today) + AccruedInterest => IndexRatio(Today) = ( DirtyPrice - AccruedInterest )/ CleanPrice

IndexRatio(Today) = ReferenceRPI(90-days ago)/BaseRPI => BaseRPI = ReferenceRPI(90-days ago) / IndexRatio(Today)

So provided you have the ReferenceRPI for the current dataset you can work out everything. This is what I do for TradeWeb. You can calculate the ReferenceRPI from the monthly RPI numbers but it is fiddly - number of days in month, interpolation, rounding rules per DMO documents.

In reality, because the BaseRPI is fixed, you might as well look it up once and just hardcode it in the Linker table. Probably should have just done that!

[Accumulator]

Thanks for following up, Helford. Will let you know when the article goes up. if there's any justice you'll get plenty of positive feedback.

Think I've solved the short / long rung problem now. I think. What a time to be alive!

[Accumulator]

Hey helford,

I have a quick question. I'm wondering why you decided to round up the numbers of gilts purchased to a whole number? Was it to provide the income calculation with a buffer? As in, round up the number of gilts to ensure the required income is definitely achieved plus a little extra?

Or was it because brokers only allow you to buy whole individual gilts?

I assumed the latter but The Investor just bought some linkers and was able to buy fractional amounts. Seems like his broker allows him to buy 'units, which are worth 1/100th of a gilt.

Personally, I didn't realise gilt 'units' were a thing but came across this reference from the DMO:

https://www.dmo.gov.uk/responsibilities ... out-gilts/
"Whilst gilt prices are quoted per £100 nominal, gilts can be traded in units as small as a penny."

[y0rkiebar]

ii quote gilt prices assuming a £1 par value i.e. 1/100th of an "actual" gilt. If I obtain a buy quotation for an amount of £100 T24 gilts then ii come back with approx 101 "units" for that £100.

[helfordpirate]

Personally, I didn't realise gilt 'units' were a thing but came across this reference from the DMO:

Neither did I!! Last time I bought gilts (>10 years ago) they were whole ones i.e. £100 nominal. I just assumed that was the case.

I presume (as its DMO approved) this doesnt fall foul of HMRC ISA rules and fractional shares!

[daveh]

Personally, I didn't realise gilt 'units' were a thing but came across this reference from the DMO:

Neither did I!! Last time I bought gilts (>10 years ago) they were whole ones i.e. £100 nominal. I just assumed that was the case.

I presume (as its DMO approved) this doesnt fall foul of HMRC ISA rules and fractional shares!

I can't comment about ISAs as I bought in a GIA (low coupon short dated gilts to avoid tax) and buying in blocks of £5000 I was given fractional holdings eg 5099.25 TN24 and 6865.95 TG31

[helfordpirate]

Thanks for comments. I have made some minor changes to reflect a better understanding of implementation based on comments here.

- The Base RPI is fixed per bond, so instead of downloading it or calculating it each time it is now simply fixed in the Linker sheet. If a new linker is issued then it will need to be entered along with EPIC, ISIN, redemption date and coupon.

- The Implementation sheet now allows you to enter the trading cost and an estimate of the achievable bid/offer spread. This will depend on the platform, the market and critically the value of gilts purchased. But as it will be much more significant than the trading cost you can at least make some allowance for the extra cost.

- The Implementation sheet now allows you to say what trading granularity your platform supports - Whole £100 Gilts, Whole £1 Units, or Fractional £1 Units (1p fractions). A quick review of a few platforms suggest this covers most bases. I suspect £100 Gilts is something from a bygone probably paper age! ii seems to require whole £1 units and the DMO and others report they can be down to 1p fractions.

- Some minor improvements in column labels.

I dont want to get into a spiral of minor changes but if anything is actually broken I will endeavour to fix it. Thanks

[Accumulator]

Thanks both. I think the change may be due to the creation of a retail investor friendly bond trading platform - ORB.

[Accumulator]

Great stuff, helford. Thank you for updating!

[Accumulator]

Hi helford, small error on latest version - Tradeweb tab - the text in the row 2 fields have all accidentally shifted one space to the left. For example, K2 text should be in cell L2.

[pbarne]

Enjoying playing with this - very useful tool. I wonder if for those of us who are contemplating building a ladder in an unsheltered account might benefit from an option to filter out those issues with higher coupons? Or is there a way to do this already and I missed it?
Cheers,
P

[helfordpirate]

Enjoying playing with this - very useful tool. I wonder if for those of us who are contemplating building a ladder in an unsheltered account might benefit from an option to filter out those issues with higher coupons? Or is there a way to do this already and I missed it?
Cheers,
P

The biggest coupon is only 1.25%! But you can simply delete (delete the whole line so the data stays contiguous) the gilts you don't want to consider from the Linker sheet (perhaps take a backup!). Obviously you will create more gaps in the ladder and so add some inflation or interest rate risk depending how you choose to fill them.

@Accumulator. fixed cut & paste. just cosmetic as data is in correct columns.

[helfordpirate]

FYI Made some minor changes...
- added filter to exclude "high" coupon linkers
- allow to override income requirement on any rung of the ladder
- changelog