Home » Blogs » k33jakha's blog

Need formula assistant for calculation of Intrest:

k33jakha's picture
Submitted by k33jakha on 29 June, 2014 - 02:27

as attached images, I need to calculate interest rate according to thecondition.

Help Me!

Excel Interest calculation
»
Almir's picture

Multiple nested IFs

Submitted by Almir on 2 July, 2014 - 22:20.

There are plenty of ways to accomplish this (VLOOKUP and alike), but try this: copy bellow formula to your D2 cell.

By the way, there is inconsistency, in the way that if the amount is greater than 50000, it considers all amounts greater than that. So, I believe you thought of amounts between 50000 and 100000.

Second, you say: amount equal to 800000, but there is no condition for amounts between 500000 and 800000, so I think you thought of that.

Here is formula:

=IF(AND(C2<=24;B2<=50000);5%;
IF(AND(B2<=50000;C2>24);6%;

IF(AND(C2<=24;B2<100000);6%;
IF(AND(B2<100000;C2>24);7%;

IF(AND(C2<=24;B2=100000);7%;
IF(AND(B2=100000;C2>24);8%;

IF(AND(C2<=24;B2<500000);8%;
IF(AND(B2<500000;C2>24);9%;

IF(AND(C2<=24;B2<=800000);9%;
IF(AND(B2<=800000;C2>24);10%;

IF(AND(C2<=24;B2<1000000);10%;
IF(AND(B2<1000000;C2>24);11%;

IF(AND(C2<=24;B2>=1000000);15%;
IF(AND(B2>=1000000;C2>24);16%;

))))))))))))))

»
k33jakha's picture

RE:

Submitted by k33jakha on 4 July, 2014 - 06:57.

Thanks Almir,

i think it works, let's check in my sheet. however i try this
=IF(C25>24,
IF(AND(B25<=50000),5+1,
IF(AND(B25>50000,B5<100000),6+1,
IF(AND(B25=100000),7+1,
IF(AND(B25>100000,B25<500000),8+1,
IF(AND(B25=800000),9+1,
IF(AND(B25>800000,B25<1000000),10+1,15+1)))))),
IF(AND(B25<=50000),5,
IF(AND(B25>50000,B5<100000),6,
IF(AND(B25=100000),7,
IF(AND(B25>100000,B25<500000),8,
IF(AND(B25=800000),9,
IF(AND(B25>800000,B25<1000000),10,15)))))))

this formula also works but in some cell only i don't know how it happen? and some cell notify the error like #REF!. I don't know when the condition is show ??

»