Excel - Regular Savings formula

Astraeus

Folks,

I have an Excel query.

I have searched the fora and found several formulae which almost fit the bill, but not quite, as they relate to compound interest on an initial deposit without scope for further deposits as in a regular savings account.

My spreadsheet is missing this one ingredient to reflect the accumulation of funds in my First Direct Regular Saver. I deposit £300 per month at a rate of 6% (for these purposes I am not deducting tax as that is done at the end of the calculation). I also appreciate the interest is payable annually however this will balance out over a 12 month period.

The formula I am using, where x is the cumulative balance, y is the monthly deposit (this month is y1, next month y2 etc.) and z is the coupon, is:

For month one, x1=y1+(y1*0.06/12)
For successive months, x2=y2+y1+(x1*0.06/12), x3=y3+y2+y1+(x1*0.06/12) and so on.

It yields, on a monthly deposit of £300, a return of £1.50 per month each month, such sum not increasing in subsequent months. I am therefore convinced I am doing something wrong as £1,500 should yield more than £1.50 per month.

Can any Excel whizz assist with this please? At this stage of the day, having worked on this spreadsheet for seven hours, my mind is a bit tired and I am sure I am missing something obvious.

Many, many thanks.

masonic

Astraeus wrote: »

For month one, x1=y1+(y1*0.06/12)
For successive months, x2=y2+y1+(x1*0.06/12), x3=y3+y2+y1+(x1*0.06/12) and so on.

x3=y3+y2+y1+(x2*0.06/12)

Edit: you do realise that the amount of interest each month also depends on the number of days in the month. I assume you are just trying to approximate it?

Gram_Parsons

Not sure why you'd want to waste all Sunday on that? A basic rate tax payer will get £93 back at the end (£117 gross) if you put in the max £300/month - it tells you that on the FD site.

Astraeus

masonic wrote: »

x3=y3+y2+y1+(x2*0.06/12)

Edit: you do realise that the amount of interest each month also depends on the number of days in the month. I assume you are just trying to approximate it?

That's the one. Perfect, thank you.

Yes, only seeking an approximation for these purposes to give an indicative savings balance at the end of the year. I'll be constantly refining it so hope to include an exact interest calculation in due course.

The hope is, within a few weeks, to add a 'stress test' formula predicting the impact of an interest rate spike combined with possible deflation including/limited to house price deflation. One step at a time!

Thanks for your help.

masonic

Astraeus wrote: »

I'll be constantly refining it so hope to include an exact interest calculation in due course.

One thing you'll want to do is refine it so that interest earned (but not paid) each month is not compounded in the next interest calculation, so have something in the form...

x3 = y3+x2+(y2+y1)*0.06/12

i.e. this months contribution + last months balance (including unpaid interest) + this months interest (excluding interest on unpaid interest)

guymo

masonic wrote: »

One thing you'll want to do is refine it so that interest earned (but not paid) each month is not compounded in the next interest calculation, so have something in the form...

x3 = y3+x2+(y2+y1)*0.06/12

i.e. this months contribution + last months balance (including unpaid interest) + this months interest (excluding interest on unpaid interest)

In fact with this account there is no compounding at all. The interest earned in a given month is just 0.06/12 * the total of contributions to the account so far.

Assuming regular contributions of £c per month, the interest earned after n months is given by

n * (n+1) * 0.06 * c / (2 * 12)

because 1 + 2 + ... + n = n * (n+1)/2.
Plug in n = 12 and c = 300 and you get the £117 figure quoted on the FD web site

Astraeus

guymo wrote: »

In fact with this account there is no compounding at all. The interest earned in a given month is just 0.06/12 * the total of contributions to the account so far.

Assuming regular contributions of £c per month, the interest earned after n months is given by

n * (n+1) * 0.06 * c / (2 * 12)

because 1 + 2 + ... + n = n * (n+1)/2.
Plug in n = 12 and c = 300 and you get the £117 figure quoted on the FD web site

This is the foggy head of hours in Excel meaning that I am so wrapped up in formulae that the reality of the account becomes lost.

Thanks to you and masonic.

Ryan_Futuristics

That formula's very useful

But remember you can always do a running total like this:

x2 = x1 + y2

i.e. This month's cumulative balance is last month's plus the contribution

Which might make it easier to modulate monthly contributions in your spreadsheet

And adding compound interest (and double check this cos I'm away from Excel and overtired) should be something like

x2 = ( x1 + ( x1 * ( 0.06 / 12 ) ) ) + y2

So that's figuring you're earning interest on your current holding and have just paid your monthly contribution in

There'd be a neat way to simplify that equation too, but at least that methods means you could change 0.06 into a variable and modulate the interest rate

fairleads

Astraeus wrote: »

Folks,

I have an Excel query.

I have searched the fora and found several formulae which almost fit the bill, but not quite, as they relate to compound interest on an initial deposit without scope for further deposits as in a regular savings account.

My spreadsheet is missing this one ingredient to reflect the accumulation of funds in my First Direct Regular Saver. I deposit £300 per month at a rate of 6% (for these purposes I am not deducting tax as that is done at the end of the calculation). I also appreciate the interest is payable annually however this will balance out over a 12 month period.

The formula I am using, where x is the cumulative balance, y is the monthly deposit (this month is y1, next month y2 etc.) and z is the coupon, is:

For month one, x1=y1+(y1*0.06/12)
For successive months, x2=y2+y1+(x1*0.06/12), x3=y3+y2+y1+(x1*0.06/12) and so on.

It yields, on a monthly deposit of £300, a return of £1.50 per month each month, such sum not increasing in subsequent months. I am therefore convinced I am doing something wrong as £1,500 should yield more than £1.50 per month.

Can any Excel whizz assist with this please? At this stage of the day, having worked on this spreadsheet for seven hours, my mind is a bit tired and I am sure I am missing something obvious.

Many, many thanks.

Try the Future Value function in Excel, Formula, Financial, FV
Just remember to input the monthly interest rate and a minus value for the investment.

colsten

fairleads wrote: »

Try the Future Value function in Excel, Formula, Financial, FV
Just remember to input the monthly interest rate and a minus value for the investment.

FV does compounding which none of the regular savings accounts does AFAIK.

I don't know of an Excel function that doesn't compound.

LXdaddy

The fist contribution earns 12 months' interest, the second 11, the third 10 etc until the tweflth contribution earns 1 month's interest. Assuming the contributions are equally spaced.

So on average contributions are in the account for 6.5 months (n+1)/2
So the average balance in the account is the monthly contribution c * (n+1)/2
Multiply that by the anual interest rate and you get the gross interest for the year.

If the monthly contribution is £300 we have 300 * (12+1) / 2 *6% = £117

If you want to be more accurate and take account of the daily calculation and the possibility of different contribution amounts then you need two series of numbers Ci and Di where Ci is the Contribution in month i and Di is the number of days between the date of that contribution and the anniversary date. Then multiply each Ci by the corresponding Di and sum the products. Then multiply the sum by the interest rate divided by 365

For example if you contributed £300 on the 15th Jan 2015 and on the 15th of each subsequent month then the interest on 15th Jan 2016 would be £117.46