Forum Discussion
Excel formula help
- 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
Maybe Mbarak630 could confirm or correct this
If this is the case I think we could use LAMBDA function with REDUCE to do this
on another note I was looking at the sheet and noted the formula in G5, which I think is overly complicated, but also bring another factor in because the payment page only identifies the customer and not the specific loan reference so a customer with multiple loans has to be considered. Please note that I would highly recommend adding the loan reference to the payment sheet (or replace customer name with loan reference). Then the customer can and should identify which specific loan they are making payment on. If you have some sort of policy that they must pay in order then YOU should be identifying which loan that payment is on which will get messy especially when a payment finishes 1 or more loans and carries into the next.
I think it would also be better to use additional columns to show the cumulating values to better understand where the extra amounts (penalties) are coming from and where the payments are going; especially when the customer calls with questions. So maybe on the payments page there could be added column(s) to track outstanding balance (pre-payment), penalties (since last payment), new outstanding balance and such. These added columns could make the formulas more reasonable and provide more transparency.
The formular in G5 was pulling payments paid and if the loan is cleared and there be overpayment then the extra amount will be used to pay for the next loan.
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.
