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 you can do a simple percentage change function comparing the current NAV with the original NAV to see how you are doing. And, just for fun, every January you can create another cell with the Jan. 1 NAV so that you can do a running year-to-date performance calculation.

This is the trick: When you deposit or withdraw cash, you treat it as though you are buying or selling the NAV units — that is, you add or subtract the appropriate number of units. (Be prepared, fractions are involved here!) So, cash in or cash out changes the number of units in the account but not the net asset value of the units. Any transaction that affects the performance of the account (dividends paid, interest earned, changes in stock value, commissions, etc) is simply added to or subtracted from the account balance. Those transactions alone will affect the NAV.

It may take a moment for this to sink in. An example might help: Say one of the positions in your $10,000 portfolio is 100 shares of an Internet start-up company that is worth $10 per share today. Tomorrow it shoots up to $20 per share. When you import that new price, your account balance goes up to $11,000. The NAV cell will automatically show a NAV of $11 ($11,000 divided by 1000 units). The change in Net Asset Value has gone from $10 to $11, a 10% increase that directly reflects your portfolio performance. (Of course, that is also the percentage change in the total value of the account. But wait. )

Suppose the next day you add $1,000 in cash to the account. When you add the cash, you have to increase the number of units. Your cash buys you another 90.9091 units at $11 each. Thus, the transaction won’t increase the NAV per unit even though the total value of the account has increased to $12,000. Each unit is still worth $11 — but you have changed the Units cell to read 1,090.9091 units. And if, next week, you decide to withdraw cash, you divide the NAV into the amount you want to withdraw to see how many units you need to sell (subtract) when you make your cash withdrawal. The cash withdrawal will reduce the account balance, but, because you also proportionally reduce the number of units, it won’t change the NAV per unit. Your fabulous performance is intact.

This, by the way, is a general explanation of how the Fool Portfolios, mutual funds, and most investment software programs handle cash flows.