How to calculate average price of stock in excel

If you bought all of your stock in a single transaction, it's easy to determine how your investment is performing. Simply look at the current share price and compare it to the price you paid.

Table of Contents Show

Site Rules - New Users - Please Read
Site Rules - New Users - Please Read
How do you calculate average stock price?
What is formula for average in Excel?
How do you calculate average monthly stock price?

However, if you bought your shares in several transactions at different price points, and bought a different number of shares each time, evaluating your investments' performance is a little more complicated. In this case, the best method is to calculate a "weighted average" of the prices you paid. Your broker can probably help you sort things out -- and if you don't have a broker, be sure to check out our Broker Center -- but in the meantime, let's take a closer look a weighted average.

What is a weighted average?
A weighted average is a method of finding the average value of a group of numbers, which takes into account how many times each number occurs, or its importance. A common real-world example is the calculation of a grade-point average in schools, where an "A" carries a greater weight than a "B", which carries a greater weight than a "C", and so on.

How to calculate your weighted average price per share
When it comes to buying stock, a weighted average price can be used when shares of the same stock are acquired in multiple transactions over time. This is necessary if the transactions were for different numbers of shares, since the larger purchases contribute more to the average. For example, the mathematical average of $100 and $200 is $150, but if you bought 10 shares of stock at $100 and only one share at $200, the lower-priced shares carry more weight when calculating the average price you paid.

In order to calculate your weighted average price per share, you can use the following formula:

In words, this means that you multiply each price you paid by the number of shares you bought at that price. Then, add up all of these results. Finally, divide by the total number of shares you purchased.

This may sound a little complicated, so let's look at an example to illustrate how it works.

An example
Let's say that you own 500 shares of Microsoft, and you acquired your shares in three separate transactions. You bought the following number of shares at each of the following price points.

150 shares at $100
250 shares at $200
100 shares at $300

In order to calculate your weighted average price per share, simply multiply each purchase price by the amount of shares purchased at that price, add them together, and then divide by the total number of shares. Written as an equation, it looks like this:

Why it's useful
Knowing the weighted average price you paid for each share of stock can help you determine how your investment is performing as a whole, relative to the current share price.

This article is part of The Motley Fool's Knowledge Center, which was created based on the collected wisdom of a fantastic community of investors. We'd love to hear your questions, thoughts, and opinions on the Knowledge Center in general or this page in particular. Your input will help us help the world invest, better! Email us at [email protected]. Thanks -- and Fool on!

The Motley Fool has no position in any of the stocks mentioned. Try any of our Foolish newsletter services free for 30 days. We Fools may not all hold the same opinions, but we all believe that considering a diverse range of insights makes us better investors. The Motley Fool has a disclosure policy.

Here is my excel sheet having buying details on the left and the selling details on the right.
A2 is the total buying average.
I2 is the total selling average.
E2 is the remaining Stock.
E4 is the Cell where I need to get the desired result of average cost of remaining (unsold) stock.

I have tried to explain it while using the data of rows 6 and 7. The average buying cost of remaining (unsold) stock.

=((C6*(B6-H6)) + (C7*(B7-H7)))/((B6-H6)+(B7-H7))

Is there any formula or shortcut method to get the desired result for whole of the sheet data, i.e. average cost of all remaining (unsold) stock.

11.3 KB Views: 26

I would suggest
= SUM(buy.quantity) - SUM(sell.quantity)
for the remaining stock and
= (SUM(buy.amount) - SUM(sell.amount)) / (SUM(buy.quantity) - SUM(sell.quantity))
for the average price of remaining stock.

Do NOT forget to keep both the Excel forum and this one (as well as any other?) informed of positive results!

unsethi
As a new member, You should reread Forum Rules:

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

I would suggest
= SUM(buy.quantity) - SUM(sell.quantity)
for the remaining stock and
= (SUM(buy.amount) - SUM(sell.amount)) / (SUM(buy.quantity) - SUM(sell.quantity))
for the average price of remaining stock.
Do NOT forget to keep both the Excel forum and this one (as well as any other?) informed of positive results!

Deducting sell amount from buy amount would not help as it would deduct the profit of sold quantity from remaining stock prices which will eventually give the profit/loss adjusted buying average.

Sorry, for posting it on multiple forum. I will surely post the answered solution on all the forums.

Sorry for posting on multiple forums. I will post solutions on each forum once I get it.

unsethi
As a new member, You should reread Forum Rules:

Site Rules - New Users - Please Read
Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org

I have gone through the rules now. It won't happen again but please someone give me the solution.

unsethi
Good that You have reread it
as well as You've noticed that You'll post after Solution.
Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

This is the last.. I had posted it on three forums simultaneously. I will post the solution everywhere as soon as I get it on any forum.

Sorry about that; brain out of gear! I think
= SUM( (1-sell.quantity/buy.Quantity)*buy.amount ) / SUM(buy.Quantity-sell.quantity)
would be closer to the mark. The first sum is over the proportion remaining multiplied by the purchase cost and the second sum is the remaining stock.

If you are using anything but the latest 365 version of Excel the formula will need to be committed with CSE as an array formula. The alternative is to define a named formula e.g. averageCost to hold the formula. Any formula that is not on the grid is and always was evaluated as an array calculation.

13.6 KB Views: 27

This is the last.. I had posted it on three forums simultaneously. I will post the solution everywhere as soon as I get it on any forum.

Sorry about that; brain out of gear! I think
= SUM( (1-sell.quantity/buy.Quantity)*buy.amount ) / SUM(buy.Quantity-sell.quantity)
would be closer to the mark. The first sum is over the proportion remaining multiplied by the purchase cost and the second sum is the remaining stock.
If you are using anything but the latest 365 version of Excel the formula will need to be committed with CSE as an array formula. The alternative is to define a named formula e.g. averageCost to hold the formula. Any formula that is not on the grid is and always was evaluated as an array calculation.

That's really great. It is very correct. But I have an issue. I am unable to use named range as I have multiple sheets in one workbook. So I have to name range on every worksheet.
I tried using the formula using the cell reference but the result is different from yours.
The same values I used but getting different result -
=SUM((1-H2/B2)*SUM(A6:A11))/E2

Can you please write the formula without reference to Named Range and with reference to Cells only.
Or is there any way to name the same range differently on different sheets in one go?

Hi @unsethi . I have described the practice of direct referencing of cells by location as an abomination that should never have entered the world of serious spreadsheet building . Reading between the lines you may gather that I am not totally enamoured of the idea.

One way of unravelling the solution to use direct referencing, is to select Formulas / Use in Formula / Paste Names ... to create a list of names along with the formulas they refer to. You can then substitute each formula back in place of the name [as the Excel calculation does]. This gives
= SUM( (1 - $H$6:$H$11/$B$6:$B$11) * $A$6:$A$11 ) / SUM($B$6:$B$11 - $H$6:$H$11)

Unfortunately, moving the formula from the defined name to the grid allows the traditional spreadsheet to make a further dog's breakfast of the calculation. Instead of performing the calculations as written, Excel will helpfully pick out a single cell (the one on the same row as the formula) from each array and use that. This process is called implicit intersection.

The way to instruct Excel

not

to use implicit intersection is to commit the formula using Ctrl+Shift+Enter.

If you wish to evaluate the option of retaining names, one way of achieving that is to copy the initial sheet as a template for further sheets that are intended to perform like calculations. Instead of getting new names, each sheet introduces a further copy of the each name but with scope local to the particular sheet so Sheet2!sell.quantity is a different value from Sheet3!sell.quantity. Any formula on a sheet will pick up the Name local to that sheet if it exists. More information than you ever wished to know …?

I am a novice to MS Excel and trying to learn.
Direct referencing also didn't helped, then I tried in my sheet by naming range as you did in your sheet.
Then I tried to apply same formula by changing the your named range to my named range but that gave an error #Value!
I know it might be irritating for you, but please bear with me and guide me through so that I may not indulge in copy/paste and try to learn by applying my self. Named ranges are same as yours but I cut their names short and indicative. Can you please tell what mistake I did?
Here is the screen shot and also the file is attached.

11.4 KB Views: 4

Your only error was to miss out on committing the formula with Ctrl+Shift+Enter. That tells Excel that you intend the calculation to apply to the entire column rather than just cells on the current row [your formula gave an error rather than a value because the range references do not intersect the row with the formula]. To use CSE, you write the formula and, with the cursor still in the formula bar, you simultaneously press the Ctrl and Shift keys and, while they are pressed, click Enter. It is a pain; it is easily forgotten; and it strikes me as being hard work just to instruct Excel

NOT

to select single cells out of your arrays before calculating.

I used the named formula averageCost to get Excel to calculate the array formula without needing CSE. It is not a basic technique; novices or intermediate spreadsheet users will not know it and it may well be that many expert users do not approve. Despite that, I used it on just about every spreadsheet I wrote, until modern dynamic arrays made array calculation the default, so special tricks were no longer needed.

In the attached, I have taken copies of the worksheet and show that what are apparently the same names give different values on different sheets.

18.2 KB Views: 15

Last edited: May 6, 2020

Ok.
I just tried to use CSE and it gave the same result as yours. Now could you please guide me how to name a formula, so that I don't require to use CSE.
And while naming the formula, would I be able to name it for different sheets, the same way I would be naming the ranges for different sheets?

One more thing, when any new entry would be made in excel will the named range automatically cover that new entry or we have to change the definition of the range?

Could you please give the formula for Sold quantity profit?

Last edited: May 7, 2020

If you think of the process as two steps, the first is to apply a Name to the cell containing the formula, scoped to worksheet. This can either be done by typing the fully qualified name into the Name Box, e.g.
Sheet1!RSAvgRate
or you can open Name Manager and the New... button. Do not forget to set the Scope as well as entering the Name.

Then replace the cell reference by the formula it contains, first copy the formula from the formula bar. Then reopen Name Manager, select the Name you are about to redefine, and paste the formula into the Refers to box (starting with the '=').

Return to the (previously named) cell and replace the formula with the newly defined name
=RSAvgRate

No, unfortunately defined names do not extend automatically. You would need to extend the range references from Name Manager. The advantage is that any formula that references the name will then update. This is inconvenient, but marginally better than traditional techniques that require any formulas that use direct references to be located and updated individually.

There are ways of making Named Ranges dynamic by using the OFFSET function in a formula but, since 2007, Excel Tables offer a better route to creating dynamic ranges. I have introduced the latter on the second sheet.

20.1 KB Views: 14

If you use Excel tables and table references, then references will update as the table grows and shrinks.

Apart from PQ tables, I have never seen a table shrink... (apart from .resize VBA stuff or a manual resize)

Guido
True the normal use case is that one continues to add data to the point where the solution collapses under its own weight!
For solutions that are truly dynamic though, it makes sense to replace old input by new and reuse an existing solution for new problems. If the new dataset is accessed through Power Query the Table will resize. Otherwise there is a manual step to adjust the resize button (more of a fly speck than a button but I don't know what else to call it).

Maybe it is traditional spreadsheet practice that is not really built for reuse?

If you use Excel tables and table references, then references will update as the table grows and shrinks.

Thanks for this helpful suggestion.

Apart from PQ tables, I have never seen a table shrink... (apart from .resize VBA stuff or a manual resize)

It does if you delete rows from it!

If you think of the process as two steps, the first is to apply a Name to the cell containing the formula, scoped to worksheet. This can either be done by typing the fully qualified name into the Name Box, e.g.
Sheet1!RSAvgRate
or you can open Name Manager and the New... button. Do not forget to set the Scope as well as entering the Name.
Then replace the cell reference by the formula it contains, first copy the formula from the formula bar. Then reopen Name Manager, select the Name you are about to redefine, and paste the formula into the Refers to box (starting with the '=').
Return to the (previously named) cell and replace the formula with the newly defined name
=RSAvgRate
No, unfortunately defined names do not extend automatically. You would need to extend the range references from Name Manager. The advantage is that any formula that references the name will then update. This is inconvenient, but marginally better than traditional techniques that require any formulas that use direct references to be located and updated individually.
There are ways of making Named Ranges dynamic by using the OFFSET function in a formula but, since 2007, Excel Tables offer a better route to creating dynamic ranges. I have introduced the latter on the second sheet.

Extension of the formula for the whole of the column has been solved by the Table creation.
Regarding RSAvgRate, I am afraid that I couldn't understand what you tried to say?

I wish to have formula for calculation profit of the sold units.
Is that possible to have? I have tried hard but failed. Please guide.

It does if you delete rows from it!

Haha, you got me there.... #Schooled.

I believe the formula you require is
= SUM( IF( SQntyTP>0, SQntyTP * (SRateTP - BRateTP) ) )

I have used a Table for the data and implemented the calculation as a named formula, but whether it simply mystifies you further I am not sure ?

16.1 KB Views: 13

Last edited: May 11, 2020

How do you calculate average stock price?

How to calculate average stock price?.

n = q1 + q2 + ... + qi — Total number of shares bought;.

q1 — Number of shares 1st buy;.

p1 — Shares purchase price 1st buy;.

q2 — Number of shares 2nd buy;.

p2 — Shares purchase price 2nd buy;.

qi — Number of shares last buy; and..

pi — Shares purchase price last buy..

What is formula for average in Excel?

For example, if the range A1:A20 contains numbers, the formula =AVERAGE(A1:A20) returns the average of those numbers.

How do you calculate average monthly stock price?

Finally, you may be obtaining data from an annual statement that shows you the stock's value at the start of the year and its value at the end of the year. Subtract the starting value from the ending value to obtain the total return for the year, then divide by 12 to obtain the monthly average.