I don't use a spreadsheet for my tracking, but I do use my Microsoft Money program that came with my computer. I created a " coin investment" account and set up categories for Copper, Nickel, and Silver that I manually update as I purchase, sort, acquire each investment type. The purchase price is always face value, (unless I buy Silver off of family/friends under melt) and I then update the existing price of each "investment" manually by checking the current price of copper pennies and nickels (entered as the current melt value) on coinflation.com.
Silver I do slightly different since each coin has different amounts based on face value. I enter the "price" as 0.888668 for 35% silver nickels, 1.38238 for 90% silver coin and 3.380823 for 40% silver coin (these are for coins found in circulation). Then I enter the face value and the software calculates the quantity for you which is the appx value of silver ounces for each entry. For any coins I buy, I figure out the silver ounces using the slv calc. and enter what I paid to get my "cost". I then again access coinfaltion and enter the current spot price of silver. It sums up what the cost, gain, ROI, and anything else you want to know from an investment stand point. Its a little bit of extra work and upkeep, but I enjoy the real time valuation it gives me on my growing hoard.
My math may be slightly off on some of these, but I used the silver melt calculator on the coinflation site for each single coin and figured it would apply across the board.
Hope this helps...
Posted: Wed Jun 15, 2011 4:09 pm