An annual pay ARM is made for $150,000 for a 7 year term with the following term
ID: 2789292 • Letter: A
Question
An annual pay ARM is made for $150,000 for a 7 year term with the following terms:
Index rate = 1 year US Treasuries (currently at 5%)
Payments reset each year
Margin = 2%
Interest rate cap = None
Payment cap = Max 3% Increase in payments any year
Discount points = 2 percent
Fully amortizing; however, negative amortization is allowed if the payment cap is reached.
Based on estimated forward rates, the index to which the ARM is tied is forecasted as follows for the beginning of each year:
Start of year 1 (i.e. now), 5%
Start of year 2, 7%
Start of year 3, 10%
Start of year 4, 9.5%
Start of year 5, 12%
Start of year 6, 8%
Start of year 5, 12%
Compute the annual payments, loan balances, and the yield (effect rate of return) for the ARM for the five year period, assuming the loan is paid off at the end of year 7 years.
****Need this question in excel format w/ formulas****
Explanation / Answer
Year Index Margin Interest Rate BOY Balance Uncapped Payment Capped Payment EOY Balance 1 5% 1,50,000 $2,120.09 $2,120.09 $1,31,642.05 2 7% 2% 9% $1,31,642.05 $2,372.92 $2,183.69 1,16,678.30 3 10% 2% 12% 1,16,678.30 $2,595.44 $2,249.20 1,02,950.55 4 9.50% 2% 11.5% 1,02,950.55 $2,685.88 $2,316.68 86,121.03 5 12% 2% 14% 86,121.03 $2,943.41 $2,386.18 68,437.69 6 8% 2% 10% 68,437.69 $1,785.47 $2,457.76 45,637.98 7 45,637.98 Year 1 Year 2 Nper 7*12 84 Nper 6*12 72 rate 5%/12 0.42% rate 9%/12 0.75% PV $1,50,000 PV $1,31,642 FV 0 FV 0 PMT PMT(rate,nper,pv,[fv]) -$2,120.09 PMT PMT(rate,nper,pv,[fv]) -$2,372.92 Capped payment in year 2 is 2120.09*1.03 -$2,183.69 Nper 7-1*12 72 Nper 12 rate 5%/12 0.42% rate 9%/12 0.75% PMT -$2,120.09 PMT $2,183.69 FV 0 PV -$1,31,642.05 PV PV(rate,nper,pmt,[fv]) $1,31,642.05 FV FV(rate,nper,pmt,[pv]) 1,16,678.30 Year 3 Year 4 Nper 5*12 60 Nper 4*12 48 rate 12%/12 1.00% rate 11.5%/12 0.96% PV $1,16,678 PV $1,02,951 FV 0 FV 0 PMT PMT(rate,nper,pv,[fv]) -$2,595.44 PMT PMT(rate,nper,pv,[fv]) -$2,685.88 Capped payment in year 2 is 2183.69*1.03 -$2,249.20 Capped payment in year 2 is 2249.20*1.03 -$2,316.68 Nper 12 Nper 12 rate 12%/12 1.00% rate 11.5%/12 0.96% PMT $2,249.20 PMT $2,316.68 PV -$1,16,678.30 PV -$1,02,950.55 FV FV(rate,nper,pmt,[pv]) 1,02,950.55 FV FV(rate,nper,pmt,[pv]) 86,121.03 Year 5 Year 6 Nper 3*12 36 Nper 2*12 48 rate 14%/12 1.17% rate 10%/12 0.96% PV $86,121 PV $68,438 FV 0 FV 0 PMT PMT(rate,nper,pv,[fv]) -$2,943.41 PMT PMT(rate,nper,pv,[fv]) -$1,785.47 Capped payment in year 2 is 2316.68*1.03 -$2,386.18 Capped payment in year 2 is 2386.18*1.03 -$2,457.76 Nper 12 Nper 12 rate 14%/12 1.17% rate 10%/12 0.96% PMT $2,386.18 PMT $2,457.76 PV -$86,121.03 PV -$68,437.69 FV FV(rate,nper,pmt,[pv]) 68,437.69 FV FV(rate,nper,pmt,[pv]) 45,637.98