Average Cost per ouce

Court Jester

Court Jester

Well-Known Member
Silver Stacker
So guys if you are like me you probably have a spread sheet where you put all your silver purchases that works out an average cost per ounce.

Now I am curious as to how you guys sort it.

Do you include everything, numi, bullion, semi numi in this or do you keep multiple spreadsheets for each class?

i.e. PM proofs @ $100 per coin will significantly push up the Average cost per ounce if included with a bunch of bullion coins.


ATM I have everything grouped together in one list so it makes my average cost per ounce look artificially high. I even include my gold in my silver chart based on the current GSR to give it a value in "silver" ounces. Is there a better way to keep track of this?
 
Court Jester said:
Do you include everything, numi, bullion, semi numi in this or do you keep multiple spreadsheets for each class?
Click to expand...

I only keep bullion in my current spreadsheet (bars and coins). Any numi stuff I've ever had is that minimal that a even a large change in spot wouldn't affect the coin that much (because of the premium slapped on top). So I really only care about the bullions current price.

Court Jester said:
ATM I have everything grouped together in one list so it makes my average cost per ounce look artificially high. I even include my gold in my silver chart based on the current GSR to give it a sive in "silver" ounces. Is there a better way to keep track of this?
Click to expand...

I'm working on an app which does the spreadsheet thing for you. There's a whole bunch of spreadsheets which separate Gold and Silver into different sheets. A great example of this is yousilverlog which stems from this thread.

There's also many free spreadsheets floating around which do pretty much the same thing as well.
 
I strongly recommend that you sell all numis and proofs and stick to bullion silver. Bullion silver includes Perth Mint. However, the moment these increase in price, sell them for cheaper bullion.

"When the SHTF, a man will be known by the quantity of his stack, not the quality!"
 
Sammysilver wrote:
...you sell all numis and proofs...
Click to expand...

If one is clever good short term gains can be made with "numis" (numismatics). For short term I mean just that.
For example the RA Mint released a new $2 coin for distribution by the Security Companies on 21st June 2013.

It was the first coloured coin released for general circulation and only 2 million were minted.
[imgz=http://forums.silverstackers.com/uploads/5103_coronation2.jpg]
5103_coronation2.jpg
[/imgz]
I have seen less than half a dozen in my change!

These coins sell on eBay for an average price around $10.00.
That's a 500% increase on issue value.

Over then past few weeks this coin has sold for over $70.
A lot of 50 coins sold for $650! = $13 a coin!

So if you are clever, don't rule out numis!
 
I include it all on the one spreadsheet.
No point lying to myself that my dollar cost average is lower than it really is, just because it's numi,

At the end of the day, I still paid $x for 1oz of silver
 
Court Jester said:
So guys if you are like me you probably have a spread sheet where you put all your silver purchases that works out an average cost per ounce.
Click to expand...


Just make sure you use PGP or Truecrypt or something similar to keep your spreadsheet ENCRYPTED - in case your computer is lost, stolen or hacked.
 
I would add the following columns to your spreadsheet

Size: (1oz/10oz/1kg etc)
Finish: (proof/mint/cast)
Type: (bar/coin/round)

Then rather than using an =sum() function in your summary use a sumifs(); same for =average / =averageifs() and =count() / countifs()
Then you can really breakdown what you pay for each type of item in your summary e.g.

Edit: =Sumifs() only got added to excel 2007 and later versions

Summary:

Column A Column B Column C Column D
1kg Coin Mint =sumifs({total cost range},{size range},"1kg",{Type range},"Coin",{Finish range}, "mint") /
sumifs({total count range},{size range},"1kg",{Type range},"Coin",{Finish range}, "mint")
1kg Bar Cast
1oz Coin Proof
1oz Coin Mint
1oz Bar Mint

For calculating average cost per ounce or premiums per ounce a (possibly) better option is to use =sumproduct()

If you have various quantities of coins/bars on each row, you need to accurately account for this by using the formula:
=SUMPRODUCT({cost per ounce range},{quantity range},({size range}="1oz")*1,({finish range}="Proof")*1,({Type range}="Coin")*1) /
SUMPRODUCT({quantity range},({size range}="1oz")*1,({finish range}="Proof")*1,({Type range}="Coin")*1)

In case your wondering what the *1 is for, it turns an array in Excel from True/false into 1's and 0's, this trick means anything that doesn't fits the criteria gets multiplied by 0 and ignored in the final calculation

Of course you can customise much more than this, for example, for a semi-numi you might be interested in (roughly) predicting total
"Market value" and "liquidation value" where you might expect the "market value" to be
(current spot price + original dollar premium paid) or (current spot price + original % premium paid)
while liquidation value (i.e. quick sale/slow moving pieces) may be (current spot price + 1/2 * original dollar premium paid) etc

If this isn't clear PM me and I can send examples
 
Numi should be counted in your dca Imo. If you are so worried about having a low dca, why bother with numi in the first place? :)
 
Good advice Trew. Was the only thing I was concerned about when I handed my laptop in for repair.

Don't see why some lump semi and semi-numi in with bullion, 2 very different products. Only bullion trades relative to spot, hence why some use numi's as a 'hedge'. I have both on separate spreadsheets and thus have separate cost averages to refer to.
 
sammysilver said:
I strongly recommend that you sell all numis and proofs and stick to bullion silver. Bullion silver includes Perth Mint. However, the moment these increase in price, sell them for cheaper bullion.

"When the SHTF, a man will be known by the quantity of his stack, not the quality!"
Click to expand...

When the SHTF maybe, but that could be a long way away, in the meantime there is money to be made in numis.

I keep my numis in a seperate spreadsheet, different asset than bullion.

I do however have a formula which adds the two spreadsheets together to give you a dollar cost. Not that I use it for anything. I also stopped updating it a long time ago, I no longer have the need to know how many 1963 shillings I have (It was quite a detailed spreadsheet)
 
House said:
Good advice Trew. Was the only thing I was concerned about when I handed my laptop in for repair.

Don't see why some lump semi and semi-numi in with bullion, 2 very different products. Only bullion trades relative to spot, hence why some use numi's as a 'hedge'. I have both on separate spreadsheets and thus have separate cost averages to refer to.
Click to expand...


yes it is partly a hedge

I like to gamble on some numi's and they generally do generally gold their value when spot sinks.
 
Jislizard said:
sammysilver said:
I strongly recommend that you sell all numis and proofs and stick to bullion silver. Bullion silver includes Perth Mint. However, the moment these increase in price, sell them for cheaper bullion.

"When the SHTF, a man will be known by the quantity of his stack, not the quality!"
Click to expand...

When the SHTF maybe, but that could be a long way away, in the meantime there is money to be made in numis.

I keep my numis in a seperate spreadsheet, different asset than bullion.

I do however have a formula which adds the two spreadsheets together to give you a dollar cost. Not that I use it for anything. I also stopped updating it a long time ago, I no longer have the need to know how many 1963 shillings I have (It was quite a detailed spreadsheet)
Click to expand...

Money to be made? Maybe.. But far more money to be lost... How many times do I see people selling there many pm proof purchases for under half the price they paid.. Ie.. $50 or so for a $109 coin...

If you haven't worked it out already... Take the very wise advice of Sammy Silver... When you buy silver bullion... You'll find they go up over spot anyway like all my bullion has... You just don't get ripped initially which is the regret you have whe you pay over 3 times spot for something you'll probably decide will be the first thing to sell when you run out of space..

I wouldn't include numismatic coins in the bullion spreadsheet... But numismatic means circulated coins of historical worth to me and I only pay premiums for actual antique numis silver.. Not the modern junk newbs always regret buying

The turd in the silver punchbowl.. Peter schiff begs people not to buy them.. I'm not going to beg but buy more silver... And help to spike the price.. Don't line the pm's pockets with junky coloured coin crap that your only going to grow to hate and sell for pennies on the dollar in the future

1for1
 
I also made up a spreadsheet. I want to keep on top of it as I build my stack rather than end up having no idea what I have and how much I paid for it. I'd actually been thinking of making a post here asking how others handle this and if there were any good templates or applications for it.

My spreadsheet has columns for date of purchase, refiner, serial number, format (ie cast/minted/coin etc), style, supplier, weight, units (oz t, g, kg), price paid, price paid per ounce (calculated from data) and spot at time of purchase. I use some hidden columns to convert the mass to ounces regardless of whether it was entered in ounces, grams or kilograms and this is used to calculate the price per ounce and for another section where the current total spot value (I have a field for current spot price), the average price paid per ounce (actual and spot), total spent, total premium paid, total mass in oz t, g and kg are calculated.

I realised I really need to add columns for premium paid for each ounce/purchase, too. I will also think about separating bullion from any coins/rounds I might get, as others have mentioned. I will at least separate out the data within the spreadsheet.
 
^^ When calculating premiums over spot. Separate out the shipping/insurance premium from the premium to spot.
Even if it was "free" subtract the actual cost from your coin/bar premium - but I would include it in your overall cost base.
(As it was a true cost - but one you wont get back when it comes time to value/sell)
 
Once you get it set up its easy to maintain.

Im doing a very basic one but entered 18 Months of data and added a couple of new columns in around 30mins.

Dont record spot at time of buying, price paid is the figure to record. I also add in postage costs so my dca is accurate. I have subtotals for coins, bars, unallocated and nuggets but only keep one overall dca total for everything.

Planning to add an "expected sale price" column that will give relevence to my dca and how my stack is.
 
bull_bear said:
^^ When calculating premiums over spot. Separate out the shipping/insurance premium from the premium to spot.
Even if it was "free" subtract the actual cost from your coin/bar premium - but I would include it in your overall cost base.
(As it was a true cost - but one you wont get back when it comes time to value/sell)
Click to expand...

So, you mean to record the total price paid as two separate things - price of the goods and price of shipping/insurance? That would make sense. I presume what you mean is that you can then use this to get a 'proper' premium for later calculating a ballpark figure for the current sale value, for which the shipping is obviously irrelevant, but still very relevant in calculating your overall costs.

So far, I have no shipping expenses, but I will definitely update the spreadsheet to accommodate this for any future purchases.

How would I go about deciding the actual cost when it is 'free', though?
 
Eureka Moments said:
Dont record spot at time of buying, price paid is the figure to record. [...]
Click to expand...

I've put both. That way, I can use it to automatically calculate the premiums for each purchase and, thus, the total premium paid.
 
When adding in stuff like gold nuggets or freebies spot and premium mean nothing...Total paid is nil.

Also would give myself a brainache trying to record spot and premiums when swapping unallocated over for phys. Wouldnt leave me any time for watching the price charts. :)
 
silver is $100/oz and gold is $2000/oz.

Now every stacker that just had a sigh of relief and feel good about their stack...you lot owe me a coffee...
 
gooby said:
bull_bear said:
^^ When calculating premiums over spot. Separate out the shipping/insurance premium from the premium to spot.
Even if it was "free" subtract the actual cost from your coin/bar premium - but I would include it in your overall cost base.
(As it was a true cost - but one you wont get back when it comes time to value/sell)
Click to expand...

So, you mean to record the total price paid as two separate things - price of the goods and price of shipping/insurance? That would make sense. I presume what you mean is that you can then use this to get a 'proper' premium for later calculating a ballpark figure for the current sale value, for which the shipping is obviously irrelevant, but still very relevant in calculating your overall costs.

So far, I have no shipping expenses, but I will definitely update the spreadsheet to accommodate this for any future purchases.

How would I go about deciding the actual cost when it is 'free', though?
Click to expand...

Yep, think youve got it
I would break down the cost into a couple of columns, so for example,
If I bought 5 kooks, for $200 including postage, I would want to know that the cost of each was $28 (spot) + $10 (premium) + $2 (postage)

From there its easy to estimate
my total each cost ($28+$10+$2),
my price paid (and current value) per coin ($28+$10)
premium % ($10/$28)
and my future estimated sales value (new spot + premium)

If I was estimating my profit (loss), it would be initially be cost - value ($30-$28).. so you need spot to rise $2 just to break even.. (why minimising shipping costs is so important)

With regards to postage costs, after a couple of transactions you will get a feel for it,
as a ball park figure, a 1kg parcel in an uninsured registered post padded bag with Australia Post is about $15. (Note that 1kg postage isn't 1kg of metal)
Insured from a site sponsor 1kg ~$17; 2kg~$21;3kg~$26. Sometimes you can just look at the stamp when you receive the item.
 
You must log in or register to reply here.
Back
Top