Forum Discussion
Interest Calculation
Sachin_Bhangale Aha. I see the problem now. Propose that you change the structure of your table a little bit. Use "Opening Balance" and "Closing Balance" to calculate the total amount due. But introduce a column "Arrears" that will be the basis for interest calculations from the first month. I did this in the attached file and conclude that this produces the results that you want.
Hi Riny,
I need to have a formula in "Principal Payment" & "Interest Payment" columns.
I have 7 examples so I will explain them one by one below.
N2=B2+E2+F2+H2-J2; O2=C2+G2-K2; P2=D2+E2+F2+G2+H2-I2
Scenario 1 : As there is no "Payment", hence, "Principal Payment" & "Interest Payment" should be zero.
Scenario 2 : "Payment" is less than "Opening Principal + Maintenance + NOC + Ch. Bouce", hence, "Principal Payment" should be exact value entered in "Payment" column & "Interest Payment" should be Zero.
Scenario 3 : "Payment" is greater than "Opening Principal + Maintenance + NOC + Ch. Bouce", hence, "Principal Payment" should have total of "Opening Principal + Maintenance + NOC + Ch. Bouce". "Interest Payment" should have "Payment - Principal Payment" amount.
Scenario 4 : "Payment" is greater than "Opening Principal + Maintenance + NOC + Ch. Bouce", hence, "Principal Payment" should have Total of "Opening Principal + Maintenance + NOC + Ch. Bouce". "Interest Payment" should have "Payment - Principal Payment" amount.
Scenario 5 : "Payment" is greater than "Opening Principal + Maintenance + NOC + Ch. Bouce". However, total payment is more than "Arrears + Maintenance + NOC + Interest + Ch. Bouce" hence, in "Interest Payment" we should have "Opening Interest + Interest" & rest value should be under "Principal Payment".
Scenario 6 : "Opening Principle" is negative (advance payment) and there is no payment done in that month hence, "Principal Payment" & "Interest Payment" should be zero.
Scenario 7 : "Opening Principle" is negative (advance payment) and more than "Maintenance + NOC + Ch. Bouce" hence, "Principal Payment" should have actual value of "Payment" column. As there is no "Opening Interest" & "Interest" hence, "Interest Payment" should have zero.
If you need any more clarification which can help you to get a formula, please let me know.
Thanks.
Sachin_Bhangale Hi again! Please see the attached file. I added two helper columns in I and J. That way, the final formula becomes easier to read. It turned out that I needed two IF statements. One for payments less than the principal (including zero payments) and one for payments exceeding the principal. Tested these in each of the 7 scenarios and then combined them into one nested IF that works for all scenarios. Demonstrated in rows 24 and below.
Sachin_Bhangale You're welcome!
Riny_van_Eekelen Thanks again.
You are amazing!!!!
I've tested it with almost 20+ examples and that helps to give the perfect solution. Thanks a lot.