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.

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Feb 15, 2020

Sachin_Bhangale You seem to have formulae in place already, except for cell G6. What is it that you need? Other formulae or just simpler formulae? Also wondering what the rules for calculating interest are. Does it only start in the 5th month? Only on arrears excluding previous month's interest?

 

Have a look at the attached file and let me know if it even comes close to what you need. If not, you need to clarify your needs.

Sachin_Bhangale's avatar
Sachin_Bhangale
Copper Contributor
Mar 10, 2020

Hi Riny_van_Eekelen 

 

Thanks for the file. I was busy hence, didn't get time to go through your file.

 

This file is quite near to what I'm looking for however, it only deduct immediate previous interest charges instead of the complete charges. So if arrears have interest of previous 2-3 months, that should be deducted and then we can apply interest on the rest amount.

 

I've attached a file wherein left side calculation is done using your formula but I need result as per the table available at right side.

 

Thanks.

    • 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.

Resources