Arithmetic Formula to Calculate Selling Price
Before calculating a selling price in Excel, it’s important to know the appropriate formulas.
If we have the Cost and our expected %Markup, then the formula for calculating the Selling price would be like that:
Selling Price = Cost * (1 + %Markup)
On the other hand, if we have the Cost and our expected %Margin, then the formula is as follows:
Selling Price = Cost / (1 - %Margin)
For this tutorial, we’ll assume you’ve got a List of Products containing Product Names in column B and Costs in column C.
Method 1 – Calculate the Selling Price from Cost and Markup
Steps:
- Select cell E5
- Copy and paste the formula below into the cell and press ENTER.
- Use the Fill Handle tool to complete the lower portion of column E.
Method 2 – Calculate the Selling Price from Cost and Margin
Steps:
- Select cell E5.
- Type the formula below, and press ENTER.
Method 3 – Calculate the Rounded Selling Price in Excel
Steps:
- Select cell F5.
- Type the formula below and tap ENTER.
Here we took Num_digits as 0 because we want to round up our result to the nearest integer.
- Use the Fill Handle tool and drag it down to get the remaining results.
Method 4 – Apply VBA Code to Calculate Selling Price in Excel
Steps:
- Right-click on the Sheet name and select View Code.
- The Microsoft Visual Basic for Applications window opens. Right-click on Sheet4 (VBA) and select Insert > Module.
- Paste the below code into the code module and hit either Run or F5.
Public Function SellingPrice(Cost As Double, Markup As Double)
A = Cost
B = Markup
SellingPrice = A * (1 + B)
End Function
- Close the code module return back to the worksheet.
- Select cell E5 and start typing down =Se, to see the SellingPrice which we have just created pop up.
- Select the function with your cursor and press TAB.
- Set the variable Cost and Markup by clicking on cells C5 and D5.
- Close the parenthesis, and press ENTER.
Things to Remember
- Always ensure that the Cost and Selling Price column is formatted in Accounting.
- Be careful to format the Markup or Margin column in Percentage.
- Remember the distinction between Markup and Margin. If a Product costs $50 and is sold for $100, then its Markup is 100% and the Margin is 50%.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice yourself.
<< Go Back to Selling Price | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!