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. Even Alan Greenspan, the previous Federal Reserve Chairman, recommended to the public and quoted that it could save the borrower (mortgagor) a substantial amount of money. However, the saving comes with its own risk. The market interest rate is essentially unpredictable, especially when stretching out for 15 to 30 years.
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. If the ARM loan is adjusted on a monthly basis, the Index might be 1-Month LIBOR. If the ARM loan is adjusted on a quarterly basis, the Index might be 3-Month LIBOR, etc. The loan agreement will specifically point out what the Index is, where to find it, and at what date and time the Index will be used.
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 margins are fixed but I did see some loans with variable margins.
Most ARM loans come with a “teaser” rate. A teaser rate is an artificial low rate for the initial period. It is the way that mortgage companies try to make the loans more attractive and lure more borrowers to take them. The teaser rate is usually much lower than the interest rate currently charged by a fixed rate loan. 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, first year will have the teaser rate (fixed), and the next adjustment period will be Month 13 (the one year anniversary). 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. If it is a 5/25 ARM loan, the next adjustment period will be Month 61 (the five year anniversary). Same logic is applied for 2/28, 3/27, and 10/20 ARM loans. Please note that the teaser rate will be higher if a borrower wants a longer period of the teaser rate. If the ARM loan is 10/20, the teaser rate will be very close to a fixed rate loan.
The above illustration is to explain how I constructed the Adjustable Rate worksheet. Column B and C let you update the index and margin manually. Other cells in blue color are for manually update as well.
The key formula for the amortization calculation is the PMT function which gives the amount of constant principal and interest payment.
Syntax
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 fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
Type Optional. The number 0 (zero) or 1 and indicates when payments are due.
0 or omitted At the end of the period
1 At the beginning of the period
