-2

I am using a formula in Excel which gives a correct result only after enabling Iterative calculation.

Excel table calculation

Excel table formula

How to solve this iterative problem in R?

Faysal Ahmed
  • 7,501
  • 5
  • 28
  • 50
Narendra Sahu
  • 136
  • 2
  • 14
  • Please post what you've tried so far, and why it's not working. – Smartcat Mar 06 '18 at 06:31
  • ` for(i in 2: nrow(ecltable)){ # ecltable$intpayment[i-1]<-(do.call(sum,as.list(ecltable$accrint[i:nrow(ecltable)]))-do.call(sum,as.list(ecltable$intpayment[i:nrow(ecltable)])))*ecltable$intIndicator[i-1] # }` – Narendra Sahu Mar 06 '18 at 06:36
  • Its a simple formula just replicating Excel formula. Its giving some weird results. Can I use _itersolve_ function to solve this. – Narendra Sahu Mar 06 '18 at 06:39
  • 1
    It would be easier to help you if you provided a reproducible example, see e.g. [here](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) – Mikko Marttila Mar 06 '18 at 06:47

1 Answers1

0

Perhaps something like this?

dat <- data.frame(
  indicator = rep(c(1,0,0), times = 4),
  interest = c(0,54,33,678,987,544,321,345,678,907,567,0)
)

intersolve <- function(indicator, interest){
  indicators = rev(cumsum(rev(indicator) == 1))
  sums = rev(tapply(interest, indicators, sum))
  interest_payment = rep(sums[-1], each = 3) * indicator
  return(interest_payment)
}

dat$interest_payment = intersolve(indicator = dat$indicator, interest = dat$interest)
dat

#    indicator interest interest_payment
# 1          1        0              765
# 2          0       54                0
# 3          0       33                0
# 4          1      678             1852
# 5          0      987                0
# 6          0      544                0
# 7          1      321             1930
# 8          0      345                0
# 9          0      678                0
# 10         1      907              567
# 11         0      567                0
# 12         0        0                0

Edit

The following function automatically detects the pattern of 1's.

intersolve2 <- function(indicator, interest){
  n = length(interest)
  indicators = rev(cumsum(rev(indicator) == 1))
  sums = rev(tapply(interest, indicators, sum))
  lens = rev(table(indicators))[-1]
  lens[length(lens)] = lens[length(lens)] + 1
  interest_payment = rep(sums[-1], times = lens) * indicator
  if (indicator[n] == 1){interest_payment[n]=interest[n]}
  return(interest_payment)
}
hpesoj626
  • 3,529
  • 1
  • 17
  • 25
  • Thanks, but it seems to solve only the given type of problem. lets say indicator is not in the given fashion (for e.g. all indicators are 1 or say it has 20 rows 1,0,0,0,0,0,1,0,0,0,0,0,1,0,0,0,0,0,1,0 or some other fashions. it will not work – Narendra Sahu Mar 06 '18 at 08:04
  • 1
    My first solution only solves the given type of problem because what you wanted to do was not clear and you did not give additional context. Perhaps `intersolve2()` will solve your problem. :) – hpesoj626 Mar 06 '18 at 08:26