How to Calculate Arrears in Excel

The concept of arrears refers to money owed that should have been paid in the past. It can occur for a variety of reasons – and it can be difficult to calculate. Fortunately, spreadsheets make this easy. Let’s learn how to calculate arrears in Microsoft Excel.

How to Calculate Arrears in Excel

To learn how to calculate arrears, let’s consider a simple example. An employee was paid $2,000 per month, but on January 1 was awarded a pay raise of $500. This means that her new salary will be $2,500. However, for three months, the raise was inadvertently not paid out. It’s now March 1, and you need to determine the amount of the arrearage.

To make this calculation, let’s create a simple template in Excel. To calculate arrearage, you’ll need to know the following:

• The amount that was actually paid
• The amount that should have been paid
• The difference between these amounts
• The number of impacted pay periods

Begin by constructing a simple layout in Excel. Create three columns: Paid, Should Have Paid, and Difference. Then, create a row for each pay period missed.

In the Paid column, place what was actually paid in each period: $2,000. In the Should Have Paid column, place $2,500. The Difference column can either be a formula, or hardcoded. For example, the formula =C2-B2 will return the correct $500 per pay period.

Repeat this step for the subsequent pay period. Your finished layout will look like this:

Now, you can simply sum up the Difference column with the formula =D2+D3. Enter it in cell C4, and then hit Enter on your keyboard. Excel will return the amount of the arrearage that should be paid to the employee: $1,000.

As you can see, Microsoft Excel makes it easy to calculate arrears.