Expert: Bill Hermanson - 11/7/2009

Question
Hi Bill,

I looking for an Excel formula to perform the following:

Objective:
The spreadsheet will compute reimbursement amounts for a Direct Reimbursement Dental Plan.

The dental plan pay out is on the following schedule:
Level 1 100% for $100
Level 2 80% of next $500
Level 3 50% of next $1000
Level 4 0% of next XXX
Maximum payout is $1000

The payout schedule I will place on a worksheet and reference the formula to it for the amounts. Level 4 I will not use for now, but I want to include it in the formula in case the reimbursement schedule changes in the future.

Here is a screen shot of a spreadsheet I looking to replicate in part.

http://ncdentaldr.com/Documents/DR%20Dental_Self-Admnin%20Excel%20Program_Screen...

This screen shot really shows what I looking to do. Other changes I looking to make have nothing to do with the formula I need.

Each plan participant will have there own section of a spread sheet and we would put in the claim amount. The reimbursement amount would be calculated based upon the schedule.

I know mathematically what I want ... it's simple I just don't know what function to use. I believe two formulas would be required. One for CLAIM 1, then a second formula for Claims 2-13 referencing the most recent claim.

Thanks you in advance for your assistance.

-Mark

Answer
Mark,

   Your problem is most likely more complex than a simple formula, especially given your last little statement, which indicates that you need to keep track of multiple dental appointments for each employee over a period of time.  I suspect you haven't thought through everything you really need!

  Let's assume that for employee X, you somehow have managed to keep a running total of the amount of dental work they have had and the amount of payout that's been made.  While your question doesn't actually address this, isn't it necessary?  Solving this part is much more complex than the formula you asked for, but it can be rather easily done [after all] using the SUMIF statement.  [However, since you didn't ask for this, I won't say more]

   Your supplied screen shot isn't very illuminating, as it is just a list of employee names with a mysterious and undefined "LINK-X" next to each name, and the table of payout schedule (which is already in your question) repeated in the upper right corner.

   But the formula you want is a simple nested IF.  Your don't actually identify what the $100, $500, or other values ARE [Are they the Y-T-D accumulated total, or the price for a single dental visit, or what?] so I will just have to assume this value [whatever it means] is in a cell somewhere.  If this value is in Cell K5, then lets say you want the result in cell L5.  So in cell L5 type:

   =IF(K5<=100,K5,IF(K5<=600,100+0.8(K5-100),IF(K5<=1000,100+0.8*500+0.5(K5-600)))

  The reason I write it this way is you say, for example , "Level 2 [is] 80% of next $500".... that means that they have already received the $100 for the first $100, and will get 80% for the NEXT $500, that is, any amount OVER $100  and up to $600... the range of the NEXT $500.  That's why I have the term 100+0.8(K5-100) in your equation.

   If I have misunderstood your question, then you can probably figure out what to do to my equation to make it correct for your application.

   Good luck in creating this entire application; I advise you to think it through thoroughly, which I don't think you have yet.

   >>> Bill