Help needed with insurance commission calculator using Vlookup

[Feeona]

I'm using an excel sheet for tracking new sales of insurance policies. I add a new line for each client, by product type, the sum of premium they will pay, I manually calculate the initial commission due and renewal commission due. But I want to reduce the manual calculations/errors of the commissions by introducing a Vlookup table outlining the different product type and commission rates. Theres about 20 different policies/commission rates initial / renewal and some depend on the age of the client. If the client has less than 10 years to termination of the policy the commission is reduced. I've attempted to create the vlookup but its not working for me.
Does anyone have any tips on how I could make it work or other functions i could use, would reduce hours and hours of work for us! hope someone can help.
Fee

 

[Tinbendr]

It is difficult to offer help without some type of sample file.

Your vague question is like taking your car to a mechanic as saying, "Something is wrong with my car", and then leave.

There are many file-sharing web sites that have a free section.

Have you tried the search feature on this board, There has to be literally thousands of question/answer about Vlookup.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>

 

[Nogslaw]

Welcome to the board!

I would first list all of your variables. From your post, I gather that your variables will be:

1. Policy Type
2. Initial Commission Rate
....a. Greater than 10 Years Remaining
....b. Less than 10 Years Remaining
3. Renewal Commission Rate
....a. Greater than 10 Years Remaining
....b. Less than 10 Years Remaining

I would then setup your table with these variables in each one. You would end up with 5 columns: Policy Type, Initial Greater than 10 Years, Initial Less than 10 Years, Renewal Greater than 10 Years, and Renewal Less than 10 Years.

In my example, this table is setup on Sheet2, in range A1:E20.
The tracking sheet is on Sheet1, with Policy Type in column A, and Date of Termination in column B. Now, I did use 3650 as my value of 10 years (10 years * 365 days). You could be more accurate by comparing months, days, years, etc. if necessary.

For your initial commission calculations on Sheet1, you could use: =VLOOKUP(A1, Sheet2!A1:E20, IF(B1-NOW() > 3650, 2, 3), FALSE)
For your renewal commission calculations, you could use: =VLOOKUP(A1, Sheet2!A1:E20, IF(B1-NOW() > 3650, 4, 5), FALSE)

 

[Michael M]

Hi Fee and Welcome to the board
Have a look at the 2nd line of my Tag for Excel HTML maker.This will enable you to post a sample of your data and what you want to achieve.
If you have created the VLOOKUP and it doesn't work, maybe post it and explain what it does or doesn't do....you have to help us help you.