pls answer ques. 3 & 4...work is required to be shown on excel if possible. Thank you
Project Assignment No. 1 PROBLEM 2- Maria is interested in financing the purchase of a property for sale for $100,000, and has $5,000 to invest as a down payment. The Best Mortgage Company is offering a new mortgage loan product called, the Stable Mortgage. The mortgage loan is comprised of both a fixed interest rate and adjustable interest rate components. . The Stable Mortgage terms she is receiving from Best Mortgage Company are: There are 2% in discount points, and allows 75% of the mortgage loan amount to be at a fixed rate of interest, while 25% to be at an adjustable rate of interest. The fixed portion of the loan is for 30 years at 10.5% interest. The adjustable rate portion of the loan also has a 30-year term, and has the following terms: o A9% initial interest rate using an index of one-year U.S. Treasuries with a 200bps margin. o Monthly mortgage loan payments adjust each year. The adjustable portion of the loan neither has an interest rate or payment cap. There is no prepayment penalty. The projected one-year U.S. Treasury rates are 10% for (the beginning of) year 2; 11% for (the beginning of) year 3; 8% for (the beginning of) year 4; and 12% for the beginning of) year 5. 3. What are Maria's monthly payments and end-of-year mortgage loan balances for the first five years? (Note: while a calculation is necessary, it is acceptable to calculate amortization on an annual basis.) 4. Now, what are Maria's monthly payments and end-of-year mortgage loan balances for the first five years assuming that there is a 9.5% initial interest rate, AND, Maria being a strong negotiator, was able to get an annual 1% interest rate cap. (Note: while a calculation is necessary, it is acceptable to calculate amortization on an annual basis.) (7) (8) (6) Monthly Amortization Annual Amortization FOY Balance WNLO + 53 QUESTION 4 54 55 Total loan Amount 50 56 Polnts 0.00% 57 Fixed rate portion 0% 58 Annual Interest rate 0.00% 59 Loan term (years) 60 61 (1) 62 (2) (3) 15) (4) Annual interest Monthly Monthly 63 Year BOY Balance Rate Interest Rate Payments Interest Amt 64 65 1 66 2 67 3 6B 4 69 5 70 71 72 73 Floating rate portion 0% UST-Year 2 0.00% 74 Annual Interest rate 0.00% UST-Year 2 0.00% 75 Interest rate cap 0.00% UST-Year 2 0.00% 76 Interest rate margin 0.00% UST-Year 2 0.00% 77 Loan term (years) 0 78 79 30 (1) 12) (3) 15) Capped Interest Monthly Monthly 81 BOY Balance Rate Interest Rate Payments Interest Amt 82 0 83 1 84 2 85 3 BG B7 5 8 89 Mortgage Summary (Total of fixed rate plus adjustable rate portions) 90 01 Your BOY Balance Total Payments OY Balance 0 1 2 3 9G 4 Problom 1 Problem 2 Problem 3 Problem 4 7) (8) 16) Monthly Amortiration Annual Amortization Balance SNER PROBLEM 1 Ann would like to buy a house. It costs $1,000,000. She will make an $80,000 down payment. She will take out a 30-year, fully amortizing, fixed rate mortgage (FRM) for $920,000. It will have equal, level monthly payments. The annual interest rate is 3.41%. She must pay 2% In discount fees at the closing of the loan. Notes: the home is bought and the loan is taken in month O, the first payment is due in month 1. Also, Ann's net cash flow each month is equal to the total cash inflows minus total cash outflows that month. Ann forecasts three possible scenarios for house price appreciation (HPA): A. Optimistic Case: 9% annual HPA, hence 9/12% monthly HPA B. Base Case: 4.5% annual HPA, hence 4.5/1296 monthly HPA C Pessimistic Case: 0% annual HPA, hence 0/12% monthly HPA 1. Prepare a mortgage loan amortization schedule for Ann using the Excel spreadsheet. 2. Assume Ann will make the required monthly payment every month for 30 years. How much home equity will Ann have after 15 years (180 months) of payments under each of the three HPA scenarios? Project Assignment No. 1 PROBLEM 2- Maria is interested in financing the purchase of a property for sale for $100,000, and has $5,000 to invest as a down payment. The Best Mortgage Company is offering a new mortgage loan product called, the Stable Mortgage. The mortgage loan is comprised of both a fixed interest rate and adjustable interest rate components. . The Stable Mortgage terms she is receiving from Best Mortgage Company are: There are 2% in discount points, and allows 75% of the mortgage loan amount to be at a fixed rate of interest, while 25% to be at an adjustable rate of interest. The fixed portion of the loan is for 30 years at 10.5% interest. The adjustable rate portion of the loan also has a 30-year term, and has the following terms: o A9% initial interest rate using an index of one-year U.S. Treasuries with a 200bps margin. o Monthly mortgage loan payments adjust each year. The adjustable portion of the loan neither has an interest rate or payment cap. There is no prepayment penalty. The projected one-year U.S. Treasury rates are 10% for (the beginning of) year 2; 11% for (the beginning of) year 3; 8% for (the beginning of) year 4; and 12% for the beginning of) year 5. 3. What are Maria's monthly payments and end-of-year mortgage loan balances for the first five years? (Note: while a calculation is necessary, it is acceptable to calculate amortization on an annual basis.) 4. Now, what are Maria's monthly payments and end-of-year mortgage loan balances for the first five years assuming that there is a 9.5% initial interest rate, AND, Maria being a strong negotiator, was able to get an annual 1% interest rate cap. (Note: while a calculation is necessary, it is acceptable to calculate amortization on an annual basis.) (7) (8) (6) Monthly Amortization Annual Amortization FOY Balance WNLO + 53 QUESTION 4 54 55 Total loan Amount 50 56 Polnts 0.00% 57 Fixed rate portion 0% 58 Annual Interest rate 0.00% 59 Loan term (years) 60 61 (1) 62 (2) (3) 15) (4) Annual interest Monthly Monthly 63 Year BOY Balance Rate Interest Rate Payments Interest Amt 64 65 1 66 2 67 3 6B 4 69 5 70 71 72 73 Floating rate portion 0% UST-Year 2 0.00% 74 Annual Interest rate 0.00% UST-Year 2 0.00% 75 Interest rate cap 0.00% UST-Year 2 0.00% 76 Interest rate margin 0.00% UST-Year 2 0.00% 77 Loan term (years) 0 78 79 30 (1) 12) (3) 15) Capped Interest Monthly Monthly 81 BOY Balance Rate Interest Rate Payments Interest Amt 82 0 83 1 84 2 85 3 BG B7 5 8 89 Mortgage Summary (Total of fixed rate plus adjustable rate portions) 90 01 Your BOY Balance Total Payments OY Balance 0 1 2 3 9G 4 Problom 1 Problem 2 Problem 3 Problem 4 7) (8) 16) Monthly Amortiration Annual Amortization Balance SNER PROBLEM 1 Ann would like to buy a house. It costs $1,000,000. She will make an $80,000 down payment. She will take out a 30-year, fully amortizing, fixed rate mortgage (FRM) for $920,000. It will have equal, level monthly payments. The annual interest rate is 3.41%. She must pay 2% In discount fees at the closing of the loan. Notes: the home is bought and the loan is taken in month O, the first payment is due in month 1. Also, Ann's net cash flow each month is equal to the total cash inflows minus total cash outflows that month. Ann forecasts three possible scenarios for house price appreciation (HPA): A. Optimistic Case: 9% annual HPA, hence 9/12% monthly HPA B. Base Case: 4.5% annual HPA, hence 4.5/1296 monthly HPA C Pessimistic Case: 0% annual HPA, hence 0/12% monthly HPA 1. Prepare a mortgage loan amortization schedule for Ann using the Excel spreadsheet. 2. Assume Ann will make the required monthly payment every month for 30 years. How much home equity will Ann have after 15 years (180 months) of payments under each of the three HPA scenarios
