Excel Forum

Can't figure out how to do this in excel

Josh Ranew

New Member

Members

Forum Posts: 1

Member Since:
January 26, 2020

Offline

January 26, 2020 - 4:06 pm

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

Mynda Treacy

Admin

Forum Posts: 4772

Member Since:
July 16, 2010

Offline

January 27, 2020 - 8:35 am

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

Anders Sehlstedt

Eskilstuna, Sweden

VIP

Members

Trusted Members

Forum Posts: 925

Member Since:
December 7, 2016

Offline

January 27, 2020 - 9:19 am

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.