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: 7009
- Attachments:
-
-
royalty calculation.xls, 56 KB
-
Replies to This Discussion
-
Permalink Reply by Aegis on
-
AWESOME! Thanks, this is what I was hoping for - not for my file to become awesome, but for someone to come along and make a better one based on the original idea. Many thanks, Ledlights!
-
Permalink Reply by Aegis on
-
Hey, Ledlights - in the first column, the mmcf per day - let's say that SONRIS says my well is producing (for the first 6 months) a value of ~200,000 mcf average per month.
What would my number be in the first yellow mmcf box?
I put in the correct values in all the other boxes, and the yearly total return is just $2213 and change. Maybe that's right for the first year for 6 acres in a 640 acre space, but it seems pretty low; it seems more like a monthly figure than a yearly return. I mean, if it takes 8 wells to make me just $16,000 a year, why are people so excited? I mean, do most people own huge amounts of acres or something?
Because if your spreadsheet is correct, and the "3" in that first box is typical, then it would take me 20 years to make $6000, which isn't particularly exciting news :).
-
Permalink Reply by Aegis on
-
My math skills are terrible, so here's my take - if it produces 200,000 1k cuft units, then that is 20,000,000 cuft a month, right? So, that mmcf is millions of cu ft - so the first number is 20?
That makes the first year payout ~14k if gas were averaged 3.50 the whole year, which seems more accurate for what SONRIS is reporting and what we've been told to expect, royalty-wise.
So, with 2 or 3 wells, 6 acres could make a decent amount of money, I suppose. Nothing earth-shaking, but some OK cash. If all wells were similar, about $150k over 20 years.
-
Permalink Reply by Skip Peel - Mineral Consultant on -
Yes, 6 acres will make for a nice ancillary income. And multiple wells throwing off royalty simultaneously will mitigate the decline rate to some extent in the future. The variable we should all be most interested in is the future price of nat gas. If we make wise choices in national energy policy and use nat gas as the Blue Bridge to a Green Energy Future (Les. B), you may have monthly royalty checks at ~$5.25 in the not too distant future. That would equate to an ~50% increase in royalty income. Plug that one into the calculator. Not bad, eh?
-
Permalink Reply by DJG on
-
Another interesting variable to hope for - but not at all possible to add to the calculations at the moment - is what developments forward in the science of the play might take place over the next 5, 10 or 20 years that would help improve upon the current estimated ultimate recovery reserves possibile from each well.
What I have read so far is that with current technologies at hand the projected EUR for each well is somewhere around only 25% of the actual Gas In Place.
I would imagine that all of the O&G's out there have some pretty darn smart teams in house working out the science as to how to improve that percentage to a much more efficient level.
Considering that horizontal fracturing techniques are still in their infancy, one could only hope that as the technology advances then we can hopefully expect for the EUR percentages relative to GIP to improve as well.
Would love to hear Les' and ShaleGeo's take on that question???? -
-
Permalink Reply by Les Bamburg on
-
DG, the 25% recovery may be conservative and performance data may show the recoveries to be 35-50% in some parts of the play. This is reason some wells could have EUR's closer to 12 Bcf. This is not based on improved technology but rather just a better understanding of the formation characteristics.
Additional experience will allow the operators to tweak their fracture stimulation design and improve performance. Finally, operators still have to determine the optimum number of wells per section in each area of the HS/BS play. -
-
Permalink Reply by ledlights on
-
Aegis, my spreadsheet is not real versatile for making it easy to use anything other than the initial daily production. But I interpret your numbers a little differently. 200,000 mcf would be 200 mmcf per month, or about 1.2 bcf for the 1st 6 months. If I guess that the second half of the year's production is somewhere between 33% and 50% of the first half then the year's total should be between 1.6 and 1.8 bcf. Playing with input to the initial production cell (yellow) works if I insert from 13.3 mmcf to 15 mmcf. Not quite as nice as 20 mmcf initial production, but not bad.
-
Permalink Reply by chilly willy on
-
If i understand correctly. It costs nat. gas developers approx. $6.5-$8 million to build out a horizonal well + lease payments over the section. Using the proposed royalty spreadsheet, the total gross sales over 29 years =$3.5 million??? Or, is that $3.5 million in total royalties for the 640 over the holding period?
Thanks for any info and the calaulator
Charles
-
Permalink Reply by ledlights on
-
Chilly,
You are correct, this well probably would not pay for itself. (I like to start out with low expectations whenever I do projections) But I believe that the average HS wells are producing much better than 3 mmcf initially. This spreadsheet is hardly precise, but you can see that initial production will have to be at least twice this to have a break even well. Plus you have to subtract the royalty pay out and other sales expenses. If the production company is able to improve their drilling and completion efficiency then the break even point goes down.
-
Permalink Reply by ledlights on
-
And the default gas price on my spreadsheet (you can change it) $ per mcf is relatively low (we hope) at $3.50. Originally some gas companies were saying they needed prices to be at least $7 per mcf to profitably drill the HS. I have seen lower estimates more recently.
-
-
Permalink Reply by Les Bamburg on
-
Aegis, a Haynesville Shale will never "run dry" or water out. In theory the well rate will decline to an economic limit but this may be after 30 to 60 years of production.
Shale gas wells have a hyberbolic decline initially but go to an exponential decline after a given number of years of production.
Support GoHaynesvilleShale.com
Top Content
Not a member? Get our email.
Groups
-
San Augustine, TX
386 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
-
Franklin County, Texas
8 members
© 2024 Created by Keith Mauck (Site Publisher).
Powered by
