i have purchased the stock like below
Date Qty Price Amt
06-Jan 50 65.1 3255
11-Jan 152 66.08 10044.16
29-Jan 8 68.88 551.04
23-Apr 31 80.2 2486.2
03-May 31 78.75 2441.25
and my total holdings are
Total Amt 18777.65
Avg Price 69.04
based on the above
Curr Price 83.11
My price 69.04
my Max Price 80.2
my Min price 65.1
i want to calculate like
mid-50 72.65
same like mid-60, mid-75, mid-90
based on the above price i want to make desision
Buy, Sell, Hold
can you please assist this?
Absolutely, Rajkumar! Let's break it down and build a clear way to help you make Buy/Sell/Hold decisions based on price levels like mid-50, mid-60, mid-75, mid-90, etc.
| Metric | Value |
|---|---|
| Total Investment | ₹18,777.65 |
| Total Qty | 272 (sum of all units) |
| Avg Price (You gave) | ₹69.04 |
| Current Price | ₹83.11 |
| Max Buy Price | ₹80.20 |
| Min Buy Price | ₹65.10 |
✅ You've got a profit since current price ₹83.11 > avg price ₹69.04.
A "mid" level (e.g. mid-50) usually means halfway between a round number and your max/min/avg price. But since you want mid-50, mid-60, mid-75, mid-90 levels, I'll assume these are price levels at which you want to evaluate decisions.
Let’s define your evaluation table like this:
| Level | Action Criteria | What to Do |
|---|---|---|
| Mid-50 (₹72.65) | Below Avg Price (₹69.04) | Buy if price < ₹72.65 (still a good deal) |
| Mid-60 | Near Avg Price | Buy/Hold, depending on trend |
| Mid-75 | Above Avg but Below Max | Hold/Sell partial to book profit |
| Mid-90 | Well above Max Price | Sell more to realize profit |
But first, let’s calculate the actual mid-levels based on your data.
Let’s do midpoints based on price range:
- Min Price: ₹65.10
- Max Price: ₹80.20
- Avg Price: ₹69.04
- Current Price: ₹83.11
Let’s now compute:
| Label | How it's Calculated | Value (₹) |
|---|---|---|
| Mid-50 | 50% between Min (₹65.1) and Max (₹80.2) | ₹72.65 |
| Mid-60 | 60% between Min and Max | ₹74.17 |
| Mid-75 | 75% between Min and Max | ₹76.88 |
| Mid-90 | 90% between Min and Max | ₹79.61 |
Now based on current price ₹83.11:
| Mid-Level | Decision | Reason |
|---|---|---|
| Mid-50 (₹72.65) | Hold/Sell | Price has crossed this long ago |
| Mid-60 (₹74.17) | Hold/Sell | Profit zone |
| Mid-75 (₹76.88) | Sell Partial | Good zone to book some profit |
| Mid-90 (₹79.61) | Sell More | Strong profit – consider selling more |
| Current (₹83.11) | Sell | Above Max Buy Price – consider selling |
Since ₹83.11 > ₹80.20 (your max buy) and you're 20.4% up from your average price:
📌 Recommendation: SELL partially or fully Lock in profit unless you expect strong upside in the near term.
Would you like me to help you build an Excel or Google Sheet where you can plug in prices and get decisions automatically?
yes
Great! Here's what I can do for you:
I'll guide you through building a smart tracker in Google Sheets that:
-
Lets you input your stock buys.
-
Automatically calculates:
- Total investment
- Average price
- Mid-levels (mid-50, mid-60, mid-75, mid-90)
- Current price (you can enter manually)
- Suggested action: Buy / Hold / Sell
In Sheet1, create the following structure:
| A | B | C | D |
|---|---|---|---|
| Date | Qty | Price | Amount |
| 08-Jan | 50 | 65.1 | =B2*C2 |
| 13-Jan | 152 | 66.08 | =B3*C3 |
| 31-Jan | 8 | 68.88 | =B4*C4 |
| 25-Apr | 31 | 80.2 | =B5*C5 |
| 05-May | 31 | 78.75 | =B6*C6 |
Then below the table:
| A | B |
|---|---|
| Total Qty | =SUM(B2:B6) |
| Total Amount | =SUM(D2:D6) |
| Avg Price | =B8/B7 |
| Min Price | =MIN(C2:C6) |
| Max Price | =MAX(C2:C6) |
| Current Price | (you input manually, say ₹83.11) |
| Mid-50 | =B10 + 0.5*(B11 - B10) |
| Mid-60 | =B10 + 0.6*(B11 - B10) |
| Mid-75 | =B10 + 0.75*(B11 - B10) |
| Mid-90 | =B10 + 0.9*(B11 - B10) |
| A | B |
|---|---|
| Recommendation | =IF(B12>B17,"Sell", IF(B12>B16,"Sell Partial", IF(B12>B15,"Hold", "Buy"))) |
This logic says:
- Sell if current price > Mid-90
- Sell Partial if price > Mid-75
- Hold if price > Mid-60
- Else Buy
If you'd like, I can create a Google Sheet and share the link with you. You’ll just need to make a copy.
Do you want me to prepare a shareable link?
Also i want to calculate profit from my average price?
Sure! We can calculate your profit based on your average purchase price and the current price. This will give you a quick view of your total profit and percentage gain.
Here's how you can do it:
The profit per share is simply:
Profit per Share = Current Price - Average Price
The total profit is calculated by multiplying the Profit per Share by your total quantity of shares.
Total Profit = (Current Price - Average Price) × Total Quantity
To get your percentage gain, use the formula:
Percentage Gain = ((Current Price - Average Price) / Average Price) × 100
Now, we can add this profit calculation to your Google Sheet.
| A | B |
|---|---|
| Current Price | (you input this, e.g., ₹83.11) |
| Average Price | =B8/B7 |
| Profit per Share | =B12 - B13 |
| Total Profit | =(B12-B13)*B7 |
| Percentage Gain | =((B12-B13)/B13)*100 |
- Current Price: ₹83.11
- Average Price: ₹69.04
- Total Quantity: 272
Using the formula:
- Profit per Share: ₹83.11 - ₹69.04 = ₹14.07
- Total Profit: ₹14.07 * 272 = ₹3,823.04
- Percentage Gain: ((₹83.11 - ₹69.04) / ₹69.04) * 100 = 20.4%
So you would be up by ₹3,823.04 or 20.4%!
Your Google Sheets structure now should look like this:
| A | B |
|---|---|
| Total Qty | =SUM(B2:B6) |
| Total Amount | =SUM(D2:D6) |
| Avg Price | =B8/B7 |
| Min Price | =MIN(C2:C6) |
| Max Price | =MAX(C2:C6) |
| Current Price | (input manually, say ₹83.11) |
| Mid-50 | =B10 + 0.5*(B11 - B10) |
| Mid-60 | =B10 + 0.6*(B11 - B10) |
| Mid-75 | =B10 + 0.75*(B11 - B10) |
| Mid-90 | =B10 + 0.9*(B11 - B10) |
| Profit per Share | =B12 - B13 |
| Total Profit | =(B12-B13)*B7 |
| Percentage Gain | =((B12-B13)/B13)*100 |
| Recommendation | =IF(B12>B17,"Sell", IF(B12>B16,"Sell Partial", IF(B12>B15,"Hold", "Buy"))) |
Would you like me to create a Google Sheet template with all these formulas in place? I can share it with you so you can just plug in your values.