Price handmade jewellery in a sheet that adds metal cost (finished weight plus casting loss, times price per gram), stones and findings, your labour at a real hourly rate, a share of overhead, and then divides by one minus your fees and target profit. AI can build the sheet and check the formulas, but the figures must be yours.
The most common failure is not arithmetic. It is leaving labour or overhead out, or applying a "double the cost" markup to a cost that was already too low. Retail prices that look fine often fall apart the moment someone asks for a wholesale price at half retail, which the worked example below shows with numbers.
The cost build in six lines
Every piece gets the same six lines, in this order. Each one is a column in the sheet.
- Metal. Finished weight in grams, plus the loss you allow for casting, filing and polishing, times the price per gram of that alloy.
- Stones. What you paid, including cutting or setting fees you cannot do yourself, plus an allowance for breakage if it happens often.
- Findings, chain and packaging. Clasps, posts, chain bought in, box, pouch, card.
- Labour. Minutes at the bench times an hourly rate that pays you properly.
- Overhead. Monthly overhead divided by productive hours, times the minutes on the piece.
- Selling costs and profit. Marketplace and payment fees as a percentage of the price, and the profit you want, as a percentage of the price.
Notice that line 6 is a share of the selling price, not of the cost. That is why the final step divides, rather than multiplies. The next sections work through why.
Metal: weight, fineness and loss
Metal price is quoted per gram or per troy ounce for the pure metal, while you buy alloys. Sterling silver is 92.5% silver (hallmarked 925, in parts per thousand). 14 carat gold is hallmarked 585, so 58.5% gold (14/24ths would be 58.3%), and 18 carat is 750, so 75%. For a rough cost per gram of an alloy, multiply the price of pure metal by the fineness, then add whatever premium your supplier charges for alloying and fabrication. The supplier's invoice price per gram is the better figure when you have it.
Then the weight. Use the finished weight of the piece, which your scale gives you after polishing, and add loss. When you cast, the metal that fills the sprues and buttons is mostly recovered if you keep and recycle scrap, but you pay for the process and for what escapes in filing and polishing. A percentage allowance is the usual way to handle it. I use 10% in the examples; use the figure your own scrap records show, not mine. Weigh your offcuts for a month and divide scrap lost by metal bought, and you have a number.
The calculation: metal cost = finished weight × (1 + loss %) × price per gram of alloy.
The Complete Excel Course with AI Integration
From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.
By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner
Buy nowA worked example: a silver pendant with a cabochon stone
All figures are illustrative. Say a sterling silver pendant has a finished weight of 6.4 g. Sterling is costing you $1.10 a gram from your supplier, and you allow 10% loss. The stone is a 6 mm cabochon that cost $14. The chain is bought in at $6.00. Box and pouch cost $2.50. The piece takes 95 minutes of bench time including setting, finishing and polishing. You pay yourself $28 an hour. Overhead (a share of rent, insurance, tools, software and consumables) is $900 a month across 90 productive hours, which is $10 an hour.
| Line | Working | Amount |
|---|---|---|
| Metal | 6.4 g × 1.10 × $1.10 | $7.74 |
| Stone | Paid | $14.00 |
| Chain | Bought in | $6.00 |
| Packaging | Box and pouch | $2.50 |
| Materials | $30.24 | |
| Labour | 95 ÷ 60 × $28 | $44.33 |
| Overhead | 95 ÷ 60 × $10 | $15.83 |
| Total cost | $90.40 |
Now the price. Suppose you sell through a marketplace or your own shop where combined selling and payment fees are about 12% of the sale, and you want 20% of the price left as profit after paying yourself. Then costs plus fees plus profit equal 100% of the price, so the cost of $90.40 must be the other 68%.
Retail price = cost ÷ (1 − fees − profit) = $90.40 ÷ 0.68 = $132.94, so $135 once rounded to a price point.
Check it: $135 less 12% fees ($16.20) leaves $118.80, less cost of $90.40 leaves $28.40 profit, which is 21% of the price. That is what the sheet should show.
Why "cost times two" would have got this wrong
Doubling cost gives $180.80, which looks safe but is unrelated to your fees and profit target. It would be too high for some pieces and too low for others, because it ignores how much of the cost is labour versus cheap findings. Divide by (1 − fees − profit) instead and every piece lands where you meant it to.
Wholesale: the check that exposes weak pricing
Shops usually expect to buy at about half your retail price. Look at what that does to the pendant: half of $135 is $67.50, which is less than your cost of $90.40. You would lose $22.90 on every sale. Selling wholesale at this price is not pricing, it is a gift.
Work backwards. Wholesale has no marketplace fee, so use a lower profit margin, say 15%: wholesale = $90.40 ÷ 0.85 = $106.35. If retail must be at least twice wholesale, retail becomes $212.70. Your options are then clear:
- Raise retail to about $213 on pieces you will sell wholesale. Test whether your market accepts it.
- Skip wholesale on this piece and sell it direct only.
- Cut cost: switch to a simpler setting that takes 60 minutes, or use a cheaper stone, and recalculate.
- Keep a separate wholesale range designed from the start around 40 to 50 minutes of labour.
The point of the calculator is that you see this before you promise a shop a price, not after.
Building the sheet: layout and formulas
Use two sheets. The first is "Rates", the only place you type prices and rates. The second is "Pieces", one row per product, all formulas.
| Rates cell | Holds | Example value |
|---|---|---|
| B2 | Sterling silver, $ per gram | 1.10 |
| B3 | 14k gold, $ per gram of alloy | (enter from your supplier) |
| B4 | Loss allowance | 10% |
| B5 | Labour rate, $ per hour | 28 |
| B6 | Overhead, $ per hour | =900/90 |
| B7 | Fees, share of price | 12% |
| B8 | Retail profit target | 20% |
| B9 | Wholesale profit target | 15% |
| B10 | Retail-to-wholesale multiple shops expect | 2 |
On "Pieces", with columns A name, B metal, C finished grams, D stones, E findings and packaging, F minutes, and N the retail price you actually charge (typed in, not calculated), put these formulas in row 2 and copy them down:
G2 Metal cost =C2*(1+Rates!$B$4)*IF(B2="silver",Rates!$B$2,Rates!$B$3)
H2 Labour =F2/60*Rates!$B$5
I2 Overhead =F2/60*Rates!$B$6
J2 Total cost =G2+D2+E2+H2+I2
K2 Retail floor =J2/(1-Rates!$B$7-Rates!$B$8)
L2 Wholesale =J2/(1-Rates!$B$9)
M2 Retail needed if sold wholesale =L2*Rates!$B$10
O2 Check =IF(N2<M2,"Too low for wholesale","OK")K is the lowest direct price that still pays you; N is what you decide to charge after looking at K and at the market. Column O is the check: if your price in N is below M, a shop buying at half your retail would push you under your wholesale floor. For the pendant, K is $132.94, M is $212.70 and N is $135, so O reads "Too low for wholesale". Add conditional formatting that turns O red when it says so. If you want to round prices, wrap K2 in MROUND(K2,5) or CEILING(K2,5); both functions exist in Excel and Google Sheets.
Asking AI to build it, and what to fix
You can describe this layout to a chat assistant and ask for the formulas, which is faster than writing them. Use a prompt like this.
Build a jewellery pricing sheet for Google Sheets or Excel.
Sheet "Rates": silver price per gram, gold price per gram, loss %, labour rate
per hour, overhead per hour, fees % of price, retail profit % of price,
wholesale profit % of price. Sheet "Pieces": one row per product with name,
metal, finished grams, stone cost, findings and packaging cost, minutes.
Formulas must read from Rates, never hard-code a price. Retail = cost /
(1 - fees - retail profit). Wholesale = cost / (1 - wholesale profit).
Add a column where I type my actual retail price, a column for the
retail price needed if I sell wholesale (wholesale x the multiple on
Rates), and flag rows where my price is below that.
Give me the formulas as text, one per column, and explain each in one line.Typical faults in what comes back, and how to fix each:
- Markup instead of margin. The assistant writes
=J2*(1+0.2)for retail. That gives a 20% markup, which is a 16.7% margin, and ignores fees. Replace it with the divide form above. - Hard-coded prices. A silver price typed inside a formula means you must edit every row when it changes. Point every row at the Rates sheet.
- A ratio that never changes. Asked for a "retail to wholesale ratio", an assistant often divides its own retail formula by its own wholesale formula. Both are the same cost divided by a fixed number, so every row shows the same answer (0.85 ÷ 0.68 = 1.25 with these rates) and the flag either fires on every piece or never. The check only works against the price you actually charge, as in column O above.
- Loss applied twice. Some versions apply the loss to finished weight and again in a "waste" column. Keep one.
- A market price quoted from memory. If the assistant offers a metal price, ignore it. Enter your supplier's price.
Check two rows by hand with a calculator, as I did for the pendant above. If the sheet says $132.94 for that piece, it is right.
A gold ring and the metal-price problem
Gold changes the sum because metal becomes the largest line and the price moves. Say a plain 14 carat band has a finished weight of 4.2 g, and you allow 8% loss. For the example only, take fine gold at $100 a gram, a round number and not a quote. 14 carat is 58.5% gold, so the alloy is worth about $58.50 a gram before any supplier premium.
Metal cost: 4.2 × 1.08 × $58.50 = $265.36. If the price of gold moves 10%, the cost moves by about $26.50. On a ring with 3 hours at the bench (about $114 of labour and overhead at the rates above), a 10% swing in gold is around 7% of the total cost, so it matters, though a price that is a few weeks old will rarely be disastrous.
Three ways to handle it:
- Re-price your gold range on a fixed day each week or month from the Rates sheet.
- For made-to-order work, quote with a validity date and ask for a deposit that covers the metal, so you can buy it when the order is confirmed.
- Add a clear line on the quote: "metal priced on the day of order" so the customer understands why it moves.
Three more pieces, three different problems
Silver stud earrings. Finished weight 1.6 g, loss 10%, silver at $1.10, posts and backs $0.80, packaging $2.50, 25 minutes at the bench. Metal $1.94, materials $5.24, labour $11.67, overhead $4.17, total cost $21.08. Retail is $21.08 ÷ 0.68 = $31.00. Wholesale is $21.08 ÷ 0.85 = $24.80, and retail would need to be $49.60 to keep the two-times rule. Here the sheet tells you that small, quick pieces suit retail and craft fairs, while wholesale needs a bigger batch process to cut minutes.
A resize on a customer's ring. No metal to buy beyond solder and consumables, say $3. Forty minutes of labour is $18.67, overhead $6.67, total $28.34. With a 20% profit target and no marketplace fee, price = $28.34 ÷ 0.80 = $35.43. Set a minimum repair charge of $40, because a shorter job still costs you the intake, the note and the packing. Repairs are often profitable, but only if you stop quoting them from memory.
A commission using the customer's own stone. Stone cost is zero, but you still carry risk if the stone chips while you set it. Charge labour and overhead, add a line for the metal and say in writing that the stone is set at the customer's risk, with an agreed allowance. The handmade pricing calculator tutorial covers custom work for other crafts with the same logic.
Stones: pricing the parts that are not metal
Stones behave differently from metal. Their price per piece is fixed by what you pay, so the risk is in what you do not count. Three habits help.
First, add a breakage and rejection allowance. If one stone in twenty chips in setting or arrives with an inclusion you cannot use, that is a 5% extra on stone cost, which for a $14 stone is $0.70. Small per piece, large over a year. Second, include any fee you pay for cutting, calibrating or setting that you do not do yourself. Third, make sure the stone line matches what you tell customers: a natural and a lab-grown stone of similar size can differ widely in cost, and the description must match the stone you priced. The lab-grown terminology checklist covers the wording.
Do you mark up stones separately? Some makers add a handling markup of 20% to 30% on stones because sourcing, matching and storing them takes time. If you do, make it a visible column so you can see it, rather than burying it in the profit line. If you already pay yourself for sourcing time in overhead, skip it, otherwise you charge twice.
Getting honest bench minutes
The labour figure is the weakest number in most sheets, because makers guess. For two weeks, jot start and stop times for every piece in a notes app, then let AI tidy the log. A messy log such as "pendant 10.05-10.50 saw, 11.00-11.40 solder + file, after lunch 1.15-2.00 set stone, polish 20 min" can be pasted into a chat tool with the instruction: "Total the minutes per stage and per piece, list any gaps over 15 minutes as breaks, and give me a table." Illustrative result: sawing 45, soldering and filing 40, setting 45, polishing 20, total 150 minutes, with one gap flagged as lunch.
Two things usually show up. Your guess was lower than the log, often by a quarter or more, and the stages you dislike take longer than the ones you like. Put the logged minutes in column F. Re-time one piece per product family each quarter, because you get quicker and your price should reflect real time, with a short note so you do not quietly under-pay yourself as the work gets slower.
Where the sheet needs a human judgement
The calculator finds the lowest price that still pays you. It does not tell you what the market will pay. Compare each result with what similar pieces sell for: if your computed price is far above comparable work, ask whether the labour time is realistic and whether the piece offers something extra such as a signature setting or recycled metal. If your price is far below, you may be underselling. A calculator gives a floor, and the final price sits above that floor by whatever your brand and design can support.
Two further costs people forget: hallmarking or assay fees where your market requires them, which are a per-piece cost to add as a column, and photography and listing time, which belongs in overhead. The hallmarking tutorial explains what those rules mean for product pages, and your assay office or equivalent will give you the fee schedule.
A quick sanity check on the whole range
Once every piece is in the sheet, sort by total cost and look at the spread of labour as a share of cost. A pendant at 49% labour and a pair of studs at 55% are plausible for handmade work. If one piece shows labour at 15% of cost and sells well, check its minutes, because it is probably the one you have under-timed. Then filter column O for "Too low for wholesale" and note which pieces cannot support a trade price. That list tells you what to design next and what to stop making for shops.
Checking the sheet against reality
After the first month, test the sheet against your sales. List the last ten sales and compare the price you charged with the sheet's retail price. For each one ask whether you charged less, and if so why: a discount, a fair, a friend. Then compare total profit against your bank account. If the sheet says you made $2,400 profit on top of your wages and the account shows far less, a cost is missing. The usual culprits are stone breakage, returns, postage you absorbed, unpaid time on custom enquiries and tools you replaced.
Update the rates on a calendar date. A sheet with last year's silver price is worse than none, because it looks right. Put a "last updated" date next to the metal prices and make it turn red after 30 days if you like.
When the pricing is settled, the next jobs are the listing copy and the photos. The tutorial on what AI gets wrong about metals lists the mistakes to avoid in descriptions, such as calling plated pieces solid or mixing up carat and fineness. Describe the metal as precisely as you priced it.
Questions about pricing handmade jewellery
How do I price jewellery when metal prices keep moving?
Keep each metal price in one cell on a rates sheet and let every piece read from it. Update the cell on a set day, for example weekly. For made-to-order gold, quote the metal separately or add a validity date to the quote, so a sudden move does not eat your margin.
What is the difference between markup and margin?
Markup is profit as a share of cost; margin is profit as a share of the selling price. A piece costing $50 sold at $100 has a 100% markup but a 50% margin. Mixing them up is the commonest pricing mistake, because a 50% markup gives only a 33% margin.
Should I pay myself in the labour line?
Yes. Put your own time in at a realistic hourly rate, then add profit on top. If labour is missing, the profit line is actually your wages, and the business looks healthier than it is. Pay yourself in the sheet even if you take it as drawings.
Can ChatGPT work out my prices directly?
It can build and explain the sheet and check formulas, but do not rely on it for today's metal price or for arithmetic you cannot see. Let the spreadsheet calculate, and check two or three rows by hand. Treat anything it says about current market prices as unverified.
How do I handle a customer's own stone or gold?
Remove the material from the cost lines and charge labour, overhead and any consumables, plus an agreed loss allowance for the metal you work. Record who supplied what on the job sheet so there is no dispute later about weights or what was returned.
Further reads
- Bakery Pricing Calculator: Build One With ChatGPT and a Spreadsheet — Same cost-build logic in a different trade, with recipe costing.
- AI for Jewellery Design: Concept Sketches to Client-Approved Renders — Quote and price a commission from an approved concept.
- Can You Put Jewellery on an AI Model? Disclosure Rules for Sellers — Disclosure rules if you show pieces on AI models.
- Jewellery Photography With AI: Clean Backgrounds, True to Life — Photography time belongs in your overhead line.
- AI Tools for a Jewellery Business, Compared by the Job They Do — See which other jobs in a jewellery business AI helps with.
- ChatGPT Prompts for Jewellery Shops: 8 for Listings and Clients — Prompts for listings once the price is set.
- AI Jewellery Model Generator: Keep Rings and Chains True to Scale — A step-by-step scale test for AI jewellery models, using a known ring size and chain length so you catch oversized rings and floating chains.
- Florist Pricing Formula for Bouquets and Arrangements, Built With AI — One bouquet priced line by line: stems, waste allowance, labour, overhead and profit, plus the prompt that gets AI to build the formula sheet from your costs.
- AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.
Sources: an assay office's published hallmarking guide (silver 925; gold 585 and 750 parts per thousand), Google Sheets and Microsoft Excel function references for MROUND and CEILING (checked 5 November 2026); all prices, rates and costs in the examples are illustrative and not quotes.