Forum Discussion

Sachin_Bhangale's avatar
Sachin_Bhangale
Copper Contributor
Feb 15, 2020
Solved

Interest Calculation

Hi Team,   Please help me to understand what formula I can add in attached file to get results reflecting in G column. Interest is calculated on 21% per year. It should be round up to next value....
excel
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Mar 16, 2020

    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's avatar
Sachin_Bhangale
Copper Contributor
Mar 16, 2020

Thanks for the quick reply Riny_van_Eekelen 

 

Your formula works fine only if the interest amount was not zero. If in between any user paid all dues and then again he miss to pay from next month, that arrears are deducting old interest as well.

 

Old interest should be deducted only when it is in continuation.

 

Please find attached file. Left side is as per your formula and right side is the correct output I'm looking for.

 

Thanks.

Sachin Bhangale

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Mar 16, 2020

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's avatar
    Sachin_Bhangale
    Copper Contributor
    Mar 17, 2020

    Riny_van_Eekelen Hi again,

     

    One error. If someone pay in advance he/she is still being charged for interest.

    Interest should be only on positive (outstanding) amount and not on negative (advance) amount.

     

    Please find attached file. I'm not sure if the right side of the file is exact but I've calculated in manually and it should be the correct output.

     

    Thanks & Regards

    Sachin Bhangale

    • Riny_van_Eekelen's avatar
      Riny_van_Eekelen
      Platinum Contributor
      Mar 17, 2020

      Sachin_Bhangale Please see attached. It's my previous file including your latest example. I notice that you deviated from the "no interest policy" during the first 4 months and I believe that your amount for Feb-21 (14) is incorrect, or at least inconsistent with all the other interest calculations. My formula suggests a -zero- amount here. Up to you to decide which is correct.

      • Sachin_Bhangale's avatar
        Sachin_Bhangale
        Copper Contributor
        Mar 18, 2020

        Riny_van_Eekelen Hi Riny, I've started Interest charges from 5th months hence, with my old file you may find no interest for first 4 months. Last file which I shared is the new one for financial year 2020-2021. Hence, in that I included interest from the first month itself. 

         

        Currently, I put zero as opening balance in first month however, that will get change once I close this year file and manually update it. The reason I've added 14/- as interest as previous month have closing balance of 782/-. If any member have outstanding greater than zero, on that amount he will be charged interest in next month.

         

        If there a way we can get that Arrears amount in negative & charge interest only on positive value then I think we can come to a proper formula.

         

        Thanks. Looking forward to hear from you.

         

        Thanks & Regards

        Sachin Bhangale

  • Sachin_Bhangale's avatar
    Sachin_Bhangale
    Copper Contributor
    Mar 16, 2020

    Riny_van_Eekelen You are ROCK STAR.

     

    Thanks a lot Riny. So far I've tested this to 12-13 flats and it worked perfect. This is the thing I was looking for. So many of my friends said this is not possible in Excel and for that we need some accounting software. I was sure this forum can help me.

     

    Thank you so much again. If any issue or need any modification, I will let you know.

     

    Thanks & Regards

    Sachin Bhangale

Resources