Mortgage loans, car loans, some commercial real estate loans and other loans are structured with a fixed interest rate and equal payments that amortize the loan. In this post, we review the math of level payment loans and present a function for analyzing them. Files for this post are found at https://github.com/karlpolen/loanamort
When considering level payment loans, there are four variables:
\( r \) is the interest rate, expressed as a decimal, not a percent. \( r > -1 \). Values of \( r \) equal to -1 or less imply a lender does not want to be repaid and do not need to be considered. Values be 0 and -1 are useful because lending rates can be below zero and, more importantly, they are useful for ex post analysis of loans that earn a negative return because they were not repaid according to their terms.
\( n \) is the number of periods in the loan. Initially, we only consider periodicity where the interest \( r \) is the same time period as \( n \). In the actual R function accompanying this post, we provide the capability to state the interest rate as an “APR”, which by market convention is 12 times a monthly interest rate for mortgages and other loans with monthly payments.
\( pmt \) is the periodic payment.
\( bal0 \) is the initial loan balance.
If you state three of the above values, you can calculate the remaining one.
We add two derivative variable for convenience in presenting formulas.
\( d \), a discounting factor for determing present values is \( \frac{1}{1+r} \). \( d^n \) is the required discount to convert a payment in period \( n \) to its present value.
\( f \), a factor to convert a current value to a future value is \( 1+r \). \( f^n \) is the future value factor for period \( n \)
Some useful identities follow:
\( f=\frac{1}{d} \)
\( r=\frac{1}{d}-1 = \frac{1-d}{d} \)
\( \frac{1}{r}= \frac{d}{1-d} \)
Let's consider the case where \( n = \infty \)
\( perp= \sum_{i=1}^{\infty} pmt \cdot d^i \)
\( d \cdot perp = \sum_{i=2}^{\infty} pmt \cdot d^i \)
\( perp \cdot (1-d) = pmt \cdot d \)
\( perp = pmt \cdot \frac{d}{1-d} = \frac {pmt}{r} \: \) (1)
\( bal0 = \sum_{t=1}^{n} pmt \cdot d^t \)
\( d \cdot bal0 = \sum_{t=2}^{n+1} pmt \cdot d^t \)
\( bal0 \cdot (1-d) = pmt \cdot d - pmt \cdot d^{n+1} \)
\( bal0 = \frac {pmt}{r} - pmt \cdot d^n \cdot \frac {d}{1-d} \)
\( bal0 = \frac {pmt}{r} - \frac {pmt}{r} \cdot d^n = \frac {pmt}{r} \cdot (1 - d^n) = \frac {pmt}{r} \cdot (1-{(\frac{1}{1+r})}^n) = perp - perp \cdot d^n \: \) (2)
Note: this formula amounts to saying the value of an annuity for n payments at time zero is the same as the value of a perpetuity at time zero minus the present value (at time zero) of a sale of a perpetuity at time n
from (2)
\( pmt = \frac{bal0 \cdot r}{1-d^n} = \frac{bal0 \cdot r}{1-{(\frac{1}{1+r})}^n} \: \) (3)
from (3)
\( 1-d^n = \frac{bal0 \cdot r}{pmt} \)
\( d^n= 1-\frac{bal0 \cdot r}{pmt} \)
\( n \cdot log(d) = log(1-\frac{bal0 \cdot r}{pmt}) \)
\( n = \frac {log(1-\frac{bal0 \cdot r}{pmt} )}{log(d)} \)
The cash flow of the loan is in the form of a polynomial.
\( 0= -bal0 + pmt \cdot d^1 + pmt \cdot d^2 + \dots + pmt \cdot d^n \)
and we are looking for the value of \( d \) where the polynomial evaluates to zero. From the rule of signs, we know that this polynomial has a single positive root.
Recall that \( d=\frac{1}{1+r} \) . Let's look at a plot of \( d \) as a function of \( r \)
r = seq(-3, 2, 0.01)
d = 1/(1 + r)
plot(r, d, type = "l", col = "blue", ylim = c(-10, 10))
abline(v = 0)
abline(h = 0)
Note the plot is discontinuous when \( r=-1 \), i.e. the rate is -100%. So, it turns out that stating the polynomial in a form where \( d \) is the unknown variable has a useful property. We can solve for the one positive root \( d \) which can then be further evaluated to the single interest rate (which can be positive or negative) that causes the net present value of the cash flow to be zero.
As discussed above, we need not concern ourselves with \( r \leq -1 \). Therefore, we need not calculate roots for \( d \leq 0 \),
R provides a function uniroot which can reliably be used to find a single root in a known range.
Code as simple as the following will do the trick. For calculation efficiency, you can confine the search for values of d between 0 and 1 for situations where the sum of the cash flows is greater than zero and the interest rate we are looking is, therefore, known to be greater than zero.
cf=c(-bal0,rep(pmt,n))
if(0<=sum(cf)) {
rootrange=c(0,1.01)} else {
rootrange=c(1,1000)
}
d=(uniroot(function(d) {sum(cf*d^(0:n))}),c(0,1000))$root
r=(1/d)-1
Now that we have determined methods for finding the values of \( n \), \( bal0 \), \( r \), and \( pmt \) the next task is to consider how a level payment loan will amortize. We will want to know the balance of the loan after a given payment and how each payment is divided between interest and principal. We define three new variables:
\( bal_t \), a time series of the loan balance after payment \( t \), subject to \( bal \geq 0 \), and \( t \) is the time after payments \( 1:n \)
\( int_t \), a time series of the interest portion of payment \( t \)
\( prin_t \), a time series of the principal portion of payment \( t \)
\( bal_i \) is a particular balance after payment \( i \). This can be calculated as the future value of \( bal0 \) at \( i \) minus the future value of \( i \) payments.
\( bal_i = f^i \cdot bal0 - \sum_{j=0}^{i-1} f^j \cdot pmt \)
The summation term of this equation can be simplified as follows.
\( pmt_{fv}=\sum_{j=0}^{i-1} f^j \cdot pmt \)
\( f \cdot pmt_{fv} = \sum_{j=1}^{i} f^j \cdot pmt \)
\( (1-f) \cdot pmt_{fv} = pmt-pmt \cdot f^i \)
Noting that \( 1-f=-r \)
\( pmt_{fv}=\frac{pmt \cdot f^i - pmt}{r} \)
Substituting into the original equation, we get
\( bal_i = bal0 \cdot f^i -\frac{pmt \cdot f^i - pmt}{r} \)
Once we know the equation for \( bal_i \) the remaining equations are easy.
\( int_i = r* bal_{i-1} \)
\( prin_i = bal_i -bal_{i-1} \)
We're now ready to right code. Let's make a function loanamort that takes as input the four variables r, n, pmt and bal0. It requires that at least three of these values are provided and if only three are provided it calculates the missing value. It then calculates an amortization table for a loan with these parameters.
To make the function more practical, we add three additional optional variables that allow you to return zoo objects with actual dates. start is a starting date of bal0. freq is the frequency of payments, 12 for monthly. If freq is not equal to 1, then r will be converted to a rate for the frequency. apr is a logical variable indicating if the given rate is stated as an “annual percentage rate”. If TRUE, then r is divided by freq before calculating. Otherwise r will be converted to a rate for freq taking a root of 1+r based on freq. If apr is TRUE then n is multiplied by freq before calculations are done. In other words, if you want to analyze a thirty year mortgage loan quoted in the conventional way, you set apr to TRUE, provide the annual percentage rate for r and provide 30 for n.
The function returns a list of values for the four loan parameters plus time series for the loan parameters.
Here is the code for the function.
require(zoo)
require(lubridate)
loanamort = function(r = NULL, bal0 = NULL, pmt = NULL, n = NULL, apr = FALSE,
start = NULL, freq = 1) {
ans = list()
risnull = is.null(r)
bal0isnull = is.null(bal0)
pmtisnull = is.null(pmt)
nisnull = is.null(n)
if (1 < sum(c(risnull, bal0isnull, pmtisnull, nisnull)))
stop("loanamort error -- need to provide at least three parameters")
n.f = n
if (apr)
n.f = n * freq
if (!risnull) {
if (apr) {
r.f = r/freq
} else {
r.f = -1 + (1 + r)^(1/freq)
}
} else {
cf = c(-bal0, rep(pmt, n.f))
if (0 <= sum(cf)) {
rootrange = c(0, 1.01)
} else {
rootrange = c(1, 1000)
}
d = (uniroot(function(d) {
sum(cf * d^(0:n.f))
}, rootrange))$root
r.f = (1/d) - 1
}
d = 1/(1 + r.f)
f = 1 + r.f
if (pmtisnull)
pmt = (bal0 * r.f)/(1 - d^n.f)
perp = pmt/r.f
if (bal0isnull)
bal0 = perp - perp * (d^n)
if (pmt <= (r.f * bal0))
stop(paste(pmt, r.f * bal0, "payment must be greater than interest"))
if (nisnull)
n.f = ceiling(log((1 - (bal0 * r.f)/pmt))/log(d))
i = 1:n.f
bal = pmax(0, ((bal0 * f^i) - (((pmt * f^i) - pmt)/r.f)))
balall = c(bal0, bal)
int = balall[i] * r.f
prin = -diff(balall)
if (!is.null(start)) {
bal = zooreg(bal, start = start + 1/freq, freq = freq)
int = zooreg(int, start = start + 1/freq, freq = freq)
prin = zooreg(prin, start = start + 1/freq, freq = freq)
}
if (apr) {
ans$r = r.f * freq
ans$n = n.f/freq
} else {
ans$r = -1 + ((1 + r.f)^freq)
ans$n = n.f
}
ans$pmt = pmt
ans$bal0 = bal0
ans$freq = freq
ans$start = start
ans$apr = apr
ans$bal = bal
ans$prin = prin
ans$int = int
return(ans)
}
In May of 2013, I bought a car borrowing 20,000 at 8% apr with monthly payments for 5 years. What's the payment?
ans1 = loanamort(bal0 = 20000, r = 0.08, n = 5, apr = TRUE, freq = 12)
ans1$pmt
## [1] 405.5
What is the total interest paid on theloan?
sum(ans1$int)
## [1] 4332
What is the balance after a year?
ans1$bal[12]
## [1] 16611
A year later, I get a raise and decide to increase my payment to $500 per month. When will the loan be paid off?
start = as.yearmon("2014-5")
ans1a = loanamort(bal0 = ans1$bal0, r = 0.08, pmt = 500, apr = TRUE, freq = 12)
start + ans1a$n
## [1] "Apr 2018"
I know I borrowed $20,000 for my car and the original payments were $405.50 per month for 5 years, but lost the papers and am curious what my interest rate is.
ans1b = loanamort(bal0 = 20000, pmt = ans1$pmt, apr = TRUE, n = 5, freq = 12)
ans1b$r
## [1] 0.0799
I borrowed $100,000 to buy a house in March, 2013 at 5% interest with monthly payments for 30 years. I am a calendar year tax payer and want to know my interest payments by year for the duration of the loan. In this case, the function returns the amortization vectors as zoo objects with the index as yearmon class. You can aggregate by year in the way shown in the second line of code.
ans2 = loanamort(bal0 = 1e+05, r = 0.05, n = 30, apr = TRUE, freq = 12, start = as.yearmon("2013-3"))
aggregate(ans2$int, list(year(time(ans2$int))), sum)
## 2013 2014 2015 2016 2017 2018 2019 2020 2021
## 3731.80 4910.24 4831.88 4749.51 4662.92 4571.91 4476.24 4375.67 4269.96
## 2022 2023 2024 2025 2026 2027 2028 2029 2030
## 4158.84 4042.04 3919.26 3790.20 3654.54 3511.93 3362.03 3204.46 3038.83
## 2031 2032 2033 2034 2035 2036 2037 2038 2039
## 2864.73 2681.71 2489.34 2287.12 2074.55 1851.11 1616.24 1369.36 1109.84
## 2040 2041 2042 2043
## 837.04 550.29 248.86 13.33
Show an amortization schedule for this loan by calendar year. In the below code, we aggregate by year, summing for the cash flow and taking the last item in each year for the balance sheet. We show a function lastinvec which grabs the last item in a vector.
lastinvec = function(x) tail(x, 1)
Interest = aggregate(ans2$int, list(year(time(ans2$int))), sum)
Principal = aggregate(ans2$prin, list(year(time(ans2$prin))), sum)
Balance = aggregate(ans2$bal, list(year(time(ans2$bal))), lastinvec)
data.frame(Interest, Principal, Balance)
## Interest Principal Balance
## 2013 3731.80 1100 98900
## 2014 4910.24 1532 97369
## 2015 4831.88 1610 95759
## 2016 4749.51 1692 94066
## 2017 4662.92 1779 92288
## 2018 4571.91 1870 90418
## 2019 4476.24 1966 88452
## 2020 4375.67 2066 86386
## 2021 4269.96 2172 84214
## 2022 4158.84 2283 81931
## 2023 4042.04 2400 79531
## 2024 3919.26 2523 77008
## 2025 3790.20 2652 74357
## 2026 3654.54 2787 71569
## 2027 3511.93 2930 68640
## 2028 3362.03 3080 65560
## 2029 3204.46 3237 62322
## 2030 3038.83 3403 58919
## 2031 2864.73 3577 55342
## 2032 2681.71 3760 51582
## 2033 2489.34 3953 47629
## 2034 2287.12 4155 43475
## 2035 2074.55 4367 39107
## 2036 1851.11 4591 34517
## 2037 1616.24 4826 29691
## 2038 1369.36 5073 24619
## 2039 1109.84 5332 19287
## 2040 837.04 5605 13682
## 2041 550.29 5892 7790
## 2042 248.86 6193 1597
## 2043 13.33 1597 0