I am trying to make a small excelfile that will calculate how much commission an insurance broker gets over 5 years.
It should start with the broker puts in how much he think he will sell for every year in a cell called SALESVALUE.
The first year he gets 4% upfront commision of the SALESVALUE and then 0,2% every year the insurance is running....
Format something like this:
ENTER SALESVALUE HERE: ________
Commission: Year One Year Two Year Three Yearh Four Year 5
.-----------------------------------------------------------------------------------------------------------
For example: Joe the broker think he will sell insurance worth 1000 000 yearly.
Year 1: He gets 4% commision of the 1000 0000.
FORMULA: 0,04*SALESVALUE = 40 000
Year 2: He gets 4% commision of 1000 000 of what he sells year2 but also a 0,2% ticking commission of the 1000 0000 he sold year 1.
FORMULA: (0,04*SALESVALUE)+(0,002*SALESVALUE)
Year 3: He gets 4% commision of 1000 000 of what he sells year3 but also a 0,2% ticking commision of what he sold year 1 and year 2.
FORMULA: (0,04*SALESVALUE)+((0,002*SALESVALUE)*2) Is this correct thinking??
Year 4: He gets 4% commision of 1000 000 of what he sells year4 but also a 0,2% ticking commission of he sold year 1, 2 and 3
(0,04*SALESVALUE)+((0,002*SALESVALUE)*3) ?????
Year 5: He gets 4% commision of 1000 000 of what he sells year5 but also a 0,2% ticking commission of he sold year 1, 2,3 and 4.
(0,04*SALESVALUE)+((0,002*SALESVALUE)*4) ???????
PLEASE CAN SOMEONE CHECK IF MY FORMULAS ARE OK, OR IF THERE IS A MORE EFFICIENT SOLUTION TO CALCULATE THIS IN EXCEL.
I WOULD BE -VERY- GRATEFUL FOR ANY INPUT
Bookmarks