Please ensure Javascript is enabled for purposes of website accessibility

Using Excel for settlement negotiations

Telling a client that the defendant’s insurer wants to settle their case for $250,000 is equivalent to explaining the intricacies of res judicata — it is usually meaningless. In order for a client to make an informed choice about whether to settle their case, they have to know where the money is going and, most importantly, how much they will end up with at the end of the day. Failure to disclose these details can impair the client’s ability to make an informed decision.

As my cases start nearing settlement discussions, I create an Excel spreadsheet that can help my clients make that informed decision. I ask my paralegal for a list of the case expenses to date, whether there are any outstanding medical bills that need to be paid, and whether there are any liens.

My chart has 6 columns:

  1. Settlement/Verdict Amount:  The first column has the possible amounts that the case could settle for. My practice is to make the first number the highest possible settlement number, and to reduce by a set amount for each row in that column. For example, if the case is filed in District Court for a maximum of $30,000, the top number would be $30,000 and each number below would be reduced by $500. The easy way to do this is to enter $30,000 in the first row (A2), and use a formula for the second row (=A2-500). To make the lower rows follow the same equation, select the cell with the formula (A3), and drag down about 50 or so rows. Then, click the “Edit” drop-down menu, and select “Fill” and “Down” (short-cut keys: Cntrl-D).
  2. Case Expenses:  The second column is for case expenses, for example expert fees, photocopying and long-distance calls. This is an exact amount, so you don’t need a formula. Simply put the total expenses in the first row (B2), and then fill down.
  3. Medical Bills: The third column is for any outstanding medical expenses that need to be paid back.  Simply put the number in the first row (C2) and then fill down.
  4. Medical Liens: The fourth column is for medical liens, whether from private insurance, Medicare, or medical assistance. I keep a separate column for this because the amount sometimes changes separate and apart from the medical expenses, whether because it is negotiated down, or some medical expenses are removed.  As before, fill down.
  5. Pre-Suit Net to Client: Many law firms have a different contingent fee, based on whether the case settles pre-suit or post-suit. If your retainer agreements have this provision, then you should have two separate columns to reflect the difference. This is where Excel’s formulas come into play — for the first row (E2), enter the following formula: (=A2-(A2/3)-(B2+C2+D2)). This assumes a pre-suit attorneys’ fee of one-third. Then, fill that formula down.
  6. Post-Suit Net to Client: The formula is basically the same as number 5 above, changed to reflect the increased fee: (=A2-(A2*0.4)-(B2+C2+D2)). This assumes a post-suit contingent fee of forty percent.  Again, fill down.

This chart is useful for the attorney and client to determine what the net effect of any settlement (or judgment) will be for the client. Also, if the client or attorney believes that the offer should be higher, it is an excellent tool to determine whether the client would be better off filing suit with an increased attorneys’ fee, or settling for an amount slightly less that he/she may think the case is worth. I often print the spreadsheet out for my clients and discuss with them where the insurance adjuster is, and what I expect from settlement discussions.

Also, it is useful to bring the chart with you to mediation. Even better, bring a laptop or your smartphone with the downloaded chart so you can immediately calculate offers throughout the day.

Excel is an easy program to use, and all of you can do this. However, if you want a shortcut, just e-mail me at [email protected], and I’ll e-mail it to you.

One comment

  1. Thank you so much for this. I was looking for this exactly. I thought about developing an app for this exactly. What do you think?