Write an excel spreadsheet to amortize a hybrid ARM. Use the (monthly) index rate supplied in the accompanying spreadsheet. Assume all rates are quoted as MEYs. Make sure that your program can handle (send me the file with inputs set as marked in […], but note that I will want to be able to change them):
1. A periodic interest rate cap (only; do not bother with lifetime or initial rate caps, not bother with payment caps). Also, for simplicity, ignore the fact that caps usually come with “floors.” [1%];
2. Different rate reset periods [1yr intervals];
3. Different initial fixed rate period length [3yrs], along with the rate for this period [3.5%];
4. Different mortgage terms (up to 30 yrs) [15yrs];
5. Different amortization terms [30yrs];
6. The margin [2.5%]; and the
7. Loan amount [$100k].