Excel Premium Bonds spreadsheet

[sparker3000]

I'm quite handy with spreadsheets but this one's got me stumped!

I want to create an spreadsheet that will calculate the interest earned
in a year through investment of UK premium bonds. Its not quite as
simple as it seems as I have made monthly payments and have not had the
full total for the full 12 months. See below:

Year 1

Jan
Feb
Mar
Apr
May deposited 1500
Jun
Jul deposited 2500 and won 50
Aug won 50
Sep deposited 6000 and won 50
Oct deposited 2000
Nov
Dec

TOTAL investment 12000
Total winnings 150

So as you can see I have invested 12000 but over the course of a full
year and have received back 150 but what is the interest rate? I need
to calculate the interest based on the number of months I've held each
deposit for.

Can anyone crack this?

 

[Roger Govier]

Hi

I am sure one of the financial gurus will give you a more accurate answer,
but a rough and ready solution might be as follows.
You do not give the dates of the investments. I assumed the first of each
month, and I assume this relates to 2004 since you say you have invested in
October.

With 1/1/04 in A1, and 1/2/04 in A2, mark both cells and fill down with the
fill handle to A13 and you should get dates running monthly to 01/01/05

In column B, enter the investments in the relevant months
In column C, enter in C1
=($A$13-A1)*B1
and copy down
in C13 enter
=SUM(C1:C12)
and in C14 enter
=C13/(A13-A5) A5 is used as this is the first month you invested (May)
This value is the AVERAGE amount you have had invested over that time period
= £7116.33

Taking your earnings, entered in column D for the appropriate months, and
with a formula in D13 = SUM(D112)

Then interest earned = D13/C13 and format as percentage. This gives a return
of 2.11%. Since this has been earned in a 245 day period (01/05/04 to
31/12/04) then on an annualised basis, this would be 3.14% i.e.
=D13/C13*(365/245)

This equates pretty closely to the stated 3% that is applied to the Premium
Bond fund.



Regards

Roger Govier