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: 7032
- Attachments:
-
-
royalty calculation.xls, 56 KB
-
Replies to This Discussion
-
Permalink Reply by Parkdota on
-
Les, on one excell spreadsheet that was on here some time back it was taken off bc it had flaws on many levels but it had one thing that I've never seen or heard of before. At some point in each well's life they had a time table formula for refracing. On their spreadsheet it appeared that this refrac increased the well's production moving forward. If memory serves they had the refrac occuring after 7 years I believe. Do companies do this? If so what is the standard time table in which this would be done? If so what might the increase be?
-
Permalink Reply by Shaleeee on
-
OK - I am willing to sound like an dummy - On the royalty estimater would the # of family interest owners be the number of people who own the land? But what does the "Family % interest in family acres mean? What number should be in the mmcf/day box? Sorry - but I really would like to understand.
Thanks
Shaleeee
-
Permalink Reply by ledlights on
-
Shalee, suppose one of your grandparents bought land in partnership with someone else. If the partnership was equal then your family would have a 50% interest in however many acres were purchased (or adjust according to whatever the % was) If your land was 100% owned by your family then it's easy - just enter 100%. The mmcf box is supposed to be the initial daily production for the well - that's real production not the IP number that is quoted from completion tests - the real initial production will be less than the well potential "IP" number. (this is what I understand but I am hardly an expert, hopefully someone will confirm, explain or correct what I have said.)
-
Permalink Reply by Les Bamburg on -
Parkdota, refracing of a horizontal Haynesville Shale well is not technically feasible. Refrac's have only been considered and executed for a some vertical Barnett Shale wells.
No company has ever stated any intent to refrac Haynesville Shale wells. The ~ 6.5 Bcf recovery per well is based on the initial completion, natural decline rate and a very long producing life. After about 18 to 24 months the well will require compression as the flowing pressure will drop below typical line pressure.
-
Permalink Reply by GHart on
-
Where do you think the production stabilizes? A 7% decline rate per month would put it at near zero in 14 months. That obviously won't happen. Do they settle in at 50%, 35% or what, of initial production; then get into a long, slow decline, or do they just fall off a cliff after strong initial flows? I assume, too, that this is based on how the Barnett declined. And if what I'm hearing about 6-7 BCF per well is reasonably accurate, what is the time frame for that production? 10, 15, 25 years? It also appears that the drainage area is much smaller than 640 acres. In your opinion, will they continue with 640 acre units, to HBP all the acreage they can, or will the RRC require 320's or even 160's? I've heard that the actual drainage is estimated to be only 80 acres or so.
-
Permalink Reply by Skip Peel - Mineral Consultant on
-
George. The first comprehensive decline rate estimates were given by Chesapeake as follows: 81% - Yr. 1, 34% - Yr. 2, 22% - Yr. 3, 17% - Yr. 4, 13% - Yr. 5, 11% - Yr. 6, 9% - Yr. 7, 8% - Yr. 8, 7% - Yr. 9, 6% Yr. 10. From Yr. 7 forward decline is ~ 1% per year. HA well spacing has already been set by the state at 80 acres. HA Drilling & Production units can be as big as an operator can make the case for but there must be an overwhelmingly compelling reason for the state to approve. Most units are, and will remain, a standard governmental section of 640 acres.
-
Permalink Reply by GHart on
-
Thanks, Skip. When we were drilling the Gladewater Cotton Valley 25 years ago, we started on 640's. Exxon eventually cut the units down to 160, as we all learned the drainage pattern was much smaller. Of course, they waited as long as they could, to HBP all the acreage they could. If the RRC has already determined 80 acre spacing, how can the O&G companies justify a 640 acre unit? Also, on a broad brush calculation, a well that initially produces 10mm/ day will drop to around 500 mcf in year 8 or 9. Is this economically feasible? Would they be moving this many rigs in for these numbers? I guess if it pays out with a small profit in year one, it works. ( I just did a back of the envelope calculation). I'm also still trying to get a handle on the unit configuration. These units appear to be long and skinny, with a good bit of rule 37 apps. How many wells can they drill on one configuration like that, and if they use the same pad to go in a different direction, doesn't that require a new unit, going in that new direction?
Confused but trying to understand!! Thanks
-
Permalink Reply by Skip Peel - Mineral Consultant on
-
George. The units are square sections and the horizontal well bores are drilled on a north-south axis. My examples are LA., not TX., but this graphic may help. The portion of the unit fraced and produced by each well is long and skinny. LOC regs prohibit a horizontal well bore closer than 330' from a unit boundary. So by deduction, the lateral area fraced and produced from each well bore is something less that 660' in total. 5,280' divided by 660' gives you 8 wells per section/unit. I don't know what will be economically feasible in the future. I do know that good early HA wells returned the total investment in 2 - 3 months when gas was in the $10-$13 range in 2008. So now the average well may pay out in 6 - 8. In that time the operator has built and paid for all the infrastructure required to produce that unit with the exception of the drilling cost for each well. There is little risk. There are no dry holes. The operator knows what they will be producing for the next 30 or 40 years.
-
Permalink Reply by DJG on
-
Great explination Skip.
Then again, we here in Texas complicate the matter by having all sorts of wacky shaped units.
-
Permalink Reply by Skip Peel - Mineral Consultant on
-
Thanks, David. Tracking Texas activity just gives me a headache. I am constantly impressed by the knowledge of the E. TX. members who are not employees of the industry. You guys do a great job. And teach me a lot.
-
-
Skip, do they always start drilling from the same side whether it's N or S? Meaning if they're going to drill 8 possible wells do they all start at the top (N) or bottom (S) of each respected section?
-
Permalink Reply by Les Bamburg on
-
Parkdota, no - some wells have surface locations on the south side and some on the north side. In fact, with the superpad concept the operator will drill wells north and south from the same surface pad.
By the way, some units have wells drilled in an east-west orientation.
Support GoHaynesvilleShale.com
Top Content
Not a member? Get our email.
Groups
-
San Augustine, TX
386 members
-
Miller County, Arkansas
27 members
-
Sabine Parish, LA
455 members
-
South Caddo Parish Group
440 members
-
Natchitoches Parish
400 members
-
Bienville Parish, LA
244 members
-
Fayetteville Shale
149 members
-
North Caddo Parish
358 members
-
Central Texas Gas
63 members
-
Cass County, TX
119 members
© 2024 Created by Keith Mauck (Site Publisher).
Powered by
