Excel is often considered an accountant’s best friend. This is because of how efficient it can be for calculations, data entry, and some level of automation. However, like in any friendship, your relationship with Excel is only as good as how much you have come to know it.
This article provides 10 different Account Payable Excel formulas that can help simplify your Account Payables going forward. They have all been shared in a step-by-step format and for better learning, you can practice these formulas by making a copy of this dummy data before you try them on your organization’s data to avoid any errors or confusion.
In this article you’ll learn how to:
– Calculate Total Accounts Payable
– Check the Aging of Accounts Payable
– Calculate the Average Payment Period
– Calculate Vendor Balances
– Check Late Payment Interest
– Conditional Formatting for Overdue Invoices
– Vendor Aging Summary
1. Calculate Total Accounts Payable
Let’s start with the basics- Using the SUM function to add up all your outstanding invoices or bills.
=SUM(B2:B12)
In this example, assume that column B contains the amounts of individual invoices.
2. Aging of Accounts Payable:
You can calculate the aging of accounts payable to see how long invoices have been outstanding. You may use SUMIF or SUMIFS along with date comparisons.
=SUMIF(C2:C10, “<=30”, B2:B10) # Amounts due within 30 days
=SUMIF(C2:C10, “>30”, B2:B10) # Amounts due between 31 and 60 days
=SUMIF(C2:C10, “>60”, B2:B10) # Amounts due over 60 days
In this example, column B contains amounts, and column C contains invoice due dates.
3. Calculate The Average Payment Period
This formula calculates the average number of days it takes for the business to pay its invoices.
=AVERAGE(C2:C10 – TODAY())
In this example, column C contains invoice due dates.
4. Calculate Vendor Balances
To calculate the outstanding balance for each vendor, you can use SUMIF or SUMIFS based on vendor names.
=SUMIFS(B2:B10, A2:A10, “Vendor A”)
In this example, column A contains vendor names, and column B contains invoice amounts.
5. Late Payment Interest
If you want to calculate late payment interest on overdue invoices, you can use this formula:
=B2 * 0.02 # Assuming a 2% monthly interest rate
In this example, column B contains the invoice amount.
6. Conditional Formatting for Overdue Invoices
Use conditional formatting to highlight overdue invoices. For example, you can set up a rule that formats cells with due dates in the past as red.
Let’s say you have a list of invoices with due dates, and you want to use conditional formatting to highlight the rows where the due dates have passed.
Step 1: Create a table with columns for Invoice Number, Vendor Name, Invoice Amount, Due Date, and Payment Status.
Step 2: Click the column header containing due dates (e.g., “Due Date”) to select the entire column.
Step 3: Go to the “Home” tab in Excel, and under the “Styles” group, click on “Conditional Formatting” and then choose “New Rule.”
Step 4: In the “New Formatting Rule” dialog box that appears, select “Use a formula to determine which cells to format.”
Step 5: In the formula box, enter a formula that checks if the due date was in the past. Assuming your due dates are in column D starting from row 2, the formula would be: =D2<TODAY()
This formula compares the due date in each row to today’s date.
Step 7: Click the “Format” button to choose the formatting style you want to apply to overdue invoices. For example, you can set the font color to red and the fill color to yellow to make it stand out.
Step 8: Click “OK” in the “Format Cells” dialog box to apply the formatting rule.
You’ll now see that any rows where the due date has passed will be formatted with the specified styles. Your overdue invoices will be visually highlighted in red, making it easier to identify and manage them within your accounts payable workflow.
7. Vendor Aging Summary
Create a summary table that lists vendors and their aging balances. Use SUMIFS to calculate these balances.
Vendor 0-30 Days 31-60 Days Over 60 Days
Vendor A =SUMIFS(B2:B10, A2:A10, “Vendor A”, C2:C10, “<=30”)
Vendor B =SUMIFS(B2:B10, A2:A10, “Vendor B”, C2:C10, “<=30”)
8. Payment Scheduling
Create a payment schedule based on due dates, available cash, and payment terms. Use Excel’s data validation to ensure payments are made within terms.
Calculate the payment date based on due dates and terms using IF and EDATE functions.
For Net 30 terms: =IF(D2<=E2, D2, EDATE(D2, 30))
For Net 60 terms: =IF(D2<=E2, D2, EDATE(D2, 60))
Use an IF function to check if there are sufficient funds for each payment.
9. Dynamic Lists
Use data validation and dropdown lists for vendor names, invoice numbers, and payment terms to reduce errors in data entry.
Step 1: Create a list of vendor names in a separate column or worksheet.
Step 2: Apply data validation to cells where vendor names are entered, referencing the vendor list.
Step 3: Create a list of payment terms (e.g., Net 30, Net 60).
Step 4: Apply data validation to cells for payment terms using a dropdown list.
10. Running Balances
Calculate running balances for accounts payable. Subtract paid amounts from the total outstanding balance.
Step 1: Use an invoice data table.
Step 2: Calculate outstanding balances for invoices in a new column using this formula: =Invoice Amount – Sum of Payments.
Step 3: Extend the formula to calculate running balances for all invoices automatically.
These formulas and techniques can help you effectively manage accounts payable in Excel. Depending on your specific needs and processes, you may need to customize these formulas to fit your organization’s requirements. However, if you want to better manage Account Payables, automate your payment workflows, and avoid any errors that may come with using Excel formulas, learn how Duplo can help you and your team simplify your payment workflows today.
