Like the previous post this worksheet calculates the APR, but for an adjustable rate mortgage or ARM. The difference between the fixed rate and the ARM is that the ARM cash flow is based upon reaching the fully-indexed rate, given the information available when the loan was made, and assumes it stays at the fully-indexed rate for the remaining term of the loan. The first five inputs are the same as we went over on the fixed rate mortgage. The Fixed Years however, is where the inputs start to change. Fixed Years is the number of years the rate will be fixed at the Initial Interest Rate. Index is the rate on the current market index (Treasury securities, LIBOR, SF Federal Home Loan Bank cost of funds, etc.). The Index rate will change over time. Margin is the amount of interest added on to the Index. Some ARMs will limit the amount the initial rate can change at the first reset. The example below has 1% for the Initial Period Cap, which means that the rate can only go up to 7% at the first reset. Enter a zero if no Initial Period Cap. There may also be Subsequent Period Caps, which means that any rate adjustment after the initial adjustment has a cap. The example below has 2%. If no cap enter zero. Life Floor is the most the adjustable rate can fall. The Life Cap of 5% in the example does not mean the rate can only move up to 5%. It means that the rate can never exceed 5% added to the initial rate, or 11% in the example.
A chart of the cash flow that will be used to calculate the APR is also shown. In our example, the initial rate of 6% stays for 5 years. Then the initial cap of 1% moves the rate to 7% for the first year. Then it moves up to the fully-indexed rate of 7.625% and stays there until the end of the loan.
Download workbook “APR-ARM” from:
Downloads Written in Excel 2013