Hi Please help Me out for the Making Excel Formula !!


Please help me out for formula making !!

How to create the formula, i multiply values manualy and want to create the formula for the calculation ?

Red Highlighted is the main confusing for me !!

Attached file is for the reference !!

Thanks in advance




In cells BI12:BP13, there is a series of percentages per full quarter, i.e.3% for Q1. 9% for Q2 etc. When the start date (e.g. B5 ) is in January, in March 3 months is due so the full 3% is paid.

In the case of B7 (01 March 2019 start), on 31 March you need the value to be 1/3 of Q1 3%, right? For 30 June 2019, shouldn't the premium be the remaining 2/3 of 3% for Q1 PLUS 1/3 of the 9% for Q2 etc. up to a final 2/3 of Q8's 3%? (That would mean the same pattern of payments would be made, just shifted by 2 months).

Or are you happy to simply move the 2/3 of 3% for Q1 to the end (making the pattern of risk and earnings a little different)?
John_Ru (rep: 502) Jan 7, '21 at 4:11 pm

Hope you are good.

in the case of B7 (01 March 2019 start) on 31 March we need the only 1 month of 3%, and for June 30 2019 i need only 9%, and 30 Sep 2019 i need 16% and so one and for the last period 1-Jan-2021 to Risk end date 28-Feb-2021 the 2 months earn of remaining 3% of the 1st quarter.

Hope this would help you.
qurat (rep: 2) Jan 8, '21 at 1:29 am
So are you looking to fix that (unusual) row by

    1. simply having 1/3 of the 3.125% paid at the start (then the delayed 2/3 of the 3.125% at the end) OR

    2. having a complicated formula which does that?

Please comment against the advice from Variatus too.
John_Ru (rep: 502) Jan 8, '21 at 2:23 am
Your 1 point is true. 

qurat (rep: 2) Jan 8, '21 at 7:09 am
Please revise your question. It should contain a description of the formula you want, like for instance, "BO47 = [Sum] * [Interest], where SUM = [This] + [That] - [Something] and [Interest] = [The rate from BI12:BP12 selected by the difference between BO4 and D4]". I know this isn't what you want but I don't know what you do want because you haven't explained. Use any words you can find but explain. We need to know the cell where you want the formula and the location of the data to be usedin the calculation.
You can modify your question by clicking the Edit button you will find under it. While at it, incorporate all the little details you gave in the discussions here and there into the question itself.
The formula you want will be easier to write and maintain if you disclose the algorithm by which BJ13:BL13 are calculated from BI13. Put it into your question in plain language. We'll figure out how to express your thoughts in a formula.
Variatus (rep: 4258) Jan 8, '21 at 7:14 pm
@ Variatus. Good advice always but I've assumed that the risk profile is customary (or empirical or proprietary!) and we don't need to know. My Answer allows them to change the risk profile (in a controlled way, precentage-wise).
John_Ru (rep: 502) Jan 9, '21 at 9:53 am
Add to Discussion


Selected Answer


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.




Wonderfull work, i appreciate you and 100 millions thanks for the help !!

Again great work and very very helpfull and you work hard for me this is awesome !! This is simple and easiest way for me to understand all the concept behind the formula

Great for me !!

qurat (rep: 2) Jan 9, '21 at 12:14 pm
Thanks Qurat, you're very kind.
John_Ru (rep: 502) Jan 9, '21 at 12:17 pm
Add to Discussion

You need to separate business management from programming. Whether to charge interest daily, monthly or quarterly - in advance or upon completion of the period - is a business decision and any decision you make in this regard may conflict with company business policy. The correct way is to ask the policy and then program what they say.

I point out that you are talking months and quarters in your question but calculate days in your worksheet.

Keep your eye also on project integrity. The base rate appears to be 3.125% which is hidden in BI13 and then processed in mysterious ways to arrive at different multiplicators. The correct way is to have only one rate, set by management (and therefore subject to change), which should be prominently displayed in the sheet and then used for all calculations. If there is a change in the future you want to change it in one cell and have it applied to every formula in the sheet or book. 

IMHO, you have arrived at a problem that you shouldn't have because you didn't answer essential questions that should have been answered and dealt with before you start. Here is my advice.

  1. Ask the company policy for how to charge interest.
  2. Set up your workbook to have a fixed interest rate which is applied to variable periods. Do not make both rate and periods variable.


@Variatus- your advice is sound (as usual).

I noticed the 3.125% in cell BI13, 9.375% in BJ13 and noted that the percentages for Q1 to Q8 summed to 100%. I wondered if this was meant to reflect some risk profile (as suggested by columns B and C) which varied with time (so not a simple flat interest rate). That would complicate matters but lead to me discussion points on the subject. 

@Qurat - I await your response to the advice above and question from me
John_Ru (rep: 502) Jan 8, '21 at 12:31 am
Yes you got my point. This is not the flat interest rate.

If you share me the formula that would be helpfull for me 

Thanks in advance.
qurat (rep: 2) Jan 8, '21 at 7:14 am
I want to create the formula for all the cases. But unfortunetly i am troudiling to making the formula in excel. 
qurat (rep: 2) Jan 8, '21 at 7:31 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login