Jun 15 2023 12:43 AM
Jun 15 2023 12:43 AM
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 H5=0 then if H5 has a value greater than 0 then it does the calculation. the challenge is that H5=E5-G5
Jun 15 2023 01:54 AM
Jun 15 2023 04:38 AM
@Mbarak630 sent me a sample workbook (attached) and the following explanation:
In payment t[r]acker sheet is where the loan taken is entered, and the other sheet is where the payment for loan taken is entered. the payment made, whether in partials is then updated on payment tracker until all outstanding is cleared.
I want that if in one month the loan is not cleared, meaning it has an outstanding balance, then another 10% is charged for the next month, and the cycle continues for the third and the fourth month. so total to be paid is principle + 1st month interest interest + interest of the defaulted months.
Jun 15 2023 05:01 AM
To calculate the total amount to be paid, including the principle amount, interest for the first month, and interest for the defaulted months, maybe can be use the following modified formula:
=IF(H5=0,"",D5*(1 +0.1)*(1.1)^(MIN(DATEDIF(F5,TODAY(),"M"),3))+(D5*0.1*MIN(MAX(DATEDIF(F5,TODAY(),"M")-1, 0),3)))
In this modified formula, the additional part (D5*0.1*MIN(MAX(DATEDIF(F5,TODAY(),"M")-1,0),3)) calculates the interest for the defaulted months, where 0.1 represents the interest rate (10%) and DATEDIF(F5, TODAY(), "M") calculates the number of months between the start date (in cell F5) and the current date. The MIN(MAX(DATEDIF(F5,TODAY(),"M")-1,0),3) part ensures that the interest is calculated only for a maximum of 3 defaulted months.
Please note that this formula assumes the interest is compounded monthly, and the interest rate remains the same for each month.
Don't know if that helps... but trying is always good :)).
Jun 15 2023 05:48 AMSolution
Jun 15 2023 06:38 AM
Jun 16 2023 06:05 AM
@Mbarak630 Ok so assuming the prior loan must be paid off before a new loan (no overlapping payments) this should work to calculate the penalties accrued during a loan. Seems overly complicated but couldn't see a more simple way:
=LET(balanceAtDueDate, [@[Principle & Interest]]+SUMIF($C$4:$C4,[@[Customer Name]],$E$4:$E4)+SUMIF($C$4:$C4,[@[Customer Name]],$H$4:$H4)-SUMIFS(Payments[Payment Received Amount (Rs.)],Payments[Customer Name],[@[Customer Name]],Payments[Date],"<="&[@[Due Date]]), IF((TODAY()>[@[Due Date]])*(balanceAtDueDate>0), LET(months,DATEDIF([@[Due Date]],TODAY(),"M")+1, s, SEQUENCE(months), INDEX(REDUCE(VSTACK(balanceAtDueDate,0),s,LAMBDA(p,q, LET(penalty, ROUND(INDEX(p,1)*0.1,2), newbalance, MAX(INDEX(p,1)+penalty-SUMIFS(Payments[Payment Received Amount (Rs.)],Payments[Customer Name],[@[Customer Name]],Payments[Date],">"&EDATE([@[Due Date]],q-1),Payments[Date],"<="&EDATE([@[Due Date]],q)),0), VSTACK(newbalance,INDEX(p,2)+penalty) ))), 2)), 0))
so in summary line 1 creates and calcs a variable called 'balanceAtDueDate' this is based on all prior loan amounts + prior penalties - all payments to that date (i.e. will incorporate overpayments from prior loans and payments made for this load before due date.
Then (line 2) if we are past that date and the balance is >0 then it will start calculating penalties (else it goes to line 11 and returns 0)
Line 3 sets 'months' to be number of months from due date to today and then Line 4 creates sequence 's' from 1 to that number (1,2,3,... )
Line 5 sets up the recursive call to go month by month (REDUCE) starting with balanceAtDueDate and 0 penalties and looping for each 'months'
So Lines 6-8 get applied for each month after the due date and will:
Line 6 calculate the penalty due to the balance left on the prior month
Line 7 calculates the new balance going into the next month based on prior balance + penalty (line 6) - any payments made this past month
Line 8 just outputs this new balance and updates the total accrued penalty to loop back for the next month
Line 10 is actually part of the INDEX on line 5 which is after that recursive loop is done it just take out the 2nd element, the accumulated penalty
see attached. Once this accumulated penalty is available the other calculations are much easier.
Jun 19 2023 06:15 AM
Jun 19 2023 06:37 AM
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
Jun 19 2023 08:22 AM
Jun 19 2023 08:53 AM
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
Jun 19 2023 09:18 AM
Jun 19 2023 09:47 AM
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