Hi everyone, I'm trying to come up with a spreadsheet to track my bullion that I've stacked. I'd like to list my cost basis and be able to average it across all purchases and also input the current spot price to update the current value. Does anyone have a spreadsheet template you use and like that you'd be willing to share? Thanks!
Let's see if we can at least come up with column headings. First, going down the page, you'd have sections for dimes, quarters, halves, dollars, rounds, bars, foreign, intact proof sets, and misc., such as 40% Kennedy and war nickels. You would need to add rows as you acquire mixed lots of various denominations; that's easy to do. For columns, reading across "A" to perhaps "M," etc., you could start a draft with: A - date B - spot price that day C - quantity D - BxC (spot for what you bought) E - price you actually paid F - payment type (cash, check, credit card, trade) G - ? That's as far as I could draft without knowing more about what you want to accomplish, or what you want to know. Is this a start, or are we on the wrong track? There's no column for face value because you have foreign and sets mixed in, but with what we have already, you can total your entire purchases and know the spot (say, 79.82 Troy ounces) and the total you paid from Day One. There is an implicit assumption in this format that you will buy "all" kinds of silver if the price is right, How experienced are you with Excel? Why don't you enter the row and column headings as per above and save that spreadsheet as a draft, i.e., not enter any transactions yet? You must have headings first, or you cause yourself a ton of work when you change your mind. Other CT-er's can come up with more columns (or rows). Don't let the fear of your spreadsheet getting too complicated slow you down. You can delete in a flash, but adding a new and essential category is a nightmare.
Yeah, I've done a few things. 1st is to value each of the coins / bars / rounds by % of silver in them (90%, .999, etc) and then you can keep quantities. Add a cell for spot price and then make the calculations across each line for "per coin value" and another column to carry the accumulated totals. example: ASE / .999 / (spot * percentage calculation) / (quantity) / (spot value * quantity) Morgan / .90 / (spot * percentage calculation) / (quantity) / (spot value * quantity) I do the same for gold jewelry. grams / ozt * percentage purity (karat) = value Also a good way to track your entire collection. Don't know how many trips to SDB I've saved. Then again it's nice to go visiting.
"I'd like to list my cost basis and be able to average it across all purchases..." I thought of one small flaw for this feature. When you sell an item, presumably you will delete it from Excel. But if you do, that will change the "average cost" for all the remaining items, but you didn't really change that average cost. There's ways around this issue, I'm thinking it over.
i find that running two sheets, one for junk silver with purity and weight calculations and the other for my bullion to work best for me. I recently started a third sheet similar to my bullion sheet but for my collectable silver bullion so i dont throw price average off with my investment silver.
Well...for whatever reason I can't upload the spreadsheet (I get some error message about an incorrect file extension? It is an Excel spreadsheet, so I don't know how to fix that). I'm happy to email it to anyone who wants it - just PM me your email address. Or, if someone knows a better way to post it here let me know.
Unless your running a large corporation, there is no need to dive that deep in to the Details, keep it simple and you will just be fine