Type Curve Economics
In Part 1 we downloaded production data and manipulated it. In Part 2, we learned how to create type-curves. Now, let’s create a quick single-well economic model, and I suppose play around with creating functions for the first time.
Follow My Blog
Functions
Not as bad as you think. Generally the rule of thumb is that if you write the same bit of code more than 3 times, then you should be using a function. Of course, if you’ve been following along with the tutorials so far then you’ve already been using functions. The pre-built R packages are really just a lot of bundled functions.
But now, we’ll just build one ourself.
Revisiting aRpsDCA
You will recall our single-segment Arps Modified Hyperbolic Decline curve from Part 2.
library(aRpsDCA) qiGas <- 20000 #mcfd bGas <- 1 #B-factor DiGas <- 0.9 #Effective Annual Decline (Initial) DfGas <- 0.08 #Effective Annual Decline (Terminal) curtail <- 5 #Months of curtailment wellLife <- 30 #Years gasFcst = curtailed.q(arps.decline( qiGas*365.25, as.nominal(DiGas), bGas, as.nominal(DfGas)), curtail/12.0,seq(1/12, wellLife, by= (1/12)))/12
There are actually three functions here, curtailed.q, arps.decline, and as.nominal. All are defined within the package documentation for aRpsDCA. These allow me to forecast well production. What I want to do is create a single function that will spit out a dataframe of oil, gas, and water production in monthly increments.
Our Function
There are two things I need to do:
- Determine the variables within function()
- Create the calculation after function()
So I will just use the above code as my guide.
prodFunc <- function(wellLife, qiGas, DiGas, bGas, DfGas, curtailGas, qiOil, DiOil, bOil, DfOil, curtailOil, qiWater, DiWater, bWater, DfWater, curtailWater) { df1 <- data.frame( #Months of Prouction monthsOn = seq(1, wellLife*12, 1), #Define Gas gas = curtailed.q(arps.decline( qiGas*365.25, as.nominal(DiGas), bGas, as.nominal(DfGas)), curtailGas/12.0,seq(1/12, wellLife, by= (1/12)))/12, #Define Oil oil = curtailed.q(arps.decline( qiOil*365.25, as.nominal(DiOil), bOil, as.nominal(DfOil)), curtailOil/12.0,seq(1/12, wellLife, by= (1/12)))/12, #Define Water water = curtailed.q(arps.decline( qiWater*365.25, as.nominal(DiWater), bWater, as.nominal(DfWater)), curtailWater/12.0,seq(1/12, wellLife, by= (1/12)))/12 ) return(df1) }
Calculation
Now that our function is build, let’s test it out. To avoid confusion, I’m going to throw a 1 behind each variable I define.
qiGas1 <- 2000 #mcfd bGas1 <- 1.2 #B-factor DiGas1 <- 0.8 #Effective Annual Decline (Initial) DfGas1 <- 0.08 #Effective Annual Decline (Terminal) curtailGas1 <- 3 #Months of curtailment qiOil1 <- 1000 #bopd bOil1 <- 1 #B-factor DiOil1 <- 0.9 #Effective Annual Decline (Initial) DfOil1 <- 0.08 #Effective Annual Decline (Terminal) curtailOil1 <- 1 #Months of curtailment qiWater1 <- 1000 #bwpd bWater1 <- 1 #B-factor DiWater1 <- 0.9 #Effective Annual Decline (Initial) DfWater1 <- 0.08 #Effective Annual Decline (Terminal) curtailWater1 <- 0 #Months of curtailment wellLife1 <- 30 #Years #Create a dataframe with our function prod <- prodFunc(wellLife = wellLife1, qiGas = qiGas1, DiGas = DiGas1, bGas = bGas1, DfGas = DfGas1, curtailGas = curtailGas1, qiOil = qiOil1, DiOil = DiOil1, bOil = bOil1, DfOil = DfOil1, curtailOil = curtailOil1, qiWater = qiWater1, DiWater = DiWater1, bWater = bWater1, DfWater = DfWater1, curtailWater = curtailWater1) head(prod)
I get this.
monthsOn | gas | oil | water |
---|---|---|---|
1 | 60875 | 30438 | 25537 |
2 | 60875 | 25537 | 21996 |
3 | 60875 | 21996 | 19317 |
4 | 53756 | 19317 | 17220 |
5 | 48244 | 17220 | 15534 |
6 | 43839 | 15534 | 14148 |
If we sum each column, that should give us our EUR (Estimated Ultimate Recovery).
sum(prod$oil)/1000 #Convert Oil EUR from BBL to MBO
666.5 MBO for this example.
Putting it All Together
Prices
Now that I can create a production function, I can start tacking on additional functionality. For this example, I am going to use a forward strip price from the Wall Street Journal for my forecast, as I covered in Scraping Strip with R (Oil & Gas Coding Series). But this time I’ll make it a function!
library(rvest) library(httr) library(dplyr) library(zoo) priceFunc <- function() { crude = 'https://quotes.wsj.com/futures/CRUDE%20OIL%20-%20ELECTRONIC/contracts' webpage <- read_html(crude) tbls <- rvest::html_nodes(webpage, 'table') tbls_ls <- webpage %>% rvest::html_nodes('table') %>% .[1] %>% rvest::html_table(fill = TRUE) wti <- tbls_ls[[1]] %>% select(MONTH, SETTLEMENT) wti <- wti %>% filter(MONTH != 'Front Month') wti$MONTH <- gsub('Crude Oil', '', wti$MONTH) wti$MONTH <- trimws(wti$MONTH, which = 'both') wti$Year <- substr(wti$MONTH, 5, 8) wti$Month <- substr(wti$MONTH, 1, 3) wti$Date <- as.Date(paste0(wti$Month, '/01/', wti$Year), format = '%b/%d/%Y') wti <- wti %>% select(DATE = Date, WTI = SETTLEMENT) gas = 'https://quotes.wsj.com/futures/NATURAL%20GAS/contracts' webpage <- read_html(gas) tbls <- rvest::html_nodes(webpage, 'table') tbls_ls <- webpage %>% rvest::html_nodes('table') %>% .[1] %>% rvest::html_table(fill = TRUE) hh <- tbls_ls[[1]]%>% select(MONTH, SETTLEMENT) %>% filter(MONTH != 'Front Month') hh$MONTH <- gsub('Natural Gas', '', hh$MONTH) hh$MONTH <- trimws(hh$MONTH, which = 'both') hh$Year <- substr(hh$MONTH, 5, 8) hh$Month <- substr(hh$MONTH, 1, 3) hh$Date <- as.Date(paste0(hh$Month, '/01/', hh$Year), format = '%b/%d/%Y') hh <- hh %>% select(DATE = Date, HH = SETTLEMENT) wti <- wti %>% left_join(hh) wti <- wti %>% filter(!is.na(WTI)) %>% filter(!is.na(HH)) return(wti) }
Then, to call my price file, I just run:
prices <- priceFunc()
So, now if I want to rerun my price file, I can just run priceFunc() and then join it to my production dataframe.
You could potentially throw this function into our original prodFunc(), but that is not preferable. You will eventually be running a lot of different variations of type-curves in the future, so it is preferable to only run the priceFunc() at the beginning of your session. Otherwise you will be trying to scrape prices each time you change a type-curve, and that is SLOW.
Below would be the preferred workflow.
prod <- prodFunc() prices <- priceFunc() #Add similar monthsOn column to prices prices <- prices %>% mutate(monthsOn = seq(1, n(), 1)) #Join prices to production file on monthsOn column prod <- prod %>% left_join(prices) #Drag missing values downwards prod$WTI <- zoo::na.locf(prod$WTI) prod$HH <- zoo::na.locf(prod$HH) #View first rows head(prod)
monthsOn | gas | oil | water | DATE | WTI | HH |
---|---|---|---|---|---|---|
1 | 60875 | 30438 | 25537 | 2020-10-01 | 38.05 | 2.406 |
2 | 60875 | 25537 | 21996 | 2020-11-01 | 38.41 | 2.873 |
3 | 60875 | 21996 | 19317 | 2020-12-01 | 38.87 | 3.241 |
4 | 53756 | 19317 | 17220 | 2021-01-01 | 39.34 | 3.361 |
5 | 48244 | 17220 | 15534 | 2021-02-01 | 39.78 | 3.323 |
6 | 43839 | 15534 | 14148 | 2021-03-01 | 40.18 | 3.201 |
Revenue
Now I will just add in a quick revenue model based on absolutely nothing but probably close enough. What will I need for this?
- Working Interest/Net Revenue Interest
- Shrink (or as I define it Gas Sold as % of Gas Produced)
- NGL Yield (BBL/MMCF of wellhead gas)
- Diff’s (I’ll just use simple percents here but can be per-unit)
wi <- 1 nri <- 0.75 #To the 100% of WI shrink <- 0.75 nglYield <- 100 oilDiff <- 0.95 gasDiff <- 0.8 nglDiff <- 0.3 #%WTI prod$ngl <- prod$gas*nglYield/1000 prod$oilRevenue <- prod$oil*wi*nri*prod$WTI*oilDiff prod$gasRevenue <- prod$gas*wi*nri*prod$HH*gasDiff*shrink prod$nglRevenue <- prod$ngl*wi*nri*prod$WTI*nglDiff prod$revenue <- prod$oilRevenue+prod$gasRevenue+prod$nglRevenue
Expenses
Expenses are usually based on the Working Interest ownership, so that’s what I will use here. There is a lot you can do, but for my analysis I am completely making it up (or maybe it’s influenced by actuals). For this model I will assume:
- Fixed Cost at $10,000 $/month until total fluid rate goes below 100 barrels per day, or 3,000 per month. $5,000 thereafter.
- Variable Expenses of $0.5/bbl oil, $0.2/mcf wellhead gas, $1/bbl water.
You could potentially run assumed Workover Expenses as well, but I usually would lump them into fixed cost assumptions for ease.
fixed1 <- 10000 switch <- 100*30.45 fixed2 <- 5000 varOil <- 0.5 varGas <- 0.2 varWtr <- 1 prod$expense <- fixed1 + prod$oil*wi*varOil+prod$gas*wi*varGas + prod$water*wi*varWtr prod$expense[prod$oil + prod$water < switch] <- prod$expense[prod$oil + prod$water < switch] - fixed2
Taxes
I am going to use Texas severance taxes, assuming an oil well, and then assume 2.5% of revenue for Ad Valorem.
stxOil <- 0.046 #Texas Oil Severance stxGas <- 0.075 #Texas Gas/NGL Severance atx <- 0.025 #Ad Valorem prod$tax <- prod$oilRevenue*stxOil + (prod$gasRevenue+prod$nglRevenue)*stxGas + prod$revenue*atx
Cash Flow
Simply enough, I will just calculate total cash flow. However, I am going to assume that the well is shut in on the last positive cash flow month.
prod$cf <- prod$revenue - prod$expense - prod$tax last1 <- which(prod$cf > 0) last1 <- last1[length(last1)] prod <- prod[1:last1,]
Capex/PNA
A somewhat lazy assumption I will make is that P&A costs are incurred at the last month of the data frame. It is not that big of an impact from a PV-perspective that far out into the future, though you COULD defer it if it so pleases you. I also need to define capex and then how long we go from SPUD to first production date.
drillCapex <- 4000000 #Drill Cost complCapex <- 6000000 #Capex + Facilities pna <- 50000 #P&A spudToProd <- 3 #Production occurs on month 4. prod <- prod %>% subset(select = -c(DATE)) #Remove useless Date column prod$capex <- 0 #Create capex column prod$capex[nrow(prod$capex)] <- pna #Add P&A to last month capex1 <- prod[1:spudToProd,] capex1[1:nrow(capex1),1:length(capex1)] <- 0 capex1$capex[1] <- drillCapex capex1$capex[spudToProd] <- complCapex prod <- rbind(capex1, prod) prod$monthsOn <- seq(0, nrow(prod)-1, 1) #New monthly column
Cash Flow/NPV-10
Now that we have the cash flows built out, we can have a quick peek at single-well economics. I am going to show discount rates assuming annual compounding (ie PV = CF / (1 + r/n) t*n). Obviously, if you change the discount rate, that changes the PV.
prod$fcf <- prod$cf - prod$capex r <- .1 n <- 1 prod$pv <- prod$fcf/((1+r/n)^(prod$monthsOn/12*n)) library(scales) scales::dollar(sum(prod$pv))
Based on my price assumption (strip as of 9/9/2020), then the NPV-10 is $3.7 Million US.
Figure 1: Cumulative Net Present Value (10%) – Example Well
IRR
Instead of an IRR package in R, I’m going to build out an IRR function that loops quickly until we find a value, or returns 0 if it’s negative.
IRRcalc <- function(cf, months){ #Function inputs are our Free Cash Flow profile and our months if(sum(cf) > 0){ IRR1 <- 3 #Start at 300%, because, come on, like it'll ever even be near this. Just starting high. loop <- 1 #Initiate loop at 1 while(sum(cf/((1+IRR1)^(months/12))) #While with our NPV Formula < 0){ IRR1 <- IRR1 - 0.01 #If not true, knock off 1% loop = loop + 1 #Next Loop } }else { IRR1 <- 0 #If total cf is less than 0, return 0 } return(IRR1) } print(scales::percent(IRRcalc(prod$fcf, prod$monthsOn)))
In this scenario, I get a 24% IRR.
Summary
That’s probably enough brain-hurt for this session. If you want to test yourself, try and combine everything into a single function and see how it goes. I will be doing a pseudo-version when I publish Part 2 of the Shiny Version later this week. Thanks for tuning in!
Pingback: Oil & Gas Coding with Python (part 3) - Shale Insights