Excel formula to see how soon invoices are paid.

Ryan Johnson
Ryan Johnson used Ask the Experts™
on
When I create an invoice for my customers, alot of my customers decide to pay me over time. Those that opt in to pay over time make a monthly payment to me. As I run some reports, I am trying to figure out two things: 1. How many months does it take for each invoice to get paid in full? 2. In how many months is 1/2 the invoice amount paid. I want to know this because when 1/2 the invoice is paid, I offer them other discounts. I attached a sample of my report I export. I added two columns (B and C). I put the answers in those columns that I am hoping you can help me with the formula. So, can you please help me with the formulas in columns B and C? Thank you in advance!!!
Data-Sample.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
I should have added, my report comes out exactly as the sample attached. I added the red color and columns B and C. So if I can keep the formatting the same, as it comes out (except i can add columns B and C), that would be helpful. Thank you!
Try this formula in B6 and copy down.

=IF(H6<>0,"",(LOOKUP(2,1/(I6:AF6<>""),COLUMN(I6:AF6)))-(COLUMN(INDEX(I6:AF6,MATCH(TRUE,INDEX(I6:AF6<>0,),0))))+1)

Open in new window

Still working on the 1/2 Invoice.

Paul
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
You may find it much easier to maintain a workbook that uses a simple-looking user-defined function to return the number of months to pay off x% of the invoice. For example, cells B6 & C6 might use formulas like:
=PaymentAging(E6,I6:U6,100%)
=PaymentAging(E6,I6:U6,50%)

Open in new window

The code for this function should be placed in a regular module, just like a recorded macro. Make sure you save the workbook as .xlsm.
Function PaymentAging(InvoiceValue As Double, Payments As Range, TargetFractionPaid As Double) As Variant
Dim i As Long
Dim cel As Range
Dim CumulatedPayments As Double, Pmt As Double, Target As Double
PaymentAging = ""
On Error GoTo Errhandler
Target = InvoiceValue * TargetFractionPaid
For Each cel In Payments.Cells
    Pmt = cel.Value
    If (i > 0) Or (Pmt > 0) Then
        i = i + 1
        If (CumulatedPayments + Pmt) >= Target Then
            Exit For
        Else
            CumulatedPayments = CumulatedPayments + Pmt
        End If
    End If
Next
PaymentAging = i - 1 + (Target - CumulatedPayments) / Pmt

Errhandler:
End Function

Open in new window

Data-Sample-1.xlsm
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Thank you flyster and byundt for your reply! byundt, when i do this, the formula works on only a few of the row. The 100% formula only returns a value for 2 of the rows and the 50% formula only returns a value for 3 of the rows. There are about 600 rows. Seems the code works because its returning some. Attached is a screenshot. Any ideas why? I have attached a screenshot. You will see the row i'm on is not showing a return for 100% or 50% payment. That invoice is paid in full, as you can see by column S. So there should be a value for both 100% and 50%. You can see this is the case for other rows. Another odd thing is two rows down from where the cursor is, the 50% payment shows 5.5 but the 100% formula doesn't show anything. Again, in column S you can see there isn't a balance due. So it should show something in the 100% formula. All the formulas are an array. I put the code you sent me. I also attached my original, i just cut it down to a few of the invoices. Any ideas? Please see the attached XLSM as well. Thanks!!!!
Screenshot.png
Invoice-Summary.xlsm

Author

Commented:
I think I figured out what’s happening. In the blank cells, there is some kind of value. But I can’t see anything when I click on the value. So, if I click each blank cell and press Delete, the array formula works! Now, how do I delete these cells that are blank all at once and not go through every one. I tried F5, select blanks, but it doesn’t select the cells because there something there. I am so confused. On the spreadsheet you’ll see what I mean. Any ideas?
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
There was a runtime error with your blank cells. I patched it by using the Val function.

I also changed your formulas to refer to the correct columns and to be not array-entered.
Function PaymentAging(InvoiceValue As Double, Payments As Range, TargetFractionPaid As Double) As Variant
Dim i As Long
Dim cel As Range
Dim CumulatedPayments As Double, Pmt As Double, Target As Double
PaymentAging = ""
On Error GoTo Errhandler
Target = InvoiceValue * TargetFractionPaid
For Each cel In Payments.Cells
    Pmt = Val(cel.Value)
    If (i > 0) Or (Pmt > 0) Then
        i = i + 1
        If (CumulatedPayments + Pmt) >= Target Then
            Exit For
        Else
            CumulatedPayments = CumulatedPayments + Pmt
        End If
    End If
Next
PaymentAging = i - 1 + (Target - CumulatedPayments) / Pmt

Errhandler:
End Function

Open in new window

Invoice-Summary.xlsm

Author

Commented:
That worked. You're awesome, thank you!!!

Author

Commented:
Flyster, thanks for your help. byundt, thank you for the formula and code. I really appreciate it!!! I appreciate the quick replies as well.
Glad you found a solution.

@ byundt: Nice job!
Benjamin LuSAP/ERP Data Player

Commented:
Also, you can use array formula to solve it:
You need to press ALT+CTRL+ENTER to enable the formula
Data-Sample-.xlsx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial