Lessons in FM: Part V - Gains and Losses

Note: This is a continuation of the series Lessons in Financial Mathematics. Reading previous posts about this topic may aid in your understanding of this article, but may not be necessary for this one. Also note that this topic is by request.

I got an email from a reader asking me to shed some light on the mystery of how to calculate your portfolio return from a mess of buys and sells that is done over the course of a period of time (the reader suggested one month). To be honest, this is not an easy problem. Even Maelstrom Baphomet has (more or less) admitted defeat on this issue.

Please take note that the tracking of financial position is a question perhaps better posed to accountants, but I'm going to show you how I would do it. I'm going to use some techniques accountants will (hopefully) recognize, but without some rigidity they demand. I will also be publishing another Lesson in FM which will be a continuation of this topic, but dealing with how to find the rate of return for your portfolio over a given time period.

I'm first going to tackle the problem of finding the (Linden) dollar value of your return and then address the problem of calculating the rate of return for the given time period. Throughout this article, I will be using one month as the time period in question.

For those of you wanting to track your gains and losses precisely, I'm going to need a few things from all of you who want to know your gains and losses.

• First, keep track of every transaction you make, including date, ticker, share amount, and share price on Excel. A few of the exchanges also allow you to download this information. I'll be starting with a CapEx download style and building from there.
• Secondly, make sure you have some way of notating which is a buy and a sell. I'm going to use separate columns for each type (debits and credits, anyone?).
• Lastly, if you can spare a few seconds, having some notes in your spreadsheet will help you out greatly when you look over it later.

It is now is a good time to talk about realized vs. unrealized gains. By now, I'm guessing most of you know by now that the value your portfolio shows at most exchanges is a fictitious number. The only way to achieve such a value would be to sell every one of your shares at the last traded price, plus the commission. While this is normally not possible or practical, even trying to get an idea of the liquidation value may be impossible, as most exchanges show only the top bid/ask prices for each security, and you may have more shares than that to sell (liquidate). Thus, if you can't tell what the whole market's prices are, you have no way of knowing how many Linden dollars you could get for your shares.

Because of this, unrealized gains can be nearly impossible to track. I'm going to give you a way to track the realized gains/losses (things that either directly cost you cash or gave you cash) and let you estimate the unrealized gains/losses as you so choose. To find the total profit/loss, then, just add the two. For the rate of return calculation, you will need to estimate your unrealized gains/losses, but for the realized portion it is not necessary.

To compute realized gains/losses, you need to know how much you have paid for a security. At first, this may seem like a nightmare, as you may have purchased different amounts at different prices, making some sort of odd average seem very difficult. However, if we just use some Excel commands, we can easily compute the total number of shares held along with how much was paid for them.

Here is a spreadsheet with some transactions (all made-up) for the month of December. Apologies to any companies whose tickers I used inadvertently. I've only got 8 transactions in there to keep life simple, so that readers can check my formulas without too much trouble if need be. I've also listed the beginning and ending balances for the month. The bolded portion of the worksheet represents the part that comes straight off of the download from CapEx - everything else I've added. (Note that because the CapEx format lists cash flows from sells as negative and buys as positive, you must subtract the sum of those cash flows rather than adding them to your beginning balance.)

You'll have to do some scrolling to see the full worksheet. What I've done is separate out the buys and the sells so that I can add them more easily later. I've put buys on the left and sells on the right, and done it for both the cash value traded, as well as the number of shares traded. IF() statements are very useful for doing this quickly.

In Excel, there is a very nice command called SUMIF, which takes the following arguments: SUMIF(lookup_range,condition,sum_range). "lookup_range" refers to the range where the condition is located. "sum_range" is then the corresponding range to sum if the condition is met. For my purposes, the ticker symbol is a nice condition to sum on. Using SUMIF, you get the small chart I have at the bottom-right.

I want to go over the formula I have in the "realized gains/(losses)" cell, however. It reads:

ROUND(Cash_In - Cash_Out * (Shares_Sold / Shares_Bought), 2)

What I'm doing is averaging what your price paid for the stock was, and what you sold it for. All of this is on average. Cash_In is fine as is - you get 100% (less commission) of your sale price as a gain. The Cash_Out, on the other hand, may have been for more shares than you sold. Say you wanted to reduce your holdings in a company. You may still hang on to some shares, but you may have realized a profit on the ones that you did sell. Because of this, I'm multiplying the Cash_Out by the percentage of your shares that you sold. In the line detailing ABC, for example, the investor sold 80% of the holdings, so only 80% of the Cash_Out is applied to the Cash_In when computing these gains/losses.

The final cell calculates a basis for the remaining shares, although not complete in the strict, IRS sense of the word. However, it will provide a nice way for you to track shares carried over from month-to-month using that as your price.

In my happy example, the investor has realized L$103.79 in gains.

A few final comments:

1. I am not an accountant. Any accountants out there who would like to critique my methods, please do so.
   
2. I realize you all cannot view the formulas and that may be less than helpful if you don't have much experience with Excel. If you would like an Excel copy of this spreadsheet, email me at guardian.market@gmail.com.
3. My First Life job requires me to be pretty disciplined at Excel, but nevertheless I do make mistakes. (Every once in awhile, Excel makes mistakes, too!) Always check at least a few parts of your calculations by hand or calculator when making spreadsheets.

Happy tracking!