FIN 344: REAL ESTATE INVESTMENT ANALYSIS

Midterm: Mortgages

Due Date
Please submit your answers in Excel through Blackboard by midnight, April 5, 2024. Name you
Excel file as your last name and first name, e.g. Smith_Joy.xlsx.

Purpose
This midterm covers different types of mortgages, the terminology of mortgages and essential
calculations. You are required to use Excel to solve the questions.

Instructions
Please answer all the questions. Present your solutions clearly in Excel and highlight the final
answers. It is useful to comment on your work in Excel, explaining what you do in each step.

Question 1

Consider a $5,000,000, 9% APR, 25-year mortgage with monthly payments. Compute the first three
payments and the loan balance after the third payment for each of the following loan types: (a)
Interest-only, (b) Constant amortization mortgage (CAM), (c) Constant payment mortgage (CPM).

Question 2

Federal Housing Administration (FHA) provides graduated payment mortgages for homebuyers who
currently have low to moderate incomes but expect them to increase substantially over the next 5 to
10 years. One of the plans provided by the FHA is such that the payments increase by 5% annually in
the first 5 years of the loan, and stays constant thereafter at the year 5 level till its maturity in year 30.
The mortgage is fully amortized (no ballon payment at the end). If the contractual interest rate is 6%
APR, what are the annual payments in years 1-10? What is the outstanding loan balance at the end of

1
the 5th year? Assume that the outstanding loan balance at origination is $250,000 and payments are
made at the end of each year.

Question 3

Consider a $5,000,000, 25-year ARM with monthly payments and annual interest adjustments. The
initial interest rate is 6.5%. The index for the loan is 1-year US Government bonds, currently yielding
5%. The loan has a margin of 200 basis points.
(a) Is the loan’s initial interest rate a “teaser rate”? How do you know?
(b) If 1-year T-bonds remain at 5%, what will be the applicable interest rate for this mortgage
after the first year?
(c) What are the initial monthly payments on this loan?
(d) Assuming T-Bonds remain at 5%, what will be the monthly payments after the first year?
(e) Under that assumption (and assuming no discount points), what is the forecasted yield to-
maturity on this loan at the time it is issued.

Question 4

Consider a $5,000,000, 9%, constant payment mortgage (CPM) with monthly payments. What is the
regular monthly payment amount and the balloon payment amount in each of the following cases:
(a) Fully-amortizing, 30-year loan
(b) 30-year amortization, 10-year balloon
(c) 15-year amortization, 10-year balloon
(d) What is the major disadvantage, and advantage, of the 15-year amortization-rate 10-
year loan in (c) as compared to the 30-year amortization-rate 10-year loan in (b)?

Question 5

Consider a $5,000,000, 8.5% APR, 30-year mortgage with monthly payments. What is the yield to
maturity (YTM) of this loan under the following circumstances:
(a) No points, fully-amortizing
(b) Three points, fully amortizing. Three points means that the present value is 3% lower than the
loan amount in the YTM calculation.
(c) Three points, 7-year maturity with balloon.

2
Question 6

Consider a $5,000,000, 8.5% APR, 30-year mortgage with monthly payments, and expected realistic
prepayment horizon of 7 years. What is the contractual yield (effective interest rate) at issuance over
the expected life of the loan under the following circumstances:
(a) No points or penalties
(b) One point of discount
(c) Three points of discount
(d) Three points of discount plus one point of prepayment penalty.

Question 7

A lender wants to achieve a 8.5% annual yield on a 30-year amortization, $5,000,000, monthly-
payment loan with an 8-year maturity with balloon. How many discount points must the lender charge
under the following circumstances:
(a) Contract interest rate is 8%
(b) Contract interest rate is 7.5%.

Question 8

As a borrower, you have the option to choose between two 30-year, monthly-payment loans: 7%
interest rate with 3 points, versus 7.5% interest with one-half point.
(a) Which loan option would you choose if you had a 10-year expected prepayment horizon?
(b) Which would you choose if you expect to pay off this loan in 5 years?

Question 9

Using the discounted cash flow (DCF) valuation method, what is the maximum loan that can be made
on a property with the following annual net before-tax cash flow, assuming a 10.5% discount rate and
underwriting criteria which specify a maximum loan/value ratio of 75%? Cash flows: $1 Million in
year 1, 1.1 Million in year 2 through 4, 1.5 Million in year 5 through 9, and $12 Million in year 10
including reversion?

Question 10

Suppose 10-year Treasury Bond yields in the bond market are 5.87% annual, and the mortgage market
requires a contract yield risk premium of 200 basis points. If a property has a (annual) net operating
income (NOI) of $1,000,000, and the underwriting criteria require a debt service coverage ratio
(DSCR) of at least 120%, then what is the maximum loan that can be offered assuming a 25-year
amortization rate and constant monthly payments on the mortgage?