Excel formula help

[Mbarak630]

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

[#2]

@Mbarak630 

Perhaps

=IF(H5>0, IF(TODAY() > F5, D5 *(1.1)* (1 + 0.1)^MIN(DATEDIF(F5, TODAY(), "M"), 3), D5*1.1), "")

[#3]

Thanks for your assistance,
However the formula does not solve the one mystery am stuck on. just to be more clear and precise, what am doing is amount borrowed is charged 10% interest, for 30 days, and next month if not paid back or not completely cleared then another 10% is charged, and for the 3rd month and 4th month.

If cleared in the 2nd, or 3rd or 4th month then amount to repay should be inclusive with the interests, and not just return nil showing nothing has been charged.

[#4]

@Mbarak630 

Please explain what each of the cells involved contains.

Preferably, also attach a sample workbook, or make it available through OneDrive, Google Drive or similar.

[#5]

@Mbarak630 

@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.

---

I'm terrible at financial stuff. Anyone else? @JoeUser @SergeiBaklan @mtarler @mathetes @NikolinoDE @Riny_van_Eekelen @Peter Bartholomew 

[#6]

@HansVogelaar , not my territory

[#7]

@HansVogelaar 

to @Mbarak630 

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 :)).

[#8 · Accepted solution]

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

[#9]

ok not sure if @Mbarak630 marked my post just to indicate it was correct but still needs the formula or if just the idea of using REDUCE was enough to get Mbarak to know what they needed and they created the formula.
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.

[#10]

@mtarler 

Yes you are right, however i do not know how to go about the formula

[#11]

@mtarler 

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.

[#12]

@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.

[#13]

@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

[#14]

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.

[#15]

@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 

[#16]

@mtarler 

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

[#17]

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).

[#18]

@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 

[#19]

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.

[#20]

@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