Margin = (Price - Cost) / Price | Markup = (Price - Cost) / Cost Gross Margin % is =(Selling_Price - Unit_Cost) / Selling_Price. Markup % is =(Selling_Price - Unit_Cost) / Unit_Cost. Break-even units is =Fixed_Costs / (Selling_Price - Variable_Cost_Per_Unit).
How to Calculate Profit Margin, Markup, and Break-Even Point in Excel
Miscalculating the difference between Margin and Markup is one of the most expensive errors in business pricing. While both measure profitability, they use completely different denominators.
This guide provides formulas for Gross Profit, Net Profit Margin, Markup, Target Price Solving, and Break-Even Volume Analysis.
1. Margin vs. Markup: The Definitive Formula Guide
$$\text{Gross Margin %} = \frac{\text{Selling Price} - \text{Cost}}{\text{Selling Price}} = \frac{\text{Profit}}{\text{Revenue}}$$
$$\text{Markup %} = \frac{\text{Selling Price} - \text{Cost}}{\text{Cost}} = \frac{\text{Profit}}{\text{Cost}}$$
Spreadsheet Formula Examples:
| Parameter | Product A (Direct) | Product B (Wholesale) | Formula Structure |
|---|---|---|---|
| Unit Cost (COGS) | $40.00 | $120.00 | Input |
| Selling Price | $100.00 | $160.00 | Input |
| Gross Profit ($) | $60.00 | $40.00 | =Price - Cost |
| Gross Margin (%) | 60.0% | 25.0% | =(Price - Cost) / Price |
| Markup (%) | 150.0% | 33.3% | =(Price - Cost) / Cost |
[!IMPORTANT] Margin can never exceed 100%, but Markup can easily exceed 100% (e.g. buying for $10 and selling for $50 is an 80% margin and a 400% markup).
2. Setting Selling Prices: Solving for Target Margin
When launching a product, you know your supplier unit cost and your target gross margin.
= Unit_Cost / (1 - Target_Margin_Percent)Pricing Table Example:
If manufactured cost is $35.00 per unit:
- For 30% Margin:
=35 / (1 - 0.30)$\rightarrow$$50.00 - For 50% Margin:
=35 / (1 - 0.50)$\rightarrow$$70.00 - For 70% Margin:
=35 / (1 - 0.70)$\rightarrow$$116.67
3. Break-Even Unit Volume and Dollar Analysis
The Break-Even Point determines the exact unit sales volume required to cover all fixed operating expenses (rent, salaries, software, insurance):
$$\text{Break-Even Units} = \frac{\text{Total Fixed Operating Costs}}{\text{Selling Price per Unit} - \text{Variable Cost per Unit}}$$
$$\text{Contribution Margin per Unit} = \text{Selling Price} - \text{Variable Cost}$$
$$\text{Contribution Margin Ratio} = \frac{\text{Contribution Margin}}{\text{Selling Price}}$$
Break-Even Model Setup:
| Cell | Cost Category | Value | Formula / Type |
|---|---|---|---|
| B1 | Monthly Fixed Overhead (Rent, Admin, Salaries) | $24,000.00 | Fixed Costs |
| B2 | Selling Price per Unit | $80.00 | Revenue per Unit |
| B3 | Variable Cost per Unit (COGS + Shipping + Fee) | $32.00 | Variable Costs |
| B4 | Unit Contribution Margin | $48.00 | =B2 - B3 |
| B5 | Contribution Margin Ratio | 60.0% | =B4 / B2 |
| B6 | Break-Even Volume (Units) | 500 units | =ROUNDUP(B1 / B4, 0) |
| B7 | Break-Even Revenue ($) | $40,000.00 | =B6 * B2 (or =B1/B5) |
4. Net Profit Margin Model (Income Statement View)
To calculate Net Profit Margin after taking into account operating expenses, depreciation, interest, and taxes:
= Net_Income / Total_Revenue| Line Item | Amount ($) | % of Revenue | Spreadsheet Formula |
|---|---|---|---|
| Gross Revenue | $500,000.00 | 100.0% | =SUM(Sales_Columns) |
| Cost of Goods Sold (COGS) | -$210,000.00 | 42.0% | Direct production costs |
| Gross Profit | $290,000.00 | 58.0% | =Revenue - COGS |
| Operating Expenses (SG&A) | -$145,000.00 | 29.0% | Payroll, marketing, rent |
| Operating Income (EBIT) | $145,000.00 | 29.0% | =Gross Profit - SG&A |
| Taxes & Interest (25%) | -$36,250.00 | 7.25% | =EBIT * Tax_Rate |
| Net Income (Bottom Line) | $108,750.00 | 21.75% | =EBIT - Taxes |
- Net Profit Margin:
21.75%($108,750 / $500,000).
Frequently Asked Questions
What is the key difference between Margin and Markup?
Margin is profit as a percentage of Revenue (Selling Price). Markup is profit as a percentage of Cost. A product costing $50 sold for $100 has a 50% margin and a 100% markup.
How do I calculate required selling price from desired gross margin?
Use Price = Cost / (1 - Desired_Margin_%). For example, if Cost is $60 and desired margin is 40%: =60 / (1 - 0.40) returns $100.00.
What is the formula for Break-Even Revenue?
Break-Even Revenue ($) = Fixed Costs / Contribution Margin Ratio, where Contribution Margin Ratio = (Price - Variable Cost) / Price.