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.
Sachin_Bhangale Hmmmm.... you seem to change the rules with every step we take. Have a look at the attached file. Starting from scratch, based on all the bits of information you have provided earlier.
Since no interest is to be charged on previously unpaid interest, it is necessary that payments made are split between principal amount and accumulated interest. Only then you can monitor how much of the previous interest has not yet been paid. In your latest example, a the lump sum payment of 15000 in the 3rd month after interest had been charged already, is to be seen as payment for interest 82 and 14918 as an advance payment on the principal. I've tested the calculations with a few examples. The 2nd an 3rd are made up by me illustrating a quarterly advance payment schedule and another with just some hick-ups in payments. The others are based on the earlier examples you provided.
I believe that the attached file contains a re-worked answer to all your earlier questions.
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