Assignment 4B: Complex ARM Amortization Schedule Step 1 Step 2 Use the same home from your Fixed Rate Mortgage (FRM) problem. Determine Payments of Principal and Interest (P&I) (a) You will put down 10 percent and finance the balance with a 1/1 LIBOR ARM for 30 years with a 2/8 Cap and a 2.25% 1-year teaser. (b) You will make monthly payments, and the current spread is 1.5% (C) LIBOR Rates at loan issuance were 2.75%, but can range from 2.50 to 10.50 percent over the life of the mortgage (d) LIBOR cannot change by more than 0.5% per month. That is LIBOR Monthly Delta (A)=0.5% Hint: Use an IF Statement with RANDBETWEEN to create the LIBOR rate Hint Part of my function for this is RANDBETWEEN(((Previous-Libora)*100000).(Previous+LiborA)* 100000)>100000) (c) The adjustable rate is calculated as LIBOR plus the spread subject to any annual or lifetime caps Hint: Use the MIN funtion to find the correct adjustable rate Hint: Note that the Adjustable Rate is subject to a one year teaser of 2.25% (1) The adjustable rate should should only adjust annually Hint: Payments 1-12 are at the Teaser rate. Payments 13-24 are at the correct Adjustable Rate at the end of Year 1 (EOY 1 = 12) Step 3: Since you did not put down a full 20 percent on the home, you will have to pay Private Mortgage Insurance (PMI) Determine PMI (a) PMI is calculated as 75 basis points (0.0075 percent) of the value of the initial loan amount (b) PMI is paid monthly, so once you compute the value in step (a), divide it by 12 to determine monthly PMI (c) PMI automatically terminate when the loan to purchase price ratio reaches 78 percent (d) A borrower can request that PMI terminate when the loan to purchase price ratio falls below 80 percent (e) Assume that you will terminate PMI when you have a loan that is 80 percent of your purchase price 2. Complete the following Table Buyer's Name: Property Street Address: Property City, State, Zip Purchase Price: 250.000 Down Payment: 25,000 Loan Amount: 225,000.00 1-Year Teaser Rate: Spread: Annual Cap LIBOR At Loan Issuance: Assumed LIBOR Minimum LIBOR Maximum LIBOR Monthly Delta (4) Lifetime Cap Origination Discount Points Paid: Other Loan Closing Costs Fees Total Closing Costs Initial Monthly P&I Payment: Monthly PMI Payment Annual Property Taxes (Year 1) Annual Homeowner's Insurance (Year 1); Monthly Escrow Payment: Initial Total Monthly Payment: APR 19 Nord Assignment 4B: Complex ARM Amortization Schedule Step 1 Step 2 Use the same home from your Fixed Rate Mortgage (FRM) problem. Determine Payments of Principal and Interest (P&I) (a) You will put down 10 percent and finance the balance with a 1/1 LIBOR ARM for 30 years with a 2/8 Cap and a 2.25% 1-year teaser. (b) You will make monthly payments, and the current spread is 1.5% (C) LIBOR Rates at loan issuance were 2.75%, but can range from 2.50 to 10.50 percent over the life of the mortgage (d) LIBOR cannot change by more than 0.5% per month. That is LIBOR Monthly Delta (A)=0.5% Hint: Use an IF Statement with RANDBETWEEN to create the LIBOR rate Hint Part of my function for this is RANDBETWEEN(((Previous-Libora)*100000).(Previous+LiborA)* 100000)>100000) (c) The adjustable rate is calculated as LIBOR plus the spread subject to any annual or lifetime caps Hint: Use the MIN funtion to find the correct adjustable rate Hint: Note that the Adjustable Rate is subject to a one year teaser of 2.25% (1) The adjustable rate should should only adjust annually Hint: Payments 1-12 are at the Teaser rate. Payments 13-24 are at the correct Adjustable Rate at the end of Year 1 (EOY 1 = 12) Step 3: Since you did not put down a full 20 percent on the home, you will have to pay Private Mortgage Insurance (PMI) Determine PMI (a) PMI is calculated as 75 basis points (0.0075 percent) of the value of the initial loan amount (b) PMI is paid monthly, so once you compute the value in step (a), divide it by 12 to determine monthly PMI (c) PMI automatically terminate when the loan to purchase price ratio reaches 78 percent (d) A borrower can request that PMI terminate when the loan to purchase price ratio falls below 80 percent (e) Assume that you will terminate PMI when you have a loan that is 80 percent of your purchase price 2. Complete the following Table Buyer's Name: Property Street Address: Property City, State, Zip Purchase Price: 250.000 Down Payment: 25,000 Loan Amount: 225,000.00 1-Year Teaser Rate: Spread: Annual Cap LIBOR At Loan Issuance: Assumed LIBOR Minimum LIBOR Maximum LIBOR Monthly Delta (4) Lifetime Cap Origination Discount Points Paid: Other Loan Closing Costs Fees Total Closing Costs Initial Monthly P&I Payment: Monthly PMI Payment Annual Property Taxes (Year 1) Annual Homeowner's Insurance (Year 1); Monthly Escrow Payment: Initial Total Monthly Payment: APR 19 Nord