Forum Discussion
Solved
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...
- 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.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 PeterBartholomew1
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 :)).
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.
