Excel - Calculating Daily Compound Interest

sloughflint

[FONT=&quot]

PBR wrote: »

I have basically ‘lent’ some money to a project over the period of 13 mths (the amounts/dates I gave were just the 5 instalments). It was agreed that when the capital was paid back to me (next week), I would also get whatever I would have earned in interest had I deposited it in an account with Egg.

Rereading your post, I could have simplified the spreadsheet I think if the figures you gave were amounts lent ie a total capital of £22102.88 rather than account balances but never mind it's done now and does the job( can't be bothered to amend set-up).

[/FONT] 05/07/2007 600 13 0.055 1.001909 601.1453 601.1453
18/07/2007 600 37 0.0575 1.005683 601.1453 604.5618
24/08/2007 11102.88 105 0.0575 1.016213 11107.44 11287.53
28/08/2007 16102.88 155 0.0575 1.024026 16287.53 16678.85
07/12/2007 16102.88 54 0.055 1.007953 16678.85 16811.49
30/01/2008 18102.88 12 0.055 1.001762 18811.49 18844.63
11/02/2008 18102.88 16 0.05 1.002141 18844.63 18884.97
27/02/2008 22102.88 44 0.05 1.005899 22884.97 23019.97
11/04/2008 22102.88 160 0.0475 1.020551 23019.97 23493.05
18/09/2008

0.8*(23493.05-22102.88)= £ 1112.14.

[STRIKE]Hope that's right.[/STRIKE]It's not. I mistakenly put 18th September ( Lokolo's payment date) rather than your 18th January. In any event, the last date should be date you get your money back ( see post below)

sloughflint

Lokolo wrote: »

Is there anyway you can get excel to do the number of the days? Formula wise?

That was the formula for number of days
C1=A2-A1

Lokolo wrote: »

Also last column should be E1*F1 not E1*B1

E1*B1 in first cell then E and F for the rest.

sloughflint

PBR wrote: »

I have basically ‘lent’ some money to a project over the period of 13 mths (the amounts/dates I gave were just the 5 instalments). It was agreed that when the capital was paid back to me (next week), I would also get whatever I would have earned in interest had I deposited it in an account with Egg.

Thinking about this then, the date interest is paid by Egg is irrelevant. You want the daily rate up to date of repayment.
Therefore the last entry in column A needs to be the date you get your money back; not 18th January.

judygarland

I hope no-one minds me piggy backing on this post, but I am trying to put together a spreadsheet to help me work out whether to cash in my endowment!

For the last few years I have been handing over £48 per month to receive £90 back in bonuses!

I have been given a surrender value and am trying to work out that if I invest it in an ISA say paying 5% interest annually and still save £48 every month and add this to the pot, what I would end up with in 7 years time (which is when it's due to mature).

I know that I would miss out on terminal bonuses but looking at the past few years' payout it's not worth it.

Any excel wizzo's out there that can help?

it would be much appreciated.

Many thanks

GlasWolf

judygarland wrote: »

I have been given a surrender value and am trying to work out that if I invest it in an ISA say paying 5% interest annually and still save £48 every month and add this to the pot, what I would end up with in 7 years time (which is when it's due to mature).

An approximation just for the part above, using interest calculated and compounded monthly:

 [FONT=Courier New]   Month        [/FONT][FONT=Courier New]   Int.      Total
[/FONT][FONT=Courier New]0  October 2008           £48.00
1  November 2008  £0.20   £96.20
2  December 2008  £0.40   £144.60
3  January 2009   £0.60   £193.20
4  February 2009  £0.81   £242.01
...
84 October 2015   £20.07  £4,883.84[/FONT]

Probably best if someone else checks this.

Of course, also bear in mind you can only pay £3600 per year into an ISA. 12 x £48=£576, meaning you could add £3024 of your endowment sum to it each tax year.

yeroash

With regards to "PBR"'s post I've uploaded an excel spreadsheet calculating daily interest etc - With regards to accuracy, I am human and there may be errors so please do not take it as is!!! Feel free to edit etc.

http://rapidshare.com/files/165525176/PBR_Savings_Account.zip

sloughflint

I'd like to have a look at this but am always dubious. Is the link safe?

I've considered in the past trying to find a safe way to show spreadsheets on here rather than lose all the formatting when copying. What's best? The above?

KingL

There's nothing wrong with Rapidshare per se, it depends whether you trust yeroash or not !!!

Googledocs might be a solution. Paul_Herring has put his formatted text doc on Fixed Rate ladders on there, or they have a shared spreadsheet feature which apparently exports to excel. Haven't used it myself, but it's here

yeroash

sloughflint wrote: »

I'd like to have a look at this but am always dubious. Is the link safe? ...

I was thinking of this exact thing while I was uploading it, best thing I can suggest is to scan the file after downloading. As far as I am aware it is 100% safe according to NOD32 Antivirus!!

Anyway if you guys do check it out, let me know if I've done anything wrong or if it can be improved - I use this spreadsheet to keep a track of my savings account.

EDIT: Just came across this site http://www.virustotal.com/ a free service which uses 36 different antivirus scanners on any file you upload. should put your mind at ease

Lokolo

sloughflint wrote: »

I'll have a look later yeroash and I'll check out google docs properly KingL. I've just very recently started creating a few documents that I can access from different machines on google so the next step would be to learn how to safely make them viewable.Far better than this pasting milarky.

You can in google docs. Example of spreadsheet. Open it up, top right is Share and somewhere you can set to anyone can read it or edit it or whatever and it'll give you a link to use.