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.
Riny_van_Eekelen I'm sorry that I failed to explain what I'm actually looking for. Below is the details, what I'm looking for. Check if you can help to create a own file using those requirements.
1. Every month on 1st day bill will generate. User will have to clear that bill amount till month end.
2. If user failed to pay bill till month end, he will be charged 21% annual interest on next month bill on total outstanding.
3. Total outstanding should have , arrears, current month maintenance charge, interest on last month outstanding excluding previous interest.
4. We cannot charge interest on advance payment. That advance payment should get be used to pay further months maintenance amount.
5. Only when advance payment is clear we can start charging interest if bill not paid till month end.
Thanks
Sachin_Bhangale I believe my latest file does exactly all of that. So, if it does not, you have lost me.
I've attached a slightly reworked sheet with some round numbers and examples. Please indicate where it goes wrong and why!
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.
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.
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.
Riny_van_Eekelen Hi, I have added 2 more columns (Payment & Total) in your file and also modify formulas.
"Payment" column is where I can simply enter total payment amount and then I will automatically get value in Principal payment & Interest Payment.
"Total" column will give me total outstanding of the member which needs to pay before month end.
I think that file should work for me now. If I need any more modification I will surely let you know.
Please check attached file and if you have any suggestions, please let me know.
Thanks.