# Spreadsheet calc (requires Excel or Excel viewer)



## Husker (Sep 15, 2008)

Updated spreadsheet: Nov 3. Added a diamond page, and a CPU/escrap page.

I think Excel viewer would work, if you do not have office, but not 100% sure.

This one is very similar to the other ones listed, but it is greatly enhanced. The enhancements are: Gold and silver. GF pricing. Auto computes percentages, and will increase the percentage you pay, as the size of the lot increases. I have this coded to pay:

60% for 0 - 2 toz (raw weight, but is simple to change to actual 24k weight)
65% for 2 - 4 toz
70% for 4 - 6 toz
75% for + 6 toz

The sheet will auto "kickup" the rate you pay, as you enter more and more items (crossing the X troy ounce levels).

Most of the "need NOT to know" data is located on the "Per" sheet. It is where the percentages are, and where the "spot" price items are. usually, the only thing you need to do on that page, is to make sure the spot is properly filled in, and possibly to sneak a QUICK peek at the 100% value, if you are needing to negotiate.

Ok, the spreadsheet has 4 main pages, 2 for gold, and 2 for silver. You should only use ONE of the gold, and ONE of the silver at a time. This is because the auto-increased % payout page looks on both the karat gram and the karat DWT pages to compute ounces being purchased. So make sure you only input the data on one of them. I usually use the gram page, unless I have chatted with the seller, and they frequent pawn shops, etc. If so, they are used to prices (at least it is so in my area) that are in DWT, and will know the price there. If so, and they see the numbers I am working with, I damn well want to make SURE they see the DWT prices, so they know how much more I am paying than the pawn dealers.


To use this sheet, go to the "per" page, and properly fill in cells C17 and C18 which are the troy spot for fine gold and fine silver. The "spot" price listed (and used for computation) on each of the 4 pages will auto update from the "per" page, so ONLY update the spot prices there. I have a laptop, and a small pocket PC that I take with me, I fill in the spot prior to going out for a purchase, so there is no reason for the "per" page to ever be seen by the seller. It contains things you might NOT want the seller to see. "hiding" the page is not a bad idea, once the spot has been entered. The forumlas will work even if the sheet is hidden.

Once you have the proper spot filled in, then simply and carefully categorize and sort the lot into karat weights, being sure to test the pieces (I test everything with electronic, and also file to find any GF material). Then weight the "like" karat material (properly with gram or DWT, depending upon the page you are using), and simply key in the number into the orange box for that karat (or GF line). Note, stones, watch mechanics, metal springs, etc, will HAVE to be accounted for (or guessed), or the numbers will be way off. Once you have ALL of the weighted items entered, then the total pay in the upper right corner should show the total value you are willing to pay (in the percentage you are paying). 

I have found this spreadsheet to be a wonderful tool, and when I have had sellers thinking that their huge (pile of crap), which there are 2 14k rings, and a 10k bracelet I want, and a BUNCH of worthless chains and such, are worth "more" than I am quoting. I simply pull out the "computer" and show them the numbers. Every time, they have agreed that the material is worth what I am quoting. I think every time, they KNOW that most of that pretty gold "colored" stuff is the crap that it is. Most in the end, do not want it, and usually will toss it all in for a few extra dollars. I take it, and have a couple flea market people I know sell it off, giving me a cut (probably so I can re-buy it again a few months later (LOL), from someone else selling their "treasures" because they need casino money.)


----------



## istari9 (Sep 16, 2008)

Very nice and small enough to carry around with me. This with some other info I have and I am set.
Thanks So Much!!

Ray


----------



## Husker (Sep 17, 2008)

The sheet I carry around with my pocket PC, I had to shrink some of the rows (and columns) down a lot, since the screen resolution is so small. I just leave the "common' ones, like 10k, 12k, 14k, 18k full sized, and shrink the others (but keeping them big enough I can still "see' what they are if I need them. Then I can simply go out with must a fanny pack, carrying my pocket pc, a small scale, and my GT-3000 tester, my loop, and a small bottle of nitric silver testing solution, and I am pretty much set to handle any jewelry purchase situation.

I tried to build the sheet to be an all-in-one, without making it unusable. It was the first larger excel coding I have done, so it was a pretty good learning experience for me also.

H.


----------



## theroots (Sep 22, 2008)

Why do you kick up the prices the more you buy. Isn't it usually the other way around?

Scott


----------



## theroots (Sep 22, 2008)

I noticed on the GCdwt page that the pay price is based on column F (grams) instead of column E (DWT). Should the formula refer to the E column since we will be using pennyweight instead of grams on this page?

Scott


----------



## theroots (Sep 22, 2008)

The spreadsheet is great by the way. It will be wonderful tool. THANKS!!!


Scott


----------



## Husker (Sep 22, 2008)

theroots said:


> I noticed on the GCdwt page that the pay price is based on column F (grams) instead of column E (DWT). Should the formula refer to the E column since we will be using pennyweight instead of grams on this page?
> 
> Scott



You are correct. I have updated, and will be reposting ASAP.


----------



## Husker (Sep 22, 2008)

theroots said:


> Why do you kick up the prices the more you buy. Isn't it usually the other way around?
> 
> Scott



I pay more, because I know I can get this to the refiner with much less chance of market change. If I only purchase 2 5g rings from someone, if I do not get anyone else selling for a while, I will have to sit on that purchase for a short while. With the volatile market, I simply do not pay as much. 

However, if someone has 4oz of karat, all decent quality, then I have no problem paying a little more, as I know I can go right from that sale to the post office, and ship it off right away, and still turn a good profit.

If you do not want the price to change at all, simply go to the "per" page, and change cell B3 to be 0.60 instead of =A4 or =A3 Those 2 cells (A3 and A4) are the change cells, but cell B3 is the cell used by all of the compute per gram or compute per DWT cells. If you change B3 to a fixed value, then it will not change. Frequently, I change B3 to .98 (what my refiner pays), to get a better idea of the value (at current spot), that the refiner will give for the load. I only do that when the customer is not over shoulder.

NOTE, only karat gold is the only thing that goes up in price for a larger lot. The filled, and sterling do not. Sterling is one thing I have had problems buying, because there are so many people who pay WAY OVER spot for the junk, that most sellers know they can get 3x or even more, than what I am offering, thus, I get very few items. Filled gold I do not pay any more than 60%, and frequently less. I love filled, there is good money in it, but you better be ready to refine it yourself, because I do not know of a reputable refiner, and have heard nothing but horror stories of people being ripped off sending fill to refiners. I simply refine (recover actually) it myself, inquart and cornflake it, and send it off to the refiner with the other karat gold, for a full value (98%) price.

Jim.


----------



## theroots (Sep 23, 2008)

Makes alot of sense now. Question for you if you'd be obliged to answer - what percentage of spot do the pawn shops in your area normally pay for gold? Thanks in advance,

Scott


----------



## Husker (Sep 23, 2008)

They are paying 40 to 50% spot, and will vary a little bit based on amount. That is for a sale price. I am not sure what value they loan on gold.

H


----------



## Husker (Oct 3, 2008)

Bump. 

Updated the spreadsheet, adding a diamond page, and a CPU/eScrap page.

NOTE both of these pages, are still a work in progress. IF you want to use them, then be DAMN sure you update them to actual values you are able to get for SELLING these items. Shoot for paying about 50% for diamonds, and 50-60% for escrap. However, these page were created using some information obtained from pages on this forum (and the diamonds from the forum's "Gold_refining_forum_handbook.pdf" (catfish's table). That table is highly out of date, but included in the spreadsheet.

Again, if people see any glaring problems (or numbers that are too high or too low), then let me know.

I think the next thing to add to this worksheet is Cat converters. I am working towards a single sheet, that I can put on my pocketpc, and have it deal with most of the noble metal scrap I come across.

Jim.


----------



## wetwillie (Nov 13, 2008)

reminds me of one I got off E-bay. This one is a little more updated though.
When you signup for GoldBay - Buy gold - Sell Gold - Gold - Coins, you can start saving money instantly. As the world's new hottest online marketplace, GoldBay - Buy gold - Sell Gold - Gold - Coins is the fastest way to open your doors to thousands buyers and sellers worldwide. Best of all, it's completely free to sign up! To sign up or learn more, click here: http://www.goldbay.com/?nrs2=3014


----------

