I have the following:
library(financialMath)
library(reshape2)
library(scales)
################################################################################
mortgage <- function(P = 500000, I = 6, L = 30, amort = TRUE) {
J <- I / (12 * 100)
N <- 12 * L
M <- P * J / (1 - (1 + J)^(-N))
monthPay <<- M
# Calculate Amortization for each Month
if (amort == TRUE) {
Pt <- P # current principal or amount of the loan
currP <- NULL
while (Pt >= 0) {
H <- Pt * J # this is the current monthly interest
C <- M - H # this is your monthly payment minus your monthly interest, so it is the amount of principal you pay for that month
Q <- Pt - C # this is the new balance of your principal of your loan
Pt <- Q # sets P equal to Q and goes back to step 1. The loop continues until the value Q (and hence P) goes to zero
currP <- c(currP, Pt)
}
monthP <- c(P, currP[1:(length(currP) - 1)]) - currP
aDFmonth <<- data.frame(
Month = 1:length(currP),
Year = sort(rep(1:ceiling(N / 12), 12))[1:length(monthP)],
Balance = c(currP[1:(length(currP))]),
Payment = monthP + c((monthPay - monthP)[1:(length(monthP))]),
Principal = monthP,
Interest = c((monthPay - monthP)[1:(length(monthP))])
)
aDFmonth <<- subset(aDFmonth, Year <= L * 12)
aDFyear <- data.frame(
Amortization = tapply(aDFmonth$Balance, aDFmonth$Year, max),
Annual_Payment = tapply(aDFmonth$Payment, aDFmonth$Year, sum),
Annual_Principal = tapply(aDFmonth$Principal, aDFmonth$Year, sum),
Annual_Interest = tapply(aDFmonth$Interest, aDFmonth$Year, sum),
Year = as.factor(na.omit(unique(aDFmonth$Year)))
)
aDFyear <<- aDFyear
}
return(aDFmonth)
# if (plotData == TRUE) {
# aDFyear2 <- aDFyear %>%
# rename(
# Interest = Annual_Interest,
# Payment = Annual_Payment,
# Principal = Annual_Principal
# )
# aDFyear2$Year <- as.factor(aDFyear2$Year)
# aDFyear2 <- melt(aDFyear2[, c("Interest", "Principal", "Year")], id.vars = "Year")
#
# ggplot(aDFyear2, aes(x = Year, y = value, fill = variable)) +
# geom_bar(position = "fill", stat = "identity") +
# labs(y = "Payment") +
# scale_y_continuous(labels = percent) +
# theme_minimal() +
# theme(legend.title = element_blank(), legend.position = "top")
# }
}
m = mortgage(P = 200000, I = 2, L = 25, amort = TRUE)
m %>%
head()
Month Year Balance Payment Principal Interest
1 1 1 199485.6 847.7087 514.3753 333.3333
2 2 1 198970.4 847.7087 515.2326 332.4760
3 3 1 198454.3 847.7087 516.0914 331.6173
4 4 1 197937.3 847.7087 516.9515 330.7572
5 5 1 197419.5 847.7087 517.8131 329.8956
6 6 1 196900.9 847.7087 518.6761 329.0326
amort.table(Loan = 200000, n = 12*25, i = 0.02, pf = 12) %>%
.[[1]] %>%
head()
Year Payment Interest Paid Principal Paid Balance
1 0.08 845.95 330.32 515.63 199484.4
2 0.17 845.95 329.46 516.48 198967.9
3 0.25 845.95 328.61 517.34 198450.5
4 0.33 845.95 327.76 518.19 197932.4
5 0.42 845.95 326.90 519.05 197413.3
6 0.50 845.95 326.04 519.90 196893.4
The amort.table() is taken from the financialMath package and the mortgage function from here: https://github.com/AntoineSoetewey/mortgage-calculator/blob/master/app.R
I am trying to replicate the mortgage function using the amort.table() function but I get slightly different results.
The payment and principal is only slightly out on both functions but this causes very different results over the whole term of the loan. Looking at the shiny App version here ( https://antoinesoetewey.shinyapps.io/mortgage-calculator/ ) we see the following summary:
Summary
Principal (loan amount): 200,000
Annual interest rate: 2%
Term: 25 years (300 months)
Monthly payment: 847.71
Total cost: 200,000 (principal) + 54,312.6 (interest) = 254,312.6
However, when I run the following:
> amort.table(Loan = 200000, n = 12*25, i = 0.02, pf = 12) %>%
+ .[[2]]
Details
Loan 200000.00000000
Total Paid 253784.16000000
Total Interest 53784.16000000
Eff Rate 0.02000000
i^(12) 0.01981898
The total interest paid is out by a $1,000 ( 54,312.6 vs 53,784.16)
How can I make the amort.table() function produce the same results as the mortgage function?
We notice i^(12) is 0.01981898 in amort.table and this should be 2. This is because the relationship between the effective annual interest rate and monthly interest rate is not simply effective annual interest rate/12 = monthly interest rate.
Replace i with the actual interest rate would yield desired results:
amort.table(Loan = 200000, n = 12*25, i = (1+0.02/12)^12-1, pf = 12) %>%
.[[1]] %>%
head()
Year Payment Interest Paid Principal Paid Balance
1 0.08 847.71 333.33 514.38 199485.6
2 0.17 847.71 332.48 515.23 198970.4
3 0.25 847.71 331.62 516.09 198454.3
4 0.33 847.71 330.76 516.95 197937.4
5 0.42 847.71 329.90 517.81 197419.5
6 0.50 847.71 329.03 518.68 196900.9
amort.table(Loan = 200000, n = 12*25, i = (1+0.02/12)^12-1, pf = 12) %>%
.[[2]]
Details
Loan 2.000000e+05
Total Paid 2.543126e+05
Total Interest 5.431260e+04
Eff Rate 2.018436e-02
i^(12) 2.000000e-02