CODEWITHSUNDEEP
r
a_js12
a_js12

Reputation: 329

Applying a Function to Expand a Single Row to Many Rows by Group in R

I'm trying to create an amortization schedule for a data frame with 3 seperate accounts. Each account has a single row which details the account's loan information ie starting balance, annual interest rate, term of the loan and payments per year.

What I'd like to do, using the loan information in my data frame, is create a loan amortization schedule to detail the monthly payments required over the time of the loan. What I have below is a function to calculate the total payment, principal payment and interest payment. What I'd like is to apply the function to the accounts in my data frame. My latest attempt was to use dplyr::group_by function but my code doesn't seem to work with a data frame that has multiple accounts whereas it will work with a single account.

I'm hoping to get some help, I'm pretty sure the issue is how I call the function and grouping but I'm not really sure. Any help is much appreciated! Thanks!

q<-data.frame(PersonID=c("A","B","C"),
              StartingBalance=c(100,900,500),
              AnnualRate=c(.02,.05,.04),
              NumberOfPayments=c(60,60,60),
              PaymentFrequency=c(12,12,12))

amortisationschedule <- function(amount, annualinterestrate, paymentsperyear, years) {
  
  nper = paymentsperyear * years
  rate = annualinterestrate / paymentsperyear
  
  AmortisationSchedule <- tibble(
    Principal = map_dbl(1:nper, function(x)
      PPMT(rate,
           x,
           nper,
           amount)),
    
    Interest = map_dbl(1:nper, function(x)
      IPMT(rate,
           x,
           nper,
           amount))
  ) %>% mutate(Instalment = Principal + Interest,
               Balance = round(amount + cumsum(Principal),2))
  
  return(AmortisationSchedule)
}

q2<-q %>%
  group_by(PersonID) %>%
  nest(test = c(-PersonID)) %>%
  mutate(test = map(quux, amortisationschedule(StartingBalance,AnnualRate,PaymentFrequency,NumberOfPayments))) %>%
  unnest(test) %>%
  ungroup()

Upvotes: 3

Views: 148

Answers (1)

Vons
Vons

Reputation: 3325

You can use apply to apply a function to each row of a data frame by setting MARGIN=1. The following is an example function that takes a data frame and returns a list of amortisation schedules for each person in the data frame. It works by arranging the columns in the order that the amortisationschedule takes arguments and then calls the function on each row with apply.

amortisation_list=function(dt) {
  dt1=relocate(dt, PaymentFrequency, .before=NumberOfPayments) %>%
  select(-PersonID)

  x=apply(dt1, 1, function(x) {
    do.call(amortisationschedule, unname(as.list(unlist(x))))
  })
  return(x)
}
amortisation_list(q)

Upvotes: 2

Related Questions