How to calculate Margin and Markup extra charge in Excel

The notion of extra charge and margin (people say "gap") are similar to each other. They are easily confused. Therefore, we first clearly define the difference between these two important financial indicators.

We use an extra charge for the formation of prices, and a margin for calculating the profit from the total income. In absolute terms, extra charges and margins are always the same, but their relative (percentage) indicators are always different.



Formulas for the calculation of margin and extra charge in Excel

To implement this task, we need only two financial indicators: price and cost. We know the price and the cost of the goods, and we need to calculate the margin and extra charge.

The formula for calculating the margin in Excel

Create a table in Excel, as it shown in the picture:

In the cell under the word margin D2 enter the following formula:

As a result, we obtain an indicator of the dimension of the margin, we had 33.3%.

The formula for calculating an extra charge in Excel

We move the cursor to cell B2, where the result of calculations should be displayed and enter the formula:

As a result, we get the following indicator of the extra charge percentage: 50% (it's easy to check 80 + 50% = 120).

Done!



The difference between margin and extra charge in the Example

Both these financial indicators consist of profit and expenses. Than they differ from each other? The differences are significant!

These two financial indicators differ in the way they are calculated and in percentage terms.

The extra charge allows the enterprises to cover expenses and to receive profit. Without it the trade and production suffered loses. And the margin is already the result after the extra charge. For an illustrative example, we may define all these concepts by the formulas:

1. Product price = Cost price + Extra charge.
2. Margin - is the disparity between price and cost.
3. Margin is the share of profit which the price contains, so the margin can not be 100% or more, as any price contains a share of the cost price in it.

The extra charge is a part of the price that we added to the cost price.

The margin is part of the price that remains after deduction of the cost price.

For clarity, let us put the above information into the formulas:

1. N = (Ct-S) / S * 100.
2. M = (Ct-S) / Ct * 100.

Description of indicators:

• N - Indicator of extra charge;
• M - Indicator of margin;
• Ct - The price of the goods;
• S - The cost price.

If you calculate these two figures in numbers the result is: Extra charge = Margin.

In percentage the result is: N > M.

Pay attention, the extra charge can be 20 000%, and the margin level will never exceed 99.5%. Otherwise, the cost price will be Less Than 0.

The entire relative (in percentage) financial indicators allow you to display their dynamic changes. Thus, changes in indicators are tracked in specific time periods.

They are proportional: the larger the extra charge, the greater the margin and profit.

This gives us an opportunity to calculate the values of one indicator, if we have the values of the second. For example, extra charges can be used to predict real profit (margin) and vice versa. If the goal is to reach a certain profit, you need to calculate how to set price to have the desired result.

Let’s summarize before the practice:

• For margin we need the indicators of the amount of sales and extra charges;
• For the extra charge we need the amount of sales and margin.

How to calculate the margin in percentage if we know the extra charge?

For obviousness, we give a practical example. After collecting the reporting data, the firm received the following indicators:

1. Volume of sales = 1000$
2. Extra charge = 60%
3. Based on the obtained data we calculate the prime cost (1000 - x) / x = 60%

Hence we have x = 1000 / (1 + 60%) = 625

We calculate the margin:

• 1000 - 625 = 375
• 375/1000 * 100 = 37.5%

From this example follows the algorithm of the formula for counting for Excel:

How to calculate the extra charge in percentage if you know the margin?

Reports on sales for the previous period brought the following indicators:

1. Sales volume = 1000$
2. Margin = 37.5%
3. Based on the obtained data we calculate the prime cost (1000 - х) / 1000 = 37,5%

Hence we have x = 625

We calculate the extra charge:

• 1000 - 625 = 375
• 375/625 * 100 = 60%

Example of an algorithm for calculating for Excel:

Download example calculate in Excel

Note. To test the formulas, press CTRL + ~ (the key "~" is before the one) to switch to the corresponding operation. To exit this operation, press again.