Tax accounting for physical metals
Moderator: Global Moderator
-
- Executive Member
- Posts: 5994
- Joined: Wed Dec 31, 1969 6:00 pm
Tax accounting for physical metals
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.
A relatively inexpensive commercial product would be okay too if you have a suggestion.
Thanks in advance for any assistance.
Re: Tax accounting for physical metals
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?
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?
Re: Tax accounting for physical metals
Actually, if it's a gain I believe you pay the 28% collectibles tax or your ordinary income rate whichever is lower.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?
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.
Re: Tax accounting for physical metals
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.
-
- Executive Member
- Posts: 5994
- Joined: Wed Dec 31, 1969 6:00 pm
Re: Tax accounting for physical metals
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.
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.
Re: Tax accounting for physical metals
Why would you do FIFO rather than operate on individual bars?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.
Re: Tax accounting for physical metals
Journal of Accountancy:jacksonM wrote: ↑Mon Apr 22, 2019 9:41 amActually, if it's a gain I believe you pay the 28% collectibles tax or your ordinary income rate whichever is lower.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?
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.
"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.
Re: Tax accounting for physical metals
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 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.
-
- Executive Member
- Posts: 5994
- Joined: Wed Dec 31, 1969 6:00 pm
Re: Tax accounting for physical metals
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.Xan wrote: ↑Mon Apr 22, 2019 11:49 amWhy would you do FIFO rather than operate on individual bars?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.
-
- Executive Member
- Posts: 5994
- Joined: Wed Dec 31, 1969 6:00 pm
Re: Tax accounting for physical metals
I haven’t found it easy to do for hundreds of different items. That’s why I asked if anyone had a solution.pmward wrote: ↑Mon Apr 22, 2019 12:06 pmI 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 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.
Re: Tax accounting for physical metals
Sounds to me like your gold holding situation is a lot more complicated than the rest of us have to deal with.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.
So maybe you are going to have to write that spreadsheet yourself after all.
-
- Executive Member
- Posts: 5994
- Joined: Wed Dec 31, 1969 6:00 pm
Re: Tax accounting for physical metals
Apparently so.jacksonM wrote: ↑Mon Apr 22, 2019 7:48 pmSounds to me like your gold holding situation is a lot more complicated than the rest of us have to deal with.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.
So maybe you are going to have to write that spreadsheet yourself after all.
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.
Re: Tax accounting for physical metals
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.
-
- Executive Member
- Posts: 5994
- Joined: Wed Dec 31, 1969 6:00 pm
Re: Tax accounting for physical metals
If I were always selling a whole lot as I bought it, it would be that easy.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.
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.
Re: Tax accounting for physical metals
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.
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.
Re: Tax accounting for physical metals
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.Libertarian666 wrote: ↑Mon Apr 22, 2019 8:55 pmApparently so.jacksonM wrote: ↑Mon Apr 22, 2019 7:48 pmSounds to me like your gold holding situation is a lot more complicated than the rest of us have to deal with.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.
So maybe you are going to have to write that spreadsheet yourself after all.
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.
Re: Tax accounting for physical metals
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.
-
- Executive Member
- Posts: 5994
- Joined: Wed Dec 31, 1969 6:00 pm
Re: Tax accounting for physical metals
Yes, that's what I wanted, and I don't know why that should be considered so weird.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.
Anyway, I have decided to just use my existing spreadsheet and keep track of the lots manually.
-
- Executive Member
- Posts: 5994
- Joined: Wed Dec 31, 1969 6:00 pm
Re: Tax accounting for physical metals
There is no way in the world that I'm going to use a web-based program for this.jacksonM wrote: ↑Wed Apr 24, 2019 10:09 amTry 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.Libertarian666 wrote: ↑Mon Apr 22, 2019 8:55 pmApparently 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.
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.
-
- Executive Member
- Posts: 5994
- Joined: Wed Dec 31, 1969 6:00 pm
Re: Tax accounting for physical metals
Not at all. Modern C++ is much easier to use than the older versions.pmward wrote: ↑Wed Apr 24, 2019 3:26 pmMicrosoft 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.
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.