Assume that the lender offers a 30-year, $150,000 adjustable rate mortgage (ARM) with the following terms:

Initial Interest Rate = 7.5%

Index = one-year Treasuries

Payments reset each year

Margin = 2%

Interest rate cap = 1% annually; 3% lifetime

Discount points = 2%

Fully amortizing; however, negative amortization allowed if interest rate caps reached.

Based on estimated forward rates, the index to which ARM is tied forecasted as follows: Beginning of year BOY2 = 7%; BOY3 = 8.5%' BOY4 = 9.5%, BOY5 = 11%.

Compute the payments, loan balances, and yields for the ARM for the five-year period.

****Please in Excel as I need to learn how to use the formulas and learn which ones to use*** Thank you!