CODEWITHSUNDEEP
rdata.tabledplyr
dukla
dukla

Reputation: 3

dplyr lag() inside mutate() for rolling values forward

I'm attempting to roll a value forward using dplyr's mutate() and lag(). I'm trying the below code to make it work. Instead of it working as I expect it to, I get ZEROs in the BegFund column after the first row. I've tried using data.table shift() with no luck, and stats::lag() with no luck as well. Anyone have any ideas?

Below is a simplified example of what I'm attempting to do. Reproduces when I test.

library(dplyr) #  0.4.3

payments <- 1:10
fund.start <- 1000
payment.percent <- .05

fund.value <- data.frame(payments)

fund.value <- fund.value %>%
  transmute(Payment = payments) %>%
  mutate(EndFund = 0) %>%
  mutate(BegFund = ifelse(Payment == 1, fund.start, lag(EndFund, 1)),
         PmtAmt = BegFund * payment.percent,
         EndFund = BegFund - PmtAmt) %>%
  select(Payment, BegFund, PmtAmt, EndFund)
head(fund.value)

EDIT: Below is the output I'd like to get out of R for this. Please excuse the awful formatting, I'm very new at this.

Payment  BegFund          PmtAmt        EndFund
1        1000             50            950
2        950              47.5          902.5
3        902.5            45.125        857.375
4        857.375          42.86875      814.50625
5        814.50625        40.7253125    773.7809375
6        773.7809375      38.68904688   735.0918906

Upvotes: 0

Views: 1016

Answers (2)

Arun
Arun

Reputation: 118799

Here's one way:

EndFund = fund.start * (1 - payment.percent) * (1-payment.percent)^(payments-1L)
BegFund = c(fund.start, head(EndFund, -1L))
PymtAmt = BegFund - EndFund

Just noticed that @Eddi also has covered this under comment.

Upvotes: 1

Naus
Naus

Reputation: 99

I know this is not the way the OP wanted to do it, but it may help

fund.value <- data.frame(payments, BegFund=0, PmtAmt=0,EndFund=0)

fund.value$BegFund[1]<-fund.start
fund.value$PmtAmt[1] = fund.value$BegFund[1] * payment.percent
fund.value$EndFund[1] = fund.value$BegFund[1] - fund.value$PmtAmt[1]

for(i in 2:dim(fund.value)[1]){
  fund.value$BegFund[i]<-fund.value$EndFund[i-1]
  fund.value$PmtAmt[i] = fund.value$BegFund[i] * payment.percent
  fund.value$EndFund[i] = fund.value$BegFund[i] - fund.value$PmtAmt[i]
}

Out is 

  payments   BegFund   PmtAmt  EndFund
1        1 1000.0000 50.00000 950.0000
2        2  950.0000 47.50000 902.5000
3        3  902.5000 45.12500 857.3750
4        4  857.3750 42.86875 814.5063
5        5  814.5063 40.72531 773.7809
6        6  773.7809 38.68905 735.0919

Upvotes: 0

Related Questions