Code for amortization schedule

amortization_example.R

# Amortization 
# https://www.wikihow.com/Calculate-Amortization

# Step 1 - Gather the information you need to calculate the loan's amortization
property_value = 240000
loan_amount = .75 
down_payment = property_value-(loan_amount * property_value)
debt_borrowed = property_value - down_payment
interest_rate = 0.06 # annual interest rate
loan_term = 360 # months 

# Calculate the principal / interest over the loan term 
# Initialize outputs 
remaining_loan_amount = seq(1,loan_term,1)
interest = seq(1,loan_term,1)
principal = seq(1,loan_term,1)
interest_principal = seq(1,loan_term,1)
# for loop to create amortization schedule 
i=1
for (i in 1:length(remaining_loan_amount)) {
  # set the first row at 1 = 1 - this is the first principal and interest payment
  if (i == 1) {
  I = debt_borrowed *(interest_rate/12) # note converting annual interest to monthly (interest_rate/12)
  P = (I / (1-(1/(1+interest_rate/12)^(loan_term)))) - I # Calculate the interest + principal then subtract interest
  IP = I + P
  interest[i] = I
  principal[i] = P
  interest_principal[i] = IP
  remaining_loan_amount[i] = debt_borrowed - P 
  } else {
    # Using the data from the first row - continue to pay down princial and debt
    I = remaining_loan_amount[i-1] *(interest_rate/12) # note converting annual interest to monthly (interest_rate/12)
    P = (I / (1-(1/(1+interest_rate/12)^(loan_term-i)))) - I # Calculate the interest + principal then subtract interest also note loan_term-i = reducing the loan term through the course of the loan
    IP = I + P
    interest[i] = I
    principal[i] = P
    interest_principal[i] = IP
    remaining_loan_amount[i] = remaining_loan_amount[i-1] - P 
    cat("This is Month",i,"Amoritization Schedule\n")
  }
}

# data prune
principal[loan_term] = NA
interest[loan_term] = NA
remaining_loan_amount[loan_term] = NA

# plot data
month_index = seq(1,loan_term,1)
ggplot() + 
  geom_line(aes(x = month_index, y = interest), color = "red") +
  geom_line(aes(x = month_index, y = principal), color = "blue") +
  xlab('Loan Term Remaining (months)') +
  ylab('Per month $ payment')+
  ggtitle("Mortgage Amortization - Principal / Interest Relationship")+
  theme(legend.position = "none")+
  scale_x_continuous(breaks = round(seq(1, loan_term, by = 5)))+
  annotate("text", x = 75, y = 400, label = "Monthly Interest Payments",colour = "red")+
  annotate("text", x = 75, y = 50, label = "Monthly Principal Payments",colour = "blue")

# calculate % stakes in the deal over the amortization schedule 
owner_equity_stake = (cumsum(principal) + down_payment) / property_value
bank_equity_stake = remaining_loan_amount / property_value
month_index = seq(1,loan_term,1)

ggplot() + 
  geom_line(aes(x = month_index, y = bank_equity_stake), color = "red") +
  geom_line(aes(x = month_index, y = owner_equity_stake), color = "blue") +
  xlab('Loan Term Remaining (months)') +
  ylab('%')+
  ggtitle("Mortgage Amortization - Time Dependant Owner and Bank Equity In The Deal")+
  theme(legend.position = "none")+
  scale_x_continuous(breaks = round(seq(1, 360, by = 19)))+
  annotate("text", x = 40, y = .8, label = "Bank Equity Stake In Deal",colour = "red")+
  annotate("text", x = 40, y = .2, label = "Owner Equity Stake In Deal",colour = "blue")

# Cumulative return 
month_index = seq(1,360,1)
ggplot() + 
  geom_line(aes(x = month_index, y = cumsum(principal)), color = "blue") +
  xlab('Loan Term Remaining (months)') +
  ylab('Deal Equity')+
  ggtitle("Mortgage Amortization - Cumulative Equity In The Deal")+
  theme(legend.position = "none")+
  scale_x_continuous(breaks = round(seq(1, 360, by = 19)))+
  scale_y_continuous(breaks = round(seq(0, 75000, by = 5000)))+
  annotate("text", x = 115, y = 65000, label = "Cumulative Sum - Monthly Principal Payments",colour = "blue")


# Real Estate Depreciation
library(fredr)
library(lubridate)
library(scales)
library(ggplot2)
# Step 1 - Calculate Depreciation 
cost_basis = 275000
useful_life = 27.5
annual_depreciation_value = cost_basis / useful_life # full year depreciation value
year_placed_in_service = 2019 # starting year placed in-service 
month_placed_in_service = 2 # starting month placed in-service +++= note used to calculate the partial month depreciation value
start_date = paste0(month_placed_in_service,"-",01,"-",year_placed_in_service)
start_date = as.Date(start_date, format="%m-%d-%Y")
library(lubridate)
end_date =  start_date + months(useful_life*12) # end date
month_taken_out_of_service = month(end_date) # end month +++= note used to calculate the partial month depreciation value
year_taken_out_of_service = year(end_date) # end year
# calculate first and end year depreciation
first_year_depreciation = (((12 - month_placed_in_service)+.5) / 12) * (cost_basis / useful_life)
end_year_depreciation = ((month_taken_out_of_service-.5) / 12) * (cost_basis / useful_life)

# Step 2 - Build the depreciation schedule 
year_index = seq(year_placed_in_service,year_taken_out_of_service,1)
depreciation_expense = rep(annual_depreciation_value,length(year_index))
depreciation_expense[1] = first_year_depreciation
depreciation_expense[length(year_index)] = end_year_depreciation
accumulated_depreciation = cumsum(depreciation_expense)

# Step 3 - Calculate tax savings 
marginal_tax_rate = .24
tax_savings = depreciation_expense * marginal_tax_rate

ggplot() + 
  geom_point(aes(x = year_index, y = cumsum(tax_savings)), color = "blue") +
  geom_line(aes(x = year_index, y = cumsum(tax_savings)), color = "blue")+
  xlab('Useful Life (months)') +
  ylab('Tax Savings')+
  ggtitle("Real Estate Depreciation - Cumulative Tax Savings @ marginal tax rate 24 %")+
  theme(legend.position = "none")+
  scale_x_continuous(breaks = round(seq(year_placed_in_service, year_taken_out_of_service, by = 2)))+
  scale_y_continuous(breaks = round(seq(0, max(cumsum(tax_savings)), by = 2000)))