Real Estate Finance homework. Attached is a file for my homework. The questions must be answered in EXCEL and formulas must be used for the answers.

Real Estate Finance Business 422 Please use use EXCEL to solve each problem. Please make sure all FORMULAS and functions are used and shown! Make sure spreadsheet is readable. Chapter 5: Questions: 1 (a. & b.), 2-3, 8, 10 1. A price level adjusted mortgage (PLAM) is made with the following terms: Amount _ $95,000 Initial interest rate _ 4 percent Term _ 30 years Points _ 6 percent Payments to be reset at the beginning of each year. Assuming inflation is expected to increase at the rate of 6 percent per year for the next five years: a. Compute the payments at the beginning of each year (BOY). b. What is the loan balance at the end of the fifth year? 2. A basic ARM is made for $200,000 at an initial interest rate of 6 percent for 30 years with an annual reset date. The borrower believes that the interest rate at the beginning of year (BOY) 2 will increase to 7 percent. a.Assuming that a fully amortizing loan is made, what will monthly payments be during year 1? b. Based on (a) what will the loan balance be at the end of year (EOY) 1? c. Given that the interest rate is expected to be 7 percent at the beginning of year 2, what will monthly payments be during year 2? d. What will be the loan balance at the EOY 2? e. What would be the monthly payments in year 1 if they are to be interest only? f. Assuming terms in (e), what would monthly interest only payments be in year 2? 3. A 3/1 ARM is made for $150,000 at 7 percent with a 30-year maturity. a. Assuming that fixed payments are to be made monthly for three years and that the loan is fully amortizing, what will be the monthly payments? What will be the loan balance after three years? b. What would new payments be beginning in year 4 if the interest rate fell to 6 percent and the loan continued to be fully amortizing? c. In (a) what would monthly payments be during year 1 if they were interest only? What would payments be beginning in year 4 if interest rates fell to 6 percent and the loan became fully amortizing? 8. Assume that a lender offers a 30-year, $150,000 adjustable rate mortgage (ARM) with the following terms: Initial interest rate _ 7.5 percent Index _ 1-year Treasuries Payments reset each year Margin _ 2 percent Interest rate cap _ 1 percent annually; 3 percent lifetime Discount points _ 2 percent Fully amortizing; however, negative amortization allowed if interest rate caps reached Based on estimated forward rates, the index to which the ARM is tied is forecasted as follows: Beginning of year (BOY) 2 _ 7 percent; (BOY) 3 _ 8.5 percent; (BOY) 4 _ 9.5 percent; (EOY) 5 _ 11 percent. Compute the payments, loan balances, and yield for the ARM for the fiveyear period. 10. A floating rate mortgage loan is made for $100,000 for a 30-year period at an initial rate of 12 percent interest. However, the borrower and lender have negotiated a monthly payment of $800. a.What will be the loan balance at the end of year 1? b. What if the interest rate increases to 13 percent at the end of year 1? How much interest will be accrued as negative amortization in year 1 if the payment remains at $800? Year 5? CHAPTER 6 Questions: 2 (a. & b.), 3 (a.), 4 (a.), 6, 10 (a) 2. An investor has $60,000 to invest in a $280,000 property. He can obtain either a $220,000 loan at 9.5 percent for 20 years or a $180,000 loan at 9 percent for 20 years and a second mortgage for $40,000 at 13 percent for 20 years. All loans require monthly payments and are fully amortizing. a. Which alternative should the borrower choose, assuming he will own the property for the full loan term? b. Would your answer change if the borrower plans to own the property only five years? 3. An investor obtained a fully amortizing mortgage 5 years ago for $95,000 at 11 percent for 30 years. Mortgage rates have dropped, so that a fully amortizing 25-year loan can be obtained at 10 percent. There is no prepayment penalty on the mortgage balance of the original loan, but three points will be charged on the new loan and other closing costs will be $2,000. All payments are monthly. a. Should the borrower refinance if he plans to own the property for the remaining loan term? Assume that the investor borrows only an amount equal to the outstanding balance of the loan. 4. Secondary Mortgage Purchasing Company (SMPC) wants to buy your mortgage from the local savings and loan. The original balance of your mortgage was $140,000 and was obtained 5 years ago with monthly payments at 10 percent interest. The loan was to be fully amortized over 30 years. a. What should SMPC pay if it wants an 11 percent return? 6. An investor has owned a property for 15 years, the value of which is now to $200,000. The balance on the original mortgage is $100,000 and the monthly payments are $1,100 with 15 years remaining. He would like to obtain $50,000 in additional financing. A new first mortgage for $150,000 can be obtained at a 12.5 percent rate and a second mortgage for $50,000 at a 14 percent rate with a 15-year term. Alternatively, a wraparound loan for $150,000 can be obtained at a 12 percent rate and a 15-year term. All loans are fully amortizing. Which alternative should the investor choose? Ignore the information concerning the availability of a new first mortgage at 12.5%. In this situation, one would not even consider that, but would instead only decide whether to do a wraparound for $150,000 at 12% or add a home equity loan of $50,000 at 14%. 10. A borrower is making a choice between a mortgage with monthly payments or biweekly payments. The loan will be $200,000 at 6 percent interest for 20 years. a. How would you analyze these alternatives? Bi-weekly payments means the loan is still amortized over the full-term, but payments are made 26 times a year at half the normally amortized rate (in effect, the borrow is making a full extra payment for each year of the loan which will lead to a shorter term until payoff). Remember to divide the annual interest rate by 26 for the bi-weekly calculation