I need to create an amortization calculation in R. The calculation for the first row works fine, but all subsequent calculations need to use values that are either calculated or in the first row and I can not get the loop to work.
df <- data.frame(
LoanID = c("LN-000249", "LN-000249", "LN-000249", "LN-000249"),
apply_date = c("2015-07-30 UTC", "2015-09-03 UTC", "2015-11-09 UTC", "2015-11-18 UTC"),
pmt_amt = c(17292.45, 17153.90, 18100.00, 107.23),
rows = c(1, 2, 3, 4),
calc_days = c(41, 35, 67, 9)
)
Say the loan is valued at 50000 and the interest rate was .08 percent
ln_amt = 50000
ln_interest_rate = .08
calculations for first row
df$interestAmt <- ln_amt * ln_interest_rate / 360 * df$calc_days
df$principalAmt <- df$pmt_amt - df$interestAmt
df$balance <- ln_amt - df$principalAmt
calculations for all other rows
df$interestAmt <- (new balance found in previous row or current balance calculation value) * ln_interest_rate / 360 * df$calc_days
df$principalAmt <- df$pmt_amt - df$interestAmt
df$balance <- (new balance found in previous row ) - df$principalAmt
# or it is equal to the (ln_amt - cumsum(df$principalAmt) )
I need to figure out how to do the calculations for all rows and not just the first row and create a dataframe. I need to do the amortization calculations based on the current balance and calculation days and the actual payment made and not a calculated payment value. Thus I can't use a formula to get these values. I can't seem to get the values correct for interestAmt or balance on rows 2-4.
df2<-df %>% mutate(interestAmt <- ln_amt * ln_interest_rate/360*calc_days,
principalAmt = pmt_amt - interestAmt,
balance = ln_amt - principalAmt) %>% mutate(interestAmt =(ifelse(is.na(lag(balance,1)),balance,lag(balance,1))) * ln_interest_rate/360*calc_days,
principalAmt = pmt_amt - interestAmt,
balance = lag(balance,1) - principalAmt)
Isn't the best approach as it does compute the initial balance and interestamount for all the rows and then overwrites, the overhead should not be much if your dataset is small