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

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) + growth
price-costs)i_coeff
NPV (time2) = (NPV(time1) + growth
price-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

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().

1 Like
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
1 Like

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

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)

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

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.

1 Like

also what a user may be better used to.

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)
1 Like

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

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)

Thank you @Jimohare, this method works :grinning: ,. 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

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

@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

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
1 Like

@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.

1 Like

@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 :slight_smile: :slight_smile:

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)

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.

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

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