← Functions

Mortgage Amortization Schedule

How to build a mortgage amortization schedule in Excel for both fixed-rate and adjustable-rate mortgages using the PMT function.

The Excel file includes two types of collateral: Fixed and Adjustable. The fixed rate mortgage is the more popular one on the market. It is simple and predictable and is well accepted by the borrowers. The Adjustable Rate Mortgage (abbreviated as "ARM") is less popular but it serves good purpose if you expect the interest rate of the market is going to decline in the future.

The interest rate of ARM loans are calculated based on Index plus margin. Index is usually the only component that fluctuates. Index ties to certain market rates, such as LIBOR rate, treasury index, COFI, etc. Most of the residential loans are adjusted on an annual basis after the initial period, so the index usually ties to 1-Year LIBOR, 1-Year COFI, etc. Commercial loans might have more frequent adjustment to the interest rate.

The margin is regarded as the risk premium. It essentially ties to the creditability of the borrower. If the borrower is perceived to be a higher risk borrower, the mortgage company tends to charge a higher margin.

Most ARM loans come with a "teaser" rate — an artificial low rate for the initial period. You might have seen the terms 1/29, 2/28, 3/27, 5/25, 10/20 loans. If it is a 1/29 ARM loan, the first year will have the teaser rate (fixed), and the next adjustment period will be Month 13. Starting from period 13, the next 29 years the interest rate of the ARM loan will be adjusted based on a formula of index plus margin.

PMT Function

The key formula for the amortization calculation is the PMT function, which gives the amount of constant principal and interest payment.

PMT(rate, nper, pv, [fv], [type])

Arguments:

  • Rate — Required. The interest rate for the loan.
  • Nper — Required. The total number of payments for the loan.
  • Pv — Required. The present value, or the total amount that a series of future payments is worth now; also known as the principal.
  • Fv — Optional. The future value, or a cash balance you want to attain after the last payment is made. If omitted, it is assumed to be 0 (zero).
  • Type — Optional. The number 0 or 1, indicating when payments are due. 0 (or omitted) = end of period; 1 = beginning of period.