buying an individual gilt question

masonic

1) When you pay a separate price for accrued interest, then the unit price is already ex-income, so it should not fall like other securities that trade cųm-income. If you bought 41717.4 units, then the face value to plug into the calculation would be £41,717.40. This brings you closer to the amount you paid. Adjusting the number of days from 182, which may not be exactly right, will probably account for the remaining discrepancy.

2) I don't know what you mean by this.

aroominyork

masonic said:

2) I don't know what you mean by this.

On reflection, neither do I.

masonic

aroominyork said:

masonic said:

2) I don't know what you mean by this.

On reflection, neither do I.

In terms of a calculation, I'd approach it like this:

• Interest: 1.5*0.00125*£41,717.40 = £78.22 (you'll get the full interest in the next distribution, having already paid for the accrued interest)
  
• Capital gain: £41,717.40 - £39,994.47 = £1,722.93 (face value minus actual acquisition cost)
• Total return = £1801.15
• % return = £1801.15 / £39,994.47 = 4.50%
• Annual % return ~ 1.0450 ^ (1/(1+ 145/365)) - 1 = 3.20% (over 95% of which is a tax exempt capital gain)
  

aroominyork

Superb. I'll have a go at turning that into a prospective equation using date formulae (unless you would like to...?).

masonic

aroominyork said:

Superb. I'll have a go at turning that into a prospective equation using date formulae (unless you would like to...?).

Please go ahead.

aroominyork

I’m approaching it a slightly different way. The first part of my original formula gives your 4.50%:

(100+(0.125*3))/95.87)-1 = 4.50%.

Then bring in your “Annual % return ~ 1.0450 ^ (1/(1+ 145/365)) - 1 = 3.20%”

((100+(0.125*3))/95.87)) ^ (1/(1+145/365))-1 = 3.20%

Does (1+145/365) represent full + part years until maturity?

MarcoM

Very useful. Once you have worked out the equation could you post it on here please? 

EthicsGradient

The XIRR spreadsheet function is very useful for working this kind of thing out. Put in the dates and amounts of what you paid and get back:

06/09/22	-39994.47
31/01/23	26.07
31/07/23	26.07
31/01/24	=41717.40+26.07

and the the function =XIRR(B1:B4,A1:A4) gives you the APR: 0.0319 , and you can even tell it to format that as a percentage, for convenience.
(Using £39,994.47 because others have, but does that mean they credited £5.53 back to your account after the purchase? If it was an adjustment saying "you must pay a little more since you bought a bit of the way into the 6 month period" and you don't get it back, then surely the actual cost was the round £40,000, for your APR calculation)

aroominyork

Thanks, EthicsGradient - though I've never used functions like XIRR (but given how simple it looks I will study it).

In response to my question "Does (1+145/365) represent full + part years until maturity?" the answer is Yes. So I have refined the equation to:

((100+(0.125*3))/95.87)) ^ (1/(([redemption date]-TODAY())/365))-1

So you set up cells for: coupon/2; # payments to maturity; current price; redemption date, and then the formula works nicely.

MK62

aroominyork said:

Thanks, EthicsGradient - though I've never used functions like XIRR (but given how simple it looks I will study it).

In response to my question "Does (1+145/365) represent full + part years until maturity?" the answer is Yes. So I have refined the equation to:

((100+(0.125*3))/95.87)) ^ (1/(([redemption date]-TODAY())/365))-1

Try

=((100+(0.0625*3))/95.87)^(1/(YEARFRAC(TODAY(),"31/01/24")))-1

PS, while you'll get 3 coupons, they'll each be only half the annual coupon rate (masonic did it the other way.....by halving the no. of coupons - same result though)