Profit Margin and Price Markup Calculation and Formulas [Excel]

In this post I am going to run through some Business Maths; to those that are heaving a sigh and have a mental block on maths, I feel your pain. As a high school student my issue with maths in a classroom was always how the examples were relevant to the real world, it was all poles and shadows, angles and area of odd shapes. In Business Maths and Business Statistics everything always ties back to a real problem, or the need for an answer that must be quantified or qualified. The problem could be how much production, how much money, how much profit or simply how many sales days to break-even? This automatically gives relevance to the numbers that you are working with because you are working with money, time (days, weeks, months, quarters, years) and the answer either tell you whether you are mistakes which will cost you money or doing it right and making profit.

This post is fairly high-level and expects you to understand basic maths. We’ll explain some of the concepts and cover Selling Price, Mark-up Percentage, Gross Margin Percentage, and Selling Price Margin which are the sales formulas for businesses. We additionally cover some of the terminology like net, gross, and cost of goods which you hear business people talk about. As we go along we’ll make mention of price-points and other marketing terminology which you might find interesting to look up, and some other academically worthwhile things, that you may or may not know.

Regardless of industry, business has a vernacular (net, gross, cost of goods, markup, markdown, margin) that you need to know, to be able to understand conversations with business people.

This gets more complicated where different businesses have their own cultural vernaculars that often don’t translate outside of their businesses (Low hanging fruit, bandwidth, unicast, multicast, swing around, beach holiday). This is all part of business, industries, and company cultures. Picking up the lingo makes understanding business much easier.

You often hear business people talk about net, gross and revenue, cost of goods. In this context gross refers to the total (everything) and net refers to the part of the total that really matters (what you take home, or were left with). The revenue is what you make (income) from sales, minus discounts and rebates via the main operations of the business. Finally the cost of goods sold (COGS) is a combined value that takes into account numerous out-going money streams such as all the costs of purchase, transport, packaging, material, labour, allocated overhead to store (this is oversimplification it also uses LIFO or FIFO). The goods not sold is deferred as cost of inventory until the inventory sells or gets written down in value.

We need to go through a very simplistic explanation on pricing. In retail psychological pricing is a strategy that says consumers assume odd or un-rounded prices are perceived lower than they actually are. An Example of this would be $ 1.99 or $ 1.98 is perceived as $ 1.00 and not $ 2.00. An easy-ish (I suppose) rounding mistake, unless you are talking about $ 49,999 and the rounding mistakes is $1,000. If you suffer from this mental tick, just practice always rounding up. Psychological pricing is one type of strategy that you can follow. Others are price skimming, price discrimination and yield management, price points, bundle pricing, penetration pricing, price lining, value-based pricing, geo and premium pricing all of which are demand-driven strategies.

For my calculation examples I am going to use a psychological selling price of $1.99

My cost price / purchase price / buy price is going to be $ 1.40

Mark-up Percent

If we know our cost, and selling price, and we want to know the mark-up percentage.

Selling Price

If we don’t have the selling price, but we know the selling price, and have a 42% mark up:

For this step we need to keep in mind that 42% can also be written as 0.42.

Gross Margin Percent

Changing the Mark-up Percentage calculation slightly to the Gross Margin Percent calculation:

Selling Price Margin

If you have a margin requirement of 30% and need to apply this to your pricing:

We’ve gone through the basic pricing calculations for Markup and Margin and looked at both cost + markup/margin and cost and selling to calculate markup/margin perspectives.

Here is a Excel template for Markup and Profit Margin to see the calculations. Click on the link.

If you want to learn more about Business Maths here is a great book on the subject.

29 thoughts on “Profit Margin and Price Markup Calculation and Formulas [Excel]”

    1. Hi Rajesh,

      Thanks for the Question and sorry for taking so long to get back to you,

      If we are discussing Markup and Margin from a pricing and discounting perspective; Markup is the difference between your cost of goods and your selling price. So you are always discussing pricing in terms of the difference between your costs and selling price. This is a popular pricing and discounting method in Retail.

      Margin based pricing is more aligned to the costs. I know that high turnover, low margin (profit) business models prefer this pricing model. This ensures that they continually drive profit in their pricing and decision making.

      Which method to use is based on your business, what matters is that a single method is decided and used. IT can become confusing if both are used in the business.

    1. Hi Lottie,

      50 ZMK (Zambian Kwachas) marked up by 50%.

      A percentage (50%) when expressed as a decimal is shown as 0.50, 20% is 0.20, 10% is 0.10 etc

      Selling Price = Total Cost x (1 + Mark-Up Percent)
      Selling Price = 50 ZMK x (1 + 0.50)
      Selling Price = 50 ZMK x (1.50)
      Rounded Selling Price = 75 ZMK

      Alternatively you could just say half (50%) of 50 ZMK (your cost) is 25 ZMK, and test a selling price of 75 ZMK (50 ZMK + 25 ZMK) as shown below,

      Mark-up Percent = (Selling Price – Cost Price) ÷ Cost Price
      Mark-up Percent = (75 ZMK – 50 ZMK) ÷ 50 ZMK
      Mark-up Percent = 25 ZMK ÷ 50 ZMK
      Mark-up Percent = 50%

      For interest a 50% mark-up is 33.3% gross margin in this case.

  1. I have a question regarding the formula you use in your Excel spreadsheet template included as a link in your post above.. In the “Know the Cost and have a Desired Selling Price?” section, why do you have the selling price column formula being divided by 1 (ie. formula in I18)?

    1. Hi Kel,

      Looking at the formula again Selling Price = ( Cost Price * ( Desired Markup % + 1 ) ) would get you the answer you are looking for; in all honesty can’t remember why I put in the division.

  2. Thanks for the great article.

    I have some suggested edits:
    I explain some of the concepts and covers Selling Price, Mark-up Percentage, Gross Margin Percentage, and Selling Price Margin which is the sales formulas for businesses.
    change “covers” to “cover”
    change “which is” to “which are”

    I’ll also explain certain of the terminology…
    change “certain” to “some”

    …some other academic worthwhile things…
    change “academic” to “academically”

    …terms and phrases and word…
    change “word” to “words”

    The revenue is what you make (income) from sales, discounts, rebates…
    change to “The revenue is what you make (income) from sales, minus discounts and rebates…”

    “Psychological pricing is one type of strategy that you can follow others are…”
    change to “Psychological pricing is one type of strategy that you can follow. Others are…”

  3. Hi,

    If we are using the last method

    Selling Price Margin

    If you have a margin requirement of 70% and need to apply this to your pricing:

    Selling Price = Total Cost ÷ (1 – Gross Margin)
    Selling Price = $1.40 ÷ (1 – .70)
    Selling Price = $1.40 ÷ $0.30
    Rounded Selling Price = $4.67

    Now using first method:

    Selling Price = Total Cost x (1 + Mark-Up Percent)
    Selling Price = $1.40 x (1 + 0.7)
    Selling Price = $1.40 x (1.7)
    Rounded Selling Price = $2.38

    If you want to use 100% margin, there is no possibility for last method l as it will make divider ZERO.

    What do you suggest? As it is better practice to use last method when there is probable customer need for discount, but it doesnt give appropriate result in the actual figure.

    1. Hi Hamzavi,

      I’m confused you want to sell at 10,000% markup? Just use 99.99% margin? You’d sell your $ 1,40 cost item for $ 14,000.00….

      Or am I not getting your point?

      Deon

  4. Hi,
    I have a pricing mark-up of 1.45 in place long time, Now I was asked to break it down and see the composition of this 1.45 mark-up like, does it include freight cost and associated cost (landed cost). Is there are formula or way to go about knowing the break ups.

    1. Hi Jebson,

      There is a way, you are looking to take your total costs (fixed and variable costs) and coming up with a contribution margin per unit for each product (look up CVP Analysis) where each sale adds a particular amount of dollars towards covering your overheads and then pricing accordingly to allow for profit margin (variable cost-plus pricing).

      Hope that helps.

  5. I am new on this I would like to learn more about pricing especially in relation to tendering in the construction industry.

    1. Hi Vusi,

      Pricing or calculating pricing is simple across all industries, the calculations are standardised.

      If you don’t have first hand expertise or adequate experience within a given industry where you are seeking to price a good or service, this is where you will make mistakes in calculating your costs and either make losses or out price yourself against competition.

      Contract in a specialist to do this for you, on the basis that they will be able to give you guidance for a fee.

  6. Hi Deon,

    Thanks for your explanation and calculation examples. They have made my life a lot easier.

    I am nowhere near an expert in Excel or formulas and am having trouble quoting when I get into a competitive situation. Known factor is Cost. I have Formulas to determine Selling Price which includes the Dist Compensation %, Dist Sales Rep Compensation (fixed amount). The fields I can change is our Margin %, the Dist Compensation %, and Dist Sales Rep Compensation.

    What I need to be able to do is enter a Final Selling Price and realize the reduction in the Dist Compensation %, our Margin %. The Dist Sales Rep Compensation is always a fixed amount.

    1. Hi Phil,

      What you want to be able to change the Selling Price and have as inputs to this formula the
      Partner Margin % (Variable) + Dist Comp % (Variable) + Dist Sales Rep Comp % (Fixed)

      What are the constraints to the variables?

      1. I’m not sure what you mean by “restraints” but the Partner Margin is only ever as high as 10% because that is all we offer. When I have to change the selling price down I would like a formula which balances our Margin and the Dist Comp Margin maybe at a 2/3’s/1/3’s ratio. Bear with me here because this is like a 3rd grader (me) trying to communicate with the Professor.

        1. Thanks Philip,

          I think I’m getting a better picture,

          What is the fixed amount you pay the Sales Rep?

          What is the Variable amount you pay the Dist Comp?

          Partner Margin % (Variable) = 10% 2/3
          Dist Comp % (Variable) = ? 1/3
          Dist Sales Rep Comp % (Fixed) = ?

          1. Cost A207
            Margin B207 = i.e. 30%
            Net Price C207 = A207÷(1−B207)
            Company Profit D207 = C207−A207
            Dist Comp E207 = i.e. 10%
            Dist Comp Amount F207 = $$$
            Selling Price G207 = C207+F207+E209
            E209 is the Sls Rep Comp and is always a fixed amount, i.e. $250, which will not vary.
            When I have to lower the selling price I would like to make it so there is a 2/3 reduction from the company and 1/3 from the Dist Comp.

          2. This compensation scheme if my understanding is correct, would only work if the cost price of deal size triple digit or larger. Minimum viable deal size $833.00 because of the fixed sales rep comp.

            If Sales Rep Comp was changed to a percentage, something like 30% of the Selling Price margin then this slides all the way to $1 deals.

            Selling Price is Cost + 30%

            Fixed Costs:
            Sales Rep Comp = $250

            Variable Costs:
            Company Profit = (Selling Price -(Cost +Fixed Cost ))*66.66%

            Dist Comp Amount = (Selling Price -(Cost +Fixed Cost ))*33.33%

    1. Hi Claire,

      Gross Margin could also refer to the difference between revenue and cost of goods sold divided by revenue. I’m assuming however mean unit margin and margin percent.

      Let’s assume Margin is 15%
      Selling Price is $150

      Selling Price ($) * Margin (%) = Margin Amount ($)

      $150 * 0.15 = $22.50

      Margin Amount ($) – Selling Price ($) = Cost Price ($)

      $150 – 22.50 = $127.50

      Checking that another way around:

      (Unit margin ($) / Selling price per unit ($)) * 100 = Margin (%)

      ( 22.50 / $150 ) * 100 = 15%

      Hope that helps.

  7. Hi Deon: Can u help with a little more challenging formula? I want to sell an item for a specific $ amount Profit. I have fixed formulated costs and want to know how much to sell an item for to make that specific $ amount.
    Example: Buy an item for $4.00, want to make $10 on that transaction. My shipping will cost $2.77 and my selling fees will be 15% of the final purchase price (sale price + shipping). Can you figure out a formula to be able to plug in $ amount (profit wanted) and plug in cost of item, to figure out what I would need to sell that item for in order to make that specific $ amount wanted after costs and expenses?

    1. Hi John,

      Happy to Help out,

      Just so that I understand your query properly,

      Selling Price = ( ( Cost + Shipping ) x 1.15 ) + $ 10

      Selling Price = ( ( $4.00 + 2.77 ) X 1.15 ) + $10

      Selling Price = ( $6.77 X 1.15 ) + $10

      Selling Price = ( $7.78 ) + $10

      Selling Price = $17.78

  8. Hi Deon: Looking for a challenging profit formula for a specific $ amount wanted?

    Have fixed selling costs along with plug in expenses. Example: Purchase an item for $4.00, want to make $10.00 net profit after fees, costs & shipping. Plug in: cost, net profit ($ amt wanted) and shipping costs. Fixed seller fees= 15% of final sale price + shipping cost. Can you create a formula for this type of scenario?

    Example Expanded:
    Cost = $4.00
    Shipping = $2.77
    Selling Fees = 15% of Total Sale Price plus shipping
    Net Profit Wanted = $10.00

    What would be the formula to achieve $10.00 net? Always looking to plug in different $ amounts for cost, shipping and net profit wanted but seller fees will always be fixed but off of final sale price + shipping.

    Is this doable?

    Thank you in advance for your consideration!
    John

    1. Hi Vinaya,

      Gross Margin Percentage = ( sales price – cost ) / selling price

      To make it easy, work out how to automate it in your spreadsheet of choice, and you can work out the price point you need to hit at 200% Gross Margin Percentage.

      In this case, it looks like this 200% = ( $150 – $50 ) / $50

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.