Tax accounting for physical metals

Discussion of the Gold portion of the Permanent Portfolio

Moderator: Global Moderator

Post Reply
Libertarian666
Executive Member
Executive Member
Posts: 5994
Joined: Wed Dec 31, 1969 6:00 pm

Tax accounting for physical metals

Post by Libertarian666 » Mon Apr 22, 2019 7:35 am

Anyone have a spreadsheet where I could enter date, unit price, weight per unit, and number of units for each type of coin or bar, then when I sell, calculate remaining inventory and profit/loss via FIFO? I'm sure I could write up something but I'd rather spend time on other things than develop a spreadsheet that may already exist.

A relatively inexpensive commercial product would be okay too if you have a suggestion.

Thanks in advance for any assistance.
User avatar
ochotona
Executive Member
Executive Member
Posts: 3353
Joined: Fri Jan 30, 2015 5:54 am

Re: Tax accounting for physical metals

Post by ochotona » Mon Apr 22, 2019 9:37 am

So this is a headache for me to think about... which is why I asked my church if they would someday accept appreciated PMs, they said yes they would. I plan to give my physical metals to my kids and my church and I don't think I will have tax issues. My kids will get the step-up in basis upon my death.

But let's think about it some more... if you sell PM at a gain, you pay the 28% collectibles tax on the gain.

What if you sell it for a loss? Do you get a 28% collectibles break on the gain? Or is it "tails I win, heads you lose?" in this case?
jacksonM
Executive Member
Executive Member
Posts: 364
Joined: Wed Sep 26, 2018 1:59 pm

Re: Tax accounting for physical metals

Post by jacksonM » Mon Apr 22, 2019 9:41 am

ochotona wrote:
Mon Apr 22, 2019 9:37 am
So this is a headache for me to think about... which is why I asked my church if they would someday accept appreciated PMs, they said yes they would. I plan to give my physical metals to my kids and my church and I don't think I will have tax issues. My kids will get the step-up in basis upon my death.

But let's think about it some more... if you sell PM at a gain, you pay the 28% collectibles tax on the gain.

What if you sell it for a loss? Do you get a 28% collectibles break on the gain? Or is it "tails I win, heads you lose?" in this case?
Actually, if it's a gain I believe you pay the 28% collectibles tax or your ordinary income rate whichever is lower.

As for a loss, I believe it's just a simple capital loss and no special rules apply.

Somebody can correct me if I'm wrong on either count.
pmward
Executive Member
Executive Member
Posts: 1731
Joined: Thu Jan 24, 2019 4:39 pm

Re: Tax accounting for physical metals

Post by pmward » Mon Apr 22, 2019 9:49 am

I have a rough spreadsheet I use for this, but not something worth sharing. I'm really just tracking all the info for each lot purchased and sold. It's quite easy to do (provided you have all the info on all your past lots). You don't have to do FIFO, you can sell from whatever lot(s) you want. Which means, you can strategically decide whether it's best to clear out some of your lots with bigger gains (like if you have a large loss in another asset to use against it), or if you want to sell some of your lots smaller gains.
Libertarian666
Executive Member
Executive Member
Posts: 5994
Joined: Wed Dec 31, 1969 6:00 pm

Re: Tax accounting for physical metals

Post by Libertarian666 » Mon Apr 22, 2019 11:34 am

I don’t need tax advice. I know how to calculate the taxes on each transaction. What I need is an inventory program to keep track of the basis for each lot of each item, e. g. I have 3 lots of 100g bars with 2 bars in the first lot, 4 bars in the second, and 3 bars in the third. The basis is $1800 an ounce for the first lot, $1700 for the second lot, and $1600 for the third lot. Then I sell 5 bars with fifo accounting at $1400. How much is my loss, and how much is my remaining basis when I sell later?

This is okay to do manually for one or two items but I have about 100 different types of items so I need an automatic way to keep track.
User avatar
Xan
Administrator
Administrator
Posts: 4392
Joined: Tue Mar 13, 2012 1:51 pm

Re: Tax accounting for physical metals

Post by Xan » Mon Apr 22, 2019 11:49 am

Libertarian666 wrote:
Mon Apr 22, 2019 11:34 am
I don’t need tax advice. I know how to calculate the taxes on each transaction. What I need is an inventory program to keep track of the basis for each lot of each item, e. g. I have 3 lots of 100g bars with 2 bars in the first lot, 4 bars in the second, and 3 bars in the third. The basis is $1800 an ounce for the first lot, $1700 for the second lot, and $1600 for the third lot. Then I sell 5 bars with fifo accounting at $1400. How much is my loss, and how much is my remaining basis when I sell later?

This is okay to do manually for one or two items but I have about 100 different types of items so I need an automatic way to keep track.
Why would you do FIFO rather than operate on individual bars?
User avatar
ochotona
Executive Member
Executive Member
Posts: 3353
Joined: Fri Jan 30, 2015 5:54 am

Re: Tax accounting for physical metals

Post by ochotona » Mon Apr 22, 2019 11:57 am

jacksonM wrote:
Mon Apr 22, 2019 9:41 am
ochotona wrote:
Mon Apr 22, 2019 9:37 am
So this is a headache for me to think about... which is why I asked my church if they would someday accept appreciated PMs, they said yes they would. I plan to give my physical metals to my kids and my church and I don't think I will have tax issues. My kids will get the step-up in basis upon my death.

But let's think about it some more... if you sell PM at a gain, you pay the 28% collectibles tax on the gain.

What if you sell it for a loss? Do you get a 28% collectibles break on the gain? Or is it "tails I win, heads you lose?" in this case?
Actually, if it's a gain I believe you pay the 28% collectibles tax or your ordinary income rate whichever is lower.

As for a loss, I believe it's just a simple capital loss and no special rules apply.

Somebody can correct me if I'm wrong on either count.
Journal of Accountancy:

"For tax purposes, physical gold investments are classified as collectibles. Gains on collectibles held for one year or less are taxed as ordinary income—the same tax treatment as short-term capital gains (STCGs). Gains on collectibles held more than one year are taxed as ordinary income, except the maximum collectibles tax rate is 28%"

I'm feeling better now... so I could sell gold during my age 65-70 low income years and get taxed as if it were ordinary income, my marginal rate will be low then.
pmward
Executive Member
Executive Member
Posts: 1731
Joined: Thu Jan 24, 2019 4:39 pm

Re: Tax accounting for physical metals

Post by pmward » Mon Apr 22, 2019 12:06 pm

Libertarian666 wrote:
Mon Apr 22, 2019 11:34 am
I don’t need tax advice. I know how to calculate the taxes on each transaction. What I need is an inventory program to keep track of the basis for each lot of each item, e. g. I have 3 lots of 100g bars with 2 bars in the first lot, 4 bars in the second, and 3 bars in the third. The basis is $1800 an ounce for the first lot, $1700 for the second lot, and $1600 for the third lot. Then I sell 5 bars with fifo accounting at $1400. How much is my loss, and how much is my remaining basis when I sell later?

This is okay to do manually for one or two items but I have about 100 different types of items so I need an automatic way to keep track.
I wasn't giving tax advice, I was telling you that it's easy enough to create a spreadsheet to keep track of the purchasing and selling of all of your lots and calculating what the tax cost would be to sell each at any given moment. Though I also don't think that FIFO is anywhere near an optimal strategy for tax management and I personally would not implement a FIFO strategy. But to each their own.
Libertarian666
Executive Member
Executive Member
Posts: 5994
Joined: Wed Dec 31, 1969 6:00 pm

Re: Tax accounting for physical metals

Post by Libertarian666 » Mon Apr 22, 2019 12:08 pm

Xan wrote:
Mon Apr 22, 2019 11:49 am
Libertarian666 wrote:
Mon Apr 22, 2019 11:34 am
I don’t need tax advice. I know how to calculate the taxes on each transaction. What I need is an inventory program to keep track of the basis for each lot of each item, e. g. I have 3 lots of 100g bars with 2 bars in the first lot, 4 bars in the second, and 3 bars in the third. The basis is $1800 an ounce for the first lot, $1700 for the second lot, and $1600 for the third lot. Then I sell 5 bars with fifo accounting at $1400. How much is my loss, and how much is my remaining basis when I sell later?

This is okay to do manually for one or two items but I have about 100 different types of items so I need an automatic way to keep track.
Why would you do FIFO rather than operate on individual bars?
For one thing, I don’t have the bars in hand. They’re at the Texas bullion depository. Also, they all have very similar bases. But I wouldn’t mind having the ability to select individual bars or coins.
Libertarian666
Executive Member
Executive Member
Posts: 5994
Joined: Wed Dec 31, 1969 6:00 pm

Re: Tax accounting for physical metals

Post by Libertarian666 » Mon Apr 22, 2019 12:10 pm

pmward wrote:
Mon Apr 22, 2019 12:06 pm
Libertarian666 wrote:
Mon Apr 22, 2019 11:34 am
I don’t need tax advice. I know how to calculate the taxes on each transaction. What I need is an inventory program to keep track of the basis for each lot of each item, e. g. I have 3 lots of 100g bars with 2 bars in the first lot, 4 bars in the second, and 3 bars in the third. The basis is $1800 an ounce for the first lot, $1700 for the second lot, and $1600 for the third lot. Then I sell 5 bars with fifo accounting at $1400. How much is my loss, and how much is my remaining basis when I sell later?

This is okay to do manually for one or two items but I have about 100 different types of items so I need an automatic way to keep track.
I wasn't giving tax advice, I was telling you that it's easy enough to create a spreadsheet to keep track of the purchasing and selling of all of your lots and calculating what the tax cost would be to sell each at any given moment. Though I also don't think that FIFO is anywhere near an optimal strategy for tax management and I personally would not implement a FIFO strategy. But to each their own.
I haven’t found it easy to do for hundreds of different items. That’s why I asked if anyone had a solution.
jacksonM
Executive Member
Executive Member
Posts: 364
Joined: Wed Sep 26, 2018 1:59 pm

Re: Tax accounting for physical metals

Post by jacksonM » Mon Apr 22, 2019 7:48 pm

Libertarian666 wrote:
Mon Apr 22, 2019 11:34 am
I don’t need tax advice. I know how to calculate the taxes on each transaction. What I need is an inventory program to keep track of the basis for each lot of each item, e. g. I have 3 lots of 100g bars with 2 bars in the first lot, 4 bars in the second, and 3 bars in the third. The basis is $1800 an ounce for the first lot, $1700 for the second lot, and $1600 for the third lot. Then I sell 5 bars with fifo accounting at $1400. How much is my loss, and how much is my remaining basis when I sell later?

This is okay to do manually for one or two items but I have about 100 different types of items so I need an automatic way to keep track.
Sounds to me like your gold holding situation is a lot more complicated than the rest of us have to deal with.

So maybe you are going to have to write that spreadsheet yourself after all.
Libertarian666
Executive Member
Executive Member
Posts: 5994
Joined: Wed Dec 31, 1969 6:00 pm

Re: Tax accounting for physical metals

Post by Libertarian666 » Mon Apr 22, 2019 8:55 pm

jacksonM wrote:
Mon Apr 22, 2019 7:48 pm
Libertarian666 wrote:
Mon Apr 22, 2019 11:34 am
I don’t need tax advice. I know how to calculate the taxes on each transaction. What I need is an inventory program to keep track of the basis for each lot of each item, e. g. I have 3 lots of 100g bars with 2 bars in the first lot, 4 bars in the second, and 3 bars in the third. The basis is $1800 an ounce for the first lot, $1700 for the second lot, and $1600 for the third lot. Then I sell 5 bars with fifo accounting at $1400. How much is my loss, and how much is my remaining basis when I sell later?

This is okay to do manually for one or two items but I have about 100 different types of items so I need an automatic way to keep track.
Sounds to me like your gold holding situation is a lot more complicated than the rest of us have to deal with.

So maybe you are going to have to write that spreadsheet yourself after all.
Apparently so.

But the only thing complicated about it is all the different coins that I've purchased over the last 40 years. It's just a matter of scale, not of intrinsic complexity.

I may just write a program, not try to use a spreadsheet. I'm a better C++ programmer than I am an Excel programmer.
User avatar
sophie
Executive Member
Executive Member
Posts: 1959
Joined: Mon Apr 23, 2012 7:15 pm

Re: Tax accounting for physical metals

Post by sophie » Tue Apr 23, 2019 7:04 am

When you posted this I thought it was for clients not for yourself. If it's only for yourself, why make it so complicated? It's just a spreadsheet with a listing of gold purchases in the order you made them. To decide on a lot to sell, you can look for the purchase with the maximum loss (if you want that) or for FIFO just sell from the first line. I don't see why any fancy programming is required, unless you want to automatically pull in numbers for sale price & selling costs for each type of item (bar, coin etc) or sort on specific criteria (date of purchase, net gain). Unless you're selling frequently, that seems hardly worth the time to do anything other than the simple list.
Libertarian666
Executive Member
Executive Member
Posts: 5994
Joined: Wed Dec 31, 1969 6:00 pm

Re: Tax accounting for physical metals

Post by Libertarian666 » Tue Apr 23, 2019 11:09 am

sophie wrote:
Tue Apr 23, 2019 7:04 am
When you posted this I thought it was for clients not for yourself. If it's only for yourself, why make it so complicated? It's just a spreadsheet with a listing of gold purchases in the order you made them. To decide on a lot to sell, you can look for the purchase with the maximum loss (if you want that) or for FIFO just sell from the first line. I don't see why any fancy programming is required, unless you want to automatically pull in numbers for sale price & selling costs for each type of item (bar, coin etc) or sort on specific criteria (date of purchase, net gain). Unless you're selling frequently, that seems hardly worth the time to do anything other than the simple list.
If I were always selling a whole lot as I bought it, it would be that easy.
But I'm not, so I have to keep track of how much is left in each lot.
Of course it's not complicated in the sense of requiring a lot (no pun intended) of logic.
It is somewhat complicated because there are dozens of different lots to keep track of.
However, it's obvious that no one here agrees with that, so I'll just drop the topic.
User avatar
sophie
Executive Member
Executive Member
Posts: 1959
Joined: Mon Apr 23, 2012 7:15 pm

Re: Tax accounting for physical metals

Post by sophie » Wed Apr 24, 2019 7:08 am

OK I think I understand what you're looking for: a sub-list of sale dates/amounts, amount left in the lot, cost basis, realized gains/losses in what year etc, sort of like what Fidelity and Vanguard provide for position holdings. Yes it would be a programming job if you do a lot of sales in different lots throughout the year. How about something like this?

https://www.gainskeeper.com/us/GainsKee ... vidua.aspx

Except, if you want a program that cleanly integrates into the rest of your system or don't want to pay for software, you probably will want to write it yourself.
jacksonM
Executive Member
Executive Member
Posts: 364
Joined: Wed Sep 26, 2018 1:59 pm

Re: Tax accounting for physical metals

Post by jacksonM » Wed Apr 24, 2019 10:09 am

Libertarian666 wrote:
Mon Apr 22, 2019 8:55 pm
jacksonM wrote:
Mon Apr 22, 2019 7:48 pm
Libertarian666 wrote:
Mon Apr 22, 2019 11:34 am
I don’t need tax advice. I know how to calculate the taxes on each transaction. What I need is an inventory program to keep track of the basis for each lot of each item, e. g. I have 3 lots of 100g bars with 2 bars in the first lot, 4 bars in the second, and 3 bars in the third. The basis is $1800 an ounce for the first lot, $1700 for the second lot, and $1600 for the third lot. Then I sell 5 bars with fifo accounting at $1400. How much is my loss, and how much is my remaining basis when I sell later?

This is okay to do manually for one or two items but I have about 100 different types of items so I need an automatic way to keep track.
Sounds to me like your gold holding situation is a lot more complicated than the rest of us have to deal with.

So maybe you are going to have to write that spreadsheet yourself after all.
Apparently so.

But the only thing complicated about it is all the different coins that I've purchased over the last 40 years. It's just a matter of scale, not of intrinsic complexity.

I may just write a program, not try to use a spreadsheet. I'm a better C++ programmer than I am an Excel programmer.
Try Google sheets. I was a C++ programmer too and always thought spreadsheets were complicated, especially one written by a company like Microsoft who "excels" at complicating things. Google sheets is a lot simpler IMHO.
pmward
Executive Member
Executive Member
Posts: 1731
Joined: Thu Jan 24, 2019 4:39 pm

Re: Tax accounting for physical metals

Post by pmward » Wed Apr 24, 2019 3:26 pm

jacksonM wrote:
Wed Apr 24, 2019 10:09 am
Try Google sheets. I was a C++ programmer too and always thought spreadsheets were complicated, especially one written by a company like Microsoft who "excels" at complicating things. Google sheets is a lot simpler IMHO.
Microsoft has gotten a lot better in the last few years. My most proficient backend language is C#, so yeah... I'm about as engrained in the Microsoft tech stack as it gets (though we do use AWS for our cloud architecture instead of Azure). They've improved a lot since Nadella took over. I personally prefer Excel to Google Sheets these days, especially if you're a 365 subscriber.

C++ on the other hand... one must be quite the masochist to code in C++ these days, haha.
Libertarian666
Executive Member
Executive Member
Posts: 5994
Joined: Wed Dec 31, 1969 6:00 pm

Re: Tax accounting for physical metals

Post by Libertarian666 » Wed Apr 24, 2019 7:04 pm

sophie wrote:
Wed Apr 24, 2019 7:08 am
OK I think I understand what you're looking for: a sub-list of sale dates/amounts, amount left in the lot, cost basis, realized gains/losses in what year etc, sort of like what Fidelity and Vanguard provide for position holdings. Yes it would be a programming job if you do a lot of sales in different lots throughout the year. How about something like this?

https://www.gainskeeper.com/us/GainsKee ... vidua.aspx

Except, if you want a program that cleanly integrates into the rest of your system or don't want to pay for software, you probably will want to write it yourself.
Yes, that's what I wanted, and I don't know why that should be considered so weird.

Anyway, I have decided to just use my existing spreadsheet and keep track of the lots manually.
Libertarian666
Executive Member
Executive Member
Posts: 5994
Joined: Wed Dec 31, 1969 6:00 pm

Re: Tax accounting for physical metals

Post by Libertarian666 » Wed Apr 24, 2019 7:05 pm

jacksonM wrote:
Wed Apr 24, 2019 10:09 am
Libertarian666 wrote:
Mon Apr 22, 2019 8:55 pm
jacksonM wrote:
Mon Apr 22, 2019 7:48 pm


Sounds to me like your gold holding situation is a lot more complicated than the rest of us have to deal with.

So maybe you are going to have to write that spreadsheet yourself after all.
Apparently so.

But the only thing complicated about it is all the different coins that I've purchased over the last 40 years. It's just a matter of scale, not of intrinsic complexity.

I may just write a program, not try to use a spreadsheet. I'm a better C++ programmer than I am an Excel programmer.
Try Google sheets. I was a C++ programmer too and always thought spreadsheets were complicated, especially one written by a company like Microsoft who "excels" at complicating things. Google sheets is a lot simpler IMHO.
There is no way in the world that I'm going to use a web-based program for this.
Especially not with Google or any of the other big tech companies that misuses personal data.
I'd rather use a ledger book with a green eyeshade and a quill pen.
Libertarian666
Executive Member
Executive Member
Posts: 5994
Joined: Wed Dec 31, 1969 6:00 pm

Re: Tax accounting for physical metals

Post by Libertarian666 » Wed Apr 24, 2019 7:08 pm

pmward wrote:
Wed Apr 24, 2019 3:26 pm
jacksonM wrote:
Wed Apr 24, 2019 10:09 am
Try Google sheets. I was a C++ programmer too and always thought spreadsheets were complicated, especially one written by a company like Microsoft who "excels" at complicating things. Google sheets is a lot simpler IMHO.
Microsoft has gotten a lot better in the last few years. My most proficient backend language is C#, so yeah... I'm about as engrained in the Microsoft tech stack as it gets (though we do use AWS for our cloud architecture instead of Azure). They've improved a lot since Nadella took over. I personally prefer Excel to Google Sheets these days, especially if you're a 365 subscriber.

C++ on the other hand... one must be quite the masochist to code in C++ these days, haha.
Not at all. Modern C++ is much easier to use than the older versions.
As it happens, I'm currently developing a hash table that is optimized for Optane DC Persistent Memory, and have ordered a server equipped with that hardware for performance testing and optimization.
So far it looks like I will have microsecond-range access to any record in a table containing billions of records.
Post Reply