adjustable interest rate with caps

[jrenglish86]

I am making a mortgage spread for an adjustable rate mortgage. Every row is a
month, 1-360. I am adding a spread percentage (2.25%) to a base percentage
(LIBOR, if you know what I am talking about) to get the interest rate for the
mortgage . The base percentage is changing every 12 months using this
formula:

=IF(MOD(A76,12)<>1,B75,(RANDBETWEEN(4,10)+RAND())/100)

The interest rate cannot change more than 3% in a year and cannot change
more than 6% from the starting rate (meaning it can't go below 0 or above 12)
in the life of the loan . For example if the base percentage is 10%+spread
(2.25%)=12.25%. This is above the max cap of 12%, so the formula needs to
make it 12%. Also, if the year before the base percentage was, say, 4%, then
4%+2.25%=6.25%. The interest rate can't change more than 3% in a year,
though, so the formula needs to take this cap into account; in this situation
it should say 9.25%. Since for the project I have to randomly generate base
percentages, the formula has to have these caps in it.

I hope I have been clear enough. Any suggestions?

 

[Fred Smith]

OK, for this project, you are randomly generating LIBOR every 12 months. No
problem with that.

To deal with your various other conditions, you just need to add the correct
Max and Min formulas.

Let's assume your generated interest rate is in A75.

Not less than 0 would be: =max(0,a75)
and not more than 12 would be: min(.12,a75)
Together, they would be: =max(0,min(.12,a75))

If you want to base it on "no more than 6% difference from the starting
rate" and the starting rate is in a1, use:

=max(a1-.06,min(a1+.06,a75))

You can use the same Max/Min combinations to control "no more than 3% change
from the previous year".

Regards,
Fred.

 

[jrenglish86]

Fred,

Thanks for your reply. That will definitely work; the only problem is I
can't figure out how to put both of the caps into the same formula.

 

[Fred Smith]

It's just an additional limitation in the Max/Min formula.

Your minimum is the higher of Starting-6% and LastYear-3%
Your maximum is the lower of Starting+6% and Lastyear+3%

Assume LastYear is in B74, and you have:
=max(a1-.06,b74-.03,min(a1+.06,b74+.03,a75))

Regards,
Fred.