Essentials ARM Calculator Spreadsheet Template for Google Sheets & Excel

The loan, the rate and the terms that govern how the rate can move. Loan amount, initial rate, how long that rate is fixed for, the full term and the first payment date go in the top block. Underneath them the four figures that actually define an adjustable mortgage: the periodic cap limiting any single adjustment, the lifetime cap limiting the total increase, the current index and the margin the lender adds to it. Those four are what the paperwork means by a 5/1 with a 2/5 cap structure, and they are the difference between an ARM you can size and one you are guessing at. The sheet reports the maximum possible rate directly - the initial rate plus the lifetime cap - which is the ceiling written into the agreement rather than a projection. Beneath that it reports the initial payment, the interest the loan would carry if the initial rate simply never changed, the projected interest and total cost using the index as entered, and the worst case if the rate climbs to its ceiling and stays there.

A row per year, each carrying the rate in force that year, the monthly payment, the annual payment, the interest for the year, the balance at the end of it and a worst-case interest figure beside the projected one. The rate column is where the caps do their work. Once the fixed period ends, each year's rate is the index plus the margin, held within one adjustment step of the year before and never above the lifetime ceiling - which is why a rate rise arrives as a series of steps rather than in one move. The payment is re-amortised over the years remaining, so it climbs faster than the rate does. The worst-case column runs alongside every year rather than as a single figure at the end, which is what makes it usable: it shows when the difference between the projection and the ceiling actually starts to bite.

Six figures. The top row carries the payment during the fixed period, the maximum rate the agreement allows and the total interest projected on the current index. The row beneath sets the projected total cost against the worst-case total cost, and adds the interest the loan would carry if the initial rate never moved - the baseline the other two are read against. The projection holds the index where you entered it, which is worth saying plainly: it is arithmetic on an assumption, not a forecast. The worst case is the more solid of the two figures, because the ceiling is written into the contract rather than into anyone's expectations. A line chart plots the projected monthly payment year by year, which is where the step pattern the caps produce becomes visible.