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:

http://www.pistulka.com/Excel_Shared/

Downloads Written in Excel 2013

DanielAny way to use your calculator to run multiple scenarios at once …

Don PistulkaPost authorHi,

This calculator was meant to be used to calculate the initial APR (Truth-in-Lending) for lending institutions offering new adjustable rate mortgages. Under normal circumstances, a lender would have no more than a few versions of ARM’s on their pricing sheets. It is not really meant for portfolio analysis. How many scenarios are you considering?

samHi Don,

Thanks again for sharing this spreadsheet. I’ve been comparing the output value to the one provided by bankrate.com, and there a nominal difference in various scenarios ( 3 to 6 basis points). The input data is the same on the bankrate.com and your excel spreadsheet. Can you advise why there may be a difference?

Don PistulkaPost authorHi Sam,

Please send me the program with your data and a copy of the Bankrate data and the output.

Don

Don PistulkaPost authorSam,

I went to another website calculator (http://calculator.me/real-estate/arm-apr.php) and they gave me an APR of 4.294%, which is the same as my spreadsheet.

Amitthanks a lot Don. i was figuring out calculation your template made it easy.. Thank you.

Don PistulkaPost authorAmit,

You are welcome. Thanks for your comment.

StevenThis is a super helpful way to calculate an APR on an Adjustable rate Mortgage in excel. Thank you for putting this together and making it simple enough for anyone to use, but also complex enough to be used by someone working at a financial institution.

Don PistulkaPost authorThanks

Don PistulkaPost authorEnjoy