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:
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: 7284

Attachments:

▶ Reply to This

Replies to This Discussion

Permalink Reply by ALongview on January 29, 2010 at 5:46
I think to know if they are in the ballpark is valuable information for someone to know.....especially in the beginning stages of someone receiving royalties. If nothing else it keeps you aware rather than disinterested and maybe will keep people from spending them before they arrive. Perfect accuracy on the other hand....I would have to agree with Harold, seems like a daunting task.
Permalink Reply by Skip Peel - Mineral Consultant on January 29, 2010 at 6:24
Agreed, ALongview. In it's simplest form, the best advise for those new to receiving royalty income is that all production declines. And shale gas production declines much more rapidly than production from conventional reservoirs. I advise my clients to not spend a dime until they have received six months of royalties. By that time the point is made and quite clear to anyone.
Permalink Reply by VSC DeSoto South on January 29, 2010 at 7:18
Do you think that as they become more experienced with the play they will figure out how to 'engineer and manage' the depletion/decline rate more smoothly. i.e. to manage the decline rate so it is not so steep and maybe not so prolific at first, and then less of a decline curve over the life so the rate of decline is smoother? Some kind of pressure valve to regulate the rate of flow, choke monitoring?
I know nothing about how to manage a well, so I am curious.
Permalink Reply by Skip Peel - Mineral Consultant on January 29, 2010 at 7:34
I think that once operator's get all their HBP wells drilled, they will experiment. Some experimentation is taking place now. VSC, the thing to keep in mind is that energy companies are much more concerned with EUR over the life of a well. That's what they will try to maximize. The high decline rate is actually a good element of shale gas production for those doing the producing. The ability to more quickly adjust supply to demand in order to maintain relatively stable nat gas prices is a huge advantage. Particularly in cases of over-supply. With high decline shale gas wells steadily increasing as a percentage of overall nat gas production, all the industry needs to do to correct an over-supply situation is to slow or temporarily stop the drilling of new wells. The high decline rate of shale gas wells will translate into a fast decline in supply. And keep prices from experiencing extreme short term corrections. Remember in 2008 we were riding high at $13/mcf and then tanked to $3/mcf. Energy pundits believe that nat gas prices will enter a new era of price stability when shale gas as a percentage of over all production approaches 50%.
Permalink Reply by Les Bamburg on January 30, 2010 at 14:16
VSC, operators already control the rate of flow - that is the purpose of the wellhead choke valve. The operators also like the initial high flow rate as this generates a better net present value and quick payout. Of course this is balanced against avoiding reservoir or equipment damage. Also, the rate may be limited due infrastructure restrictions, low commodity prices or to maximize recovery efficiency.
Permalink Reply by Aegis on January 29, 2010 at 9:01
Yeah, I know this won't be accurate to any large degree. But after a couple months of data, you can see a bit of what the "about" value would be for a check. I didn't mean for this to predict, more to analyze data that already has happened in a faster way than plugging away at a calculator.

Obviously, nothing can really predict an accurate value- although decline, etc are something that you could probably factor in if you wanted to and had the knowledge. But by using an average monthly price, and assuming that gas wells don't produce 100,000 units on day one, and 0 units on day two, you can get a general idea I think. Of course, being new I can be completely, utterly wrong on this.

What this does do is let you know that you won't get rich on shaling - but that with a OK chunk of land you can make a nice supplemental income for a while. By my estimates, my family might make as much as $4k a month for a while, and even if it's 6 months and then it's dry at least that's 24k I didn't have.

Or, if you are lucky enough to have a producing well and a sizable chunk of the drilling unit, you may well get rich in a few short months (we aren't that lucky :) )
Permalink Reply by Skip Peel - Mineral Consultant on January 29, 2010 at 9:12
You might try dividing 85% by 12 and calculating a monthly decline of 7%. The second month check is 93% of the first. The third month is 86% of the first. The fourth is 79% of the first. The fifth is 72% of the first. And the sixth is 65% of the first. If the first month is $4K, the sixth is ~ $2.6K. Not scientific but much closer to reality.
Permalink Reply by Aegis on January 29, 2010 at 9:20
Interesting; this would make it a predictor of future income (with a high degree of error, likely) whereas I was using this to make dollars and sense out of the numbers from the SONRIS production reports. Like, it might help to know if your check should be ~$2000 for the past month, but then you see that the royalty check is just $500. It might still be a valid check, but would illuminate where you lacked knowledge of the process as to why there was a disparity - or, it might help you catch a wild miscalculation of royalties (if that ever happens).
Permalink Reply by Skip Peel - Mineral Consultant on January 29, 2010 at 9:26
Gook Luck. SONRIS gives you the production for a month by unit (LUW code). So you don't know daily production. Did your well produce for 30 days or 20 days? You can always call your operator and ask. The degree of error is less than that involved in using a calculator that does not include a decline variable or one that is significantly inaccurate such as 50%.
Permalink Reply by Wildcat on January 29, 2010 at 8:14
I use this when calculating. Remember this is an estimate, and doesn't account for transportation costs, etc. This is annual production, avg. your production for a year to get a good calculation.


http://geology.com/royalty/
Permalink Reply by Skip Peel - Mineral Consultant on January 29, 2010 at 8:26
None of the on-line calculators I have found to date use a decline rate based on the Haynesville Shale. Unless they calculate monthly income, you can't tell. Geology.com, Penn State U, etc. all give royalty income as "first year". It must be remembered that until about a year ago almost no one had royalty from a HS well and other than the Barnett, there was practically zero any and everywhere. There was no need or demand for a calculator based on shale gas declines. These calculators are set up to calculate royalties based on the type of declines found in "conventional" reservoirs, not "unconventional". They all significantly over-state royalty because they use a decline close to 50%, not 85%.
Permalink Reply by Aegis on January 29, 2010 at 9:29
This opens an interesting question, at least to me - how long do these "unconventional wells" last before they run dry (or produce so little that it isn't worth the effort)? After the first year's steep declines, is there a trickle-out that produces low, but relatively stable, amounts of about 15% the value of the original numbers?

I read the petrohawk report on the apparent hyperbolic decline of the wells. Skip, is your feeling that these reports are correct?

RSS

Support GoHaynesvilleShale.com

Top Content 

1 Expand Confirms Presence In The Western Haynesville

Expand Confirms Presence In The Western Haynesville
2 Olympia Fields

Olympia Fields
3 U-Turn (Horseshoe) Wells In The Haynesville Play

U-Turn (Horseshoe) Wells In The Haynesville Play
4 Request and Offer

Request and Offer
5 E.U. Trade Deal Comes With Impossible Energy Promises

E.U. Trade Deal Comes With Impossible Energy Promises
6 Current Leases Details if Franklin County

Current Leases Details if Franklin County
7 Black Stone Minerals - Western Haynesville Expansion

Black Stone Minerals - Western Haynesville Expansion

Groups

Blog Posts

Tuscaloosa Trend Sits On Top Of Poorest Neighbourhood For Decades - Yet No Royalties Ever Paid To The Community -- Why??

In researching the decades-old Tuscaloosa Trend and the immense wealth it has generated for many, I find it deeply troubling that this resource-rich formation runs directly beneath one of the poorest communities in North Baton Rouge—near…

Continue

Posted by Char on May 29, 2025 at 14:42 — 4 Comments

Not a member? Get our email.



© 2025   Created by Keith Mauck (Site Publisher).   Powered by

Badges  |  Report an Issue  |  Terms of Service