Calculating daily interest accrued above a threshold

Hi there

I'm setting up a wages sheet for hourly paid casual staff, and I need to be able to calculate the NI (national Insurance) payments accrued by these casual staff. The staff have variable rates of pay and have variable hours across the week.

In this example NI is paid on any earnings above £110, at 12.8%, so will tend to kick in on different days for different staff as they build up their hours

I can find the total accrued for each staff member over the course of the week, and the total overall for all staff, without any problem. I have also found a (rather clunky) way of calculating the daily amount accrued each day by an individual member of staff as the week progresses.

Where i am running into a brick wall is to find a neat way of finding the total of NI accrued each day as the week progresses. I'm thinking there must be some sort of financial function which could help.

My only way of thinking is to do it for each individual and add up the values - but i don't really want extra rows and calculations in the worksheet if there is an easier way. If there isn't an easier way then the simplest way to calculate the daily amount accrued by an individual would help.

I should think this is something that is done for any number of businesses employing hourly staff so there must be a way, mustn't there?

wage sheet.jpg

Many thanks

Nick