Excel - Regular Savings formula

Astraeus

Some really clued-up Excel experts on here.

May I ask, did any of your teach yourself? I'm hoping, through challenging myself, to begin to develop an appreciation of Excel's utility.

B_G_B

Astraeus wrote: »

Some really clued-up Excel experts on here.

May I ask, did any of your teach yourself? I'm hoping, through challenging myself, to begin to develop an appreciation of Excel's utility.

I’ve learnt a few bits as I’ve needed them. I find the youtube tutorials useful. Also just punching a specific question into a search engine usually brings up the answer. There is also “The Techie Stuff Board” on this forum for anything that you can’t find elsewhere.

The one thing that I have come to appreciate with Excel is that it does a lot more than I will ever use.

edinburgher

Astraeus wrote: »

Some really clued-up Excel experts on here.

May I ask, did any of your teach yourself? I'm hoping, through challenging myself, to begin to develop an appreciation of Excel's utility.

I'm completely self taught, although I'm limited to formulae and functions, fancy formatting and some basic VBA. As a starter, reasonable head for maths helps you to error check your own work :coffee:

IronWolf

Unless I am missing something then the formulas in here are wrong. The monthly interest is not 0.06/12 it is 1.06^(1/12)-1.

If you are depositing £300 per month then the final balance after a year is

300*1.06^(12/12) + 300*1.06^(11/12) + 300*1.06^(10/12) +.....+ 300*1.06^(1/12).

DominicH

IronWolf wrote: »

Unless I am missing something then the formulas in here are wrong. The monthly interest is not 0.06/12 it is 1.06^(1/12)-1.

If you are depositing £300 per month then the final balance after a year is

300*1.06^(12/12) + 300*1.06^(11/12) + 300*1.06^(10/12) +.....+ 300*1.06^(1/12).

That would be if it were compounded monthly, but there's no compounding in this scheme, it's just 6% annualised, calculated at the end of the year.

fairleads

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.

300 per month at 6% p/a IR = 3719.17 - 3600 = 119.17 interest
Don't complicate matters Guys, let the Excel FV work for you.
It does for me.

masonic

fairleads wrote: »

300 per month at 6% p/a IR = 3719.17 - 3600 = 119.17 interest
Don't complicate matters Guys, let the Excel FV work for you.
It does for me.

Doesn't FV assume interest is paid and compounded in each period? If you set the initial value to £1000 and monthly payment to £0, you end up with £1061.68, which is more than 6% interest. This looks like it is simulating 6% gross paid monthly (6.168% AER).

edinburgher

masonic wrote: »

Doesn't FV assume interest is paid and compounded in each period? If you set the initial value to £1000 and monthly payment to £0, you end up with £1061.68, which is more than 6% interest. This looks like it is simulating 6% gross paid monthly (6.168% AER).

I know what you're doing there. You have used a value of 12 for nper and have used 6%/12 for your interest rate. You should be using a value of 1 for nper and 6% for your interest rate, as it's a single period (1 year) paid at 6%. If you do this, you'll get the expected £1060.

masonic

edinburgher wrote: »

I know what you're doing there. You have used a value of 12 for nper and have used 6%/12 for your interest rate. You should be using a value of 1 for nper and 6% for your interest rate, as it's a single period (1 year) paid at 6%. If you do this, you'll get the expected £1060.

But the OP wants to see capital plus interest accrued each month over the 12 months.

Edit: I suppose it could be fudged by entering (1.06^(1/12))-1 for the interest rate, but the n*(n+1)*0.06*£300/(2*12) formula suggested by guymo would be a more accurate representation.

Krash420

Just to give my two cents... 6% is the ANNUAL Effective rate of Interest. As said above if you put in £1000 into the account today, next year it'll go up to £1060.

If you're using calculating interest monthly, then you should not be using an annual interest rate. Instead convert the annual effective rate into a 'nominal interest rate convertible m times a year':

i(m) = m*((1+i)^(1/m) -1)

Slot in m=12 for monthly, and i(m) is your nominal interest rate convertible monthly. It is converted into monthly by dividing by m, in this case 12.

So in the future value calculation, if you're earning interest monthly, and you put in 1000, then the FV would be:

=FV(((1.06^(1/12))-1),12,0,-1000)

Note how I've entered the MONTHLY rate (ie i(m)/12)

This gives you the exact same answer as =FV(6%,1,0,-1000) I.e. 1060

Also I should note that the FD Account does compound interest, and that interest is actually calculated daily. So if you know the days that you make the deposit you can calculate how much interest you have accrued upto that deposit (use m=365 for your daily interest rate)

So if you make first deposit on March 1st and then second on April 1st, then prior to your second deposit, the value of your savings will be: X1 = 300*(1+31*((1.06) ^(1/365)-1)),

Between April and May you have 30days of interest accrued and so you'd adjust that formula to X2= (X1 + 300) * (1+30*((1.06)^(1/365-1))
To calculate how much you have accrued up until 1St may, before you make your deposit.

Just be warned, that if your deposit goes on the 1st normally, there may be times when it instead goes on the 2nd or 3rd, meaning you will lose 1 or 2days of interest for that deposit. This usually happens when the 1st falls on a weekend, the deposit is moved till the following working day, I believe.


...Just my two cents...