Farm Futures logo

This farmer-created spreadsheet looks at expected profit for various price and yield scenarios.

John Otte 1, Economics Editor

February 5, 2016

3 Min Read

Jordan Shockman, who farms near LaMoure, ND, created a spreadsheet to calculate expected profit at various price and yield scenarios. Shockman figures both income from crop sales and income from Revenue Protection crop insurance, when applicable, into the final profit per acre number.

You can change the values in any of the green cells.  When you change a value in a green cell, the table automatically updates with the new figures. 


A cell that changes color to “light blue” indicates profit at that price and yield combination.  A cell that changes color to red indicates the worst possible yield and price combination that you could receive based upon what you entered for your crop insurance coverage as well as your costs per acre at the top of the spreadsheet.  “White” cells indicate a loss as indicated by a number in parenthesis.   

 The red line moves to the left when the market price rises to a certain threshold.  Above that price level it is more advantageous to get more bushels than it is to collect on crop insurance.  

One row has no red cells because your maximum loss is the same all the way across.  Below that price it’s more advantageous to collect crop insurance revenue because the less bushels you produce the more you will collect. Above that price you’re better off getting more bushels and selling them at the higher price. 

“This spreadsheet has helped me get a bigger picture as to how crop insurance and farm income work together,” says Shockman.  “It will help give farmers a good snapshot of how likely they are to make a profit which will help them decide a comfortable marketing price as well as crop insurance protection level.”

Contact Shockman ([email protected]) if you have questions on the spreadsheet.

You can download the spreadsheet using the link below.

Do you have a financial tool that has made a difference on your operation that you would be willing to share with other readers? Drop us a line at [email protected] with a description of the spreadsheet. We’ll pay $100 if we feature your farmer-written noncommercial program as our Financial Tool of the Month.

Recognize risk of leverage

Leverage is powerful. It amplifies earnings. But it can cut you to shreds should an unexpected market move occur.

Leverage was at the heart of the hedge-to-arrive grain trade debacle in the mid-1990s. Farmers who priced corn on HTA contracts were leveraged by the underlying futures contracts sold on their behalf. Corn prices moved sharply higher. The old crop-new crop corn price spread more than doubled its previous widest level. That unexpected price move really hammered sellers on multi-year HTA contracts.

Other sectors can be even more vulnerable to leverage. Currency traders typically seek returns of fractions of a percent. They often leverage--borrow money--to multiply their returns.

In late January, Switzerland’s central bank scrapped a three-year cap on the value of its franc against the euro. The swiss franc skyrocketed. That was a major, and unexpected, move. Leveraged currency traders, who were on the wrong side of the market, suffered.

New York-based broker FXCM serviced retail currency investors. Its clients lost so much money that the company hemorrhaged over $200 million of cash. Shares of FXCM plunged over 87% after the firm revealed its equity was wiped out by massive client losses.

Understand leverage position of investments you contemplate making.

About the Author(s)

Subscribe to receive top agriculture news
Be informed daily with these free e-newsletters

You May Also Like