How to do calculation referencing previous row value in the same vector?

[Abdel]

Hey Everyone,
Note: I'm a beginner but I'm trying to switch from excel to R . I tried to do some economic calculation with R but i have somme coding problems :
I want to code my equation of Net Present Value (NPV) for each year (time):

NPV (time0) = (growth*price-costs)i_coeff
NPV (time1) = (NPV(time0) + growthprice-costs)i_coeff
NPV (time2) = (NPV(time1) + growthprice-costs)*i_coeff
.
.
.
NPV(time15)

Could you please help me to do this automatiqually ?

My data
time<-c(1:15)

growth<-c(0,0,0,20,37,58,83,116,151,182,203,215,224,231,236)
price<-c(66)
i<-c(0.02)
costs<-c(1800,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
i_rate<-1/(1+i)^time
i_coeff<-1/(1+i)^time

Thank you for help

[jrkrideau]

Do you want to learn how to program the function or just calculate an npv. If the latter just install the FinancialMath package and load it.

install.packages("FinancialMath")
library(FinancialMath)

and have a look at the NPV function

?NPV

or see if FinancialMath has an online vignette for NPV().

[technocrat]

suppressPackageStartupMessages({
  library(dplyr)
})

DF <- data.frame(growth = c(0,0,0,20,37,58,83,116,151,182,203,215,224,231,236))

DF %>% mutate(last_value = lag(growth))
#>    growth last_value
#> 1       0         NA
#> 2       0          0
#> 3       0          0
#> 4      20          0
#> 5      37         20
#> 6      58         37
#> 7      83         58
#> 8     116         83
#> 9     151        116
#> 10    182        151
#> 11    203        182
#> 12    215        203
#> 13    224        215
#> 14    231        224
#> 15    236        231

[Abdel]

@jrkrideau

FinancialMath

Thanks for your answer. FinancialMath but it gives only the laste value at 15 years : I have growth data for eucalyptus (15 years) what I'm trying to calculate is NPV for each year :
Ex : If we cut trees at year 5 I need to sum previous cashflows X discounte coefficient (year 14).
So for each row : i need to add the previous result from the same colomn.
Thank you again

[Abdel]

Thank you for response. I don't need the data of growth vector, i need to add the previous NPV (n-1) to NPV at (n)

Please see in Excel file.
Thanks

[Abdel]

@jrkrideau

how to program the function

Could you please help me to programm this function (see screenshot from my Excel file)?
I appreciate your help.
Kindly

[Jimohare]

time<-c(0:15)
growth<-c(0,0,0,0,20,37,58,83,116,151,182,203,215,224,231,236)
price<-c(66)
i<-c(0.02)
costs<-c(1800,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
i_rate<-1/(1+i)^time
i_coeff<-1/(1+i)^time

npv <- vector("double", length(time))

for(i in time){
  index = i + 1
  if(i == 0){
    npv[index] <- -costs[index]
  }else{
    npv[index] <- i_rate[index] *
                  (npv[index-1] + price*(growth[index] -growth[index-1]))
  }
}

View(npv)

[technocrat]

Same principle. Also, screenshots are unhelpful. See the FAQ: How to do a minimal reproducible example (reprex for beginners

[woodward]

You should use dplyr::lag like @technocrat said.

Coding a low level loop in R is considered poor practice and very slow. Get into the habit of vectorising everything if possible.

If you really need to code a low level loop, I recommend using Rcpp::cppFunction; you can write a C++ loop that will be blisteringly fast but can be accessed easily from R.

[technocrat]

@woodward

you can write a C++ loop that will be

also what a user may be better used to.

[Jimohare]

Can this type of iterative calculation be done using dplyr::lag?
On the loop, an Npv calc is only ever used with a small number of times steps and therefore the loop won't have lots of iterations. So I think coding in c++ would be overkill for this type of calculation, and unlikely to give a significant speedup.

The answer below works and hopefully is useful to the user. I'd love to see a more elegant solution using dplyr if it can be done.

time <- c(0:15)
growth <- c(0,0,0,0,20,37,58,83,116,151,182,203,215,224,231,236)
price <- c(66)
i <- c(0.02)
costs <- c(1800,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
i_rate <- 1/(1+i)^time
i_coeff <- 1/(1+i)^time

DF <- data.frame(time, growth, price, costs, i_rate, i_coeff)
DF <- DF %>% 
  mutate(annual_growth = lag(growth, default = 0)) %>% 
  mutate(val = -costs + price * annual_growth) %>%
  mutate(npv = cumsum(val * i_rate))

View(npv)

[Abdel]

Thank you for response, the code not working for me (sorry i'm beginner on R)

[Jimohare]

apologies, pasted wrong code, try this one

time<-c(0:15)
growth<-c(0,0,0,0,20,37,58,83,116,151,182,203,215,224,231,236)
price<-c(66)
i<-c(0.02)
costs<-c(1800,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
i_rate<-1/(1+i)^time
i_coeff<-1/(1+i)^time

npv <- vector("double", length(time))

for(i in time){
  index = i + 1
  if(i == 0){
    npv[index] <- -costs[index]
  }else{
    npv[index] <- i_rate[index] *
                  (npv[index-1] + price*(growth[index] -growth[index-1]))
  }
}

View(npv)

[Abdel]

Thank you @Jimohare, this method works ,. I have an other question please :
I would like to simulate the same loop for 300 years (X20), using costs (1800$) every 45 years (3 rotations).
how can i set this up?
I appreciate your help
Kindly

[Jimohare]

You can extend your time and costs variables like so...

time <- c(0:300)

costs <- vector("integer", 300)
costs[time %% 45 ==0] <- 1800

The i_rate and i_coeff variables can be recalculated using the new time variable. You will need to extend your growth variable to the extended timeline

Jim

[Abdel]

@Jimohare it works for costs, but i still have a major problem :
time for 1 rotation or loop is 15 year, i'd like to programm automatically a function that uses the new npv0 at every new rotation :
Ex : at time 16, 31, 46, 61.... I don't need to use the previous value (n-1) because it is new rotation, i'd like to start the same calculations with new npv0 calculated at times (16, 31, 46...286) but with différents i_rate because time is changing (0-300)
Could you help me to solve this please?
Thanks
Best regards

[technocrat]

I misunderstood the question.

suppressPackageStartupMessages({
  library(dplyr)
})

DF <- data.frame(
  N = c(1:16),
  PMT = c(-1866,0,0,0,20,37,58,83,116,151,182,203,215,224,231,236)
)

i = 0.02

DF %>% mutate(FV = PMT / (1 + i)^N,
              NPV = cumsum(FV))
#>     N   PMT          FV        NPV
#> 1   1 -1866 -1829.41176 -1829.4118
#> 2   2     0     0.00000 -1829.4118
#> 3   3     0     0.00000 -1829.4118
#> 4   4     0     0.00000 -1829.4118
#> 5   5    20    18.11462 -1811.2971
#> 6   6    37    32.85494 -1778.4422
#> 7   7    58    50.49249 -1727.9497
#> 8   8    83    70.83970 -1657.1100
#> 9   9   116    97.06361 -1560.0464
#> 10 10   151   123.87259 -1436.1738
#> 11 11   182   146.37587 -1289.7979
#> 12 12   203   160.06411 -1129.7338
#> 13 13   215   166.20199  -963.5318
#> 14 14   224   169.76401  -793.7678
#> 15 15   231   171.63640  -622.1314
#> 16 16   236   171.91321  -450.2182

[woodward]

@Jimohare you are right, my mistake, lag doesn't work for iterative calculation. And I agree C++ would be overkill for this example.

@technocrat I agree that NPV calculation does not actually require iterative calculation anyway.

[Abdel]

@woodward woodward
Hey Everyone,
I need to add another condition with same logic as for if i == 0 :
if "i" is multiple of 15+1 "16, 31, 46, 61.....286(max))
npv[index] <- -costs[index]i_rate[index]
}else{
npv[index] <- i_rate[index] * (npv[index-1] + price(growth[index] -growth[index-1]))

Do you have some brilliant idea to integrate this to my calculations?
thank you

More explanations____________
Time from 0 to 300 : for each start of rotations or loops (15 years) i need to start with npv[index],
then for next 14 years npv[index] <- i_rate[index] * (npv[index-1] + price*(growth[index] -growth[index-1]))

time<-c(0:300)
growth<-c(0,0,0,0,20,37,58,83,116,151,182,203,215,224,231,236,0,0,0,20,37,58,83,116,151,182,203,215,224,231,236,0,0,0,20,37,58,83,116,151,182,203,215,224,231,236,0,0,0,20,37,58,83,116,151,182,203,215,224,231,236,0,0,0,20,37,58,83,116,151,182,203,215,224,231,236,0,0,0,20,37,58,83,116,151,182,203,215,224,231,236,0,0,0,20,37,58,83,116,151,182,203,215,224,231,236,0,0,0,20,37,58,83,116,151,182,203,215,224,231,236,0,0,0,20,37,58,83,116,151,182,203,215,224,231,236,0,0,0,20,37,58,83,116,151,182,203,215,224,231,236,0,0,0,20,37,58,83,116,151,182,203,215,224,231,236,0,0,0,20,37,58,83,116,151,182,203,215,224,231,236,0,0,0,20,37,58,83,116,151,182,203,215,224,231,236,0,0,0,20,37,58,83,116,151,182,203,215,224,231,236,0,0,0,20,37,58,83,116,151,182,203,215,224,231,236,0,0,0,20,37,58,83,116,151,182,203,215,224,231,236,0,0,0,20,37,58,83,116,151,182,203,215,224,231,236,0,0,0,20,37,58,83,116,151,182,203,215,224,231,236,0,0,0,20,37,58,83,116,151,182,203,215,224,231,236,0,0,0,20,37,58,83,116,151,182,203,215,224,231,236)
price<-c(66)
i<-c(0.02)
costs <- vector("integer", 300)
costs[time %% 45 ==0] <- 1800
i_rate<-1/(1+i)^time
i_coeff<-1/(1+i)^time

npv <- vector("double", length(time))

for(i in time){
index = i + 1
if(i == 0){
npv[index] <- -costs[index]i_rate[index]
}else{
npv[index] <- i_rate[index] * (npv[index-1] + price(growth[index] -growth[index-1]))
}
}

View(npv)