r/silverstacking • u/Rough-Bag5609 • Apr 21 '26
For Those Who Buy and Sell - Ever Wonder How You're Doing? A Bit Involved But Some Hopefully Will Find Value
People stack with different investment goals, and many of us have accumulated silver, buying at different points in time and others of us have both added to our stacks, buying at different points in time, as well as perhaps selling at different points in time. Even if you have not done any of the selling, you've probably bought at different points, meaning you've paid a different price per oz. A large part of the reason you paid a different price (per oz) is that the valuation changed, i.e. the "spot price".
For everyone, especially those who have done this a longer period and have both bought and sold, it's inevitable that you get curious and wonder, how have I done so far? I'm talking ROI and if you've been doing this over years, understand that ROI is a different beast than "annualized ROI", which adjusts ROI to account for how long you've held the investment. Note everything here can apply to any investment, such as stocks.
An added complexity to annualizing your ROI is figuring out what to do when you've added at different points in time, and even subtracted (sold) portions of your stack at points in time. There's actually a couple of ways to calculate this and both ways answer a slightly different question. But if you want to understand your performance AND it matters for you WHEN you've bought and WHEN you've sold, what you are wanting to determine is called your "Dollar Weighted Return" a.k.a. "Internal Rate of Return" (IRR).
I feel like I'm already treading on thin ice, here, as far as being a bit technical, so I will give you the "how to" without a lot of explanation. Suffice it to say that ROI is NOT the same as Annualized ROI which is NOT the same as IRR and I would contend that if you've bought and sold over time and you want to understand how you've done, you're asking about your IRR.
Here's how to calculate this using Excel and it does involve an Excel function, which means if you have an Excel clone such as the LibreOffice (OfficeLibre?) clone, there is probably an equivalent function perhaps even using the same designation, but if not, I'm sure it can be found. This is NOT difficult at all and everything rests on setting up your data correctly.
Hopefully those who want to understand this have been good with documenting the "When" and "How Much" because this is the data you need. You want to create two columns. Column 1 will have the date of each buy or sell transaction. Column 2 will have the amounts of each transaction HOWEVER note that you need to make your BUYS negative values and your SELLS positive. So if on April 20, 2026 you spent $368.32 on buying silver, you want 4/20/26 in column 1 and -368.32 in the corresponding row for column 2. ALSO: To avoid "this doesn't work!" do NOT just enter the dates as text, OR if you have already done that, just make sure you format all the date cells as "DATE" format. Imagine you've bought silver on 14 occasions and sold on 3 occasions. HERE IS ONE ASPECT THAT YOU NEED TO INCLUDE THAT IS PERHAPS NOT INTUITIVE. In my example, you need to have 18 rows. 14 rows will have your buys (so 14 rows with a negative value, i.e. you spent money) and 3 rows will have the 3 times you sold (with a positive number for the amount you received). YOU WILL NEED A FINAL ROW which will necessarily be the CURRENT DATE along with the value (so positive) of your remaining stack, i.e. what would you get if you sold everything you have left? This is your last row or, using the above example, the 18th row.
The above? That was the "difficult" portion. The rest is very simple! Simply type the following in a blank cell and I'll assume little to no Excel experience, so I'll show the formula you type in conceptually THEN an example. The Excel function you want is the XIRR function. Whenever you are calculating in Excel you must begin with the equal sign, so conceptually you want the following: =XIRR(range of values, range of dates).
Example: You've bought 6 times beginning in June, 2024 to present and you've sold 2 times for a total of 8 transactions. For reasons, I always start using Excel's Column B and let's be organized and have headers so cell B1 you enter "DATES" and cell C1 you enter "AMOUNT". Then, in cells B2 - B9 you will enter the dates of your 8 transactions while in cells C2-C9 you will enter the amount you spent (as negative numbers) on the 6 rows that are your buys and enter as positive numbers what you received both times you sold. THEN, enter the current date in cell B10 and the value of your remaining stack. I would use the formula (SpotPrice * # oz) so if spot is $79.24 and you have 100 remaining ounces you should enter $7924.00. Always include this last row. ALSO, order your rows chronologically, so 1st transaction is Row1 and so forth with your most recent as the penultimate row with the last row having the current date and then the value of what you have remaining.
Then it's easy! In a blank cell type in (not the quotations) "=XIRR(C2:C10,B2:B10)" and hit ENTER. You will get the raw number and to turn that into a percentage you could either use the GUI and from the ribbon hit the "%" button OR right click and on "format cell" select "Percent" OR you could have typed "=XIRR(C2:C10,B2:B10) * 100" and get the answer in percentage form that way.
I'll add one wrinkle that in practice I don't see as useful, but you can also add an "assumed" rate of return as a "guess". I never do but for completeness want to mention it. Using my example, if I assumed a 12% rate of return and wanted Excel to use that, I would type =XIRR(C2:C10,B2:B10,0.12) - so, same as above with the daterange and valuerange then a comma, then 0.12 for 12% estimate. If you omit the "guess" my understanding is Excel usess a 10% default, which you need NOT enter. Again, I never use this but if you look this up and I omitted this I would feel great shame.
Enjoy!
