Excel calc for working our interest on a regular saver account.

smiffy

Hi, Can someone help me create a spread sheet that will work out the interest on my kids savings accounts.

The rate for the 1st year is 5% AER.

Each month we invest £300. At the end of the year we can then remove the £3600 +plus the interest.

I'm trying to work out the interest each month.

thanks

YorkshireBoy

smiffy wrote: »

I'm trying to work out the interest each month.

May I ask why?

smiffy

Because presumably the interest is compounded, so that month two will earn interest on the first £300, the first months interest, and the 2nd £300. and so on. *one I have a working formula, I can apply the same logic to my ISA and/or other savings accounts.) Thanks

YorkshireBoy

smiffy wrote: »

Because presumably the interest is compounded, so that month two will earn interest on the first £300, the first months interest, and the 2nd £300. and so on.

Not necessarily, and if it did then you'd need to use the gross p.a. rate (which would be a little less than your AER figure given...around 4.89%).

On an annual paying interest account you'll accrue interest each day at the rate of...

Closing balance x 5% / 365

365 days of this daily figure will then be paid (gross if they're non-tax payers) at the end of the year.

Which account is it by the way?

As a rule of thumb, interest paid on a regular saver is calculated as...

(12 x £300) x 5% / 12 x 6.5

However, if you're depositing on an irregular basis, ie different days each month due to SOs delayed at weekends etc or you're depositing different amounts each month, then you'll need to calculate interest on a daily basis. This will require 365 date rows on your spreadsheet.

.......A.............B..................C.............D.................E
1 - Date.......Open bal.........credit......close bal........Interest
2 - 8/10/09........0.................300........=B2+C2......=D2*5%/365
3 - 9/10/09......=D2.................0..........=A2+B2......=D2*5%/365

(copy row 3 down to row 366, and then add your monthly credits in column C)

367 - 8/10/10....................................................=sum(E2:E366)

Cell E367 is your gross annual interest payable.

smiffy

"Which account is it by the way?"

Its the Halifax Regular Saver Account.

Annual Rates
6.00% AER
6.00% Gross PA
4.80% Net PA

Also I don't follow the 12 x 6.5 bit.

YorkshireBoy

smiffy wrote: »

Its the Halifax Regular Saver Account.

Annual Rates
6.00% AER
6.00% Gross PA
4.80% Net PA

It was 5% AER in your OP!

Also I don't follow the 12 x 6.5 bit.

Your 1st payment is in the account 12/12ths of the year, the 2nd, 11/12ths...and the last for only 1/12th of the year.

12/12 + 11/12 +............2/12 + 1/12 = 78/12 = 6.5

Do you follow now?

The clue (for you) that interest is paid annually (and therefore accrues in the background rather than being paid and compounded monthly) is that the AER and gross p.a. figures are the same at 6% (or is it 5% ) in your case.

opinions4u

Its the Halifax Regular Saver Account.

Because presumably the interest is compounded, so that month two will earn interest on the first £300, the first months interest

Interest is accrued daily and doesn't compound monthly.

The Kids Regular Saver account (max £100 pm) pays 6% gross.

The Regular Saver account (max £500 pm) pays 5% gross assuming the first funding takes place by 8th October.

oldwiring

Is interest actually applied to the account each month to give the stated AER?
If it is and you ignore months do not have the same lengths then for a 5% AER the monthly interest can be calculated as (1+A1/100)^(1/12)-1* last balance. Then for the next month you will use the last balance plus latest interest as your last balance.

To calculate to the exact number of days between interest payments multiply the result of the formula by 12 and divide by 365 to give a daily rate. Multiply that by the number days, then apply the result to the last balance to give that month's interest.

PM also on way

rb10

oldwiring wrote: »

Is interest actually applied to the account each month to give the stated AER?

No. All the interest is paid at the end of the year. This is why the calculation of [monthly payment]*[interest rate]*6.5 works, where the monthly payment is the same each month.

smiffy

Thanks - I get the 6.5 bit now. And it is 6% AER.

So the interest over the year is £117.

12 x 300 = £3600 x 6% = £216 /12 = £18 x 6.5 = £117

Thanks all who have contributed on a Sunday afternoon

Baldur

smiffy wrote: »

Thanks - I get the 6.5 bit now. And it is 6% AER.

So the interest over the year is £117.

12 x 300 = £3600 x 6% = £216 /12 = £18 x 6.5 = £117

Thanks all who have contributed on a Sunday afternoon

The Regular Savings Calculator may have eased your struggles with Excel - you just enter monthly payment, period of saving and AER (selecting the appropriate tax band).