In the attached file is yet one more Royalty Estimator spreadsheet....

First a disclaimer:

- This one still has some tweeking to do, so please do feel free to add input that might help improve things from what I have here so far.

- Use this for entertainment purposes only and don't make any stupid purchases or hasty financial decisions from what you see here or from any other Royalty Calculator.   This one, and all others, certainly have their flaws so take that into consideration.

-  Although this calculator uses recent Haynesville decline projections (as per HK's Feb 2010 presentation), that part of the equation is still really a big crystal ball at this point in time.

A few things to note regarding this version:

A.  Decline curve is based upon HK most recent chart from their Feb 2010 Investors Presentation

B.  Daily production decline is calculated each day for every given calendar year which, hopefully, gives a more accurate forcast than calculating the average monthly decline.

C.  Well production is carried out for a 50 year period.    Actual production might trickle onward longer or, perhaps, the operator might shut it down earlier if the annual maintainance costs end up exceeding income at some earlier date in time.

D. I have also included a section that would give some snapshot of what the royalty income MIGHT look like as in-fill production occurs in the unit.    In-Fill production for this worksheet currently sets the in-fill production cycle at a rate of one new well at end of every 4th year.

E.  Unfortunately, the calculations in this worksheet currenly have the annual decline curves as a static field.   Ideally, it would be great to have that field modifiable so that any adjustments to the decline curve (upwards or downwards) could then be easier to calculate.

(Any math wizards out there who might be able to help with the algebra formulas for that function would be greatly appreciated!!!)

F.  The In-Fill production cycle for adding Well's #2 - #8 is also currently static rate  and assumes a value of adding one new well at the end of every 4th year.     It would be great to figure out a way to be able to have that cycle period be a flexible field if the In-Fill production ends up proving to happen a a quicker or later rate in real life.

G.  In-Fill production is also Static for these calculations in respect that royalty/production numbers generated for each additional well is calculated at exactly the same rate and royalty as the 1st well.   That, of course, is not a real-life scenario.   If I can wrap my brain around how to format the spreadsheet to allow for seperate IP/Gas Price/Acreage info for each additional well then I will repost an improved version at that time.

(Again, anyone with input on how to improve, please bring on your suggestions)

H.  Gas Price is currently a Static Field as well and, of course, in the real world there will be fluctuations (hopefully going upwards as time marches onward).

Instructions:

A)  Plug your own variable information in to the fields colored Yellow.   

-  Unit acreage

-  Acres in Unit owned by you

-  Initial Production Reported (or hoped for???)

-  Price of Natural Gas

-  Royalty Percentage per your lease agreement

-  Discount Percentage (i.e. transport costs, enhancing, treating), if any deductions are required by the terms in your lease agreement

B) The output in the Green Fields will display the Royalty Estimates calculated

Any and all suggestions for improvement are welcomed.......