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