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
Feb 26, 2020

Riny_van_Eekelen 

Hi Yes,

 

There is already one formula but it seems not right all the time and hence, I needed a new one. More simpler and correct one.

 

I started to apply interest from 5th month, prior to that I was not charging any interest.

 

Thanks.

Resources