Calculating/predicting accrued interest for a tax year

boingy

dosh37 said:

boingy said:

Does anyone know of an interest calculator/predictor that can take account of changing interest rates and account balances throughout the year? I guess it's more likely to be a spreadsheet-style thing than anything else.

Like many others I'm starting to look at how much interest I am likely to accrue by the end of the tax year. Obviously you can't predict future interest rates but it would be really useful to see a prediction based on the story so far. I've got a half-a$$ed spreadsheet on the go but it's getting pretty messy, and could easily contain errors where my assumptions are wrong.

I'm curious to know why you think you need such information.

Once you have maxed out your annual ISA limit, and you cross the savings tax threshold, there is little you can do about paying savings tax, so I can't see much point in trying to predict interest figures in advance unless you are a couple and can open accounts under different names or you invest elsewhere such as the stock market.

I maintain an Excel spreadsheet, but it's sole purpose is to keep track of the interest from many different savings accounts for filling in the HMRC Self Assessment form in April. I don't bother trying to predict interest income in advance.

The £85K FSCS limit and frequent rate interest changes mean I have several savings accounts. I open and close accounts many times when chasing the best interest rates.

Many online savings organisations do not provide Interest and Tax Statements (especially for closed accounts) so the spreadsheet is about the only way I have to keep track.

Because I have not yet used any ISA allowance for this year and, for this tax year only, I'm in a transitionary period where I can earn 6K interest without paying any tax but I also need to get 20K into an ISA before next tax year. In cash-land non-ISAs pay more than ISAs so I'm aiming to time my ISA entry to suit. I have not yet decided whether that ISA will be a fixed one. It depends on how interest rates are going as we approach winter/Christmas. Next year's ISA 20K will be an S&S one so I'll probably dive into that as soon as I can after April, unless the markets are looking crazy high, which I seriously doubt. I'm currently on track to pay absolutely no tax on income or savings for this tax year. I might even manage it next year too, but it's not so certain due to the squeeze on CGT and dividend allowances.

Next autumn-ish we'll likely have a new govt and I've got one eye on what that might mean. In the next few years I may well be shifting funds to my wife and possibly even jumping back into Premium Bonds to minimise tax but that depends on a number of things that are not yet known. Interesting times though. I'm enjoying it!

Newbie_John

Some of the accounts like Chip donut for you, you can see how much you've earned so far, and how much is due.. some others like Nationwide only show you this figure when you about to close the account. 

Overpaying mortgage also allows you to save the tax on interest rate.

But if you earn £20k and pay 20% tax than best paying saving account of 6% will still give you more than 4.8% return.

The only situation that I would carefully keep track on my interests earned over the year is if I was earning £49.9k a year 😅

[Deleted User]

Hi,

EthicsGradient said:

When you're within the PSA, you can get better interest in a normal account than in an ISA. But if you're going to exceed the PSA in a tax year, it could be worth putting some in an ISA. So a tool to predict the end-of-year outcome can be useful.

here's the MSE cash ISA guide and how to compare rates.

SnowMan

In case it is of interest this is a screenprint of my excel interest calculator. The yellow cells are the inputs

I created it and use a similar version to check interest payments on my accounts.

In the made up example it estimates the gross interest payment on 2nd July from an initial deposit of £5,000 on 15th April with a withdrawal of £3,000 on 1st June and with an interest rate increase from 3.6% AER to 4 AER on 3rd June.

The actual interest paid (say £29.61 here) differs slightly from the estimated payment of £29.51 because institutions will use slightly different calculation methods (usually based on daily interest apportioning)

The spreadsheet also estimates the accrued but unpaid gross interest (up to the last date of 10th July here) since the last actual interest payment of £3.16 in this case. That is in this example after a further deposit of £2,000 on 4th July and interest rate increase on the same day to 4.2% AER.

Exodi

EthicsGradient said:

D3 =(1+B2)^((A3-A2)/365)*E2

For the record, this formula is completely fine to use so please don't take think I'm in any way trying to correct it. But for reference, Excel has a built-in function for calculating this called 'FV' (Future Value) which does the same thing without any of the Mathematical know-how, which you can use as follows:

=FV(RATE,NPER,PMT,PV,TYPE).

As an example, if you put £100 into an account every month paying 5% interest for a year:

RATE = (0.05/12)... this is the rate per period
NPER = 12... this is the number of periods (so 12 would be one year)
PMT = -100... this is how much you're contributing per period (this is expressed as a negative)
PV = 0... this is the present value or how much you're starting with (this is expressed as a negative)
TYPE = 1... this can be either 1 (meaning you're contributing at the start of the period) or 0 (the end).

So the formula would be =FV(0.05/12,12,-100,0,1) which would return the result £1,233.00 ... though in reality you'd have it linking to various cells where you enter the rate, value, etc. The period doesn't have to be round numbers like a month, it could be two dates subtracted from each other etc.

rabbituk

Exodi said:

EthicsGradient said:

D3 =(1+B2)^((A3-A2)/365)*E2

RATE = (0.05/12)... this is the rate per period

Is the RATE value in this alternative way to calculate correct? I would have thought that the calculation for the monthly rate would be (assuming all months are equally long which they are not)

RATE = ((0.05 + 1)^(1/12)) - 1

since that would then take into consideration the interest compounding and is what EthicsGradient is doing. Merely dividing the annual interest rate by the number of months will give a higher monthly figure than it really is.