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

[helfordpirate]

Hi All

I have been mulling over the idea of implementing an index linked gilt ladder for an inflation-proof base income. So I went looking for a spreadsheet on the web but could only find TIPS versions. As the weather is rubbish and the fishing worse, I have had a go at building one!

I am no gilt expert or Excel wizard but I think I know enough to do some damage. But I can see there are people on this forum who have more knowledge than me.

I have two questions...

- have I missed something and there is a decent well-tested linker ladder builder out there?
- would people here be interested taking a look at my effort and help me check that it looks correct?

The spreadsheet has a number of functions

- Data collection and cleansing. Takes price/bond data from either TradeWeb or YieldGimp and calculates some key attributes e.g. index ratio, RPI base. Only mark II 3-month lag bonds.

- Linker ladder model. Enter desired income, start year, duration of ladder and it constructs a linker ladder that delivers the real income through redemptions and final coupon(s) and rolled up coupons from later bonds in the ladder. Handles gaps in maturities by selectably either selling later bonds or redeeming earlier bonds.

- Ladder implementation. From the model ladder, list of bonds to purchase and cost (dirty price).

- Future cash flow. A simplistic analysis of cash flow of future money by year. You can enter assumptions about RPI growth and see how the ladder flexes to meet the real income requirement. Not sure if its really that useful but...

I have made a reasonable effort to make it user-friendly so should not be too hard for others to understand.

I dont want to just post it in case there is one already in use or no interest. Thanks

[gpadsa]

Yes, please do share somehow, I guess I'm not the only one who would like to do some trials of the idea of setting up a gilt ladder & it sounds like you've made great strides.

gpadsa

[JohnW]

I can’t do your spreadsheet justice, but could you not test it yourself by manually constructing a ladder using Pfau’s method of working back from the last year of spending, selecting a suitable bond for each year or so? Then see if yours give a comparable result.

[spasmodicus]

I was just struggling, as I suspect others do, with back-of-envelope calculations on an ad hoc (not index linked) ladder, trying to reconcile clean and dirty prices, durations and what was optimal from present bonds on offer etc. etc. so any insights into bond calculations will be most useful.
S

[helfordpirate]

So here is the spreadsheet....

https://1drv.ms/x/s!ArEsIHcdrWAHg4QD2GJ ... g?e=XC6NpU

There are six tabs:
Model Ladder - enter key parameters of ladder here (start, duration, required income). Shows the model ladder.
Implementation - a table showing what linkers to buy, how many and the overall cost
Cash Flow - a table showing the future cash flow estimates given a selected inflation scenario
Linkers - a list of all the candidate gilts and key parameters such as price, index ration, RPI base.
YieldGimp - current prices and data from YieldGimp (you have to cut & paste)
TradeWeb - current prices and data from TradeWeb (you need an account and have to cut & paste)

There is a switch on Linkers to select which source of data. I have pasted current prices which are good enough for testing.

The methodology is for sure correct ("start at the last gilt, set the amount, roll-back the coupon, rinse repeat"). But it would be helpful for any sanity checking of the results.

Any comments appreciated (within reason)

Thanks

[Midsmartin]

I'd like to add my thanks for this. It's an amazing bit of work! I had recently started designing my own ladder, but much more approximate and lumpy in results. This will do much better. Thank you.

[monabri]

Thanks for the post. I've downloaded a copy and have been 'having a play'. It seems a useful planning tool.

[helfordpirate]

Thanks. I'll take it that so far no-one has seen any obvious errors.

Must say for someone who is thinking of taking a chunk of money "off the table", it looks reasonably attractive - inflation proof (albeit not necessarily your inflation but RPI) and a locked in positive return - and if you change your mind and want to fly round the world in Emirates First you still can!
Problem for me, is that taking money of the table would be a big CGT hit which makes it much less attractive.

[dealtn]

Thanks. I'll take it that so far no-one has seen any obvious errors.

Must say for someone who is thinking of taking a chunk of money "off the table", it looks reasonably attractive - inflation proof (albeit not necessarily your inflation but RPI) and a locked in positive return - and if you change your mind and want to fly round the world in Emirates First you still can!
Problem for me, is that taking money of the table would be a big CGT hit which makes it much less attractive.

If you change your mind can you explain how you are not at risk of the market having gone against you?

Or am I making that "obvious error"?

[helfordpirate]

If you change your mind can you explain how you are not at risk of the market having gone against you?

No of course particularly at the long end of the ladder it could be quite ugly!

I was more comparing it to the obvious other "take money off the table" option being an annuity - where there is no going back!

[dealtn]

If you change your mind can you explain how you are not at risk of the market having gone against you?

No of course particularly at the long end of the ladder it could be quite ugly!

I was more comparing it to the obvious other "take money off the table" option being an annuity - where there is no going back!

Understood. Thank you.

[Midsmartin]

The only thing I've noticed is that if you set the number of years to 40 (indicated as the maximum that works), and then change the desired income, the spreadsheet does not update. If you set it to 39 years, then it does!

Thanks. I'll take it that so far no-one has seen any obvious errors.

Must say for someone who is thinking of taking a chunk of money "off the table", it looks reasonably attractive - inflation proof (albeit not necessarily your inflation but RPI) and a locked in positive return - and if you change your mind and want to fly round the world in Emirates First you still can!
Problem for me, is that taking money of the table would be a big CGT hit which makes it much less attractive.

[GoSeigen]

The only thing I've noticed is that if you set the number of years to 40 (indicated as the maximum that works), and then change the desired income, the spreadsheet does not update. If you set it to 39 years, then it does!

I think I noticed that too, but my (old) version of Excel reported a circular reference.

GS

[helfordpirate]

The only thing I've noticed is that if you set the number of years to 40 (indicated as the maximum that works), and then change the desired income, the spreadsheet does not update. If you set it to 39 years, then it does!

Thanks. fixed.

[Sleepingdogs]

Thanks very much for this work! I've been searching for a UK version without success for a while now.
However, I'm having trouble with the copy and paste from the yieldgimp page. I can't get it to line up correctly.
I'm also unsure how to obtain the Base RPI from the index ratio. You have 375.43 in there in the download.
Any help gratefully received. Cheers..

[Accumulator]

Hi helfordpirate,

Thank you very much for your amazing spreadsheet. I'm in the midst of a Monevator post on building linker ladders and your spreader is way better than anything I could have come up with.

Would you mind if I link to your spreadsheet and use it to illustrate how to build a ladder?

Hat tip to Sleepingdogs who shared a link to your work on Monevator.

@ Sleepingdogs - I've spot checked some of the Base RPIs on Yield Gimp and they're all correct so far. You can dig out Base RPI info via the DMO but it's tedious to do. My apologies if I've misunderstood what you're trying to do.

[helfordpirate]

Would you mind if I link to your spreadsheet and use it to illustrate how to build a ladder?

Sure. It would be an honour! Learnt a lot from your site.

[helfordpirate]

Thanks very much for this work! I've been searching for a UK version without success for a while now.
However, I'm having trouble with the copy and paste from the yieldgimp page. I can't get it to line up correctly.
I'm also unsure how to obtain the Base RPI from the index ratio. You have 375.43 in there in the download.
Any help gratefully received. Cheers..

If you start your selection on YieldGimp where it says "IndexLinkedSummary: IL Yields" then paste into the spreadsheet at A2 it should all line up. You can also just align it later - select a column subset, "Insert.." and "Shift cells to the right...".

Do you mean the "Reference RPI"? You only need to put that in if you want to estimate future cash flows AND you are using the TradeWeb dataset (not YieldGimp). You don't need it to build the ladder as that is all in "real" money. The Reference RPI is the value of the RPI index 3-months ago (simplistically) from the date of the dataset you are using.

(Because the TradeWeb dataset does not give the per bond Base RPI which is needed to calculate the estimated actual coupon and redemption in the future based on your estimated future RPI.)

[Accumulator]

Thank you Helford! Much obliged.

To answer your question earlier in the thread, I can't find any mistakes. Haven't checked the Cashflow tab - but I've given the other elements a good going over

YieldGimp base RPIs seem to be correct. I haven't checked every single one yet, but no mistakes so far. You could substitute these in when using Tradeweb data?

Was wondering why 8-month linkers aren't included? Do they screw things up somehow?

[helfordpirate]

YieldGimp base RPIs seem to be correct. I haven't checked every single one yet, but no mistakes so far. You could substitute these in when using Tradeweb data?

My intention was that you might either have access to TradeWeb (which is a login) or to YieldGimp. But I suppose as YieldGimp seems completely open and free one could assume it was available. Some may prefer TradeWeb as it is DMO approved.

As far as I could tell, crosschecking between TradeWeb and YieldGimp, the data is good. The Base RPIs seem correct - you can reverse-calculate them from the TradeWeb index ratio and a Reference RPI and they are consistent.

Was wondering why 8-month linkers aren't included? Do they screw things up somehow?

In my mind, there are only a few left (3 I think) and 8 months is quite a lag in the context of inflation in the last few years! Also originally, I had intended to do a more accurate cash flow analysis which they would have complicated. In the end, the Excel mechanics were too much for me and I assumed all rolled-up coupons were paid in a single payment with a reference RPI 90-days into the year. But as the coupons are relatively small it is not too far off.

BTW I did this spreadsheet because I was/am seriously interested such a ladder. My problem though is where to put it - the ladder itself is very tax efficient so it is a waste to put in a tax sheltered account where my remaining equity pot should be; but to put in taxable I would have to sell holdings that are showing 150%+ capital gains (Vanguard Dev Wrld ex-UK for 15 years). So still pondering - of course the bond market might move back up and I will have missed the chance.