The Bullion Spreadsheet: What Belongs In It, and the Three Things It Cannot Do

Almost every stacker ends up with a spreadsheet. It usually starts as three columns after the second or third purchase, and it usually grows into something that proves the stack exists without quite answering what it is worth, at least not without an afternoon of work.

Writing it down is the whole difference between a stack and a pile. So this is the honest version of the sheet, the columns that actually earn their place, and then the three things no spreadsheet can do however well you build it.

The columns that earn their place

Anything that cannot be derived from something else belongs in the sheet. Anything that can be derived should be a formula, not a typed number, because a typed number goes stale the moment it is typed.

• Date bought. The single most valuable column and the one most people leave out. Almost every interesting question later is a question about that day.
• What it is. Plain description, the way you would say it out loud. "1 oz Silver Maple Leaf 2021", not "SML21".
• Metal. Its own column, even though the description implies it. You will want to total by metal, and a formula cannot read your description.
• Gross weight and unit, in two columns. Never one. See the mistakes below.
• Purity as a decimal. 0.9999, not 99.99 and not "four nines".
• How many pieces. A tube of twenty five is one row with a quantity of twenty five, not one row that quietly means twenty five.
• All-in price and currency. What actually left your account, premium and fees and shipping and tax included. At auction that means the hammer plus the buyer's premium, not the hammer.
• Where you bought it. Worth more than it looks. It is the only way to ever answer which shop actually charges you the least.
• Serial or certificate number. For bars and graded coins. This is what turns "a 10 oz bar" into a specific bar for an insurer or a buyer.
• Notes. Anything that will not fit above, including condition and what came in the box.

Two more once something leaves: date sold and what you got. Which brings its own problem, further down.

The one formula everything hangs off

Pure metal content is the number every other number is built on, and it is two steps.

Grams to troy ounces is a division by 31.1035. So a 100 gram bar is 100 divided by 31.1035, which is 3.2151 troy ounces.

Then pure ounces is that weight times the purity times the quantity. A tube of twenty five one ounce Maples at 0.9999 is 25 times 1 times 0.9999, so 24.9975 pure ounces. A 100 gram bar at 0.999 is 3.2151 times 0.999, so 3.2119 pure ounces.

As a column it computes itself and never needs typing again. If you want to see it worked through on real pieces, the melt value walkthrough does exactly that.

Five things that quietly break a sheet

None of these announce themselves. The sheet keeps adding up, and the total is wrong.

One weight column holding both grams and ounces. A 100 in a column that mostly holds 1s is a hundred gram bar sitting in your sheet as a hundred ounces. Two columns keeps them apart, with the unit picked from a dropdown.

Purity written two ways. One row says 0.999, the next says 999, a third says 92.5 for sterling. Every total that touches purity is wrong from there on. Decimals throughout keeps it consistent.

Junk silver entered by its weight. A pre 1968 Canadian dime is not one tenth of an ounce of silver, and its actual content depends on the year and the country. Junk silver is calculated from face value, not from what the coins weigh.

An exchange rate typed in once. Somebody buys in US dollars, pastes today's rate into a cell, and that rate is still sitting there three years later converting every purchase since. The rate belonged to the day it was true.

A tube counted as one. Twenty five coins entered as quantity one is a stack that reports itself as four percent of its real size, and nothing in the sheet flags it.

The three things a spreadsheet cannot do

Build all of that perfectly and three questions stay out of reach.

What is it worth right now. Every answer means looking up four metal prices, converting anything in another currency, and multiplying it all out by hand. In practice that gets done a few times a year, so the number in the sheet is usually the one from the last time it was worked out.

What was the metal worth on the day you bought it. This is the important one and almost nobody has it. It is the number that separates the premium you paid from what the metal has done since. Without it, a coin bought last month at a fair price over a rising spot looks identical to a coin bought at a terrible premium, because both just show "paid more than melt". Getting it into a spreadsheet means keeping your own record of the daily price of four metals going back years, and nobody does that.

Keep what you sold. The natural move when something goes is to delete the row, and with it goes what you paid, when you bought it and what it taught you. The alternative, a second sheet, is a second sheet nobody maintains.

There is a fourth, smaller one that arrives when you finally call your insurer: they want a dated schedule, valued, with serial numbers, and a spreadsheet of purchase prices is not that. What insurance actually asks for is worth reading before that phone call rather than after it.

When the sheet stops being enough

For ten or fifteen holdings it never really does. Keep the sheet, keep it clean, and it will serve you well.

The turn comes somewhere around the point where you stop being able to answer "what is this worth" from memory, or the first time you go to sell something and cannot remember what you paid, or the first time somebody asks you for a list and you realise the list is a year out of date.

If you get there, you do not have to retype anything. The vault here reads the spreadsheet you already keep, whatever you called the columns, shows you what it matched before it saves a thing, and lets you correct any column it guessed wrong from a dropdown. It flags rows that look like ones you already have, so importing the same file twice does not double your stack. The free plan holds fifteen holdings and needs no card, which is enough to see whether it beats the file you already have.

The short version

Keep the date, the description, the metal, the weight and its unit as two columns, the purity as a decimal, the quantity, the all-in price with its currency, where you bought it, any serial number, and a notes column. Make pure ounces a formula: grams divided by 31.1035, then times purity, times quantity. Never mix units in one column, never write purity two ways, never enter junk silver by weight, never hard code an exchange rate, and never let a tube count as one. Then accept that the sheet cannot price itself, cannot tell you what the metal was worth the day you bought, and cannot keep what you sold, and decide whether those three matter to you yet.