0

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
dukla
  • 3
  • 2
  • "I'm trying the below code to make it work. Instead of it working as I expect it to..." -- and what are you expecting the result to be? – Arun Nov 18 '15 at 20:36
  • Arun, Fair question. I've added a table there that may be more helpful for what I'm trying to get out of it. – dukla Nov 18 '15 at 20:48
  • Hm.. strange, but I find this Q quite similar to http://stackoverflow.com/q/33760314/559784 – Arun Nov 18 '15 at 20:54
  • 2
    The operations that you have are not done the way you're imagining - I think you're imagining it doing the first row, then using that for the second row, then using that for the third, etc. But what instead is happening (and this is normal, due to R being a vectorized language) is the operations are done for all rows the way they are *before* you start your operations, or in other words "simultaneously". For something as simple as what you have, use an explicit formula (e.g. `BegFund = fund.start*(1-payment.percent)^(Payment-1)`) – eddi Nov 18 '15 at 21:57

2 Answers2

1

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.

Arun
  • 116,683
  • 26
  • 284
  • 387
  • Works for the simplified example I gave, but it's impractical for the actual application (because rates aren't actually constant and there are 20+ types of payments). – dukla Nov 19 '15 at 19:16
0

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
Naus
  • 99
  • 10