I am trying to do the reverse of what I have attached. This is a very basic set of formulas that start with a base premium, then add in a state tax that is the base premium + 2 fees (they can vary every year) multiplied by a set percentage. Then do the exact same with a second percentage (another tax) of the base + the 2 fees. I can input the base premium and come up with the final every time with no issue. What I am trying to do is the exact opposite, start with the final number (including all fees and taxes) and let it tell me what each percentage is, and ultimately the base premium. It will save a ton of time trying to back into numbers to get the final desired outcome.
Any help would be greatly appreciated. I am by no means an excel wizard (obviously) but do better than most.
Thanks,
Josh
Hi Josh,
You can use this formula to work back from the Final Premium in the E&S Liability example:
=20127.70/(1+0.1%+5%)-250-35
This formula will work for Frontline:
=(20131.70-4)/(1+0.1%+5%)-250-35
And this one for Generic E&S Property:
=(24711.96-4)/(1+0.1%+5%)-172-150-300
Replace the hard keyed figures in the formulas above with cell references if you prefer.
Mynda
Hello,
The fees calculated with percentage, are they calculated on the base value only or with the fees and taxes added?
Example:
Base Premium: $18 866
Policy Fee: $35
Inspection Fee: $250
FSLSO Fee (on base): $18 866 * 0.1% = $18.87
FSLSO Fee (with added fees): ($18 866 + $35 + $250) * 0.1% = $19.15
Florida Tax (on base): $18 866 * 5% = $943.30
Florida Tax (with added fees): ($18 866 + $35 + $250 + $19.15) * 5% = $958.51
EMPA: $4
Final Premium (on base): $18 866 + $35 + $250 + $18.87 + $943.30 + $4 = $20 117.17
Final Premium (with added fees): $18 866 + $35 + $250 + $19.15 + $958.51 + $4 = $20 132.66
___________________
None of the sums above matches the sum of the Frontline range in your Excel file. We need to know the calculation order to be able to reverse it successfully.
Reverse calculation with percentage is as simple as dividing.
0.1% (*1.001) in reverse is /1.001
5% (*1.05) in reverse is /1.05
But as you see, you need to have correct calculation order to get correct numbers.
