Forum Discussion

Gooner09's avatar
Gooner09
Copper Contributor
Apr 20, 2026

Margin calculator for media budget

Hi, I need help formulating a calculator where the 'Fixed Margin' is separated out from the total budget. My calculation needs to add a buffer to the margin as the platform where this is inserted will reduce the nominal margin when you also include a flat cpm fee as it increases the total amount billed. For example, if margin was 20% of $10,000 then the remaining budget left should be $8,000. However, when you add a rate card on top and deliver x amount of media impressions, the effective margin as a percent of total cost is always going to be less than the nominal margin percentage.

Example:

  • Total cost: $10,000
  • Margin fee: 20%
  • Flat CPM rate: $1.50
  • Impressions: 2,000,000

 

then this would lead to this:

  • Media Cost $5,600
  • Margin Fee $1,400
  • Flat CPM rate ($1.5) $3,000 
  • Total $10,000

 

Ideally, I would like to ensure the margin is kept at $2,000 (20%) so need help creating a formula that will raise the margin to a % that keeps your nominal margin rate whilst factoring the flat cpm fee and the impressions served.

 

excel
Formulas and Functions

4 Replies

  • Olufemi7's avatar
    Olufemi7
    Steel Contributor
    Apr 22, 2026

    HelloGooner09​,

    You need to separate CPM as a fixed cost, then preserve margin as a fixed cash value and adjust the margin percentage based on the reduced base after CPM is removed.

    Definitions:
    T = total budget
    m = margin %
    CPM = cost per 1,000 impressions
    Impr = impressions

    Step 1 CPM cost
    CPM_cost = (Impr / 1000) * CPM

    Step 2 fixed margin amount
    Margin = T * m

    Step 3 media budget
    Media = T - CPM_cost - Margin

    Step 4 adjusted margin % for platform input
    Adjusted margin % = (T * m) / (T - CPM_cost)

    Example:
    T = 10000
    m = 20%
    CPM = 1.5
    Impr = 2000000

    CPM cost = 3000
    Margin = 2000
    Media = 5000

    Effective margin on remaining base = 2000 / 7000 = 28.57%

    Key point:
    The nominal margin (20%) stays fixed in cash terms, but the required margin percentage increases because CPM reduces the base that the margin is applied to.

    • Gooner09's avatar
      Gooner09
      Copper Contributor
      Apr 23, 2026

      This has given me more clarity. Thanks for your input

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Apr 20, 2026

    Based on your example (20% margin on $10,000 total = $2,000, plus $3,000 CPM fee), there are two mathematically valid approaches. Choose the one that fits your workflow.

     

    Option A – Fixed Total Budget
    Media cost adjusts down

    Media Cost = Total × (1 − Margin%) − CPM Fee

    Excel: = A1 * (1 - B1) - (C1 / 1000) * D1

    Where:
    A1 = Total budget ($10,000)
    B1 = Margin % (20%)
    C1 = CPM rate ($1.50)
    D1 = Impressions (2,000,000)

    Result: Media cost = $5,000, Margin = $2,000 (20% of total)

     Fixed Total Budget

    Cell

    Label

    Example Value

    Description

    A1

    Total Budget

    10,000

    Fixed total amount client pays

    B1

    Margin %

    20%

    Desired margin as % of total

    C1

    CPM Rate

    1.50

    Flat fee per 1,000 impressions

    D1

    Impressions

    2,000,000

    Total impressions delivered

     

    Option B – Fixed Media Spend
    Total budget adjusts up

    Total = (Media Cost + CPM Fee) / (1 − Margin%)

    Excel: = (E1 + (F1 / 1000) * G1) / (1 - H1)

    Where:
    E1 = Media cost ($5,600)
    F1 = CPM rate ($1.50)
    G1 = Impressions (2,000,000)
    H1 = Margin % (20%)

    Result: Total = $10,750, Margin = $2,150 (20% of total)

    Fixed Media Spend

    Cell

    Label

    Example Value

    Description

    A1

    Media Cost

    5,600

    Fixed media spend (cannot change)

    B1

    CPM Rate

    1.50

    Flat fee per 1,000 impressions

    C1

    Impressions

    2,000,000

    Total impressions delivered

    D1

    Margin %

    20%

    Desired margin as % of total

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

  • mathetes's avatar
    mathetes
    Gold Contributor
    Apr 20, 2026

    You've had a number of views with no response as of this writing. I suspect that could be because, although I'm sure you are entirely clear on what you are saying, those of us from outside your working environment are having a hard time figuring out what you mean. For instance, you refer to "Fixed Margin" in your opening sentence, but nowhere else in the examples. Yes, you refer to margin fees and presumably there's a relationship between the two, but those margins fees aren't fixed (or if they are, it's in some undefined way). 

    And you no doubt understand the significance of adding "a rate card on the top" but that's just jargon from an outsider's point of view.

    My suspicion is that the formula you're looking for is going to be very simple; but one needs to understand the situation more clearly to write it.