I maintain an Excel spreadsheet that is updated daily with the % gain/loss on
my stock brokerage accounts. How do I change the formula to calculate the
accurate % gain in my stock portfolio when I add cash equity to one of the
accounts? If I don't change the cost basis in the formula, the % gain in the
account increases excessively because it thinks I have gained all of the
added money as interest over the preceding 24 hours. If I change the cost
basis to account for the new money added to the account, my % gain year to
date decreases (from what it was the day before adding the cash equity)
because the cost basis is now higher than it was the day before. I want to
be able to change the formula going forward each time I add cash equity, but
still have an accurate year-to-date % gain from interest I have earned.
Thanks.