Excel Spreadsheet to Calculate Well Payouts
After some initial assistance from Skip Peel in learning about how royalty calculations work, I whipped up this spreadsheet to estimate the royalty payments from a well.
Start by filling in some values on the first sheet:
The spreadsheet has flaws, certainly - it only calculates monthly totals based on an average price of gas in a particular month, which you must figure out yourself (this seems to be a good place for that). Also, I have a value on the first sheet (fill in your values) that allows me to split the royalties evenly between several people. This might not fit your situation. If you are the only owner of land in a drilling unit, just set that number to 1.
Otherwise, it does a good estimation of royalties for the Gas produced I think. I have included enough columns for 6 separate wells. If you have more, you can either copy the "estimations" sheet's cells and copy in a new sheet or whatever other method makes you happy.
The last columns add up all the values in a row (a row corresponds to a month). One column is the gross amount, the other is the net after the deductions percentage estimation is applied (for instance, 10% for transportation costs).
All along the top is a running total of how much individuals, the group, and the individual after 30% income tax is taken out.
All these numbers are ESTIMATES - don't use them to plan your retirement on, but they are a good way to see how a wellhead that made $1.5 million in a month gets you $2500 (or whatever based on your numbers).
I would love if a real Excel freak made something like this that sucked less. But I think that novices (like me) could find this useful in getting some estimates out of SONRIS' monthly well production reports.
Anyone who wants to post a better, or modified version of this, please feel free.
Also, you can see it in Google Docs here:
http://spreadsheets.google.com/ccc?key=0Ag4sFAekraKEdFhtS2FYbVEtaEx...
Start by filling in some values on the first sheet:
| Acreage owned in unit- very few people are lucky enough to own all the acres in a drilling unit. You'll have some subset of the full acreage. For instance, we own 36 acres in a 640 acre unit. |
| Total Acreage in Unit - this is how many acres make up your drilling unit. 640 is a common number. |
| Royalty Fraction - expressed as a decimal. If you have a 3/16th agreement, then the number here (.1875) is correct |
| Deductions - expressed as a decimal. Here, 10% (.10) is deducted for transportation costs. |
| Your share - perhaps you own the acreage with several people. Put the number of people who get a percentage here. The shares are considered equal; if they are not then enter '1' in cell E2 and perform a percentage calculation on your own. |
The spreadsheet has flaws, certainly - it only calculates monthly totals based on an average price of gas in a particular month, which you must figure out yourself (this seems to be a good place for that). Also, I have a value on the first sheet (fill in your values) that allows me to split the royalties evenly between several people. This might not fit your situation. If you are the only owner of land in a drilling unit, just set that number to 1.
Otherwise, it does a good estimation of royalties for the Gas produced I think. I have included enough columns for 6 separate wells. If you have more, you can either copy the "estimations" sheet's cells and copy in a new sheet or whatever other method makes you happy.
The last columns add up all the values in a row (a row corresponds to a month). One column is the gross amount, the other is the net after the deductions percentage estimation is applied (for instance, 10% for transportation costs).
All along the top is a running total of how much individuals, the group, and the individual after 30% income tax is taken out.
All these numbers are ESTIMATES - don't use them to plan your retirement on, but they are a good way to see how a wellhead that made $1.5 million in a month gets you $2500 (or whatever based on your numbers).
I would love if a real Excel freak made something like this that sucked less. But I think that novices (like me) could find this useful in getting some estimates out of SONRIS' monthly well production reports.
Anyone who wants to post a better, or modified version of this, please feel free.
Also, you can see it in Google Docs here:
http://spreadsheets.google.com/ccc?key=0Ag4sFAekraKEdFhtS2FYbVEtaEx...
Views: 6895
- Attachments:
-
-
royalty calculation.xls, 56 KB
-
Replies to This Discussion
-
Permalink Reply by Skip Peel - Mineral Consultant on -
Aegis. There is a wealth of information and opinion concerning decline curve in the archives of GHS. On the Main Page under the current Discussion Topics, click on "View All". Then enter "decline curve" in the search box. It was one of my favorite topics and the discussions are far too wide ranging to try and cover here. Take Petrohawk's decline figure and Chesapeake's, add them together and divide by two. Yes, I think they are correct as long as you understand that there is no one number. Decline for HS horizontal wells will be defined by a range. The steep decline curve presently predicted flattens out in year five and remains basically flat through out the commercial life of the well. And no, I can't answer how many years that represents. In large part it will be a function of the price of natural gas in the future.
-
Permalink Reply by MB on
-
Skip,
Do you think that choking the wells will provide a flatter decline curve perhaps over a several year period or are there some nuances to this conjecture I am not aware of?
TIA,
Brent
-
Permalink Reply by Skip Peel - Mineral Consultant on
-
MB. I expect so but IANARE (I am not a reservoir engineer). LOL! You should ask Mmmarkkk or Shalegeo. There are a ton of nuances which are the province of geologists and reservoir engineers. They are above the pay grade of this landman. And all those that I know personally with the exception of Les B. He would also have more to add to a discussion of your question.
-
Permalink Reply by Parkdota on
-
MB, on some wells that I've followed I've notice a flatter decline curve on tighter chokes. Companies are starting to employ tighter chokes. I think it's due in part to the large natgas in storage but I also think some are doing it bc it flattens the decline curve as well.
-
Permalink Reply by Les Bamburg on
-
MB, all HS wells are "choked" initially so it is more a question of choke size. Petrohawk is experimenting with producing some wells on a smaller choke and lower rate initially to determine if the ultimate recovery may be higher.
-
Permalink Reply by ledlights on
-
FWIW - I created a crude HS decline spreadsheet (uploaded below) based on a Chesapeake HS decline curve graph from one of their presentations - hoping to get a ball park idea of ongoing production amounts and rough payback - although this sheet only allows the use of an average $/mcf guess. Of course you can play with that guess to get an idea of the potential range of payback - if the decline curve proves to be reasonably accurate.
-
Permalink Reply by Skip Peel - Mineral Consultant on
-
ledlights. Your spreadsheet looks good to me. Thanks for taking the time to provide us with a calculator formula that includes a reasonable HS decline.
-
Permalink Reply by ledlights on
-
You're welcome, Skip. I ran it out for 20 years. I have heard estimates of 20 - 40 years of production for HS wells. Is that all just speculation or do they have some method or idea for how long these wells may really produce?
-
Permalink Reply by Skip Peel - Mineral Consultant on
-
I suspect that the commercial life of each individual well is closer to 20 years than to 40. And that it will take 10 to 20 years for the majority of HA drilling units to get their last well (8th.?). There will be production and royalty cranked out by the HS Play for the next 50 years at least. The one variable that no one can know is future national energy policy. If that policy recognizes the potential inherent in shale gas and provides the incentives to grow the uses for nat gas, it might be a little less. But then that would be a good development on several levels.
-
Permalink Reply by Les Bamburg on
-
Skip, many shale gas wells in the older plays have produced for 60+ years and HS wells should produce economically for more than 30 years minimum.
The majority of the HS/BS development will be completed in 40 years but drilling will continue at a slower pace well beyond that time. Keep in mind the development will require over 40,000 wells in Louisiana alone.
-
Permalink Reply by Les Bamburg on
-
Ledlights, HS wells could produce gas economically for 60+ years. Most operators are using +/- 30 years in assigning reserves but this may be conservative.
-
Permalink Reply by ledlights on
-
Here's the file
- Attachments:
-
-
Pub_WellDeclineRates.xls, 38 KB
-
Support GoHaynesvilleShale.com
Top Content
Not a member? Get our email.
Groups
-
San Augustine, TX
386 members
-
Natchitoches Parish
399 members
-
Cass County, TX
119 members
-
Franklin County, Texas
8 members
-
Rusk County
97 members
-
Vernon Parish
59 members
-
Bienville Parish, LA
242 members
-
Sabine County, Texas
172 members
-
Sabine Parish, LA
455 members
-
Angelina County, TX
87 members
© 2024 Created by Keith Mauck (Site Publisher).
Powered by
