Tracking a stock portfolio #2

This is follow up post to: Tracking a stock portfolio in excel (auto update)
In this post we are going to calculate cost basis and returns. The calculations are simplified, commissions, stock splits and dividends are removed from calculations.

In the first post we created dynamic ranges.

We also identified accumulated stocks and the number of shares. I have now added cost basis and returns.

Excel formula in cell C2:

Copy cell C2 and paste down as far as needed.

How this formula works in cell C2

Step 1 - Calculate total cost you paid

=IF(A2<>"", (SUMPRODUCT((A2=Symbol)*(Type="Buy")*Shares*Price)-SUMPRODUCT((A2=Symbol)*(Type="Sell")*Shares*Price))/(SUMPRODUCT((A2=Symbol)*(Type="Buy")*Shares)-SUMPRODUCT((A2=Symbol)*(Type="Sell")*Shares)), "")

SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays

SUMPRODUCT((A2=Symbol)*(Type="Buy")*Shares*Price)

becomes

SUMPRODUCT((F={F, MSFT, MSFT, F, GOOG, MSFT, GOOG, GOOG, GOOG}))*({Buy, Buy, Buy, Buy, Sell, Sell, Buy, Buy, Sell}=Buy)*{100, 100, 50, 100, 25, 50, 10, 100, 200}*{12, 25, 28, 16, 550, 24, 500, 500, 550})

becomes

SUMPRODUCT(({TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE}))*({TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, TRUE, TRUE, FALSE})*{100, 100, 50, 100, 25, 50, 10, 100, 200}*{12, 25, 28, 16, 550, 24, 500, 500, 550})

becomes

SUMPRODUCT({1200, 0, 0, 1600, 0, 0, 0, 0, 0}) returns 2800.

Step 2 - Calculate total amount you sold

=IF(A2<>"", (SUMPRODUCT((A2=Symbol)*(Type="Buy")*Shares*Price)-SUMPRODUCT((A2=Symbol)*(Type="Sell")*Shares*Price))/(SUMPRODUCT((A2=Symbol)*(Type="Buy")*Shares)-SUMPRODUCT((A2=Symbol)*(Type="Sell")*Shares)), "")

SUMPRODUCT((A2=Symbol)*(Type="Sell")*Shares*Price)

becomes

SUMPRODUCT({0, 0, 0, 0, 0, 0, 0, 0, 0}) returns 0.

Step 3 - Calculate accumulated shares

=IF(A2<>"", (SUMPRODUCT((A2=Symbol)*(Type="Buy")*Shares*Price)-SUMPRODUCT((A2=Symbol)*(Type="Sell")*Shares*Price))/(SUMPRODUCT((A2=Symbol)*(Type="Buy")*Shares)-SUMPRODUCT((A2=Symbol)*(Type="Sell")*Shares)), "")

(SUMPRODUCT((A2=Symbol)*(Type="Buy")*Shares)-SUMPRODUCT((A2=Symbol)*(Type="Sell")*Shares

becomes

SUMPRODUCT({100, 0, 0, 100, 0, 0, 0, 0, 0}) - SUMPRODUCT({0, 0, 0, 0, 0, 0, 0, 0, 0})

and returns 200.

Step 4 - Calculate cost basis

=IF(A2<>"", (SUMPRODUCT((A2=Symbol)*(Type="Buy")*Shares*Price)-SUMPRODUCT((A2=Symbol)*(Type="Sell")*Shares*Price))/(SUMPRODUCT((A2=Symbol)*(Type="Buy")*Shares)-SUMPRODUCT((A2=Symbol)*(Type="Sell")*Shares)), "")

becomes

=IF(A2<>"", (2800-0)/200, "") returns 14 in cell C2.

Excel formula in cell D2

Copy cell D2 and paste down as far as needed.

How this formula works in cell D2

Step 1 - Calculate average price you paid per share

=IF(A2<>"", (IFERROR(SUMPRODUCT(--(A2=Symbol), --("Sell"=Type), Price, Shares)/SUMPRODUCT(--(A2=Symbol), --("Sell"=Type), Shares), 0)-IFERROR(SUMPRODUCT(--(A2=Symbol), --("Buy"=Type), Price, Shares)/SUMPRODUCT(--(A2=Symbol), --("Buy"=Type), Shares), 0))*(MIN(SUMPRODUCT(--(A2=Symbol), --("Sell"=Type), Shares), SUMPRODUCT(--(A2=Symbol), --("Buy"=Type), Shares))), "")

(IFERROR(SUMPRODUCT(--(A2=Symbol), --("Sell"=Type), Price, Shares)/SUMPRODUCT(--(A2=Symbol), --("Sell"=Type), Shares), 0)

returns 0.

Step 2 - Calculate average selling price

=IF(A2<>"", (IFERROR(SUMPRODUCT(--(A2=Symbol), --("Sell"=Type), Price, Shares)/SUMPRODUCT(--(A2=Symbol), --("Sell"=Type), Shares), 0)-IFERROR(SUMPRODUCT(--(A2=Symbol), --("Buy"=Type), Price, Shares)/SUMPRODUCT(--(A2=Symbol), --("Buy"=Type), Shares), 0))*(MIN(SUMPRODUCT(--(A2=Symbol), --("Sell"=Type), Shares), SUMPRODUCT(--(A2=Symbol), --("Buy"=Type), Shares))), "")

IFERROR(SUMPRODUCT(--(A2=Symbol), --("Buy"=Type), Price, Shares)/SUMPRODUCT(--(A2=Symbol), --("Buy"=Type), Shares)

returns 14.

Step 3 - Multiply with bought or sold shares

=IF(A2<>"", (IFERROR(SUMPRODUCT(--(A2=Symbol), --("Sell"=Type), Price, Shares)/SUMPRODUCT(--(A2=Symbol), --("Sell"=Type), Shares), 0)-IFERROR(SUMPRODUCT(--(A2=Symbol), --("Buy"=Type), Price, Shares)/SUMPRODUCT(--(A2=Symbol), --("Buy"=Type), Shares), 0))*(MIN(SUMPRODUCT(--(A2=Symbol), --("Sell"=Type), Shares), SUMPRODUCT(--(A2=Symbol), --("Buy"=Type), Shares))), "")

MIN(SUMPRODUCT(--(A2=Symbol), --("Sell"=Type), Shares), SUMPRODUCT(--(A2=Symbol), --("Buy"=Type), Shares))

returns 0.

Step 4 - Calculate returns

=IF(A2<>"", (IFERROR(SUMPRODUCT(--(A2=Symbol), --("Sell"=Type), Price, Shares)/SUMPRODUCT(--(A2=Symbol), --("Sell"=Type), Shares), 0)-IFERROR(SUMPRODUCT(--(A2=Symbol), --("Buy"=Type), Price, Shares)/SUMPRODUCT(--(A2=Symbol), --("Buy"=Type), Shares), 0))*(MIN(SUMPRODUCT(--(A2=Symbol), --("Sell"=Type), Shares), SUMPRODUCT(--(A2=Symbol), --("Buy"=Type), Shares))), "")

becomes

=IF(A2<>"", (0-14)*0) returns 0

Final notes

I am not telling you to sell or buy any stock, this is just an example.

I hope I got all calculations right.

Get excel file

Tracking-a-stock-portfolio2.xlsx
(Excel 2007 -2010 Workbook *.xlsx)

Finance category

Automate net asset value (NAV) calculation on your stock portfolio
Table of Contents Automate net asset value (NAV) calculation on your stock portfolio Calculate your stock portfolio performance with Net […]

Schedule recurring expenses in a calendar in excel (Personal Finance)
Below is an excel table containing recurring expenses and corresponding amounts, dates and recurring intervals. An excel table allows you to […]

Bill reminder in excel
Brad asks: I'm trying to use your formulas to create my own bill reminder sheet. I envision a workbook where […]

Excel categories

Comments (5)