Help with excel savings spreadsheet

bigfreddiel

noggin1980 wrote: »

Which is an interest rate of around 3% totally worth worrying about, if you are going to be rude at least be right first.

Absolutely correct, my mistake, I do apologise profusely for accusing anyone being ignorant with simple arithmetic.

Apologies once again fj

Newme2014

The interest is calculated daily but added at the end of the month, I realised what I was originally doing was going to add it as cumulative which was totally wrong so what I've now done is (short extract)
29th £3982.65 =33p
30th £3982.65 =33p
1st £4325.65 =35p
Total £1.01
would that be right or am I still doing it wrong?

talexuser

Start a thread politely addressed to Snowman to see if he still has his excellent spreadsheet available? It cleverly calculates daily interest. You just input the rates, rate change dates, balance, money in and out amounts and dates and it does everything for you. I have used it for years with many savings accounts, and the interest amounts rarely differ from the actual by more than pence a year (depending on when varying organisations define their day/next day changeover times, or count the first or last day in their internal calculations I guess).

Eco_Miser

Newme2014 wrote: »

The interest is calculated daily but added at the end of the month, I realised what I was originally doing was going to add it as cumulative which was totally wrong so what I've now done is (short extract)
29th £3982.65 =33p
30th £3982.65 =33p
1st £4325.65 =35p
Total £1.01
would that be right or am I still doing it wrong?

Where do you get the 33p from? The total of £1.01 is correct for the three days shown, but should be around £9.90 for a month (assuming 33p a day)
Why has the balance gone up by £343 on the 1st. What is needed to discuss this, and on the spreadsheet, is formulae, not numbers.
See my previous post #11.

Newme2014

33p is 0.0082% of the total for that day, it went up by £343 as that is what I transferred in that day, that is just a section of the month, hence the total of only £1.01 for those days. I didn't fancy typing out all the transfers in for the whole month.

polymaff

Newme2014 wrote: »

Hi, not sure if this is possible or worth the hassle but I'm trying to do a spreadsheet that shows starting balance then daily incomings and outgoings with the daily interest added.
I've got column A with dates, column B with income, C with outgoings and then D would show the running total. I've tried things like =2000+0.0082% but it doesn't do anything, I'm not very good with this stuff though so any help appreciated, even if it's to say I'm totally wasting my time and it's a pointless idea

Thanks

You're going to have one hell of a long spreadsheet if you ask it to compute daily. If you just want to know how much gross interest you've earned between the last transaction and the one you have just entered - say at spreadsheet row 200 - then the formula is

= (A200-A199) * D199 * the annual gross rate / 365

or, during years where the bank considers the year - not necessarily the calendar year, BTW - to be 366 days long, 366 instead of 365.

You might like to add two columns, say E and F, to hold the interest rate and 365 or 366 respectively, when the formula becomes:

= (A200-A199) * D199 * E199 / F199

That way you can also record "events" like a change of interest rate or a change of the days-per-year, or the compounding of interest, just like you record your transactions. Make sure that column E is cell-formatted as a percentage, then you can actually put in meaningful numbers such as 2.98% as 2.98

JohnRo

I use oocalc, so this is the formula I'd use in there.

6 columns, A,B,C,D,E,F
date - in - out - balance - interest earned - net interest rate

assume headings are on line 1 and the latest date/entry is on line 2 with older dates descending chronologically on the lines below it, to calculate the daily compounded interest earned to date for each line enter the following in E2 and drag it down as far as needed

D3*((1+(F3/365.25)%)^DAYS($A2;$A3)-1)

excel uses commas instead of semi colons I believe.

Eco_Miser

Newme2014 wrote: »

33p is 0.0082% of the total for that day, it went up by £343 as that is what I transferred in that day, that is just a section of the month, hence the total of only £1.01 for those days. I didn't fancy typing out all the transfers in for the whole month.

Right, but how was I, or any of us, supposed to know that?
For not typing the whole lot out, just use ellipses (...) to indicate missing days, and show the formulae you used, not the result.

Anyway you've now had more suggestions, you should be able to construct your spreadsheet now.

Ballard

polymaff wrote: »

or, during years where the bank considers the year - not necessarily the calendar year, BTW - to be 366 days long, 366 instead of 365

Sterling is generally based on a 365 day year regardless of the actual number of days. There are exceptions but this is the rule. For many other currencies (USD & EUR to name two) it's 360.

Ballard

I have had to calculate interest for accounts in the line of my work on occasion and would hapilly knock up a spreadsheet and email it to you if you like. PM me if this would help.