Amortization CalculatoR

Total Monthly Payments (Principal + Interest)

The following formula calculates the monthly mortgage payment, which includes both the principal and interest:

The code below is the equation translated to R. I’ve also defined the other variables we’ll need with default values.

# Define the variables

term <- 360 # 30 years in months
original_loan_amount <- 150000 # $150,000 loan
annual_rate <- 0.04 # 4% interest rate
monthly_rate <- annual_rate/12 # rate converted to monthly rate

# Formula to calculate monthly 
# principal + interest payment

total_PI <- original_loan_amount * 
   (monthly_rate * (1 + monthly_rate) ^ term)/
   (((1 + monthly_rate) ^ term) - 1)

In this example, the total monthly payment (i.e. total_PI) is \(\$716.12\), which when multiplied by the term of the loan results in a total payment amount of \(\$257,804.26\) – in other words, over the life of this loan, we’ll be paying \(\$107,804.26\) in interest.

Breakdown of Principal and Interest in Each Payment

The portion of each payment that goes towards the principal – or the original loan amount – is what pays down your loan and builds equity. The portion that goes to interest is the cost you pay for the bank to give you the loan. Basically, the amount of interest you pay each month is determined by multiplying the monthly_rate by the remaining balance of the loan. This number will be less than the total_PI, and whatever the difference is between the two will be the principal portion of the payment.

Since total_PI is fixed and the interest portion of the payment is a function of the remaining loan balance, as you pay down the loan, the portion that goes to interest decreases, which results in a comparable increase in the portion that goes to principal.

Now we’re getting very close to being able to create our amortization table. We know what our total_PI is, so now we just need to write code that will calculate the interest and principal portion of each payment, and then calculate the remaining principal.

The following code will create numberic vectors for each value, with the length of each being set to the term of the loan.

# Initialize the vectors as numeric with a length equal
# to the term of the loan.

interest <- principal <- balance <- date <- vector("numeric", term)

loan_amount <- original_loan_amount
# For loop to calculate values for each payment

for (i in 1:term) {
   intr <- loan_amount * monthly_rate
   prnp <- total_PI - intr
   loan_amount <- loan_amount - prnp
   
   interest[i] <- intr
   principal[i] <- prnp
   balance[i] <- loan_amount
}

# Throw vectors into a table for easier use

library(tidyverse) # for data manipulation going forward

standard_schedule <- tibble(payment_number = 1:term,
                            interest,
                            principal,
                            balance)

# Print head of standard_schedule

library(knitr) # both libraries for printing tables
library(kableExtra)

standard_schedule %>%
  
  # Format columns to display as dollars
  
  modify_at(c("interest", "principal", "balance"), scales::dollar,
            largest_with_cents = 1e+6) %>%
  
  # Limit to first 10 payments
  
  head(10) %>% 
  kable(booktabs = T) %>%
  kable_styling()

With our payments now calculated for the full term of the loan, we can visualize the interest and principal portions in a line graph.

# Pivot longer makes it easier to visualize,
# but isn't totally necessary

standard_schedule %>%
  pivot_longer(cols = c("interest", "principal"), 
               names_to = "Payment Portion", 
               values_to = "amount") %>%
  ggplot(aes(payment_number, amount, color = `Payment Portion`)) +
  geom_line() +
  
  # '#85bb65' is the color of $$$
  
  scale_color_manual(values = c("red", "#85bb65")) +
  
  # Change the theme for better appearance
  
  theme_minimal() +
  scale_y_continuous(labels = scales::dollar) +
  labs(title = "Payment Portions of Monthly Mortgage")

Judging from this visual, we can estimate that the portion of each payment going to principal will exceed the portion going to interest at about the 150th payment. To be exact, we can filter the amortization table to find where principal exceeds interest for the first time.

# Filter for interest less than principal

standard_schedule %>%
  filter(interest < principal) %>%
  
  # Include only the first observation
  
  head(1) %>%
  
  #  Prettify for table
  
  modify_at(c("interest", "principal", "balance"), scales::dollar,
            largest_with_cents = 1e+6) %>%
  kable(booktabs = T) %>%
  kable_styling()

So payment number 153 is the first payment where the portion going to principal is greater than the portion going to interest.

Adding Dates

Now, a natural follow-up question is when will the 153rd payment take place? This is actually an easy question to answer – all we need to do is add a date vector to our table. We will create a variable with the date of the first monthly payment, and then the vector will be a sequence of dates by month for the term of the loan. The following code accomplishes this task.

library(lubridate)

# Set first payment date

first_payment <- "2020-01-01"


# Add vector as variable to standard schedule

standard_schedule <- standard_schedule %>%
  mutate(date = seq(from = ymd(first_payment), by = "month",
            length.out = term)) %>%
  select(date, everything())

standard_schedule %>%
  modify_at(c("interest", "principal", "balance"), scales::dollar,
          largest_with_cents = 1e+6) %>%
  head(10) %>%
  kable(booktabs = T) %>%
  kable_styling()

Now when we look at the 153rd payment, we see the date is September 01, 2032.

standard_schedule %>%
  filter(interest < principal) %>%
  head(1) %>%
  modify_at(c("interest", "principal", "balance"), scales::dollar,
            largest_with_cents = 1e+6) %>%
  kable(booktabs = T) %>%
  kable_styling()

The Effect of Extra Payments

For the last payment, the interest is calculated as normal, but the principal portion is only the remainder of the balance less the interest instead of the remainder of the total_PI we calculated in the beginning. The extra payment is lowered to zero as well, since it isn’t necessary.

We now see that by making an extra monthly payment of $100, we will make 74 fewer payments and we will make our final payment on October 01, 2043 instead of December 01, 2049.

And finally, we will pay $25,205.42 less in interest over the life of the loan, while the amount of principal we pay will remain the same, of course.

The visual below depicts the resulting savings.

# Viz is easier if schedules are joined
# First, create matching variables 

ss <- standard_schedule %>%
  mutate(schedule = "standard",
         extra = 0)

us <- updated_schedule %>%
  mutate(schedule = "updated")

both_schedules <- bind_rows(ss, us)

both_schedules %>%
  group_by(schedule) %>%
  mutate(cum_int = cumsum(interest),
            cum_prnp = cumsum(principal)) %>%
  pivot_longer(cols = c("cum_int", "cum_prnp"), 
               names_to = "Payment Portion", 
               values_to = "amount") %>%
  filter(`Payment Portion` != "cum_prnp") %>%
  ggplot(aes(date, amount, 
             group = schedule)) +
  geom_line(aes(linetype = schedule), color = "red") +
  
  # Change the theme for better appearance
  
  theme_minimal() +
  scale_y_continuous(labels = scales::dollar) +
  scale_linetype_discrete(labels = c("Standard Schedule", "Updated Schedule")) +
  labs(title = paste("Amount in Interest Saved: ", scales::dollar(sum(interest) - sum(interest1)), sep = ""),
       subtitle = paste("By Paying", scales::dollar(extra), "Extra Per Month", sep = " "),
       y = "Total Interest Paid",
       x = "Payment Date", linetype = "",
       caption = paste("Based on ", scales::dollar(original_loan_amount), " loan over " , (term/12), " years.", sep = "")) +
  guides(linetype = guide_legend(override.aes = list(col = "red")))