Excel template to track and allocate to carry forward pension allowances

intalex

Hi, does anyone have or know of a freely available Excel file designed to easily track carry forward pension allowances? Trying to build one myself but a bit facing a bit of a brain freeze on how to use MIN/MAX/other functions to program the automatic allocation of contributions across multiple prior years, so am currently allocating and tracking residual carry forward allowances manually. Hoping someone has made one with complete automation....

artyboy

I've never considered myself a maths genius, but on the face of it, this doesn't sound like a complex calculation - assuming you know what your PIA was for prior years then it's just a case of adding up the residue below £60/40k (or possibly lower if you were subject to tapering) and then using that number. Possibly with a focus on ensuring you at least use the amount that relates to the oldest eligible year.

I've tracked this in Excel for me/Mrs Arty, but it's very light on formulae!

sofm

Not quite what you asked for, but this calculator might help: https://www.gov.uk/guidance/check-if-you-have-unused-annual-allowances-on-your-pension-savings

sausage_time

intalex said:

Hi, does anyone have or know of a freely available Excel file designed to easily track carry forward pension allowances? Trying to build one myself but a bit facing a bit of a brain freeze on how to use MIN/MAX/other functions to program the automatic allocation of contributions across multiple prior years, so am currently allocating and tracking residual carry forward allowances manually. Hoping someone has made one with complete automation....

Verbally, what are you trying to calculate using MIN/MAX etc?

intalex

Just something that allows you to key in contributions by tax year, and returns a value for the total allowance (current year + carry forward) available in the current tax year.

Prefer it to be in Excel with as simple formulae as possible (MIN/MAX combos is fine).

I've managed to evolve my file further since this morning and is a bit better formularised now, but my gut says it can be even better, I just can't visualise it as yet let alone implement it...

sausage_time

I'm not sure where MAX/MIN functions come into this.  Is it not just sum of (Annual Allowance - Contributions Made) for each of the years in question?  So this year plus the three previous years.  Annual Allowance was £40k until 2023/24 when it increased to £60k.

If you're a high earner then the tapered annual allowance might apply to you, and may impact how much you can carry forward.  But I still don't see the need for anything more than basic arithmetic.

Sarahspangles

I think if I was doing this at scale as some kind of advisor it would justify the effort but for an individual the sums are relatively simple. A negative number in ‘AA available’ after summing the PIA/contributions tells you to deduct that amount as ‘Carry forward used’ from three years prior and add it to the current year to arrive at ‘Adjusted AA available’ of zero. You never have more than four columns ‘live’.

intalex

Fair enough, I'll just keep using what I have for now...