How to Track Your Stack in a Spreadsheet, and the Columns That Go Wrong
Once you can work out the melt value of a single coin, the next question arrives on its own: what is the whole pile worth? That is where the spreadsheet starts, usually on a Sunday afternoon, usually with three columns.
It is the right first tool. It is free, it is private, and nobody can take it away from you or change the terms on it.
We ended up building something that reads other people's stack spreadsheets, which meant reading a lot of them. The same handful of things go wrong in almost every one, and here is the part worth saying up front: none of them look like mistakes. They are all reasonable choices that quietly make a total wrong, and a wrong total is worse than no total, because you believe it.
Weight is per piece, and quantity does the multiplying
This is the big one, and it is the one that produces numbers that are off by a factor of a hundred.
If you own a hundred silver dimes, there are two ways to write that down. One row saying you hold 250 grams, or one row saying you hold 2.5 grams and a quantity of 100. Both are true sentences. Only one of them survives you editing it later.
Pick per piece, every time. Melt value is weight times purity times quantity, so as long as the weight column means one coin, the quantity column can change and the total follows. Put the combined weight in and the two columns now contradict each other, and the day you buy ten more dimes you will fix one and forget the other.
The same rule saves you on tubes, rolls and monster boxes. A tube of twenty is a quantity of 20, not a weight of 20 ounces.
Purity is not the number stamped on the coin
Stamps use three different conventions and your spreadsheet has to pick one.
You will see .9999 on a Maple, 999 on a bar, 92.5 on sterling, and 22K on an older sovereign. Those are a fraction, a millesimal, a percentage and a karat, in that order, and they do not mean the same thing until you convert them.
Store the fraction. Silver Maple is 0.9999. Sterling is 0.925. A 22 karat coin is 22 divided by 24, which is 0.9167. Junk silver dimes and quarters are 0.9. Once every row is a fraction between 0 and 1 you can multiply without thinking, and a row that reads 999 sticks out as obviously wrong instead of being off by a thousand times.
A gift is not a purchase with the price left blank
This one is subtle and it will bite you a year later.
An empty price cell can mean two completely different things. It can mean the coin was given to you, so there was never a price. Or it can mean you bought it and never wrote down what you paid. Those are opposite facts, and a spreadsheet cannot tell them apart once the memory fades.
Add a column that says how you got it. Bought, gift, inherited. It costs you one word per row and it is the difference between "my cost basis is incomplete" and "my cost basis is complete and some of it is legitimately zero."
Never put an estimate in the paid column
If you buy at auction you will see a lot of numbers attached to one lot. An estimate, a starting price, a reserve, the current bid, and finally what you actually paid. Only the last one belongs in your sheet.
It sounds obvious written down, and it is the easiest column in the whole file to get wrong, because when you are recording a lot you did not win, or recording one before the invoice arrives, the estimate is the only number in front of you. Put it in once and every figure that depends on cost is quietly wrong from then on: your premium over melt, your gain, your average price per ounce.
If you want to track estimates, give them their own column with a name that could never be confused for what you paid.
Write dates so they only mean one thing
03/04/2026 is the third of April to most of the world and the fourth of March to an American, and a spreadsheet full of both is unfixable later.
Use the year first. 2026-04-03. It sorts correctly with no special handling, it means exactly one day everywhere on earth, and it is the format every tool understands.
This matters more than it sounds, because the purchase date is what lets you separate the two halves of a gain. What the metal itself did since you bought it is one thing. What premium you paid over the metal on that day is another. Lose the date and you can only ever see the two mixed together.
Take ours if you want one
We put the whole thing in a file you can download and keep: a free precious metals inventory spreadsheet. The columns are named, four rows are filled in as examples, and it opens in Excel, Numbers, Google Sheets or anything else that reads a CSV. No account, no email address.
Keep it as your own record for as long as it suits you. If the day comes that adding up ounces by hand stops being fun, the vault reads that exact file without you renaming a single column, and prices it against the live metal price for you. That is the only pitch in this post, and the spreadsheet is yours either way.
The short version
Weight per piece, with quantity doing the multiplying. Purity as a fraction between 0 and 1. A column for how you got it, so a blank price is never ambiguous. What you actually paid, never an estimate. And dates written year first so they mean one day only.
Five columns, done properly once. Everything else you might want to know is math on top of them.