Premium Bond Winner ?

Swipe

Cressida100 said:

Swipe said:

Can I make it 4 consecutive blanks in a row on the max holding?

OMG I feel for you. I keep waiting for a blank. Good luck next month!

Well I got £550 this month so not so bad.

fredflower

Hi, I'm new to this thread and will never catch up with all that has been said so bear with me if I ask things that were answered previously.

I've just spent ages finally putting an Excel spreadsheet together to record my buys, wins and auto investments. I've numbered each block of bonds and when I've won I've highlighted which block won. I was beginning to think the reinvested bonds would never win, until some earned in Feb 2024 won in Aug 2024. Yay! 😁 

I started 2024 with £4475 and now have £5100 thanks to my wins.

Jan £0
Feb £500
Mar £0
Apr £25
May £0
Jun £0
Jul £0
Aug £50
Sep £50

What I'd like to do is calculate (easily! i.e. enter a formula and pull down for other cells) how my wins over time compared over time to savings, i.e. as a percentage.

I tried to rehash some calculations found on page 987 or thereabouts, but it got more complicated trying to incorporate the reinvestments and calculating month by month. My maths head has well and truly left the building for now (brain fog). 😫

Please can anyone help me? 🙏 

If you've read this far, thank you! 

llcomper

llcomper said:

My first ever win - £100 on a £580 holding! Very happy with that, I didn't expect to win anything with such a small holding.

I've been just adding spare change (£30 or so) each month for the thrill as an alternative to throwing my money away with the lottery

Unbelievably I won again this month, premium bonds must be like buses! A £50 win on an £840 holding this time. I've probably used up all my luck now!

SnowMan

fredflower said:

Hi, I'm new to this thread and will never catch up with all that has been said so bear with me if I ask things that were answered previously.

I've just spent ages finally putting an Excel spreadsheet together to record my buys, wins and auto investments. I've numbered each block of bonds and when I've won I've highlighted which block won. I was beginning to think the reinvested bonds would never win, until some earned in Feb 2024 won in Aug 2024. Yay! 😁 

I started 2024 with £4475 and now have £5100 thanks to my wins.

Jan £0
Feb £500
Mar £0
Apr £25
May £0
Jun £0
Jul £0
Aug £50
Sep £50

What I'd like to do is calculate (easily! i.e. enter a formula and pull down for other cells) how my wins over time compared over time to savings, i.e. as a percentage.

I tried to rehash some calculations found on page 987 or thereabouts, but it got more complicated trying to incorporate the reinvestments and calculating month by month. My maths head has well and truly left the building for now (brain fog). 😫

Please can anyone help me? 🙏 

If you've read this far, thank you! 

Because your winnings are being reinvested in premium bonds (I'm assuming) then something like this should work if you are looking for the annualised return from the start of the year to the end of each respective month (which is what you would use to compare with a savings account interest rate over that period). 

I've highlighted the only difficult formula for cell F7 (see the formula bar at the top) which can be copied up/down that column. 

Note the annualised return is quite high at 88.8% for month 2 because annualising assumes your good luck over 2 months is repeated for the following 10 months. After 9 months you've increased your capital by 14%, and 14% over 9 months is equivalent to 19% per annum.

fredflower

One thing I noticed on my NS&I online account is that there's no mention of my earlier wins. The wins were received as a cheque rather than my later ones that I set to auto reinvest, but surely they should still be showing on the system..? 🤔

I've checked previous paperwork against what is currently on my account and the holder's numbers match though the hole in data and no more wins for those bonds does make me wonder...

The paper record sent in 2011 when I bought more bonds shows the holder's number, but not an NS&I number. Is it possible to have multiple NS&I numbers?

Thoughts please. 

fredflower

SnowMan said:

fredflower said:

Hi, I'm new to this thread and will never catch up with all that has been said so bear with me if I ask things that were answered previously.

I've just spent ages finally putting an Excel spreadsheet together to record my buys, wins and auto investments. I've numbered each block of bonds and when I've won I've highlighted which block won. I was beginning to think the reinvested bonds would never win, until some earned in Feb 2024 won in Aug 2024. Yay! 😁 

I started 2024 with £4475 and now have £5100 thanks to my wins.

Jan £0
Feb £500
Mar £0
Apr £25
May £0
Jun £0
Jul £0
Aug £50
Sep £50

What I'd like to do is calculate (easily! i.e. enter a formula and pull down for other cells) how my wins over time compared over time to savings, i.e. as a percentage.

I tried to rehash some calculations found on page 987 or thereabouts, but it got more complicated trying to incorporate the reinvestments and calculating month by month. My maths head has well and truly left the building for now (brain fog). 😫

Please can anyone help me? 🙏 

If you've read this far, thank you! 

Because your winnings are being reinvested in premium bonds (I'm assuming) then something like this should work if you are looking for the annualised return from the start of the year to the end of each respective month (which is what you would use to compare with a savings account interest rate over that period). 

I've highlighted the only difficult formula for cell F7 (see the formula bar at the top) which can be copied up/down that column. 

Note the annualised return is quite high at 88.8% for month 2 because annualising assumes your good luck over 2 months is repeated for the following 10 months. After 9 months you've increased your capital by 14%, and 14% over 9 months is equivalent to 19% per annum.

Thanks SnowMan. This is incredibly helpful. 😊

As my spreadsheet goes back to 2005 and not wanting to bother with inserting a load of Month 0s, I've used the value for the previous year end (Month 12).

I also realised it wouldn't work for the year where I bought a block rather than auto reinvested. I was going to faff around and take from different columns but as I'm unlikely to be able to afford to buy more bonds it's not worth the bother. Going back further also doesn't make sense as the 2 x £100 I bought in 2005 and 2011 haven't won anything since 2016 and 2014 & 2019 respectively. I thought I was very lucky to win £75 from £200 of bonds, but since buying a big block at the end of 2021 I've won £900. 😁

Thanks again! Much appreciated. 

Swipe

fredflower said:

One thing I noticed on my NS&I online account is that there's no mention of my earlier wins. The wins were received as a cheque rather than my later ones that I set to auto reinvest, but surely they should still be showing on the system..? 🤔

I've checked previous paperwork against what is currently on my account and the holder's numbers match though the hole in data and no more wins for those bonds does make me wonder...

The paper record sent in 2011 when I bought more bonds shows the holder's number, but not an NS&I number. Is it possible to have multiple NS&I numbers?

Thoughts please. 

You could write to them and ask for them to send you the records they have for you internally that the portal doesn't show.

SnowMan

fredflower said:

I also realised it wouldn't work for the year where I bought a block rather than auto reinvested.

You can use the XIRR function for more complicated scenarios where only some winnings are reinvested, and where you buy a new block of bonds, to calculate the average annualised return over the whole period

1. The purchase of bonds including the initial purchase should be treated as +ve cashflows in this function at the date of purchase.

2. Any winnings reinvested in bonds should be ignored completely as cashflows in this function, as these reinvestments form part of the return you are trying to calculate and so you don't want to consider them as cashflows

3. But any winnings paid out and not reinvested should be treated as -ve cashflows (as you are taking the money out of premium bonds) at the date they are paid out.

4. And the final cashflow is the value of bonds you hold now but treat this as negative (so a cashflow of minus 5,100 at say 1st September 2024).   

The XIRR function can be a bit volatile in some circumstances, because there isn't always a unique answer, but it should work OK in this scenario.

For example applying this to the last year gets the same result as before of about 19%, see cell I8 and the formula in the formula bar. The first date is 1st December 2023 because you have to hold bonds for a full month for them to qualify for prizes. The slight difference from before (19% vs 18.9%) is because before we were treating all months as having exactly the same number of days. Of course there are no cashflows of type 3 in this example because all winnings were reinvested. But where there are type 3 cashflows the previous method doesn't work.

molerat

Swipe said:

fredflower said:

One thing I noticed on my NS&I online account is that there's no mention of my earlier wins. The wins were received as a cheque rather than my later ones that I set to auto reinvest, but surely they should still be showing on the system..? 🤔

I've checked previous paperwork against what is currently on my account and the holder's numbers match though the hole in data and no more wins for those bonds does make me wonder...

The paper record sent in 2011 when I bought more bonds shows the holder's number, but not an NS&I number. Is it possible to have multiple NS&I numbers?

Thoughts please. 

You could write to them and ask for them to send you the records they have for you internally that the portal doesn't show.

What does your on line bond holding show ?  All bonds held against your account should be showing and if not then something is amiss.

On my account the prize history only goes back to 2020.

george4064

£300 on a £25k holding. Pretty sure I won above average this month!