Selected Answer

Qurat.

The attached file solves your problem I believe. Open it and you'll see the first sheet (now Calcs) is yours (but with hidden rows H:BA for old quarters) and a working formula in the cells BB5:BQ9 (so and extra row 9 "New" and two columns to the right, BP:BQ).

I've made cells B7, BG7 and BQ7 green now (not red), since the formula calculates the correct values automatically.

If you change the date in new B9 to 01 May 2019 (say) then the premium pattern in that row will shift one cell right (and the single month payment for Q1 becomes 2 months - with the altered balance appearing after quarter 8).

Have a play with that row (change the value in E5 too, if you like) and it should work as you have requested. Note that rows 12:18 shows the percentages applied (to the gross premium) in the rows above (row 19 will change as you change cell B9). That area is FOR ILLUSTRATION... as it says and you can delete all those rows when you are happy.

So how does it work?

There's a new sheet called Policy, in which you will see a table of percentages (in C5:M16). Note that the sheet is protected (with no password) so you can only see and change the values in yellow (and then only by pasting from the "experimental" area in orange cells where you could experiment with a different risk profile). All other cells are copies or calculations based on the yellow cells.

In column A, is the month the Risk starts. If that's February say (month 2) we want to look along that row to see which percentages apply for each of the quarters. In that case, the percentage paid in Q1 will be 2/3 of a full quarter (as it will be in months 5,8 and 11) and after Q8, the balance of 1/3 will be due. For row 5 in the main (Calcs) sheet, the start month can be calculated separately with the simple formula =MONTH($B5) (where the $ fixes that column, for copying down) and MATCH can determine the row number for the percentage table.

In row 3, you will see quarter numbers (with 1-8 and 9 for the payment of the delayed Q1, if any). I use a calculation (below) to determine that quarter so all we need to do is look up the row and the column to determine the % due.

To get the correct percentage, I use the INDEX formula, with the form =INDEX(array, row, column). In that, the array is the table of percentages (C5:M16 on that sheet). The row is calculated using MONTH plus MATCH as stated above.

Calculating the relevant Quarter for row is a little harder. I've used the difference in months between risk start date and the charging quarter e.g. =MONTH(BG$2)-MONTH($B5)+1), plus the difference in years (e.g. =YEAR(BG$2)-YEAR($B5) times 12. That gives the number of whole months from risk start to quarter end.

Divide that by 3 and you get a number of quarters (can be fractional) but I surround with ROUNDUP(<<number of quarters>>,0) so a value like 0.333 becomes 1.

The formula to use with MATCH to get the column in the INDEX formula is therefore this (for row 5 in Calcs):

`=ROUNDUP((MONTH(BG$2)-MONTH($B5)+1 +12*(YEAR(BG$2)-YEAR($B5)))/3,0)`

For negative numbers, that can produce and error (since the table has no negatibe quarters in row 9 so, like you did, I surround the whole INDEX formula (multipled by the value) with (IFERROR(<<value * table formula>>,0) which put zero instead of ~N/A. The whole formula (for anywhere in row 5 of Calcs) is thus:

`=IFERROR($E5*INDEX(Policy!$C$5:$M$16,MATCH(MONTH($B5),Policy!$A$5:$A$16),MATCH(ROUNDUP((MONTH(BG$2)-MONTH($B5)+1 +12*(YEAR(BG$2)-YEAR($B5)))/3,0),Policy!$C$3:$M$3)),0)`

You can copy that right and down **from BG5** and it should work well.

Hope this works for you- it took me a time! Please feedback when you can. Thanks in advance.