Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
adjustable interest rate with caps
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
adjustable interest rate with caps
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" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
adjustable interest rate with caps
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" wrote: 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" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
adjustable interest rate with caps
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. "jrenglish86" wrote in message ... 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" wrote: 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" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adjustable Rate APR | Excel Discussion (Misc queries) | |||
to compute interest rate from principal and interest amount | Excel Discussion (Misc queries) | |||
How do I calculate APR for an adjustable rate mortgage? | Excel Discussion (Misc queries) | |||
Is there an adjustable rate amortization schedule in Excel? | Excel Worksheet Functions | |||
APR - Annual Percentage Rate to Actual Interest Rate | Excel Worksheet Functions |