• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

Excelypedia

The Excel Encyclopedia of The Internet

  • Tutorials
  • Functions
  • Formulas
  • Templates

How to Calculate Arrears in Excel

October 4, 2021 by Andrew Childress
Calculate arrears in Excel

The concept of arrears refers to money owed that should have been paid in the past. Let’s learn how to calculate arrears in Microsoft 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.

Calculate arrears in Excel

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:

calculate arrearage in Excel

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.

How to calculate arrears in Excel

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

Category: Excel FormulasTag: Excel, How To, Instructions, Tutorial

About Andrew Childress

Previous Post:Akaikes information criterion ExcelHow to Calculate AIC in Excel
Next Post:Calculate ATR in ExcelAverage true range in Excel

Sidebar

Recent Posts

  • How to Do a Contingency Table in Excel
  • Format Cell Alignment in Excel
  • Default Width in Excel
  • Translate in Excel
  • Switch Windows in Excel

Categories

  • Basics
  • Examples
  • Excel Formulas
  • Excel Functions
  • Excel Shortcuts
  • Excel Templates
  • Excel Tutorials
  • Uncategorized

Excelypedia

Master Excel to add a highly valuable asset to your employability portfolio. Learn how to analyze data in Excel.

    • Facebook
  • Twitter
  • Instagram
  • YouTube

Learn

  • Functions
  • Formulas
  • Shortcuts

Legal

  • Privacy Policy
  • Cookie Policy
  • Terms & Conditions

Help

  • FAQ
  • Contact Us

Copyright © 2023 · Excelypedia · All Rights Reserved

Back to top