Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel Premium Bonds spreadsheet
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? -- sparker3000 ------------------------------------------------------------------------ sparker3000's Profile: http://www.excelforum.com/member.php...o&userid=27572 View this thread: http://www.excelforum.com/showthread...hreadid=470916 |
#2
|
|||
|
|||
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(D1:D12) 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 sparker3000 wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Excel 2K3: Opening spreadsheet, also opens book1.xls? | Setting up and Configuration of Excel | |||
Is there a way to insert a formula, password or macro in an excel spreadsheet that will automatically delete the spreadsheet? | Excel Discussion (Misc queries) | |||
Copying an Excel spreadsheet to a Word document | Excel Discussion (Misc queries) |