Dear Community,

I'm trying to work out a formula so that if an invoice issued in Jan with 30 days payment term, this amount is collectible in Feb, if 45 days payment term then collectible in Mar and so on. I tried if function, but it didn't seem to work. I might have formulated it incorrectly.

I attached the excel sheet and what expect return is. Hope for some help please.

Thanks,


Haha

Attached Files

AR collection.xlsx (9.5 KB, 3 views)

Do you only issue invoices on a particular day of the month?

Even if so, say you produce and send invoices on the 5th, or the last, day of every month for the month before, with 45 day terms you're going to have overlapping data from January and February into March.

I'm going to assume that the invoice data on the left is a summary from somewhere else, where individual invoices with individual dates exist and are summed or pivoted into what you have there.

If you invoice all month long, different invoices from March with a 45-day cycle could become past due in both April and May. So how do you decide how much money to allot to April and how much to May? The 30- and 60-day ones, again presuming all invoices have the same date for the month, are fairly straightforward, but the 45-day cycle won't really work in this style unless you had specific date information.

Best bet, to me, would be to apply the terms to each individual invoice in a spreadsheet which includes the date of the invoice, then for Client B, an invoice sent on 2/6/23 would become past due on the 46th day, or 3/24/23. Then on any given day you could filter for which invoices, and sum up to accounts, were past due.

Thank you for looking into this mater for me.

I'm thinking, it doesn't have to be specific therefore can assume all invoices are issued at the beginning of the month.

Will this work?

Thanks,

My advice is to use table like one in sheet Invoices. From your table it looked like payment terms are fixed for client, so I added a sheet with Clients table where payment terms are stored.

In case payment terms can vary for same client, you need a column for them in Invoices table too (and remove payment terms column from Clients table - and edit the formula in payment dates column accordingly).

The main difference between 2 designs is, that with your design, you have to add new columns into your table whenever you register new payments. With my design, you simply add new payment entries into table. Also, whenever you will have new clients, you simply register it in Clients table, and it will be all you need to add invoices for this new client.

You can also have any number of report sheets designed, which read data from Invoices and clients tables, can do any calculations based on read data, and display the results in any format you want.

Attached Files

AR collection.xlsx (15.8 KB, 4 views)

Brilliant!
Many thanks,