math modeling and R code
1
`
CONSULTING REPORT ON MORTGAGE RATES
Samuel M. Diego
San Diego State University Research Team
email: sdsudiego@gmail.com
phone: 1.619.788.8888
website: https://www.sdsu.edu/
29 February 2016
2
Abstract
One of the biggest financial decisions people make is purchasing a home. Home
ownership provides many benefits such as building equity and tax deduction. However, in
purchasing a home, most people need to take out a loan, or mortgage, in order to afford the
home. Mortgages are profitable because the interest on the loan is paid to the bank.
This report examines fixed-rate mortgages, where the interest rate does not change over
time. The example most commonly used in this report is of a $200,000.00 loan with a 4% annual
interest rate. Using R code, we are able to analyze the potential profit of different mortgages by
changing the principal, length of term, and interest rate. The results of this analysis show that
term length has the greatest effect on overall profit. A longer pay period (30 years is a popular
option) greatly increases profit in comparison to a shorter term of 15 years. This often applies
even if the annual rate is lower. The monthly payment for a 30-year mortgage is also
considerably lower, which is attractive to customers. Because a higher percentage of the overall
payment is interest, it would be most profitable to encourage customers to choose a 30-year
mortgage.
The beginning years of the payment term are most profitable, because a higher
percentage of the monthly payment goes to interest. The principal is paid down at a slower rate
in the beginning of the term, and so the majority of the overall profit is paid in the first half of the
term. This is beneficial because even if a homeowner sells the home, the bank will have already
profited. Hence, dealing in mortgage loans is profitable for the bank.
The techniques used in this report can be applied to fixed-rate mortgages to find the most
rewarding option based on the customer’s wants. Different rates, principals, and terms affect
3
overall profit, and these methods can be used to see which option yields the most profit.
Introduction
There are multiple elements to be considered in creating a mortgage loan. If we consider
the principal loan amount, the annual interest rate, length of term, and monthly payment, we will
be able to solve for the profit. Changing any of these terms will affect the overall interest and
profit of the bank. The aim of this report is to determine which factor is most effective in
increasing profit.
In solving for the monthly payment, we will set up a formula which includes the principal
amount, interest rate, and number of months. Then we can use algebra to find X, the monthly
payment. Once we have found X, we can change any of the terms to see how it affects the
monthly payment. Multiplying the monthly payment by the number of months in the term will
give us the total payment. Subtracting the principal from the total payment will give us the total
interest, or profit earned. By changing only one variable at a time, we will be able to see which
element has the largest overall effect on profit while holding the other variables in the model
constant.
We will utilize R in order to speed up this process. By programming our equation and
any other data using variables, we can change the variables and quickly learn valuable
information about the different elements of a mortgage loan and their effects on profit.
According to www.bankrate.com, the most popular mortgage option is the 30 year fixed
mortgage with a 3.82% annual interest rate. Hence, for our primary example, we will use a 30
year fixed $200,000.00 loan with a 4% annual rate(for simplicity). We will then substitute
different elements in order to examine the effect these have on profit.
4
Data and Method
In order to compare the profits on different mortgage rates and payment options, we must
first find an equation to solve for the monthly payment. If we know the principal loan amount(P),
monthly interest rate(i), number of months(n), we can solve for X, the monthly payment.
Let’s suppose we have a loan with a payment plan of only 1 month, n=1. This means in one
month, the loan and interest will be completely paid off. We can solve for the payment, X, with
this equation:
𝑃(1+ 𝑖) − 𝑋 = 0 𝑋 = 𝑃(1+ 𝑖)
𝑃 = 𝑋
(1+ 𝑖)
ie. The principal plus the interest, minus the monthly payment, would equal zero. If we were to continue with this method, adding to the number of months, we would find that
𝑃 = 𝑋 ( 1
(1+ 𝑖) + 1
(1+ 𝑖)! + … .+ 1
(1+ 𝑖)! )
Using the principles of geometric series, we can derive an equation to solve for this sum for any
number of months(n).
This yields the equation:
𝑋 = 𝑃 ∗ 𝑖 (1+ 𝑖)!
(1+ 𝑖)! − 1
Using this equation, we can solve for X if we know the loan principal(P), number of months(n),
and interest rate(i).
Note: A comprehensive analysis of this derivation is included in the appendix.
For our primary example, we will use a Principal P = $200,000, interest rate i = 4%
annually (.33% monthly), and n = 360 (360 months in a 30 year loan). Using our formula, we
5
find that the monthly payment should be $954.83. At this rate, the principal and all interest will
be paid of in 360 months.
By changing i, we can solve for different monthly payments based on interest rate. A table of
different rates and payments is shown below.
Interest Rate (annual) Monthly Payment
5% $1073.64
4.75% $1043.29
4.5% $1013.37
4.25% $983.88
4% $954.83
3.75% $926.23
This table illustrates a somewhat obvious fact, that as the interest rate goes up, the monthly
payment does as well, by about $120.00 for each additional percentage (when P = 200,000 and n
= 360). A higher interest rate yields a larger profit. We can also examine the length of the loan to
see how this affects profit, which will be considered in the next section.
Results
It is also important to consider how much of the final payment will profit the bank,
namely, what percentage of the total payment goes to interest, and what percentage goes to
paying off the loan. We can solve for this by adding up the monthly payments for the entire
period. For instance, in our main example, our monthly payment is $954.83. Over the course of
30 years, the customer will end up paying a total of ($954.83 * 360) = $343,739.00. This means
that $143,739.00 of the payment is purely profit for the bank. With a higher interest rate, the
monthly payments are higher and the total mortgage payment will be higher. This means that as
interest rate increases, the bank’s profit will increase. This is illustrated in the charts below.
6
These charts show that as the interest rate increases, the profit increases. On a 4% annual
interest, 42% of the total mortgage is direct profit to the bank, whereas increasing the interest by
1% give the bank 48% of the mortgage as profit.
It is also noteworthy to consider the amount of years taken to pay off the loan. Some
customers will want to take longer to pay off the loan, while others may opt for a shorter
payment period. Many loans are commonly given in 15 and 30 year periods. By changing the
number of months(n) in our equation, we can solve for the monthly payment and use this to see
how much profit will be gained from each type of loan. Let us use our example of a $200,000
with a 4% annual interest rate.
15 year option 30 year option
Monthly Payment $1479.38 $954.83
Total Paid $266,287.70 $343,739.00
Total Interest Paid $66,287.70 $143,739.00
Extra Earned on 30 year $77,451.30 With the 30 year option, although the monthly payment is lower, there is more interest because
the pay period is twice as long. We can see that profit more than doubled in the 30 year option.
7
We can also examine the percent of the total payment that goes to paying off the principal
and what percentage goes to interest. The charts below illustrate the difference in a 15 and 30
year loan with the same principal and interest rate.
With the 15 year option, only 25% of the total payment is profit; however, 42% is profit when
the 30 year option is used. Based on this we can conclude that a longer mortgage payment will
create more profit. In the table below, an interest rate of 3% over thirty years yields over
$18,000.00 in comparison to the same loan with a 5% interest rate over fifteen years.
5% Interest on 15 year 3% Interest on 30 year
Total Paid $284,685.70 $303,554.90
Total Interest $84,685.70 $103,554.90
Extra Earned on 30 year $18,869.20
Even with a higher interest rate, the 15 year option generally gives less total profit to the bank.
This means that the bank could offer a longer payment period with a lower interest rate. This
would attract more customers due to the low interest rate and lower monthly payments, but
would eventually give more profit to the bank.
It is also important to consider our profit on a monthly basis. In the first month, most of
the payment for that month is going towards interest, because the principal is very high. As the
8
principal is paid down, the monthly interest is lower, and so a smaller percentage of the payment
goes toward interest. Let us look at our primary example again. Our monthly payment is
$954.83. Multiplying our principal($200,000) by our monthly interest rate (4%/12 months) gives
us our interest for the first month, $666.67. This means that only $288.16 actually went to paying
down the principal loan. 70% of the first months payment was interest. The next month, our
principal is $199,711.84. So our interest is lower for this second month.
Month Principal Interest Principal Paid Down
Percent of Payment to
Interest 1 $200,000.00 $666.67 $288.16 70%
2 $199,711.84 $665.71 $289.12 70%
3 $199,422.71 $664.74 $290.09 69%
… … … … …
360 $951.66 $3.17 $951.66 0%
This illustrates how profit is higher in the earlier payments of the mortgage. The graphs below
plot the percent of the payment that goes to interest for our main example, for both the fifteen
and 30 year options. The green signifies the months where over 50% of the payment was profit,
and the red signifies the months where less than 50% of the payment went to interest.
9
This once again reinforces why the 30 year option is more profitable. In the 15 year option, over
50% of the monthly payment goes to paying off the principal for the majority of the months. This
means that the interest lowers more rapidly each month and gives less total profit. With the 30
year option, however, it is not until half way through the pay period that the majority of the
monthly payment goes to paying down the principal. This means that for the first 15 years, the
majority of the monthly payments profits the bank. Once again, this illustrates why a longer
payment period is more profitable.
10
As the graph below illustrates, the most profit is made in the beginning of the term.
Interest is higher in the first years, which shows why the slope is greater. As the payment period
nears its end, the slope approaches zero because less of the payment is going to profit the bank.
From this data we conclude that for a fixed rate mortgage, a longer term is ideal. The most profit
is made during the beginning of the term.
Conclusion
A thorough examination of the monthly and overall profit of mortgage interest rates
reveals the best way to profit. The different examples in this report illustrate that in general, the
most profitable mortgages are those with a longer pay period. This usually affects profit more
than increasing the annual interest rate. A longer payment period also gives a lower monthly
payment. This can be attractive to customers, because they could afford a larger house since the
monthly payment is lower. That, along with lowering the annual rate for the 30 year option (or
whatever extended payment period that the bank chooses), could attract customers while also
increasing overall profit in comparison to a 15 year mortgage. There is more profit in the
beginning years of the mortgage as well, so if the customer sold the house, the bank would still
be profiting overall.
11
This report only considered fixed-rate loans, ie. The interest rate does not change over
time. Adjustable-rate loans are also a popular option for home-buyers, because many people do
not plan to stay in a home for fifteen to thirty years. Hence, these results may not apply in some
situations.
The results of this report can be reproduced using the R code included in the appendix,
and new results can be found for different payment periods, interest rates, and principal amounts.
It would thus be wise to utilize this technology when discussing a mortgage loan with a
customer, in order to see which option would be most profitable. These results may not apply to
all loans, and so it would be best to act with discretion instead of generalizing these examples to
all results. However, the examples in this report normally showed that the length of the payment
period had the greatest effect in overall profit for the bank. Using the R code below could
provide insight into any potential earnings for a new mortgage.
References
"Mortgage Rates." Today. N.p., 2016. Web. 05 Mar. 2016. http://www.bankrate.com/mortgage-
groupd.aspx?market=30&loan=300000&perc=20&prods=216&fico=740&points=Zero&ic_id=st
ory_mortgage_mortgages_globalnav,opt_mtg_hp,
Appendix
In order to compare the profits on different mortgage rates and payment options, we must
first find an equation to solve for the monthly payment. If we know the principal loan amount(P),
monthly interest rate(i), number of months(n), we can solve for X, the monthly payment.
Let’s suppose we have a loan with a payment plan of only 1 month, n=1. This means in one
month, the loan and interest will be completely paid off. We can solve for the payment, X, with
this equation:
12
𝑃(1+ 𝑖) − 𝑋 = 0 𝑋 = 𝑃(1+ 𝑖)
𝑃 = 𝑋
(1+ 𝑖)
ie. The principal times the interest, minus the monthly payment, would equal zero. Now, let us suppose that n = 2. The loan and interest will be paid off in two months. Then:
(𝑃(1+ 𝑖) − 𝑋)(1+ 𝑖) − 𝑋 = 0 𝑋 = (𝑃(1+ 𝑖) − 𝑋)(1+ 𝑖)
𝑋 (1+ 𝑖) = 𝑃(1+ 𝑖) − 𝑋
𝑋 + 𝑋
(1+ 𝑖) = 𝑃(1+ 𝑖)
𝑃 = 𝑋
(1+ 𝑖) + 𝑋
(1+ 𝑖)!
If we were to continue with this method, adding to the number of months, we would find that
𝑃 = 𝑋 ( 1
(1+ 𝑖) + 1
(1+ 𝑖)! + … .+ 1
(1+ 𝑖)! )
where n is the number of months until the loan is paid. Using the principles of geometric series,
we can derive an equation to solve for this sum for any number of months(n).
𝐿𝑒𝑡 𝑆 = 1
1+ 𝑖 + 1
1+ 𝑖 ! + … .+ 1
1+ 𝑖 !
Then,
𝑃 = 𝑋 ∗ 𝑆
Let
𝑟 = 1
(1+ 𝑖)
Then,
𝑆 = 𝑟! + 𝑟! + 𝑟! + … .+𝑟!
We can solve for S by multiplying both sides by r.
𝑟𝑆 = 𝑟! + 𝑟! + 𝑟! + … .+𝑟! + 𝑟!!!
13
𝑆 − 𝑟𝑆 = 𝑟 − 𝑟!!! 𝑆(1− 𝑟) = 𝑟 − 𝑟!!!
𝑆 = 𝑟 − 𝑟!!!
1− 𝑟
We can then plug this into our original equation to find
𝑃 = 𝑋 ∗ 𝑆
𝑃 = 𝑋 ∗ 𝑟 − 𝑟!!!
1− 𝑟
𝑋 = 𝑃 ∗ 1− 𝑟
𝑟 − 𝑟!!!
𝑊ℎ𝑒𝑟𝑒 𝑟 = 1
(1+ 𝑖)
This yields the equation for the monthly payment, X:
𝑋 = 𝑃 ∗ 𝑖 (1+ 𝑖)!
(1+ 𝑖)! − 1
Where
P = principal loan amount
i = interest rate
n = months in pay period
R Software was utilized for computations and graphs throughout the report. The R code that was
used is given below. By substituting R(annual interest rate), P(principal), and N(number of
months), one can reproduce the results of this report and find other results for different rates,
principal loan amount, and number of years.
##### Mortgage Rate Calculator ##### R<-0.05 #annual rate P<-200000 #principal N<-30 #number of years rate<-R/(12) #monthly rate n<- N*12 #number of months rate n int<- P * rate #interest for first month int
14
####### Formula for finding x, monthly payment ####### X<-((P*rate)*((1+rate)^n))/(((1+rate)^n)-1) X I<- X*n - P ##Total interest earned by bank I TotalPayment<- P+I TotalPayment PercenttoInt<-I/TotalPayment PercenttoPayment<-1-PercenttoInt TotalMortgage<-round(cbind(TotalPayment,I,PercenttoPayment,PercenttoInt),2) TotalMortgage #####Pie Chart ###### PiePercentPay<-round(PercenttoPayment,2) PiePercentInt<-round(PercenttoInt,2) Pie<-c(PiePercentPay,PiePercentInt) pie(Pie,main="Percentages of Total Mortgage, 30 year", col=rainbow(length(Pie)),labels=c(PiePercentPay,PiePercentInt)) legend("topright" ,c("Payment","Interest"), cex=0.7, fill = rainbow(length(Pie))) Principal<-200000 month<-0 PPD<-0 #principal paid down that month PPI<-0 #percent of principal towards interest Interest<-0 AccumInt<-0 #Accumulative Interest for(i in 1:n) { month[i]<-i Interest[i]<- Principal[i]*rate PPD[i]<- X -Interest[i] Principal[i+1]<-Principal[i]-PPD[i] PPI[i]<-Interest[i]/X AccumInt[i+1]<-AccumInt[i]+Interest[i] } mortgage<-round(cbind(month, Principal, Interest, PPD, PPI, AccumInt),2) mortgage ####### Plots of Data plot(Interest, main="Plot of Interest", sub="How Much of the Monthly Payment Goes to Interest Over Time", xlab="Month", ylab="Dollars",type="o",col="mediumspringgreen") plot(AccumInt, main="Plot of Accumulative Interest", sub="Earnings Over the Entire Mortgage Period", xlab="Month", ylab="Dollars",type="o",col="plum2")
15
plot(PPI, main="Percent of Payment that Goes to Interest", xlab="Month", ylab="Percentage",type="o",col=ifelse(PPI<.5, "brown2", "seagreen2"))