Calculating the overall profit or loss on a share portfolio

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

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.

If this solves things for, please mark my answer as Selected.

Cheers   :-)