Excel - Regular Savings formula

edinburgher

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

I wasn't answering that question, I was explaining why FV wasn't behaving for you.

masonic

Krash420 wrote: »

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*31*((1.06) ^(1/365)-1),
<snip>

You are correct that it is calculated daily, but it is not compounded daily. You can tell because the gross rate = AER, which would not be the case unless there was no compounding taking place during the year. Interest is normally paid and compounded in the same interval and this account is no exception.

The correct daily rate is therefore 0.06/365 not (1.06^(1/365))-1. This is why the PV function is less accurate for calculating interest accrued within the 12 month period.

anoncol

Gram_Parsons wrote: »

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.

Because some people actually find maths fun?

fairleads

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).

Yes, That is what CI is all about.
The FV function replicates the IR calculations of a normal savings account.
As for the IR itself, its up to the individual to interpret and input the correct IR.

masonic

fairleads wrote: »

Yes, That is what CI is all about.
The FV function replicates the IR calculations of a normal savings account.
As for the IR itself, its up to the individual to interpret and input the correct IR.

My point is that the FV function is not designed to calculate accrued interest between payments, so it is unsuitable for the task under discussion. It cannot accurately determine how much interest has been accrued after month 2 of a 12 month regular saver when interest is paid and compounded annually, which was the OP's question.

AlwaysLearnin

The 6% isn't actually applicable until maturity either:

"no partial withdrawals. If the account is closed before the end of the fixed period, interest will be paid at the standard Savings Account variable rate"

therefore, strictly speaking, 6% shouldn't really be used for accrual pre maturity...

masonic

AlwaysLearnin wrote: »

therefore, strictly speaking, 6% shouldn't really be used for accrual pre maturity...

The same thought had crossed my mind, but compare it with the index linked saving certificates calculator - this includes index linking that hasn't been paid and wouldn't be paid if you were to withdraw prior to the next anniversary. I must admit, even I log in now and then to see how much mine are worth even though it doesn't mean anything intra-year.

fairleads

masonic wrote: »

My point is that the FV function is not designed to calculate accrued interest between payments, so it is unsuitable for the task under discussion. It cannot accurately determine how much interest has been accrued after month 2 of a 12 month regular saver when interest is paid and compounded annually, which was the OP's question.

But if interest is paid and compounded annually then there is no need to use a formula more complicated than capital x r = interest
But if i is accrued and compounded monthly then FV can still be used even to determine the interest at month 2.
But it might not be more efficient than constructing a spreadsheet using basic running formula.

masonic

fairleads wrote: »

But if interest is paid and compounded annually then there is no need to use a formula more complicated than capital x r = interest

There is, because capital is drip fed into the account over the 12 months. In fact, a significant number of people make the mistake of doing a calculation like £3,600 x 6% to arrive at interest of £216 after month 12 (and £600 x (2 x 6%/12) = £6 after month 2), when the actual calculation they should be using is n*(n+1)/2*c*r/12, where n is the number of months, c is the monthly contribution and r is the gross rate of interest. This gives the correct answer of £117 after 12 months (and £4.50 after 2 months).

If I'm wrong and you know of a simple formula in the format capital x r = interest, that will work out the interest accrued during the term of this regular saver (i.e. after n months and at maturity), perhaps you could share it with us?