Tracking Your Junk Silver & Bullion Purchases Using Excel (v 1.0)

This is a post for anyone that is currently buying Silver or plans to start investing in Silver.  

In particular those interested in Silver Bullion (e.g. American Silver Eagles, generic bars & rounds, etc.) and U.S. Junk Silver coins (e.g. 90%, 40% or 35%).  It's always a great feeling when you find a great deal on a some silver.  Whether your buying .999 fine bars/rounds or something like 90% U.S. Junk silver, a great deal is a great deal.  

However if you're not careful with detailing your purchases, you may not know as much about your collection as you should.  

Here are just a couple examples of issues, not having a good record of your purchases can lead to:

- Wasting time doing repeat inventory of your collection

- Selling for a loss (because you aren't aware of your initial purchase price, not due to the market)

- How much have you spent in a given time period or in general

For anyone that is buying (and selling) silver, I really feel that using software like Microsoft Excel is essential.  And you don't have to be a pro to use this software.

I have created a spreadsheet that aims at helping people track their Bullion and U.S. Junk Silver coin purchases.  

Below is a YouTube Tutorial for the spreadsheet I have created

What can you expect to get from using this Tracker?

At the very least I hope that this tracker will provide those who use it with the following:

- A simple way to keep track of their Silver Bullion & Silver U.S. Junk Silver purchases
- Keep a running total on the total Cost per ounce of silver in their entire collection
- Know how much the collection has appreciated or depreciated
- Have a running breakdown of each purity in the collection (by total oz. and % of collection)

Screenshots:

Start by selecting the product from the drop down list

The enter the quantity of that coin.  So how many of that product you bought.  Careful not to confuse the Quantity cell with the face value.  Once you have entered a product and a quantity, the spreadsheet will automatically tell you the Total Troy Ounces within that lot.  Please note this total is based on ideal measurements (newly minted bullion or silver coins), and does not factor wear on the products that could offset the actual silver content.

Finally enter the Unit Price, which is what you actually paid for the product.  You also have the option to include any additional costs in the Other Costs cell.  This can be a great place for expenses like premiums or travel expenses.

Now over time as you continue to make additional purchases, just update the tracker and it will keep a record for you to refer to.

BUT WAIT IT GETS BETTER!!!!!

There is also another worksheet in this tracker that will give you a summary of your purchases.  I'm calling it the SnapShot page.

You do need to Manually enter the Current price of Silver in cell B2.

But after that the SnapShot page provides you with:
- Total T oz. of Silver in the collection
- Your cost per ounce of Silver (this will be skewed if you include anything with a high $$ premium)
- Cost of Investment (how much you've spent on the collection)
- Net Profit (Loss)
- Return On Investment

As well as the breakdown by purity:
- For .999 Fine bullion you can see Total T oz.  & % of Collection
- For Junk U.S. Silver Coins you see Total T oz., Total Face, & % of Collection

Here is the link to the Excel Template: 
Silver U.S. Junk Coin & Bullion Buy Spreadsheet

Some important limitations of this tracker:

- It is not designed to track sales
- It will not track Non-U.S. Silver coins
- It is only set-up to track U.S. Silver coins and common bullion amounts
- Does not include a section for grades of coins

This is my first attempt at creating an Excel Spreadsheet to track Silver Bullion & Junk U.S. Silver Coin collections and purchases.  If you try it out, please comment on the YouTube video, or here on this post.  I would love to learn if this is useful, or if you have a better system (digital or analog) for tracking your silver stacking.

Happy Hunting Everyone!!!
~JobIII

Stay updated on all the latest and greatest:
Subscribe to our YouTube Channel
Follow us on Blogger