Where has my accrued interest formula gone wrong?

aroominyork

I recently came up with a clever (I thought…) Excel formula to show accrued interest to date on a nominal gilt. I own 73753 units of TE28, 4,375% maturing 7/3/28, ie last coupon paid 7/9/26. My formula is #units*4.375%*((TODAY()-DATE(2026,9,7))/365). Excel shows £450.85 as the accrued coupon, but if get a sell quote on ii the accrued interest is shown as £472.42. Where have I gone wrong please?

EthicsGradient

I can't see anything obviously wrong with your calculation. Dividend Data TE28 - 4 3/8% Treasury Gilt 2028 has 51 days of accrued interest being £0.616 per £100, which is within 1% of your £0.611 per £100 (which may be explainable because the 365 day year is divided into 181 and 184 day "halves" between 7th Sept and 7th March).

SnowMan

You are essentially assuming that dividends are paid every 182.5 days (= 365/2). In practice they are paid every 6 months which isn't quite the same as the next coupon period is only 181 days.

You are also assuming settlement today, in reality they use T + 2 so accrued interest is to the settlement date on 30/10/2026

8/9/2026 to 30/10/2026 = 53 days

8/9/2026 to 7/3/2027 = 181 days

And so the calculation is

= coupon/2 x 53/181 x number of units

= 0.04375 x 0.5 x 53/181 x 73,753 = 472.42

Linton

Google tells me that the accrued interest for gilts is based on the settlement date which is trade date+n. n=1 if bought direct but for AJBell n=2. I guess other platforms would be the same.

I think if n=2 the amounts would be close.

aroominyork

Thanks All, that’s interesting on two counts. First, the coupon per day can be different between two halves of the year depending on the number of days in the six months. Second, you receive the coupon for two days after sale and up to settlement. Since the (unsettled) funds are shown on my account almost immediately after sale, those funds can be invested in another asset, eg a STMMF (assuming you don’t get charged interest by your platform if the second asset settles before the gilt). For a high coupon gilt doesn't that let you earn double income on the same funds?

PS It now works to the penny with a formula of #units*4.375%/2*(TODAY()-DATE(2026,9,7)+2)/(DATE(2027,3,7)-DATE(2026,9,7))

PPS Is the answer that you only start accruing interest after two days (T+2) when you buy the gilt?

phlebas192

I was under the impression that gilts settled on T+1, not T+2. But, yes, entitlement to the coupon only occurs when the trade is settled, not when it is purchased.

FWIW, gilts go ex-dividend 7 business days before the coupon is paid. Between the ex-dividend and payment date, accrued interest is negative!

See DMO About Gilts page

If you ever want to calculate interest for index linked gilts then the formulae can be found in https://www.dmo.gov.uk/media/0ltegugd/igcalc.pdf

slinger2

I started with Scottish Widows and they use T+2. Now using Lightyear and they use T+1.

aroominyork

https://forums.moneysavingexpert.com/discussion/comment/82184648#Comment_82184648

Interesting. I thought it was asset specific, not platform specific.

masonic

https://forums.moneysavingexpert.com/discussion/comment/82184801#Comment_82184801

Back in the day, some of the better platforms offered extended settlement as a feature. For example TD Direct up until it was hoovered up by the very hungry iii of the time.

Nowadays you have platforms using different depositories (CREST / Euroclear), which could introduce some variation.

EthicsGradient

https://forums.moneysavingexpert.com/discussion/comment/82184811#Comment_82184811

ii offers extended settlement (at, I think, an extra £5 charge) in its trading account; but the wording looks like only for the trading account (would it count as lending money, and thus not "suitable" for an ISA or SIPP?)