Gilt ladder building tool

[LateGenXer]

I created a tool for building a gilt ladder – lategenxer.streamlit.app/Gilt_Ladder – (I'm not allowed to post any links yet, so one needs to copy'n'paste this link onto the browser's address bar.)



It can take in consideration one's marginal income tax. Instead of using heuristics to filter out bonds with high coupons, it will select the optimal combination of all bonds using linear programming, and therefore it always make the right trade off between high coupons and depreciating cash balances.

It can also take arbitrary withdrawal schedule as an CSV. This can be useful if one wants to use gilts to fund nominal or real expenses in the medium term, such as partially paying out a mortgage when fixed deal ends, or school tuition fees.

The tool has some limitations and makes certain assumptions. If you plan to use this please read the disclaimer on the bottom, the description on the About tab, the help on the (?) icons, and any notices produced. That said, I do hope others find this useful.

[thegr8Destructo]

very cool!

[monabri]

Thanks for sharing!!

[helfordpirate]

Hi - impressive bit of work! Thanks

Couple of questions if I might...
- could you just clarify what the "income" column in "cash flow" means vs "incoming"? I see it is identical to "incoming" after the first year?
- if I use Index linkers and have "interest rate" set to 0% there seem to be some anomalies in the cash flow balances e.g

2024-11-22 Coupon from 135.54 × T47 @ 0.7001 92.30 46,705.05 92.30
2025-01-01 Withdrawal 25,000.00 21,555.76

if I set the interest rate to something non-zero then the anomaly goes?

Thanks

[LateGenXer]

could you just clarify what the "income" column in "cash flow" means vs "incoming"? I see it is identical to "incoming" after the first year?

Incoming is money flowing in. Outgoing is money flowing out (only withdrawals for now, but might one day include dividind reinvestment.)

"Income" is taxable income, ie. what's the income tax will apply to. It differs from incoming due to:
- accrued interest (reduces income)
- redemptions (considered capital return, not income)

if I use Index linkers and have "interest rate" set to 0% there seem to be some anomalies in the cash flow balances

I don't spot the anomaly. Could you elaborate? What were the parameters?

Not the values are in todays money. So the £46,705.05 on 2024-11-22 depreciates to £25,000.00 + £21,555.76 on 2025-01-01. Was that the issue?

I could show the nominal values instead -- they would add up perfectly -- but not be very useful to reason about...

[monabri]

I say..sexy graphics!

[helfordpirate]

"Income" is taxable income, ie. what's the income tax will apply to. It differs from incoming due to:
- accrued interest (reduces income)
- redemptions (considered capital return, not income)

Understood. It was the affect of accrued interest I was seeing in the first year.

Not the values are in todays money. So the £46,705.05 on 2024-11-22 depreciates to £25,000.00 + £21,555.76 on 2025-01-01. Was that the issue?

Yes I can see now that the cash balance is being depreciated. It confused me because I was just sanity checking looking at the cash flow at the first withdrawal. Because cash interest was set to x% there was an interest payment on the day of the withdrawal and so the withdrawal transaction exactly balanced (as all the depreciation was accounted for in the interest transaction). When I set interest to 0, the same transaction no longer "added up" as it took all the depreciation hit. But that is clear now.

So last question ..
just curious to understand how you calculate the coupon and redemption payments in today money and with the lag on the indexation? So you can calculate or lookup the index ratio for the linker today which will be based on the RPI "three months ago" (say) and the linkers base RPI. Then you can calculate a redemption value in todays money. Do you just use that value on the date of redemption? It's making my head hurt - but wouldn't that imply inflation indexation all the way to the redemption date whereas the last three months it is depreciating. Perhaps it all comes out in the wash.

(btw thanks also for introducing streamlit - looks an interesting platform)

[LateGenXer]

just curious to understand how you calculate the coupon and redemption payments in today money and with the lag on the indexation?

What I do is relatively simple:
1. I take the RPI series from ONS and extrapolate it into the future, assuming it grows exponentially with a 3% inflation rate, that is, multiply the index by (1 + .03)^(1/12) every month
2. I multiply all withdrawals by RPI_date/RPI_today to transform figures from real to nominal
3. Calculate all cash flows in nominal terms (which will look at the lagged RPI internally to compute the inflation uplifts)
4. Throughout all resulting cash flows, divide figures by RPI_date/RPI_today to get back real figures

Because all index-linked cash flows are indexed to inflation it doesn't matter what inflation rate one assumes -- it should be a wash as far as index linked gilts are concerned.

The exception is the cash balances left between coupons/redemptions and withdrawals -- the higher the assumed inflation the more they depreciate.

Why 3%? It's a nice round number, it's commonly used, and it's already higher than what most assume it will be the long term inflation (which is around 2.5%.) I'm considering eventually using BoE 's inflation curve, it's a decent inflation predictor for the short term, but it can be biased -- it tends to overestimate inflation due to the inflation risk premium (the fact that indexation is a form of insurance, therefore a premium will be expected, and the more volatile inflation is, the higher it tends to be.)

thanks also for introducing streamlit - looks an interesting platform

Yes, Streamlit is a wonderful piece of kit, making it really easy to build this sort of interactive sites requiring just a bit of Python knowledge.

BTW, thank you for your spreadsheet you posted back in October! I perused it while learning about gilts and gilt ladders, and I do mention it on the Additional resources in the About tab. There was really nothing else for gilts before it.

PS: Still not allowed to post any links here.