Unrealized Gain and Loss

Excel 2016
For the analysis of the investment portfolio, it is helpful to see the unrealized gain or loss. These amounts show how many you will receive or lose if you realize all available stocks right now. Sure, there are some fees for the operations that can decrease gain or increase loss, but, at least, you want to see the least approximate amounts:
Unrealized Gain and Loss in Excel 365

To create pie charts like ones above, do the following:

   1.   Create or add a data range:

   a)   with stock ticker symbols and prices (see How to view current stocks prices and other quotes in Excel).

For example:

Data for charts in Excel 365

   b)   with transactions (if you have a fixed quantity, you can add them without an additional calculation).

For example:

Transactions for charts in Excel 365

   2.   Add new columns to calculate current value, Gain and Loss:

  • Quantity (if you have fixed quantity, you can add them without additional calculation) using the following formula:

    = SUMIFS (Transactions[Quantity], Transactions[Symbol], "="&<symbol>)


    • SUMIFS is a function to sum cells that meet multiple criteria:

      SUMIFS (<the range to be summed>, <the first range to evaluate>, <the criteria for the first range>, [<the second range>], [<the criteria for the second range>], ...)

    • Transactions is a table from p.1.b),
    • <symbol> is a stock symbol from column C.

    For the cell E4 this formula is:

    = SUMIFS (Transactions[Quantity], Transactions[Data], "="&C3):

    Quantity for charts in Excel 365
  • Value using the formula:

    = <price> * <quantity>

    For the cell F4 this formula is:

    = D4 * E4:

    Value for charts in Excel 365

    Note: You can hide the price and quantity data and use the formula:

    = <stock>.Price * SUMIFS (Transactions[Quantity], Transactions[Symbol], "="&<symbol>)

    Where, <stock>.Price is a reference to the Price field of the Stocks data type.

    For example:

    Value formula for charts in Excel 365
  • Gain:

    = IF ((<value> - <cost>) > 0, <value> - <cost>, 0)


    • <value> is a calculated amount from the previous step,
    • <cost> can be calculated like a <quantity> using the formula:

      = SUMIFS (Transactions[Amount], Transactions[Symbol], "="&<symbol>)

    For this example:

    Gain for charts in Excel 365
  • Loss:

    = IF ((<cost> - <value>) > 0, <cost> - <value>, 0)

    For this example:

    Loss for charts in Excel 365

   3.   Select the data range to create a pie chart.

For this example, C3:C7 and E3:E7.

   4.   On the Insert tab, in the Charts group, click the Insert Pie or Doughnut Chart button:

Insert Pie or Doughnut Chart in Excel 365

Choose the Pie chart Pie Chart in Excel 365:

Simple pie chart in Excel 365

Make any other adjustments to get the look you need.

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.