Allocate pre-payments to oldest debt first

  • Hi,
    Am seeking assistance on an Excel macro. The issue is where an entity has debts as well as credits (pre-payments) which have different age profiles. In essence, the pre-payments should go towards clearing the oldest debt first. The screenshot below illustrates this (where pre-payments are the negative amounts). For instance, for Client X below, the prepayments in the 90-180 days and 181-365 days should clear the debt over 365 days. Any ideas will be of great help
    Original data [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 446"]

    [tr]


    [TD="width: 65"]Client[/TD]
    [TD="width: 105"]0-30 days[/TD]
    [TD="width: 80"]31-90 days[/TD]
    [TD="width: 84"]91 - 180 days[/TD]
    [TD="width: 92"]181 - 365 days[/TD]
    [TD="width: 84"]>365[/TD]
    [TD="width: 84"]Total due[/TD]

    [/tr]


    [tr]


    [td]

    X

    [/td]


    [TD="class: xl63"] $ 30,000.00[/TD]
    [TD="class: xl63"] $25,000.00[/TD]
    [TD="class: xl63"] $-20,000.00[/TD]
    [TD="class: xl63"] $ -5,000.00[/TD]
    [TD="class: xl63"] $ 34,900.00[/TD]
    [TD="class: xl63"] $ 64,900.00[/TD]

    [/tr]


    [tr]


    [td]

    Y

    [/td]


    [TD="class: xl63"] $ - [/TD]
    [TD="class: xl63"] $ - [/TD]
    [TD="class: xl63"] $ -4,500.00[/TD]
    [TD="class: xl63"] $ -6,000.00[/TD]
    [TD="class: xl63"] $ - [/TD]
    [TD="class: xl63"] $-10,500.00[/TD]

    [/tr]


    [tr]


    [td]

    Z

    [/td]


    [TD="class: xl63"] $ 4,500.00[/TD]
    [TD="class: xl63"] $ 5,790.00[/TD]
    [TD="class: xl63"] $ 9,000.00[/TD]
    [TD="class: xl63"] $ 2,000.00[/TD]
    [TD="class: xl63"] $ - [/TD]
    [TD="class: xl63"] $ 21,290.00[/TD]

    [/tr]


    [tr]


    [td]

    A

    [/td]


    [TD="class: xl63"] $ - [/TD]
    [TD="class: xl63"] $ - [/TD]
    [TD="class: xl63"] $ -4,550.00[/TD]
    [TD="class: xl63"] $ 20,000.00[/TD]
    [TD="class: xl63"] $-23,500.00[/TD]
    [TD="class: xl63"] $ -8,050.00[/TD]

    [/tr]


    [tr]


    [td]

    B

    [/td]


    [TD="class: xl63"] $ 7,000.00[/TD]
    [TD="class: xl63"] $ 4,500.00[/TD]
    [TD="class: xl63"] $ - [/TD]
    [TD="class: xl63"] $ 9,000.00[/TD]
    [TD="class: xl63"] $ -8,500.00[/TD]
    [TD="class: xl63"] $ 12,000.00[/TD]

    [/tr]


    [tr]


    [TD="class: xl64"]Total[/TD]
    [TD="class: xl64"] [/TD]
    [TD="class: xl64"] [/TD]
    [TD="class: xl64"] [/TD]
    [TD="class: xl64"] [/TD]
    [TD="class: xl64"] [/TD]
    [TD="class: xl65"] $ 79,640.00[/TD]

    [/tr]


    [/TABLE]

    Required results [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 446"]

    [tr]


    [TD="width: 65"]Client[/TD]
    [TD="width: 105"]0-30 days[/TD]
    [TD="width: 80"]31-90 days[/TD]
    [TD="width: 84"]91 - 180 days[/TD]
    [TD="width: 92"]181 - 365 days[/TD]
    [TD="width: 84"]>365[/TD]
    [TD="width: 84"]Total due[/TD]

    [/tr]


    [tr]


    [td]

    X

    [/td]


    [TD="class: xl65"] $ 30,000.00[/TD]
    [TD="class: xl65"] $25,000.00[/TD]
    [TD="class: xl65"] $ - [/TD]
    [TD="class: xl65"] $ - [/TD]
    [TD="class: xl65"] $ 9,900.00[/TD]
    [TD="class: xl65"] $ 64,900.00[/TD]

    [/tr]


    [tr]


    [td]

    Y

    [/td]


    [TD="class: xl65"] $ - [/TD]
    [TD="class: xl65"] $ - [/TD]
    [TD="class: xl65"] $ -4,500.00[/TD]
    [TD="class: xl65"] $ -6,000.00[/TD]
    [TD="class: xl65"] $ - [/TD]
    [TD="class: xl65"] $-10,500.00[/TD]

    [/tr]


    [tr]


    [td]

    Z

    [/td]


    [TD="class: xl65"] $ 4,500.00[/TD]
    [TD="class: xl65"] $ 5,790.00[/TD]
    [TD="class: xl65"] $ 9,000.00[/TD]
    [TD="class: xl65"] $ 2,000.00[/TD]
    [TD="class: xl65"] $ - [/TD]
    [TD="class: xl65"] $ 21,290.00[/TD]

    [/tr]


    [tr]


    [td]

    A

    [/td]


    [TD="class: xl65"] $ - [/TD]
    [TD="class: xl65"] $ - [/TD]
    [TD="class: xl65"] $ -4,550.00[/TD]
    [TD="class: xl65"] $ -3,500.00[/TD]
    [TD="class: xl65"] $ - [/TD]
    [TD="class: xl65"] $ -8,050.00[/TD]

    [/tr]


    [tr]


    [td]

    B

    [/td]


    [TD="class: xl65"] $ 7,000.00[/TD]
    [TD="class: xl65"] $ 4,500.00[/TD]
    [TD="class: xl65"] $ - [/TD]
    [TD="class: xl65"] $ 500.00[/TD]
    [TD="class: xl65"] $ - [/TD]
    [TD="class: xl65"] $ 12,000.00[/TD]

    [/tr]


    [tr]


    [TD="class: xl66"]Total[/TD]
    [TD="class: xl66"] [/TD]
    [TD="class: xl66"] [/TD]
    [TD="class: xl66"] [/TD]
    [TD="class: xl66"] [/TD]
    [TD="class: xl66"] [/TD]
    [TD="class: xl67"] $ 79,640.00[/TD]

    [/tr]


    [/TABLE]

    Regards

  • Hi and Welcome to the Forum :smile:


    In order to make things a lot easier ... why don't you attach your test file with your next message ... :wink:


    Regards

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hi Carim,


    Many thanks for your assistance. It's greatly appreciated.


    However, there is a slight problem. The formula should be dynamic, so that it checks the whole row for any negative numbers. Any negative number (pre-payment) offsets the oldest positive debt. If the pre-payment is greater than the oldest debt, then the remaining pre-payment offsets the next oldest debt, and so on, until all pre-payments are exhausted


    I have attached the file with the comment and an example, hopefully to make it clearer.


    Regards [ATTACH]n1216073[/ATTACH]

  • Hi Carim,


    Many thanks for you assistance, I really appreciate. The macro works fantastically. However, upon application of the formula to a much larger database, some cells still retain the negative (pre-payment) values, although those pre-payments have totally cleared the debt in that cell (please refer to the message inboxed)

  • Hello,


    Noticed a mistake in the Variables definition ...


    Could you just replace As Long by As Double ...


    It will correctly adjust for the hidden decimals ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hi Carim,
    I have changed the variables, but the cells where the negative values still remain after offsetting the positive value still retain the issue. I have also noticed that the issue affects those particular cells, even if you move them within the worksheet. So, for all the other rows, the macro is perfect, but for particular cells, if a pre-payment completely offsets a positive value (receivable), that cell will retain the difference between the pre-payment and the receivable, instead of having the value zero

  • Hello,


    Have extracted out of your 990 records the remaining 25 records which do not reconcile in order to deal with their specifics ...


    Attached is your test file Version 3 ...


    Hope this will help

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Thanks Carim for this. I really appreciate. I have tried it on various scenarios and it's good.


    Out of curiosity, I wonder why the macro just didn't work on particular random rows while it was worked perfectly for the others.
    Again, thanks