Disclaimer: I am not an lawyer, and offer this spreadsheet free with no guarantees either written or implied, as to the appropriate use of this spreadsheet, or its accuracy. I am not recommending this spreadsheet to be used for any particular state or municipality.

Let us assume that you live in a state that has recently clarified the way overtime should be calculated. Now you find out that you need to go back and recalculate each overtime payment for certain employees. After calculating the additional amounts owed to the employees, for each occurrence, you now need to include interest in the additional amounts owed.

After completing your Excel worksheet with the date of each occurrence and the additional amount owed, you look for a future value formula (using dates) in Excel to determine the total owed each employee, plus interest. Not finding a built-in formula for future value with dates, you need a workaround.

The Excel workbook can be downloaded here: ipenalty.xlsx

Two future value with dates formulas are shown on sheet “Future Value”. Sheet “Many” gives a format to calculate the total owed each employee (future Value) and the interest broken out separately. The interest rate and the ending date used to calculate the amounts owed are entered once. Two examples are entered to show the format.