Weighted average. A google sheets template for profit calculation

Thought I’d share this.

If you invest £100 for 360 days and make £10 profit then in almost a year you have made 10%. Easy

But say 5 days before year end you invest an extra £1,000.

You then find after 365 days you have made £20 profit. But is that good? 20/1,100 is 1.8% but that’s just obviously not right since you only held the £1,000 for 5 days, but the £100 for 365.

You want to find effectively what that £1,000 is worth. So if you divide it by 365 and held it for 5 days its like holding £13 for a year.

So in reality your profit of £20/100+13 = 17.7% weighted average.

This is where I’m sharing my way of monitoring profit.

It works for withdrawals and of course for profits over 1 year. You simply record each capital transaction (money in or out of freetrade) and at any time just dump your current profit in to find out how well you are doing.


This is great, thank you for sharing! :slight_smile:

That looks really useful, thanks for sharing. When I get home this evening I’ll start plugging my own numbers in and see how I’m doing with my investments.

(If I could make one small suggestion, for clarity’s sake you could change step 1 of the instructions to “‘Make a Copy’ of this document from the file menu”.)

Done @HoldenCarver

You are welcome @TomShepherd

@Rollingskies remarkable tool. It can give insights on returns on deposited funds in Freetrade which is awesome. I suppose it can also be used to track the performance of individual investments within the platform but I can’t get my head around on how to see it. Am I making any sense here?

Edit: Maybe I could use column F to add a description of what the action refers to?

1 Like

Yes you could do that. You would list stocks on the columns as a b c etc and then date of investing on each stock as rows 1 2 3 etc. But very OCD. :wink:

I would have to use double entry for every buy/sell action otherwise I would completely mess up the totals, right? If yes, very much indeed, (OCD) but worth it. Based on the intensity of my activity it would take up to 2 minutes per month. It’s more than what I can spare but, no pain no gain :+1:

Is there a good way to handle reinvesting dividends? These were not fresh deposits of cash, but they were new buys, sometimes in the same stock, sometimes in new stock.