Computing profit and loss for a portfolio

This note provides a simple example how to compute the P/L for a portfolio of stocks over time. To run the example, we'll need three pieces of information, called J, P and timestamp.P. They are defined at the end of the example.

1 Computing P/L

Suppose we have traded three stocks: AMZN, AAPL and MSFT. Here is our trade list; in PMwR, this would be a 'journal'.

| timestamp        | amount |   price | instrument |
|------------------+--------+---------+------------|
| [2019-12-27 Fri] |     10 |  1869.8 | AMZN       |
| [2020-02-10 Mon] |    -10 | 2133.91 | AMZN       |
| [2020-01-30 Thu] |    -10 | 1870.68 | AMZN       |
| [2019-12-30 Mon] |    -10 | 1846.89 | AMZN       |
| [2020-01-15 Wed] |     10 | 1862.02 | AMZN       |
| [2020-01-10 Fri] |     10 |  310.33 | AAPL       |
| [2020-01-23 Thu] |     20 |  319.23 | AAPL       |
| [2019-12-02 Mon] |     10 |  264.16 | AAPL       |
| [2019-12-16 Mon] |    -10 |  279.86 | AAPL       |
| [2020-02-12 Wed] |    -10 |   327.2 | AAPL       |
| [2019-11-27 Wed] |     20 |  152.32 | MSFT       |
| [2019-12-24 Tue] |    -20 |  157.38 | MSFT       |
| [2020-02-20 Thu] |     10 |  184.42 | MSFT       |
| [2019-12-02 Mon] |    -10 |  149.55 | MSFT       |
| [2020-01-06 Mon] |     10 |  159.03 | MSFT       |

This list was actually created randomly. (See below for how the trades were sampled.) In R, these trades are stored in a journal J.

J
    instrument   timestamp  amount    price
1         AMZN  2019-12-27      10  1869.80
2         AMZN  2020-02-10     -10  2133.91
3         AMZN  2020-01-30     -10  1870.68
4         AMZN  2019-12-30     -10  1846.89
5         AMZN  2020-01-15      10  1862.02
6         AAPL  2020-01-10      10   310.33
7         AAPL  2020-01-23      20   319.23
8         AAPL  2019-12-02      10   264.16
9         AAPL  2019-12-16     -10   279.86
10        AAPL  2020-02-12     -10   327.20
11        MSFT  2019-11-27      20   152.32
12        MSFT  2019-12-24     -20   157.38
13        MSFT  2020-02-20      10   184.42
14        MSFT  2019-12-02     -10   149.55
15        MSFT  2020-01-06      10   159.03

15 transactions

The function pl computes profit or loss.

pl(J)
AAPL
  P/L total           NA
  average buy   303.2375
  average sell    303.53
  cum. volume         60

AMZN
  P/L total           NA
  average buy    1865.91
  average sell  1950.493
  cum. volume         50

MSFT
  P/L total           NA
  average buy   162.0225
  average sell    154.77
  cum. volume         70

‘P/L total’ is in units of instrument;
‘volume’ is sum of /absolute/ amounts.
‘sum(amount)’ is not zero for AAPL: specify ‘vprice’ to compute P/L.
‘sum(amount)’ is not zero for AMZN: specify ‘vprice’ to compute P/L.
‘sum(amount)’ is not zero for MSFT: specify ‘vprice’ to compute P/L.

But as you see, it can't: all trades are still open.

position(J)
     2020-02-20
AAPL         20
AMZN        -10
MSFT         10

Thus, all the P/L values are NA. To actually compute the P/L, provide a valuation price for the open positions.

pl(J, vprice = c(AMZN = 2095.97, MSFT = 178.59, AAPL = 313.05))
AAPL
  P/L total        202.1
  average buy   303.2375
  average sell    308.29
  cum. volume         60

AMZN
  P/L total        236.9
  average buy   1942.597
  average sell  1950.493
  cum. volume         50

MSFT
  P/L total        -51.9
  average buy   162.0225
  average sell   160.725
  cum. volume         70

‘P/L total’ is in units of instrument;
‘volume’ is sum of /absolute/ amounts.
 for AAPL: average sell includes ‘vprice’
 for AMZN: average buy includes ‘vprice’
 for MSFT: average sell includes ‘vprice’

Now, suppose we wanted P/L for particular periods. Then we'll need to value the portfolio, at least at the beginning/end of every period. Assume we value the portfolio every day. To do that, we need the daily prices of the assets, which are stored in a matrix P. Every column of the matrix holds the prices of one asset; every row holds the prices for one day. The dates that correspond to the rows of P are stored in timestamp.P.

str(P)
num [1:66, 1:3] 1739 1753 1753 1746 1735 ...
- attr(*, "dimnames")=List of 2
 ..$ : NULL
 ..$ : chr [1:3] "AMZN" "AAPL" "MSFT"
str(timestamp.P)
Date[1:66], format: "2019-11-15" "2019-11-18" ...

To compute P/L over time, pass the matrix P as vprice, and also the timestamp at which the portfolio is to be valued.

PL <- pl(J, vprice = P, along.timestamp = timestamp.P)

This invocation of pl deserved a line of its own, because this is where all the work is done. It now remains to extract the P/L: we now have the cumulative P/L of the assets for every day, stored in a matrix PL.

PL <- sapply(PL, `[[`, "pl")
tail(PL, 5)
            AAPL   AMZN  MSFT
2020-02-14 440.1 -152.1   6.4
2020-02-18 321.1 -360.1   6.4
2020-02-19 413.5 -505.6   6.4
2020-02-20 347.1 -334.4   6.4
2020-02-21 202.1  236.9 -51.9

So finally, the see the P/L for a particular period, simply look at the changes in total P/L for that period. For instance, for monthly P/L:

library("datetimeutils")
ii <- c(1, nth_day(timestamp.P,  ## extract position of last day of month
                   period = "month", n = "last",
                   index = TRUE))
diff(PL[ii, ])
             AAPL   AMZN  MSFT
2019-11-29    0.0    0.0 -18.8
2019-12-31  157.0 -229.1  38.5
2020-01-31 -202.6   86.6 -13.3
2020-02-21  247.7  379.4 -58.3

A final sanity check: Summing the monthly profits should result in the same numbers as the overall profits computed above. (That is the case because the vprice passed there happened to be the latest prices in P.)

colSums(diff(PL[ii, ]))
 AAPL  AMZN  MSFT
202.1 236.9 -51.9

2 Data

library("PMwR")

P <- structure(c(1739.49, 1752.53, 1752.79, 1745.53,
                 1734.71, 1745.72, 1773.84, 1796.94,
                 1818.51, 1800.8, 1781.6, 1769.96, 1760.69,
                 1740.48, 1751.6, 1749.51, 1739.21, 1748.72,
                 1760.33, 1760.94, 1769.21, 1790.66,
                 1784.03, 1792.28, 1786.5, 1793, 1789.21,
                 1868.77, 1869.8, 1846.89, 1847.84, 1898.01,
                 1874.97, 1902.88, 1906.86, 1891.97,
                 1901.05, 1883.16, 1891.3, 1869.44, 1862.02,
                 1877.94, 1864.72, 1892, 1887.46, 1884.58,
                 1861.64, 1828.34, 1853.25, 1858, 1870.68,
                 2008.72, 2004.2, 2049.67, 2039.87, 2050.23,
                 2079.28, 2133.91, 2150.8, 2160, 2149.87,
                 2134.87, 2155.67, 2170.22, 2153.1, 2095.97,
                 265.76, 267.1, 266.29, 263.19, 262.01,
                 261.78, 266.37, 264.29, 267.84, 267.25,
                 264.16, 259.45, 261.74, 265.58, 270.71,
                 266.92, 268.48, 270.77, 271.46, 275.15,
                 279.86, 280.41, 279.74, 280.02, 279.44,
                 284, 284.27, 289.91, 289.8, 291.52, 293.65,
                 300.35, 297.43, 299.8, 298.39, 303.19,
                 309.63, 310.33, 316.96, 312.68, 311.34,
                 315.24, 318.73, 316.57, 317.7, 319.23,
                 318.31, 308.95, 317.69, 324.34, 323.87,
                 309.51, 308.66, 318.85, 321.45, 325.21,
                 320.03, 321.55, 319.61, 327.2, 324.87,
                 324.95, 319, 323.62, 320.3, 313.05, 149.97,
                 150.34, 150.39, 149.62, 149.48, 149.59,
                 151.23, 152.03, 152.32, 151.38, 149.55,
                 149.31, 149.85, 149.93, 151.75, 151.36,
                 151.13, 151.7, 153.24, 154.53, 155.53,
                 154.69, 154.37, 155.71, 157.41, 157.41,
                 157.38, 158.67, 158.96, 157.59, 157.7,
                 160.62, 158.62, 159.03, 157.58, 160.09,
                 162.09, 161.34, 163.28, 162.13, 163.18,
                 166.17, 167.1, 166.5, 165.7, 166.72,
                 165.04, 162.28, 165.46, 168.04, 172.78,
                 170.23, 174.38, 180.12, 179.9, 183.63,
                 183.89, 188.7, 184.44, 184.71, 183.71,
                 185.35, 187.23, 187.28, 184.42, 178.59),
               .Dim = c(66L, 3L),
               .Dimnames = list(
                   NULL, c("AMZN", "AAPL", "MSFT")))

timestamp.P <- structure(
    c(18215, 18218, 18219, 18220,
      18221, 18222, 18225, 18226,
      18227, 18229, 18232, 18233,
      18234, 18235, 18236, 18239,
      18240, 18241, 18242, 18243,
      18246, 18247, 18248, 18249,
      18250, 18253, 18254, 18256,
      18257, 18260, 18261, 18263,
      18264, 18267, 18268, 18269,
      18270, 18271, 18274, 18275,
      18276, 18277, 18278, 18282,
      18283, 18284, 18285, 18288,
      18289, 18290, 18291, 18292,
      18295, 18296, 18297, 18298,
      18299, 18302, 18303, 18304,
      18305, 18306, 18310, 18311,
      18312, 18313), class = "Date")

J <- structure(list(timestamp = structure(
                        c(18257, 18302, 18291, 18260, 18276, 18271,
                          18284, 18232, 18246, 18304, 18227, 18254,
                          18312, 18232, 18267), class = "Date"),
                    amount = c(10, -10, -10, -10, 10, 10, 20,
                               10, -10, -10, 20, -20, 10, -10, 10),
                    price = c(1869.8, 2133.91, 1870.68, 1846.89,
                              1862.02, 310.33, 319.23, 264.16,
                              279.86, 327.2, 152.32, 157.38, 184.42,
                              149.55, 159.03),
                    instrument = c("AMZN", "AMZN", "AMZN", "AMZN",
                                   "AMZN", "AAPL", "AAPL", "AAPL",
                                   "AAPL", "AAPL", "MSFT", "MSFT",
                                   "MSFT", "MSFT", "MSFT")),
               class = "journal")

The journal J was actually sampled as follows.

J <- journal()
for (asset in colnames(P)) {

    amount <- sample(c(-20, -10, 10, 20), size = 5, replace = TRUE)
    timestamp <- sample(timestamp.P, size = 5, replace = TRUE)
    J <- c(J,
           journal(amount = amount,
                   timestamp = timestamp,
                   price = P[match(timestamp, timestamp.P), asset],
                   instrument = asset))
}