Description
Check the attached filesMortgage Assignment InstructionsThis document may be modified for clarification, especially in response to student questions.
Unformatted Attachment Preview
Mortgage Calculations
and Decisions
© 2021 McGraw Hill. All rights reserved. Authorized only for instructor use in the classroom. No reproduction or
further distribution permitted without the prior written consent of McGraw Hill.
▪ Key quantitative mortgage features
▪ Payment amount
▪ Remaining mortgage balance at any point in time (RMB)
▪ Lender’s yield
▪ Effective borrowing cost (EBC)
▪ We’ll compute all 4 for fixed-rate loans and the
first 2 for adjustable-rate and hybrid loans
▪ Unless noted otherwise, assume mortgage loans
involve monthly payments
© McGraw Hill
2
▪
▪
▪
▪
Loan has an initial principal amount
Interest accrues on loan for period 1
Payment is made for period 1
Loan balance in 1 period / end of period 1 / start of
period 2 is
▪ Principal amount at start of period 1 (initial loan amount) + interest
accrued in period 1 – payment for period 1
▪
Loan balance in t periods / end of period t / start of
period t+1 is
▪ Principal amount at start of period t (end of period t-1) + interest
accrued in period t – payment for period t
▪
© McGraw Hill
Note: loan balance in t periods / end of period t /start of
period t+1 is also equal to the present value of all future
loan payments (after time t) discounted by the interest rate
on the loan
3
▪ Computing the regular mortgage payment
▪ N = number of payments remaining = number of
years until maturity × number of payments per year
▪ I% = interest rate per period = annual interest rate ÷
number of payments per year
▪ PV = principal balance (RMB, which is loan amount at
origination)
▪ FV = any balloon payment at time N
▪ END mode since mortgages accrue interest over the
period
▪ Solve for PMT to get CF associated with payment, so
flip sign to get payment amount
© McGraw Hill
4
▪
Loan
▪ 30-year maturity with monthly payments
▪ Fully-amortizing loan of $300,000
▪ Fixed annual interest rate of 6.00%
▪
Compute the regular mortgage payment
▪ N = 30 × 12 = 360
▪ I% = 6.00 ÷ 12 = 0.50 (recall calculator knows it’s a percentage)
▪ PV = 300,000
▪ FV = 0
▪ END mode since mortgages accrue interest over the period
▪ Solve for PMT → -1,798.65, so monthly payment = $1,798.65
© McGraw Hill
5
▪ Computing the mortgage balance for fully-
amortizing, fixed-rate loan
▪ N = number of payments remaining = number of
years until maturity × number of payments per year
▪ I% = interest rate per period = annual interest rate ÷
number of payments per year
▪ PMT = CF associated with payment, so enter a
negative number, the opposite of the payment
amount
▪ FV = any balloon payment at time N
▪ END mode since mortgages accrue interest over the
period
▪ Solve for PV = remaining loan balance
© McGraw Hill
6
▪
Loan
▪ 30-year maturity with monthly payments
▪ Loan was originated 8 years ago (8 × 12 = 96 payments
made and 30 – 8 = 22 years remaining on loan)
▪ Fully-amortizing loan of $300,000
▪ Fixed annual interest rate of 6.00%
▪
Compute the mortgage balance
▪ N = (30 – 8) × 12 = 22 × 12 = 264 = 360 – 96
▪ I% = 6.00 ÷ 12 = 0.50
▪ PMT = -1,798.65
▪ FV = 0
▪ END mode since mortgages accrue interest over the period
© McGraw Hill
▪ Solve for PV → 263,317 = mortgage balance
7
▪ Excel is a useful tool
▪ Can use PMT & PV functions similarly to the financial
calculator
▪ Can also set up a spreadsheet that “tracks” the loan
▪ Row 1, columns A through H
▪ Period, Opening balance, Period interest rate, Interest, Opening
balance + interest, Payment, Closing balance, Payments remaining
at period end
▪ Row 1, columns K through ??
▪ Mortgage “facts” that can be referenced
▪ Loan amount, Annual interest rate, Years to maturity, Payments
per year, Number of payments, Regular payment, Balloon
payment, & anything else useful
© McGraw Hill
▪ Number of payments computed as years to maturity × payments per year
▪ Regular payment computed as –PMT(annual interest rate/payments per
year, number of payments, loan amount, balloon payment, 0)
▪ 0 at end is “END” mode
8
▪
Lender fees
▪ “Points” is essentially an interest expense paid up-front
▪ Equals (quoted points/100) × loan amount
▪ Lenders charge other fees like loan origination fees, etc.
▪
Third-party expenses are up-front expenses
incurred by borrower but not paid to lender
▪ Examples: mortgage insurance premium, title insurance,
charges to record mortgage (county), appraisal, & survey
Lender’s yield is the return for the lender, taking
lender fees into account
▪ Effective borrowing cost is the cost of the loan for
the borrower (as a rate) taking all lender fees and
third-party expenses into account
▪
© McGraw Hill
9
▪ The signs on cash flows for lender are used to
compute lender’s yield and are different than
signs used elsewhere, as lender pays out cash at
time 0 and then receives payments
▪ Cash flow at time 0 = -loan amount + (points + other
fees paid to lender)
▪ Cash flows after time 0 = loan payment amounts (as
positive cash flows)
© McGraw Hill
10
▪
Computing lender’s yield
▪ N = number of payments remaining = number of years
until maturity × number of payments per year
▪ PV = cash flow at year 0 = -loan amount + (points + other
fees paid to lender)
▪ PMT = CF associated with payment, so enter a positive
number, the payment amount
▪ Based on loan amount (without considering fees)
▪ FV = any balloon payment at time N
▪ END mode since mortgages accrue interest over the period
▪ Solve for I% and then multiply by the number of periods
per year to get lender’s yield (typically 12)
▪ If you round I%, go at least 3 decimal places
Lender prefers higher lender’s yield, holding risk,
timing, etc. equal
▪ In Excel, the RATE function can be used similarly to
the financial calculator
▪
© McGraw Hill
11
▪
Loan
▪ 30-year maturity with monthly payments, $300,000, fully-
amortizing loan, fixed annual interest rate of 6.00%
▪ Points paid = 1%
▪ Other up-front financing costs paid to lender = $1,000
▪ Up-front financing costs paid to third party service
providers (appraiser, surveyor, etc.) = $2,000
▪
© McGraw Hill
Compute the lender’s yield
▪ N = 30 × 12 = 360
▪ PV = -300,000 + {(1% × 300,000) + 1,000} = -296,000
▪ PMT = 1,798.65
▪ FV = 0
▪ END mode since mortgages accrue interest over the period
▪ Solve for I% → 0.510, so lender’s yield = 12 × .510% = 6.12%
12
▪ The signs on cash flows for borrower are used to
compute effective borrowing cost and are
similar to signs used elsewhere, but different
than signs used with lender’s yield
▪ Cash flow at time 0 = loan amount – (points + other
fees paid to lender + fees paid to other parties)
▪ Cash flows after time 0 = -loan payment amounts (as
negative cash flows)
© McGraw Hill
13
▪
Computing EBC
▪ N = number of payments remaining before maturity = number of
years until maturity × number of payments per year
▪ “Normal” EBC is computed as if loan is not prepaid
▪ PV = cash flow at year 0 = loan amount – (points + other fees
paid to lender + fees paid to other parties)
▪ PMT = CF associated with payment, so enter a negative number,
the opposite of the payment amount
▪ Based on loan amount (without considering fees)
▪ FV = any balloon payment at time N
▪ END mode since mortgages accrue interest over the period
▪ Solve for I% and then multiply by the number of periods per
year to get EBC (typically 12)
▪ If you round I%, go at least 3 decimal places
Borrower prefers lower EBC, holding risk, timing, etc.
equal
▪ In Excel, the RATE function can be used similarly to the
financial calculator
▪
© McGraw Hill
14
▪
Loan
▪ 30-year maturity with monthly payments, $300,000, fully-
amortizing loan, fixed annual interest rate of 6.00%
▪ Points paid = 1%
▪ Other up-front financing costs paid to lender = $1,000
▪ Up-front financing costs paid to third party service
providers (appraiser, surveyor, etc.) = $2,000
▪
© McGraw Hill
Compute the EBC
▪ N = 30 × 12 = 360
▪ PV = 300,000 – {(1% × 300,000) + 1,000 + 2,000} = 294,000
▪ PMT = -1,798.65
▪ FV = 0
▪ END mode since mortgages accrue interest over the period
▪ Solve for I% → 0.516, so EBC = 12 × .516% = 6.19%
15
▪ EBC understates true cost of loan if paid early
▪ EBC spreads up-front costs over full loan term
▪ If prepaid early, then costs spread out over fewer
periods
© McGraw Hill
16
▪
EBC with expected prepayment can be computed
▪ N = number of payments remaining before prepayment =
number of years until loan paid off × number of payments
per year
▪ PV = cash flow at year 0 = loan amount – (points + other fees
paid to lender + fees paid to other parties)
▪ PMT = CF associated with payment, so enter a negative
number, the opposite of the payment amount
▪ Based on loan amount (without considering fees)
▪ FV = opposite of remaining balance at time N, which can be
© McGraw Hill
computed (negative number, since this amount must be paid)
▪ END mode since mortgages accrue interest over the period
▪ Solve for I% and then multiply by the number of periods per
year to get EBC
17
▪
Loan
▪ 30-year maturity with monthly payments, $300,000, fully-
amortizing loan, fixed annual interest rate of 6.00%
▪ Loan is expected to be prepaid in 10 years (20 years remaining)
▪ Points paid = 1%, other up-front financing costs paid to lender =
$1,000, and up-front financing costs paid to third party service
providers (appraiser, surveyor, etc.) = $2,000
▪
Compute the EBC
▪
▪
▪
▪
N = 10 × 12 = 120
PV = 300,000 – (1% × 300,000 + 1,000 + 2,000) = 294,000
PMT = -1,798.65
FV = -251,057
▪ N = 20 × 12 = 240 = 360 – 120; I% = 6.00 ÷ 12 = 0.50; PMT = -1,798.65;
▪ FV = 0; END mode; Solve for PV → 251,057 = mortgage balance in 10 years
▪ END mode since mortgages accrue interest over the period
▪ Solve for I% → 0.524, so EBC = 12 × .524% = 6.29% > 6.19%
© McGraw Hill
18
▪ Federal Truth in Lending Act requires
disclosure of annual percentage rate (APR) on
most home mortgage loans
▪ APR is a special case of EBC that includes most costs
▪ Stated APRs are based on maturity of loan and
therefore typically understate the cost to home
buyers, because many home buyers prepay their
loans (they move, make extra payments, etc.)
▪ Note: this APR does not refer to the same rate as
the APR in FNAN 303
© McGraw Hill
19
▪ Computations are similar with adjustable-rate
mortgages (ARMs) as with fixed rate mortgages
▪ However, things change over time with ARMs
that don’t change with FRMs, so tracking,
analyzing, etc. is more dynamic with ARMs
▪ Interest rate changes
▪ Rates typically reset annually
▪ Payment amounts change and are based on remaining
loan balance at time of interest rate change
© McGraw Hill
20
▪
Loan
▪ 30-year maturity, monthly payments, $300k, fully-amortizing loan
▪ Adjustable annual interest rate
▪ Teaser rate of 6.00% for 1 year
▪ Rates reset annually to LIBOR + 200 basis points
▪ Caps
▪ Maximum rate increase in a year = 240 basis points (2.40 percentage points)
▪ Lifetime interest rate cap of 1,000 basis points over initial rate (10.00 percentage points)
▪
Compute regular mortgage payment for the first 12 months
▪
▪
▪
▪
▪
▪
© McGraw Hill
N = 30 × 12 = 360
I% = 6.00 ÷ 12 = 0.50 (recall calculator knows it’s a percentage)
PV = RMB after 0 years = 300,000
FV = 0
END mode since mortgages accrue interest over the period
Solve for PMT → -1,798.65, so monthly payment = $1,798.65
21
▪
Loan
▪ 30-year maturity, monthly payments, $300k, fully-amortizing loan
▪ Adjustable annual interest rate
▪ Teaser rate of 6.00% for 1 year
▪ Rates reset annually to LIBOR + 200 basis points
▪ Caps
▪ Maximum rate increase in a year = 240 basis points (2.40 percentage points)
▪ Lifetime interest rate cap of 1,000 basis points over initial rate (10.00 percentage points)
▪ In one year, LIBOR = 7.00%
▪
▪ Rate is min of (LIBOR + spread, previous rate + period cap, initial rate + lifetime cap)
▪ Equals min(7.00% + 2.00%, 6.00% + 2.40%, 6.00% + 10.00%) = min(9.00%, 8.40%, 16.00%) = 8.40%
Compute regular mortgage payment for year 2 (months 13 to 24)
▪ N = 29 × 12 = 348 = 360 – 12 (step 1)
▪ I% = 8.40 ÷ 12 = 0.70 (step 3)
▪ PV = RMB after 1 year = 296,316 (step 2)
▪ Compute RMB as if payment & rate from previous year continued until maturity
▪ N = 29 × 12 = 348 = 360 – 12; I% = 6.00 ÷ 12 = 0.50; PMT = -1,798.65; FV = 0; END mode
▪ Solve for PV → 296,316 = mortgage balance after 1 year
▪ FV = 0
▪ END mode since mortgages accrue interest over the period
▪ Solve for PMT → -2,274.99, so monthly payment = $2,274.99 (step 4)
© McGraw Hill
22
▪
Loan
▪ 30-year maturity, monthly payments, $300k, fully-amortizing loan
▪ Adjustable annual interest rate
▪ Teaser rate of 6.00% for 1 year
▪ Rates reset annually to LIBOR + 200 basis points
▪ Caps
▪ Maximum rate increase in a year = 240 basis points (2.40 percentage points)
▪ Lifetime interest rate cap of 1,000 basis points over initial rate (10.00 percentage points)
▪ In two years, LIBOR = 6.16%
▪
▪ Rate is min of (LIBOR + spread, previous rate +period cap, initial rate + lifetime cap)
▪ Equals min(6.16% + 2.00%, 8.40% + 2.40%, 6.00% + 10.00%) = min(8.16%, 10.48%, 16.00%) = 8.16%
Compute regular mortgage payment for year 2 (months 13 to 24)
▪ N = 28 × 12 = 336 = 360 – 24 (step 1)
▪ I% = 8.16 ÷ 12 = 0.68 (step 3)
▪ PV = RMB after 2 years = 293,811 (step 2)
▪ Compute RMB as if payment & rate from previous year continued until maturity
▪ N = 28 × 12 = 336 = 360 – 24; I% = 8.40 ÷ 12 = 0.70; PMT = -2,274.99; FV = 0; END mode
▪ Solve for PV → 293,811 = mortgage balance after 2 years
▪ FV = 0
▪ END mode since mortgages accrue interest over the period
▪ Solve for PMT → -2,226.30, so monthly payment = $2,226.30 (step 4)
▪ Repeat process over life of loan
© McGraw Hill
23
▪ Can conduct analysis in Excel
▪ Set up cells to compute & find minimum of
▪ Interest rate according to formula
▪ Maximum interest rate from period cap
▪ Previous rate + maximum period increase
▪ Maximum interest rate from lifetime cap
▪ Note: our approach doesn’t account for interest rate
floors
© McGraw Hill
▪ We won’t work with floors in FNAN 351 in our quantitative
analysis
▪ Note: max (rate after taking caps into account, minimum rate
based on annual floor, minimum rate based on lifetime floor)
24
▪ Computations are similar with hybrid
mortgages (like a “5/1”) as with adjustable-rate
mortgages, except the first period lasts longer
▪ With an “X/1” mortgage, the interest rate is
fixed for the first X years and then resets
annually until the end of the loan term
© McGraw Hill
25
▪
Loan
▪ 30-year maturity, monthly payments, $300k, fully-amortizing loan
▪ 5/1
▪ Initial rate of 6.00% for 5 years
▪ After 5 years, rates reset annually to LIBOR + 200 basis points
▪ Caps
▪ Maximum rate increase in a year = 240 basis points (2.40 percentage points)
▪ Lifetime interest rate cap of 1,000 basis points (10.00 percentage points)
▪
Compute regular mortgage payment for the first 60 months
▪
▪
▪
▪
▪
▪
© McGraw Hill
N = 30 × 12 = 360
I% = 6.00 ÷ 12 = 0.50 (recall calculator knows it’s a percentage)
PV = 300,000
FV = 0
END mode since mortgages accrue interest over the period
Solve for PMT → -1,798.65, so monthly payment = $1,798.65
26
▪
Loan
▪ 30-year maturity, monthly payments, $300k, fully-amortizing loan
▪ 5/1 with initial rate of 6.00% for 5 years
▪ After 5 years, rates reset annually to LIBOR + 200 basis points
▪ Caps
▪ Maximum rate increase in a year = 240 basis points (2.40 percentage points)
▪ Lifetime interest rate cap of 1,000 basis points (10.00 percentage points)
▪ In 5 years, LIBOR = 6.16%
▪
▪ Rate is min of (LIBOR + spread, previous rate + period cap, initial + lifetime cap)
▪ Equals min(6.16% + 2.00%, 6.00% + 2.40%, 6.00% + 10.00%) = min(8.16%, 8.40%, 16.00%) = 8.16%
Compute regular mortgage payment for the year 6
▪ N = 25 × 12 = 300 = 360 – 60 (step 1)
▪ I% = 8.16 ÷ 12 = 0.68 (step 3)
▪ PV = 279,164 (step 2)
▪ N = 25 × 12 = 300 = 360 – 60; I% = 6.00 ÷ 12 = 0.50; PMT = -1,798.65; FV = 0; END mode
▪ Solve for PV → 279,164 = mortgage balance after 5 years
▪ FV = 0
▪ END mode since mortgages accrue interest over the period
▪ Solve for PMT → -2,184.31, so monthly payment = $2,184.31 (step 4)
▪ Repeat process over life of loan
© McGraw Hill
27
▪ Can conduct analysis in Excel
▪ Set up cells to compute & find minimum of
▪ Interest rate according to formula
▪ Maximum interest rate from period cap
▪ Previous rate + maximum period increase
▪ Maximum interest rate from lifetime cap
▪ Note: our approach doesn’t account for interest
rate floors
© McGraw Hill
28
The examples have provided loan size, but questions
could give house price and loan type and/or LTV,
which would require you to determine loan size
▪ Examples
▪
▪ If a house was purchased for $200,000 and the loan was the
largest possible where PMI insurance wasn’t required, then loan
would be 80% × $200k = $160,000
▪ If a house was purchased for $200,000 and the loan was the
largest possible for an FHA loan, then loan would be 96.5% ×
$200k = $197,000
▪ If a house was purchased for $200,000 and the loan was the
largest possible for a VA loan, then loan would be 100% × $200k
= $200,000
▪ If a house was purchased for $200,000 and the LTV ratio was
90%, then 90% = loan/$200k, so loan would be 90% × $200k =
$180,000
© McGraw Hill
29
▪ Potential question: find monthly payment(s)
and balloon payment with a partially
amortizing loan
▪ Analysis is straightforward, as it can be evaluated by
▪ 1) Analyzing a loan with a maturity equal to the amortization
term to find the monthly payments
▪ 2) Looking at the remaining balance for the loan at the point in
time when the subject loan matures
© McGraw Hill
30
▪
▪
Loan
▪ Loan amount of $300,000 with monthly payments
▪ Matures in 10 year and amortizes over 30 years (assume this means fully amortized)
▪ Fixed annual interest rate of 6.00%
Compute the regular mortgage payment
▪
▪
▪
▪
▪
▪
▪
N = 30 × 12 = 360 (based on amortization term)
I% = 6.00 ÷ 12 = 0.50
PV = 300,000
FV = 0
END mode since mortgages accrue interest over the period
Solve for PMT → -1,798.65, so monthly payment = $1,798.65
▪ Reminder: loan payment calculations unaffected by loan fees
Compute the balloon payment due at maturity
▪ N = (30 – 10) × 12 = 20 × 12 = 240 = 360 – 120 = number of payments remaining until
fully amortized
▪ I% = 6.00 ÷ 12 = 0.50
▪ PMT = -1,798.65
▪ FV = 0
▪ END mode since mortgages accrue interest over the period
▪ Solve for PV → 251,057 = mortgage balance at maturity
© McGraw Hill
31
▪
Loan
▪ Loan amount of $300,000 with monthly payments
▪ Matures in 10 year and amortizes over 30 years (assume this
▪
means fully amortized)
▪ Fixed annual interest rate of 6.00%
Regular mortgage payment = $1,798.65
▪ Reminder: loan payment calculations unaffected by loan fees
RMB as of a time before maturity can be determined
similarly to finding RMB of fully amortizing loan with
maturity at end of amortization period
▪ Compute RMB after 5 years
▪
▪ N = (30 – 5) × 12 = 25 × 12 = 300 = 360 – 60 = number of
payments remaining until fully amortized
▪ I% = 6.00 ÷ 12 = 0.50
▪ PMT = -1,798.65
▪ FV = 0
▪ END mode since mortgages accrue interest over the period
© McGraw Hill
▪ Solve for PV → 279,163 = RMB after 5 years
32
Period
Opening
balance
Period
interest
rate
Interest
Opening
balance +
interest
Payment
Closing
balance
Payments
remaining at
period end
Loan
amount
Annual
interest Years to Payments Number of
rate
maturity per year payments
Regular
payment
Balloon
payment
Period
Opening
balance
Period
interest
rate
Interest
Opening
balance +
interest
Payment
Closing
balance
Payments
remaining at
period end
LIBOR
Loan
amount
Annual
interest Years to Payments Number of
rate
maturity per year payments
Spread
Pd cap
Life cap
LIBOR+
Prev
Prev+
Initial
Initial+
Regular
payment
Int rate
Balloon
payment
Period
Opening
balance
Period
interest
rate
Interest
Opening
balance +
interest
Payment
Closing
balance
Payments
remaining at
period end
Loan
amount
Annual
interest Years to Payments Number of
rate
maturity per year payments
Spread
Pd cap
Life cap
Regular
payment
Balloon
payment
Purchase answer to see full
attachment
