calcMortgage.R

# load libraries
library(ggplot2)
library(plyr)
library(reshape2)


# function to calculate monthly payments
paymnth=function(loanamt,irate,nyr,xtraprin,xtraprintime){

  # Calculate monthly payments
  
  # loanamt - loan amount
  # irate -  interest rate
  # nyr number of years of mortgage
  # xtraprin extra amount paid towards principal per period
  # xtraprintime period frequency when extra principal is paid
  
  # NPV factor  
  disc=1/(1+(irate/12))
  discfact=(1-disc^(nyr*12))/(1-disc)
  
  # monthly payment (principal + interest)
  mnthpay=loanamt/discfact*(1/disc)
  
  # Monthly split of interest and principal
  nmnth=nyr*12
  
  imnth=rep(0,nmnth) # interest paid each month
  pmnth=rep(0,nmnth) # principal paid each month
  resloan=rep(0,nmnth) # remaining principal after payment in a month
  
  for(i in 1:nmnth){
    
    if(i==1){
      
      imnth[i]=loanamt*(irate/12)
      pmnth[i]=mnthpay-imnth[i]
      resloan[i]=loanamt-pmnth[i]
      
    } else if (resloan[i-1]>0) {
      
      imnth[i]=resloan[i-1]*(irate/12)
      if((i-1) %% xtraprintime == 0){
        pmnth[i]=mnthpay-imnth[i]+xtraprin
      } else {
        pmnth[i]=mnthpay-imnth[i]
      }
      pmnth[i]=min(pmnth[i],resloan[i-1])
      resloan[i]=resloan[i-1]-pmnth[i]
      
    }
    
  }
  
  
  df=data.frame(imnth=imnth,pmnth=pmnth,resloan=resloan)
  df$mnth=seq(1:nmnth)
  df$yr=rep(c(1:nyr),each=12)
  df$totmnth=df$imnth+df$pmnth
  df$cum_imnth=cumsum(df$imnth)
  df$cum_pmnth=cumsum(df$pmnth)

  return(df)

} # end paymnth function


## home price, downpayment and mortgage rates
homeprice=250000 # home price
pctdown=0.2 # percentage downpayment
loanamt=homeprice*(1-pctdown) # loan amount
nyr=30 # loan time period in years
irate=4.5e-2 # interest rate
xtraprin=0 # extra amt to principal
xtraprintime=12 # frequency of extra amt to principal

# property tax and home owner insurance
ptaxrate=0.01 # annual property tax
ptaxyr=homeprice*ptaxrate
insrate=60/homeprice*100000 # home insurance in $ per month per $100000 in home value

# tax break
dedother=5000 # other deductions in itemized deduction
taxrate=0.25 # tax rate
dedstd=12600 # standard deduction

# 401K loan
loan401K=0 # 401K loan amount
irate401K=4.5e-2 # interest rate for 401K
nyr401K=10 # period for 401K


pi_df=paymnth(loanamt,irate,nyr,xtraprin,xtraprintime)
pi_df$ptax=homeprice*ptaxrate/12
pi_df$hins=homeprice*insrate/100000

intyr=ddply(pi_df,c("yr"),summarize,intyr=sum(imnth))
intyr$taxbreakmnth=pmax(intyr$intyr+ptaxyr+dedother-dedstd,0)*0.25/12

pi_df2=merge(pi_df,intyr[,c("yr","taxbreakmnth")],by=c("yr"))


pay401K=paymnth(loan401K,irate401K,nyr401K,0,1)
pay401K$totmnth401K=pay401K$totmnth

pi_df3=merge(pi_df2,pay401K[,c("mnth","totmnth401K")],by=c("mnth"),all.x=TRUE)
pi_df3$totmnth401K[is.na(pi_df3$totmnth401K)]=0
pi_df3$totmnthfull=pi_df3$totmnth+pi_df3$ptax+pi_df3$hins-pi_df3$taxbreakmnth+pi_df3$totmnth401K

# upfront cash outlay
initcash=homeprice*pctdown-loan401K
initcash

ggplot(data=pi_df3,aes(x=mnth,y=totmnthfull))+geom_point()

pi_df3tall=melt(pi_df3,id.vars=c("mnth"),measure.vars=c("imnth","pmnth","ptax","hins"),
                variable.name=c("varname"),value.name=c("value"))

ggplot(data=pi_df3tall,aes(x=factor(mnth),y=value,fill=varname))+geom_bar(stat="identity")


# show payment for a given set of years
yrlist=c(1,2)
pay3yr=pi_df3[pi_df3$yr %in% yrlist,
              c("mnth","yr","totmnth","ptax","hins","taxbreakmnth","totmnth401K","totmnthfull")]
names(pay3yr)=c("mnth","yr","p_and_i","ptax","hins","taxbreakmnth","totmnth401K","totmnthfull")
pay3yr