Excel spreadsheet

[DonWerner]

Hey,

 

I was wondering if anyone on here would be willing to share their silver stacking spreadsheet with me? Would like to see how you guys have created yours, what info do you include, and which calculations do you make? 

[AgD]

3 minutes ago, DonWerner said:

Hey,

 

I was wondering if anyone on here would be willing to share their silver stacking spreadsheet with me? Would like to see how you guys have created yours, what info do you include, and which calculations do you make? 

I'm sure @BackyardBullion shared his spreadsheet on here somewhere he will be able to point you in the right direction ?

[BackyardBullion]

http://thesilverforum.com/applications/core/interface/file/attachment.php?id=39772

[DonWerner]

Awesome, thanks!

 

[Alymac]

I really fearing what I have spent on things like my PAMP bars!

[RisinSun]

@DonWerner If you use the table function on excel and google how to use the formula function and finally, if you have various pieces of metals to compile; I really recommend making your own unique worksheet. I think it is a valuable part of the stacker's journey.

@Alymac I have never been in profit, however I do not take numismatic value into account. Embrace the loss and keep on losing!

Summary	Spot Price Value Totals	Investment Value Totals	Profit/Loss
Gold Stack	£21,368.16	£21,841.36	-£473.20
Silver Stack	£3,959.28	£5,342.79	-£1,383.51
Total	£25,327.44	£27,184.15	-£1,856.71
Market Spot	Spot Price
Gold	£971.28
Silver	£12.22

[Pipers]

5 hours ago, RisinSun said:

@DonWerner If you use the table function on excel and google how to use the formula function and finally, if you have various pieces of metals to compile; I really recommend making your own unique worksheet. I think it is a valuable part of the stacker's journey.

@Alymac I have never been in profit, however I do not take numismatic value into account. Embrace the loss and keep on losing!

Summary	Spot Price Value Totals	Investment Value Totals	Profit/Loss
Gold Stack	£21,368.16	£21,841.36	-£473.20
Silver Stack	£3,959.28	£5,342.79	-£1,383.51
Total	£25,327.44	£27,184.15	-£1,856.71
Market Spot	Spot Price
Gold	£971.28
Silver	£12.22

This is correct, you should never take numismatic value into account when stacking.  Imo spot price value is the best and most accurate way of valuing a stack any premium received when selling will be offset by the below spot price you will get for other parts of your stack, this includes postage etc.  @Alymac I think you are a bit harsh on yourself, this is a long term game with solid foundations give it a few more years and you will be ok. 

[Bullionaire]

I tried making my own spreadsheet, but then BYB's looked nicer, so i started using that instead. I have added a summary tab on though that tells me interesting titbits like what the spot price would have to be for me to break even - just based on the value of the content of silver or gold.

I just need to figure out how to get the spot price to auto-update and i'm golden (pardon the pun)

 

 

[Bullionaire]

I was just wondering, how do you guys deal with PM's you no longer have (that you've sold etc) on your spreadsheets?

I was wondering how I could incorporate this into my own. I don't really want to just delete the entry. Maybe another column or tab that deals with this? 

[richatthecroft]

7 minutes ago, Bullionaire said:

I was just wondering, how do you guys deal with PM's you no longer have (that you've sold etc) on your spreadsheets?

I was wondering how I could incorporate this into my own. I don't really want to just delete the entry. Maybe another column or tab that deals with this? 

Hi, I guess this is a pointless response as I haven’t a clue how to adapt a spreadsheet to include sales, I guess you could ask a teenager, they’ll know!

Although I keep a spreadsheet, my go to record of all my PM’s is an app on my phone. My suggestion is dependent of you owning a smartphone- iPhone or android and downloading ‘Gold Tracker’ to it.  It costs £2.99. 

It provides an immediate overview of your PM’s it auto updates according to live price changes, you have at your fingertips a summary of your holdings including your current worth, your average buy price etc. etc. and it reports and provides a breakdown for each PM. 

You can add purchases on the fly and separate out postage and associated costs. It provides a nice colourful Inventory and a PM price calculator for differing PM’s and their respective purity. 

Crucially,  and to eventually answer your question, it also allows you to keep track of your sales, date, what it was you sold, price achieved and price paid, percentage gain and also how long you held it for. 

My advice for the sake of £2.99 is try it out- in my opinion there’s nothing not to like. My spreadsheet is pretty much defunct since I have been using this. 

[Coolsmp]

2 hours ago, Bullionaire said:

I was just wondering, how do you guys deal with PM's you no longer have (that you've sold etc) on your spreadsheets?

I was wondering how I could incorporate this into my own. I don't really want to just delete the entry. Maybe another column or tab that deals with this? 

Just add a sold section. Excell is really intuitive and you can pretty much do anything on it.. even fractional spot amounts. For example ive got some silver proof 50p's, the silver content is not a great deal so it looks like a huge loss. 

[TonyS]

All my life has revolved around spreadsheets (depressing things) the last thing I want to know is how much I’ve lost.

 

Pass the valium please!

[Big23]

57 minutes ago, TonyS said:

All my life has revolved around spreadsheets (depressing things) the last thing I want to know is how much I’ve lost.

 

Pass the valium please!

Pah, just depends on the estimated sell price you enter

[lmwstamps]

My spreadsheet has multiple tabs which allows to to keep track of both metrics and inventory.  I have a summary page which contains this:

 

Silver Spot:	$14.73
Silver Oz:	100	(Trans Amt - 100.0000)
Silver Value:	$1473
Silver Collector Value:	$1600
Silver Investment:	$900
ROI Silver Spot Value:	10%
ROI Collector Value:	16%

Spot is current spot price, Silver Oz is the total number of ounces held, the transaction amount is a check from another tab used for inventory, Silver value is spot value, Collector value is collector retail value of items, Investment is my cost, ROI is projected return on investment.  It also contains this:

Government Bullion, Silver 1 Ounce
Type	Size (Oz)	Quant	Ag Tot	Value
Armenia Noah's Ark	1.0000	10	10.00	$147.30
Australian Kangaroo	1.0000	5	5.00	$73.65

These are breakdowns of different items in my stack.  This tab is an overall snapshot of my holdings at any given time.  The summary page is driven by data from the Holdings tab which contains information like this:

Government Bullion, Silver 1 Oz
Quantity	Description	Year	Price	Denom	Size (Oz)	Purity	Mintage	Motif	Link
	Andorra Eagle	2008	$34.00	1 Dinar	1.0000	0.999	Unknown	Eagle	Bavarian
2	Andorra Eagle	2009	$34.00	1 Dinar	1.0000	0.999	Unknown	Eagle	Bavarian
	Andorra Eagle	2010	$34.00	1 Dinar	1.0000	0.999	Unknown	Eagle	Bavarian
	Andorra Eagle	2013	$34.00	1 Dinar	1.0000	0.999	Unknown	Eagle	Bavarian
	Andorra Eagle	2014	$34.00	1 Dinar	1.0000	0.999	10,000	Eagle	Bavarian
1	Armenia Noah's Ark	2011	$20.00	500 Dram	1.0000	0.999	268,325	Noah's Ark	Leipziger
1	Armenia Noah's Ark	2012	$19.00	500 Dram	1.0000	0.999	457,576	Noah's Ark	Leipziger
	Armenia Noah's Ark	2013	$19.00	500 Dram	1.0000	0.999	581,800	Noah's Ark	Leipziger
3	Armenia Noah's Ark	2014	$19.00	500 Dram	1.0000	0.999	566,323	Noah's Ark	Leipziger
3	Armenia Noah's Ark	2015	$19.00	500 Dram	1.0000	0.999	960,182	Noah's Ark	Leipziger
2	Armenia Noah's Ark	2016	$19.00	500 Dram	1.0000	0.999	529,202	Noah's Ark	Leipziger
	Armenia Noah's Ark	2017	$19.00	500 Dram	1.0000	0.999	479,551	Noah's Ark	Leipziger
	Armenia Noah's Ark	2018	$19.00	500 Dram	1.0000	0.999	Unknown	Noah's Ark	Leipziger

Most of it is self explanatory.  The price is the retail value of the item.  The link is a link to the mint which produced the item.  The Holdings tab is used as a granular inventory page and also as a reference for sales and purchases.   I both buy and sell and when I set up at shows, this gives me information as to pricing and also items I'm looking for.  The last tab contains a record of every transaction that I make.  It contains information that looks like this:

Holdings	Ag Amt	$ Amt	$/Oz	Spot	G/L	Cum $	Tot $/Oz	Cost	Profit	Action	Item	Party
95	-1.0000	-$27.00	$27.00	$14.57	$12.04	$848.14	14.9632	$17.00	$10.00	Sold	2011 Canadian Wildlife Grizzly	Auction
96	-1.0000	-$17.00	$17.00	$14.57	$2.02	$875.14	14.9765	$15.50	$1.50	Sold	2006 ASE	Auction
97	-1.0000	-$50.00	$50.00	$14.57	$35.02	$892.14	14.9787	$21.50	$28.50	Sold	2005 Maple Leaf Victory in Japan Privy	Auction
98	-1.0000	-$24.00	$24.00	$14.57	$8.98	$942.14	15.0174	$15.00	$9.00	Sold	2010 Maple Leaf Vancouver Privy	eBay
99	-1.0000	-$25.00	$25.00	$14.57	$9.97	$966.14	15.0274	$17.95	$7.05	Sold	2016 Maple Leaf Wolf Privy	eBay
100	-1.0000	-$15.00	$15.00	$14.57	-$0.04	$991.14	15.0384	$14.00	$1.00	Sold	Generic silver bar	eBay
101	-1.0000	-$21.86	$21.86	$14.68	$6.82	$1006.14	15.0383	$19.00	$2.86	Sold	2000 Britannia	eBay
102	2.0000	$28.00	$14.00	$14.78	$2.09	$1028	15.0458	N/A	N/A	Bought	2000 ASE’s (2)	Coin Show
100	5.0000	$70.00	$14.00	$14.78	$5.24	$1000	15.0481	N/A	N/A	Bought	1999 ASE’s (5)	Coin Show

I have a date for each transaction out on the left.  Each transaction contains such as what it was, whether it was a purchase or sale, where it took place, the amount and cost.  Each transaction also calculations/data that I find useful such as the spot price, cost per oz,  and profit/loss on the item if it was a sale.  Finally, each transaction contains cumulative calculations for the stack such as Holdings which is the total number of ounces, the Cum $ cost to date, and the cumulative average cost per ounce of the stack.  The G/L column is a calculation which is associated with the cumulative cost per Oz.  The top row of this tab is always the current status of the status of my stack.  

Overall, even though it's a complicated spreadsheet, it allows me to keep track of anything I might want to do with my stack, holdings, sales, want lists, and metrics galore.  As a disclaimer, some of the data in the rows have been changed but this gives a good picture of what my spreadsheet looks like.  Like @Coolsmp said, you can pretty much do anything you want with Excel.  If you have particular things you want to do and don't know how to do them I'm sure many of us in the group wouldn't mind helping you get your spreadsheet set up the way you would like it.

[TonyS]

On 03/12/2018 at 15:27, Big23 said:

Pah, just depends on the estimated sell price you enter

I won’t be selling, if my son decides to sell it’s up to him.

[Big23]

52 minutes ago, TonyS said:

I won’t be selling, if my son decides to sell it’s up to him 

Same here, so we have lost:

a) nothing

b) everything

[intelinside]

Thanks to all that shared their experience and the spreadsheets this is helping me a lot.  I sorta have 'silver fever' and need to be more structured and itemized lol

[TonyS]

I do have a spreadsheet which includes my other collecting “bad habit” of vintage champagne.

I don’t know if others remember spreadsheets from the days before computers. I had one that was five A0 sheets taped together on my office wall. I hated the bloody thing! What takes a minute to enter now would take an hour to calculate through.