Forum Discussion
Sliding scale Commission calculation
Hello ,
I am trying to create a formula in Excel to calculate interpolated commission rates based on a given loss ratio using a predefined table of loss ratios and corresponding commission rates.
Here’s the structure of my data:
- Loss Ratios are in the range B29 to B42.
- Corresponding Commission Rates are in the range D29 to D42.
- I want to input a specific loss ratio in cell F1 and calculate the interpolated commission rate in cell F3.
- Additionally, I want to calculate the final commission amount based on a total premium input in cell F2.
| Loss ratio(B) | Commission(D) | |
| 25.00% | 42.50% | |
| 30.00% | 41.25% | |
| 35.00% | 40.00% | |
| 40.00% | 38.75% | |
| 45.00% | 37.50% | |
| 50.00% | 36.25% | |
| 55.00% | 35.00% | |
| 60.00% | 33.75% | |
| 65.00% | 32.50% | |
| 70.00% | 31.25% | |
| 75.00% | 30.00% | |
| 79.00% | 29.00% |
I attempted to use the INDEX and MATCH functions for interpolation, but I’m having trouble getting the formula to work correctly.
So , if my loss ratio is 45.7346, I want a formula to calculate the commission % .
Could someone please provide a concise formula or guidance on how to achieve this? Any help would be greatly appreciated!
Thank you!
3 Replies
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
In F3:
=48.75%-F1/4
Final commission:
=F2*F3
Thank you for the above reply . I need to use this on multiple worksheets so I need a dynamic formula .
