Need help creating a formula

[Dongkatsu]

Hello all,

I have a complex formula that i'm trying to create in Excel and was wondering if you guys could help me out on this. What the formula needs to do is look at the Current Int Rate and see if it can soft cap by comparing the max interest rate in increment of .25% and come up with the new rate which i have the answers to. First row is pretty simple due to the fact that current rate is lower than the max interest rate. Second row since max int rate is higher vs the current rate i have to increase the rate by .25 (20.5 + .25 = 20.75... if i add another .25 it'll be over 20.89 of 21 which cant go over the max rate vise versa for the last row).. is there anyone who can create a formula?

Max Int Rate Current Int Rate New Rate
21.75 20.5 21.75
20.89 20.5 20.75
20.08 20 20
19.78 20.25 19.75

[misrasomendra]

Hi,

Can you explain your fourth sample and how your output should looks like.

Regards,

[Dongkatsu]

Hello! max is interest rate is set at 19.78% but the current rate is 20.25% which is higher than 19.78% so i have to decrease the rate from 20.25 to 19.75 due to the fact that it can only increment @ .25% and cannot exceed 19.78%.

Thank you!

[misrasomendra]

And how you want your output to look like as in the first row adding 0.25 to 20.5 will give 20.75 and repeating the same process for 5 times will give max value?

Regards,

[Dongkatsu]

yes i want the first row to have 20.75 because since max rate is @ 20.75 and current rate is 20.5 I need to use max rate of 20.75..

Another example i can give is if max rate is 21.75 and current rate is 22, i have to use 21.75 since this rate increments in .25

example 2: max rate is at 19.6 and current rate is 21.25: I have to use 19.5% since rate is has to increment in .25 and the closest rate to this % is 19.5%

example 3: max rate is 22 and current rate is 21.25: It can stay @ 22%

[misrasomendra]

Try below:

In A2: Max Int
in B2: Current Int

Formula in C2:

=MROUND(MIN(A2,B2+0.25),1/4)

Regards,

[Dongkatsu]

Does not work and one formula has to work for all other scenarios. I tried example two and i get 25% :/

[misrasomendra]

Well it worked for all your posted samples.

Regards,