Product Pricing Calculator in Google Sheets (With Margin)

Photo by Sasun Bughdaryan on Unsplash

What margin and markup actually mean

Before building any pricing model it is worth being clear about two terms that are often confused.

Margin (also called gross margin) is profit expressed as a percentage of the selling price. If you sell a product for £50 and make £10 profit, your margin is 20 %.

Markup is profit expressed as a percentage of the cost. The same £10 profit on a £32 cost gives a markup of roughly 31 %.

A 20 % margin and a 20 % markup produce different prices. This calculator uses margin throughout because platforms such as Amazon and Etsy report profitability as a percentage of revenue, not cost.

All monetary inputs should be in the same currency and exclude recoverable tax (such as VAT you reclaim). Do not add a tax rate inside this model; applying tax to each cost and then again to the final price double-counts it. If you charge sales tax to customers, add it to the published price separately after you have the pre-tax list price from this calculator.

Use the ready-made template

If you have GSheetPress installed, the fastest starting point is the built-in template. In Google Sheets open Extensions → GS2Web SaaS → Calculator Templates → Business → Product Pricing Calculator. When prompted, give the copy a meaningful name such as Pricing Model – Spring 2025. The template already contains all the row labels and result formulas described in this article.

You can edit the text in column A (labels) and column E (default values) without breaking anything, provided you do not insert or delete rows or move cells in columns B through H. Review the sample numbers in the result rows before changing anything, so you understand what each output represents. When you are ready to make it public, follow the publishing steps in the final section of this article — the template is not live until you publish it yourself.

If you prefer to understand every cell or do not yet have the template, continue with the step-by-step build below.

Prerequisites

• A Google account with Google Sheets access.
• The GSheetPress GS2Web SaaS add-on installed from the Google Workspace Marketplace. The account you install it under must match the account you use to open the spreadsheet.
• A Calculator entitlement on your GSheetPress plan. Check GSheetPress pricing if you are unsure which plan includes the Calculator tool.
• A dedicated sample workbook. Create a blank Google Sheet named something like Pricing Calculator – Sample. Do not use a sheet that contains real business data, because the publishing step later makes the spreadsheet editable by anyone with the link.

How the pricing model works

The model has two modes: it recommends a list price that will achieve your target margin, and it checks whether a price you have in mind (the test price) also meets that target.

The cost base

Total cost = product cost + packaging cost + shipping cost. Shipping is treated as a seller expense; there is no separate shipping revenue line. Any cost not entered here is excluded from the profit figure, so the result is contribution margin, not complete business net profit.

Discounted revenue

Discounted revenue = List price × (1 − Discount rate). The discount rate is the percentage markdown you offer (for example, a voucher or promotional price). When there is no discount, this equals the list price.

Profit

Profit = Discounted revenue − Commission − Total cost, where Commission = Commission rate × Discounted revenue. Both commission and your target margin are deducted from the same discounted revenue base. Writing that out as an equation:

Discounted revenue × (1 − Commission rate − Target margin rate) = Total cost

Substituting the definition of discounted revenue and solving for list price:

List price = Total cost ÷ ((1 − Discount rate) × (1 − Commission rate − Target margin rate))

Rates in that equation are fractions (10 % = 0.10). In the spreadsheet the inputs use whole numbers (enter 10, not 0.10) and the formulas divide by 100.

For the commission and margin rates to make mathematical sense, their sum must be below 100 %. The discount rate must also be below 100 % on its own. There is no combined ceiling for commission plus discount.

Build the pricing calculator step by step

Step 1 — Create the calculator shell

1. Open your dedicated sample workbook in Google Sheets.
2. Click Extensions → GS2Web SaaS → Calculator → New Calculator.
3. In the dialog, enter the name Product Pricing Calculator.
4. Set the number of inputs to 7. (There are seven data inputs; the button does not count.)
5. Leave the graphs option unselected unless you want to add charts later.
6. Click Create. GSheetPress inserts a new sheet tab and generates the header row in A1:H1.

The generated headers are: Labels, Input Type, Required, Data, Default, ToolTips, Inputs, Rules (older templates spell the sixth header ToopTips — either spelling works). Do not rename or move these headers.

With seven inputs the layout is:

Step 2 — Fill in the input rows (A2:H8)

Column G (Inputs) holds the current values submitted from the web form. Column E (Default) holds the values shown when the calculator first loads. To test formulas before publishing, copy E2:E8, click G2, and use Paste special → Values only so that G2:G8 contain the defaults. G will be overwritten each time a visitor submits the form, so never put result formulas there.

Fill each cell exactly as shown. Cells marked blank should be left empty.

Row 2 — Product cost

Row 3 — Packaging cost

Row 4 — Shipping cost

Row 5 — Commission percent

Row 6 — Discount percent

Row 7 — Target margin percent

Row 8 — List price to test

Step 3 — Verify the button row (row 9)

GSheetPress generates the button row automatically. Check that it looks like this and correct it if it does not:

The label in A9 is the button text displayed on the form. E9 contains the word Reset, which is a system value that tells GSheetPress to add a reset button; it is not the button's caption. Do not change D9 or E9.

Step 4 — Add the result heading row (row 11)

Row 10 is the merged separator that GSheetPress generated. Leave it alone. Click cell A11 and type Result. Click cell B11 and type Value. These are column headings for the results table.

Step 5 — Enter the result formulas (rows 12–23)

Click the target cell, type or paste the formula starting with =, and press Enter. Formulas reference G2:G8 for live inputs. The number format 0.00 should be applied to B13:B22 (select the range, then Format → Number → Custom number format → enter 0.00).

B12 — Input check

This guard cell checks that all seven inputs are present and sensible before any result is shown. It reads as: if any of the seven G cells is non-numeric, return an error message; if any cost is negative, the total cost is zero or below, any percentage is negative, the test price is zero or below, the discount is 100 % or more, or commission plus target margin is 100 % or more, return a warning; otherwise return Ready.

Click A12 and type: Input check

Click B12, paste the formula below, and press Enter:

=IF(COUNT(G2:G8)<>7,"Enter all seven numbers",IF(OR(MIN(G2:G4)<0,SUM(G2:G4)<=0,G5<0,G6<0,G7<0,G8<=0,G6>=100,G5+G7>=100),"Check input values","Ready"))

B13 — Total product and fulfilment cost

Click A13 and type: Total product and fulfilment cost

Click B13 and enter:

=IF(B12="Ready",SUM(G2:G4),"")

This sums product cost, packaging, and shipping. All other results build on this figure.

B14 — Recommended list price

Click A14 and type: Recommended list price

Click B14 and enter:

=IF(B12="Ready",ROUNDUP(B13/((1-G6/100)*(1-G5/100-G7/100)),2),"")

This applies the canonical equation derived earlier. ROUNDUP (rather than ROUND) ensures the recommended price is never fractionally below what is needed to cover costs, even after rounding.

B15 — Recommended price after discount

Click A15 and type: Recommended price after discount

Click B15 and enter:

=IF(B12="Ready",ROUND(B14*(1-G6/100),2),"")

B16 — Commission on recommended sale

Click A16 and type: Commission on recommended sale

Click B16 and enter:

=IF(B12="Ready",ROUND(B15*G5/100,2),"")

B17 — Profit on recommended sale

Click A17 and type: Profit on recommended sale

Click B17 and enter:

=IF(B12="Ready",ROUND(B15-B16-B13,2),"")

B18 — Margin on recommended sale (%)

Click A18 and type: Margin on recommended sale (%)

Click B18 and enter:

=IF(B12="Ready",IF(B15=0,"Sale price rounds to zero",ROUND(B17/B15*100,2)),"")

The inner IF guards against dividing by zero if an extreme discount causes the discounted price to round to zero.

B19 — Test price after discount

Click A19 and type: Test price after discount

Click B19 and enter:

=IF(B12="Ready",ROUND(G8*(1-G6/100),2),"")

B20 — Commission on test sale

Click A20 and type: Commission on test sale

Click B20 and enter:

=IF(B12="Ready",ROUND(B19*G5/100,2),"")

B21 — Profit on test sale

Click A21 and type: Profit on test sale

Click B21 and enter:

=IF(B12="Ready",ROUND(B19-B20-B13,2),"")

B22 — Margin on test sale (%)

Click A22 and type: Margin on test sale (%)

Click B22 and enter:

=IF(B12="Ready",IF(B19=0,"Sale price rounds to zero",ROUND(B21/B19*100,2)),"")

B23 — Does the test price meet the target?

Click A23 and type: Does the test price meet the target?

Click B23 and enter:

=IF(B12="Ready",IF(B19=0,"Check test price",IF((B21/B19*100)>=G7,"Yes","No")),"")

This compares the unrounded margin ratio (B21/B19*100) against G7 rather than comparing rounded display values, which avoids false negatives at boundary margins.

Step 6 — Test with the defaults before publishing

Select E2:E8, copy, click G2, and choose Edit → Paste special → Values only. G2:G8 should now contain 25, 2, 5, 10, 5, 20, 50. Check that B12 shows Ready and verify the remaining results against the worked example in the next section.

Worked example with expected results

Using the default inputs — product cost 25, packaging 2, shipping 5, commission 10 %, discount 5 %, target margin 20 %, test price 50 — the model should produce the following values.

The margin in B18 is 20.01 % rather than exactly 20 % because ROUNDUP in B14 adds one cent to guarantee costs are covered. The underlying unrounded price meets the target precisely; the rounding moves margin fractionally above it.

Alternative scenario — lower test price

Change G8 to 45 (keeping all other inputs the same). The test price after a 5 % discount becomes 42.75, but because the commission is applied to that lower base, profit on the test sale drops to 6.47 and margin falls to 15.13 %. B23 shows No, confirming that 45 does not meet the 20 % target. To hit the target you would need to use the recommended list price of 48.13 or higher.

Zero-fee scenario

If you sell direct with no commission and no discount, enter 0 for commission, 0 for discount, 0 for target margin, and 32 for the test price. The recommended list price equals the total cost (32.00), margin on both the recommended and test sale is 0 %, and B23 returns Yes because zero equals the zero target.

Troubleshooting

B12 shows "Enter all seven numbers"

At least one of G2:G8 is empty or contains text. If you are testing before publishing, copy E2:E8 and paste values into G2. On the live form, this message means a visitor left a field blank. All seven fields are marked required, so the form should prevent submission — if you see this in the sheet it usually means you edited G manually and left a gap.

B12 shows "Check input values"

One of the following is true: a cost (G2, G3, or G4) is negative; the total of G2:G4 is zero or negative, meaning all three costs are zero; commission (G5) or discount (G6) or target margin (G7) is negative; the test price (G8) is zero or negative; the discount (G6) is 100 or more; or commission plus target margin (G5+G7) is 100 or more. Check each input in turn. For example, entering 80 for commission and 25 for target margin gives a combined 105 %, which is impossible — the platform would take more than 100 % of revenue before you account for margin.

B18 or B22 shows "Sale price rounds to zero"

The discounted price rounded to zero or below. This happens if you enter an extremely small test price (such as 0.001) with a high discount. Enter a realistic price.

Recommended margin is slightly above the target

This is expected. ROUNDUP lifts the recommended price by at most one cent above the algebraically exact value, which nudges the margin fractionally above the target. If you need to report exactly 20.00 %, note that the actual margin is close enough; ROUNDUP is deliberate to ensure costs are always covered.

Results look correct in the sheet but the published calculator shows blanks

G2:G8 are populated when the web form is submitted, not before. The published form starts with column E defaults displayed in the fields; it only writes G after the visitor clicks Calculate. This is normal — the result cells show blank until the first submission populates G.

Formulas return errors instead of results

Check that every formula references B13 (total cost) rather than a manually typed number, that G2:G8 contain numbers and not text, and that you have not accidentally left a space before the equals sign in any formula cell. If a formula cell shows the formula text rather than a result, select the cell and confirm the cell format is not set to Plain text (Format → Number → Automatic).

The published calculator link is not working after re-publishing

Re-publishing the same existing calculator retains its original link. If you created a second calculator by mistake, open the Calculator list from the add-on, find the original entry, and use its link. See the GSheetPress support page if the link still does not resolve.

Frequently asked questions

Why does the margin on the recommended sale show 20.01 % and not exactly 20 %?

The formula uses ROUNDUP to lift the recommended list price to the next whole cent whenever the exact algebraic answer falls on a fraction. That extra cent increases revenue by a tiny amount, which pushes the margin fractionally above the 20 % target. This is intentional: rounding down would produce a price that is theoretically one cent too low to cover all costs at exactly the target margin. If you compare the unrounded margin against the target (as the Yes/No formula does) the relationship holds precisely.

Can I add sales tax or VAT to the model?

This model intentionally excludes tax from its formulas because applying a tax rate inside the model and then again when you set your storefront price would double-count it. Use the recommended pre-tax list price from B14, then calculate the tax-inclusive price separately outside this sheet. If you are not registered for VAT or your platform handles tax on top of the listed price automatically, no adjustment is needed. Always verify the treatment with your accountant or tax authority, as rules vary by jurisdiction. The Google Sheets function reference documents all calculation functions if you want to extend the model yourself.

What is the difference between this calculator and a full profit and loss statement?

This calculator covers the direct costs you enter — product, packaging, shipping, and platform commission — against a single discounted selling price. It does not include fixed overheads such as rent, salaries, software subscriptions, returns, or payment processing fees beyond commission. The profit figure is therefore a contribution margin: the amount each sale contributes toward fixed costs and overall business profit. For a complete picture you would need to allocate fixed costs per unit and add any additional variable costs not entered here.

For more ways to display and share calculated data, see how GSheetPress calculators work and how to present results in published tables. If you want to offer this pricing tool as part of a product or service page, the GSheetPress shop feature lets you embed calculators alongside product listings.

Publish and test your web calculator

1. First, share the spreadsheet so GSheetPress can serve the public form. In Google Sheets click Share (top right), then under General access choose Anyone with the link, set the role to Editor, and click Done. Important: this setting allows anyone who holds the spreadsheet URL to edit the file directly. Use only your dedicated sample workbook — never a file containing real business or personal data.
2. Make sure you are on the calculator tab (the sheet GSheetPress created). Click Extensions → GS2Web SaaS → Calculator → Publish Calculator.
3. Wait for the confirmation message. GSheetPress will indicate that the calculator has been published successfully.
4. Click Extensions → GS2Web SaaS → Calculator → Get Links (or the equivalent option in your add-on version). A sheet named something like Calculator Links will open or refresh.
5. Find the row whose Sheet Name matches Product Pricing Calculator. Column B contains the Edit Link (for your own use only); column C contains the public Link — open column C, not column B.
6. Open the public link in a new browser tab. You should see the web form with all seven input fields pre-filled with the defaults from column E.
7. Enter the worked-example values: product cost 25, packaging 2, shipping 5, commission 10, discount 5, target margin 20, list price to test 50. Click Calculate and confirm that the results match the table in the worked example section above — particularly that the recommended list price is 48.13 and B23 shows Yes.
8. Then change the test price to 45 and recalculate. Confirm that the margin on test sale is 15.13 % and the answer is No.
9. If you want to embed the calculator on a website, return to the Calculator Links sheet, copy the iframe code from column D, and paste it into an HTML block on your page.
10. If you publish the same calculator again in future (for example after editing labels or defaults), it keeps the same public link — you do not need to update any existing embeds.