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)) }