##### Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

# Calculating the overall profit or loss on a share portfolio

0

I have a range of share options in a portfolio and I can easily work out the % profit or loss on each option individually, however, I cannot quite get my head around how to work out the overall combined % profit or loss given that each option is not the same size.  I have attached a sheet that I have been using to try and solve this.  Can anyone assist?

0

Hi Geoffery

The profit or loss is amount (\$ in your case) the share value moves from the "original" value.

The percentage profit or loss is that movement divided by the original value of a given individual share (or grouping of shares). You can add individual profit or loss (\$) values to combine them into an overall profit or loss but you can't just add the individual percentage profit or loss values.

To calculate the overall percentage, you need to divide the sums of the individual profit or loss (\$) values by the sum of the original values (which you seem to refer to as Start Balances).

Your current spreadsheet doesn't show what the original value so, in the attached revised file, I inserted (now) column C and labelled C5 as "Original Start Balance (deduced)". Pink cells calculate that original value usinng the formula (for D6) like:

``=100*D6/E6``

.

and likewise to row 9 and for rows 13:16.

(Note: that might not produce the exact amount,depending on the accuracy of your \$ and  % figures but you could overtype inexact numbers with the correct values).

Row 10 uses SUM calculations on the \$ values in C and D to give the totals so the overall percentage is easily calculated (in blue cell D10) as:

``=100*D10/C10``

To get the combined percentage profit of loss, the yellow cells in rows 20:23 simply add the category values, e.g. for "Balanced - Risk Adjusted", D5 is just

``=C6+C13``

etc.

The same "blue cell" formula then works just the same. The grey cells E20:E23 calculate "category" percentages from the values in C20:D23.

Note that I didn't do anything with columns !:L (now hidden) or the chart.

Hope this makes sense and fixes your problem. If so. please remember to mark this Answer as Selected (or choose Willie's if that's better for you).

### Discussion

Thanks, that helped and all works well now
geodav52 (rep: 2) Sep 15, '24 at 1:23 am
Glad that helped. Thanks for selecting my Answer (TBH I'd given up any hope of a response, given the delay!)
John_Ru (rep: 6377) Sep 15, '24 at 9:33 am
0

Hello geodav52,

I took a look at your sheet and it seems you have used the wrong formulas. I un-hid rows 11 thru 24 so I could view everything.

Firstly, I see that your portfolio is rows 6 thru 10; Dianne's portfolio is rows 12 thru 16 (there are #REF! errors in line 16); and the combined is rows 19 thru 23 (there are #REF! errors in row 23).

The formulas you have in Col "E" and Col "J" for rows 6 thru 9 are good. You should have used the same formula structure for rows 12 thru 15 (Dianne's) and rows 18 thru 22 (combined) as what you used for rows 6 thru 10. Because of the #REF! errors in lines 16 and 23 I don't know how you arrived at those values, so in my revision of your sheet I took a guess.

The blue cells are the corrected formulas, and the pink ones are the #REF! errors.