Forum Discussion

Mbarak630's avatar
Mbarak630
Copper Contributor
Jun 15, 2023

Excel formula help

kindly assist on formula below in cell E5   =IF(TODAY() > F5, D5 *(1.1)* (1 + 0.1)^MIN(DATEDIF(F5, TODAY(), "M"), 3), D5*1.1)   how do I formulate it to first check if (outstanding amount) column...
Formulas and Functions
  • mtarler's avatar
    mtarler
    Jun 15, 2023
    I'm not sure but I think what the OP is getting at is that the partial payments need to be taken into account such that the 10% is applied to the remaining balance at that time. So if the original loan is 1000 and 10% interest so principle & Interest is 1100 that has to be paid by due date but if only 500 was paid then 600 is outstanding balance and would get 10% penalty so new loan balance would be 660 the month after the due date and if they paid 260 then the next month would have another 10% penalty but only on the remaining 400 so the new balance would be 440.
    Maybe Mbarak630 could confirm or correct this
    If this is the case I think we could use LAMBDA function with REDUCE to do this
Mbarak630's avatar
Mbarak630
Copper Contributor

mtarler 

Thanks alot, it worked as desired.

Am very greatful to you and everyone who offered assistance.

 

I hope others my learn from the knoledge given

mtarler's avatar
mtarler
Silver Contributor
Jun 19, 2023
I'm glad it is working as desired and I hope the explanation was help/enough for you to have some understanding of how it is working.
  • Mbarak630's avatar
    Mbarak630
    Copper Contributor
    Jun 19, 2023

    mtarler 

    You are correct, i was just trying to see if a customer has two loans, what was the effect and i saw the issues arising, and so to avoid it then only one loan for a customer and must be cleared before another is taken.

     

    Also, its better if I use the Loan ID, and the penalties were necessary because if loan wasnt paid by due date, then the penalties apply.

     

    I will replace customer name with Loan ID 

     

     

     

  • mtarler's avatar
    mtarler
    Silver Contributor
    Jun 19, 2023

    Mbarak630  OK i re-worked the formula to track the amount paid toward this loan and hence only apply discount on the penalty based on +pays against this loan and cap penalties if pay>base+penalties.  Basically this new version should allow for multiple loans 🙂

    try it out ...

     

  • Mbarak630's avatar
    Mbarak630
    Copper Contributor
    Jun 19, 2023

    mtarler 

    I wouldn't say that i fully understood, but after testing the first row 5 and and can say that it was working as i was expecting.

     

    I changed the invoice dates did calculations manualy and saw i was getting same results.

     

    I changed the invoice dates for both row 5 and row 6, as 18-feb-23, same customer name, loan amount is same,  however cell H6 and I6 have values different to H5 and I5 .

     

    This got me a little confused 

  • Mbarak630's avatar
    Mbarak630
    Copper Contributor
    Jun 19, 2023

    mtarler 

    I noticed where the issue was, using the customer name was not the unique identifier, i will replace it with the Loan Reference

  • mtarler's avatar
    mtarler
    Silver Contributor
    Jun 19, 2023
    yes, as I noted in an earlier message "Please note that I would highly recommend adding the loan reference to the payment sheet (or replace customer name with loan reference). " That said I did try this and it was working but I did make the assumption as you noted that for a given customer "the prior loan must be paid off before a new loan" is issued. If the loans overlap that would cause an issue. IF you change this to be based on a specific loan ID then the formula can be simplified as the whole summation of prior loans and payments and penalties wouldn't be needed.
    That all said, you say "I changed the invoice dates for both row 5 and row 6, as 18-feb-23, same customer name, loan amount is same, however cell H6 and I6 have values different to H5 and I5 ." but I don't know what you are saying. Is there something wrong with the calculation? You should expect rows 5 and 6 to be different as they are for 2 different loans and if they are the same customer then I5 better be zero based on the above assumption while I6 will depend on if it paid off yet. Column H is specifically the 'Applied Penalties' accrued for that specific loan (not the total for the customer).
  • mtarler's avatar
    mtarler
    Silver Contributor
    Jun 19, 2023
    If I remember correctly the problem is that this formula doesn't track which loan a given payment is being applied to. Technically the 2 loans could overlap as long as the first is paid off before the DUE date of the next. An additional conditional on the penalty accrual function could be added to check if the NET total payment has exceeded all prior loan commitments (i.e. prior loans paid off) and not apply payments until that happens.
  • Mbarak630's avatar
    Mbarak630
    Copper Contributor
    Jun 19, 2023

    mtarler 

    yes, payments made were and the allocation was given priority to the earlier loans.

     

    I think best is to avoid multiple loans running for one customer, but stick one loan per customer and until it is fully paid inclussive of all penalties and there is no outstanding amount, then a new loan can be