Forum Discussion
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 H5=0 then if H5 has a value greater than 0 then it does the calculation. the challenge is that H5=E5-G5
- 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
Perhaps
=IF(H5>0, IF(TODAY() > F5, D5 *(1.1)* (1 + 0.1)^MIN(DATEDIF(F5, TODAY(), "M"), 3), D5*1.1), "")
- 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.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.
