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
Forgot screenshot
