The Fool FAQ
I'm trying to prepare my own spreadsheet to track a couple of portfolios. My problem is trying to account for cash coming in and how this effects the percentage gain (loss). How do I add cash to my portfolio without inflating the returns?
Tracking cash inflows (and outflows) is actually quite easy, once you get the concept.
First, don't even try to base portfolio performance on the account balance -- therein lies madness and humiliation. Set up a separate track to calculate the portfolio's performance based on units of Net Asset Value (NAV).
Here's how you do that: Say you have a portfolio valued at $10,000. You start off by arbitrarily dividing the account into some number of units -- let's say 1000 units. Create a cell for number of units and enter 1000. Now create another cell that calculates the NAV of each unit as the account balance divided by the number of units. In this example, the NAV of each unit would be $10.
The NAV is what you will use to track portfolio performance. If you create another cell that contains the initial NAV value (not the formula), then yo