Originally posted by Zestfulmocha
Thanks for that mate,
The NAB reference was only due to the fact I know ANZ (and maybe other brokers) offer a 'tax summary' type function within their platforms, but I haven't seen anything for NAB - so yes was referring to ATO restrictions/guidelines (as advised by my accountant), but appears he was wrong! Good to know you can give a valuation at batch level instead of holding/HIN level, so thank you for clarifying that i'll be sure to chat to him about that one.
"The key is looking at the complete formula and the rule that opening value in one year MUST equal the closing value of the previous year."
The formulas I am okay with, although in your formulas I assume you're just looking at P&L for a single line item/batch, not account level? Either way, I think i'm comfortable on this front. My biggest issue is how do you change the 'opening price' without over-writing the buy price, however as I read your post and think about this a bit more, perhaps the issue is i'm trying to keep it in one sheet, perhaps I just need a clear slate - new spreadsheet for every FY instead of trying to run the filter on on master sheet... this would allow for different opening rates in each FY and make reporting easier (But it would destroy my formula for calculating time held and auto calculating taxable income on capital gains, but maybe can live without that...
"n my spreadsheet, for each batch of shares I own I have a column that shows the value of the batch at cost price, a column that shows the value of the batch at market price and a third column that shows the lower of the first two values. This is what I use to value my closing inventory, namely the lower of cost or market. This means some batches may be valued at market and others at cost.
I do similar, I assume you manually do this at EOFY for all open positions, or do you do it for all positions throughout the year? (Do you sue excel or google sheets? Do you have addons/formula to scrape ASX prices?)
Appreciate your time typing out such a detailed response - if you would be able to send me a wiped copy of your spreadsheet that would be hugely appreciated!
I'm happy to flick you through what i'm using too if it's of any interest, here's a snip of some of the formula's I've built in to help with my reviews (The third column will check IF position has been open 365 days AND position has been closed AND position is in profit, it reduces the profit amount by 50% so that the totals are only displaying taxable profit as opposed to all profit, otherwise will just display full profit amount if criteria are not met (maybe helpful for investors or those with a lot of positions claiming CGT, or a mix of those that are and aren't) Formula is .....=IF(AND('days open'>365,'profit'>0,('sell date'-'buy date'+1)>0),'profit or loss'/2,'profit or loss')..... Hopefully that is useful to someone here lol, took me long enough to figure out how to do it properly
Cheers,
Zest
I actually use Apple's Numbers as my spreadsheet. This is the gist of what I do, though in reality I do it much more concisely using flags to separate out purchases this year from carry forward open positions from batches sold this year, but that would be too difficult to describe so I will detail my methodology using tables for each value I need to calculate. A table in Numbers is just a single spreadsheet (rows X columns) that records certain things needed for my reporting.
I have a table called Purchases This Year. This has a row for each batch and each row has the following columns: Purchase Date, Ticker, Quantity, Unit Cost, Brokerage and Total Cost (extended cost including brokerage). This has no entries at the beginning of the year, but every time I a make a stock purchase, I add it as a row in this table (I group by ticker, but that is only to facilitate reporting).
I also have a table called Sales This Year. That has a row for each stock sale and each row has the following columns: Sale Date, Ticker, Quantity, Unit Sale Price, Brokerage and Net Proceeds (extended sale value less brokerage). This has no entries at the beginning of the year, but every time I make a stock sale, I add a row to this table (again grouped by ticker).
I also have a table called Stock On Hand. That is the same as the Purchases This Year table, except at the beginning of the year it is populated with those purchases (or parts thereof) not yet sold at the end of the previous year. It also has 3 extra columns after Total Cost; Current Unit Price, Market Value and Lower of Cost/Market. During the year as I add items to the Purchases This Year table, I also add them to this table. When I sell a batch, I delete the relevant row from this table (or adjust the quantity for part sales or other irregularities (stock split, consolidations, etc.)).
At year end I get my Sales for the year for calculating this years Profit/Loss by totalling the Net Proceeds column in the Sales This Year table. I get my Purchases for the year for calculating this years Profit/Loss by totalling the Total Cost column in the Purchases This Year table. Both of these will be over all rows, so are the relevant totals for ALL batches purchased or sold.
At year end I also fill in the Current Unit Price in the Stock On Hand table with the year end price for each ticker. I don't do this manually as it might mean entering BHP's year end price 20 times if I have 20 unsold batches of BHP. What I do is I have a separate table (Current Ticker Price table) that just has two columns: ticker and unit price and one row for each ticker that I hold shares in. I have a Yahoo portfolio list that also has these tickers in it in the same order. I then use Yahoo's export function to down load into this table (there is an extra step that I won't go into here). The Current Unit Price field for each batch in the Stock On Hand table uses a Lookup function (I am sure Excel has it too) to pull into that field the price from the Current Ticker Price table. The Market Value is then calculated by multiplying the quantity for each batch by the Current Unit Price Field. The remaining field, Lower of Cost/Market, is got by simply comparing the Total Cost value with the Market Value and which ever is lower is replicated to that field. I then use the column total for Lower of Cost/Market across all batches for my Closing Stock On Hand value in the Profit/Loss formula.
The only thing missing is the Opening Stock On Hand value. As this MUST always equal the Closing Stock On Hand value of the previous year, I just use the Closing Stock On Hand value from the previous years spreadsheet.
Just as an aside, I can always calculate at any time the market value of my stock on hand throughout the year by downloading the Current Unit Prices via Yahoo just as I did at year end.
Also, if I wanted to change from valuing all closing Stock On Hand from Lower of Cost/Market, to either ALL at Cost or ALL at Market Value, I can simply use the column total for these values in the Stock On Hand table at year end rather than the total for the Lower of Cost/Market column.
