Rafiul Haq
Managing money is a vital skill, and it is even more important while trading. In this article, we will show you a step-by-step guide on how to create a money management Excel sheet for trading.
Download Practice Workbook
Money Management
Money management is the art of investing and spending your hard-earned money. This is a crucial skill that everyone needs in their lives. Knowledge of money management is a must for trading. Without a proper understanding of this, one may suffer a devastating loss.
The money management sheet for trading can be used to determine the maximum loss or risk that can be tolerated. Those who want to stay in the trading market for a long period must learn about this. Because luck or random decisions will only work for a short time, and they will eventually run out.
Step-by-Step Procedures to Create Money Management Excel Sheet for Trading
There will be four main components of our money management Excel sheet for trading. Those are:
- “Key Factors”.
- “Date for Trades”.
- “Probable Trades (Qty Calculation)”.
- “Actual Trades”.
This is the final snapshot of our money management Excel sheet for trading. We will show you how we have created this in the following steps.
Step 1: Inputting Key Factors for Trading
In this initial step, we will include the crucial elements of trading money management.
- To begin with, type the following key factors:
- “Beginning Capital” → the initial investment from an individual to start this trading.
- “Worst Case Loss” → the maximum amount of loss that a person can suffer, which ranges from 20% to 30%.
- “Risk per Trade” → this is the amount of risk that will be taken for each trade and it should be less than 2%.
- “Income” → the profit or loss generated from actual trading.
- “Current Capital” → summation of the initial capital and the profit or loss amount.
- Next, type the amount of beginning capital.
- After that, the “worst case loss” is equal to 25% of the “beginning capital”. The industry’s standard risk ranges from 20% to 30%. So, we type the following formula in cell C6.
=C5*25%
- Then, we keep our risk to 1%. This risk should be between 0.5% and 2%. If you take less than 0.5%, then your risk will be too low. So, type this formula in cell C7.
=C5*1%
- Lastly, add the start and exit dates for your trade. Moreover, we will input the values for the “Income” and “Current Capital” cells in the final step.
Read More: How to Multiply Time by Money in Excel (with Easy Steps)
Step 2: Trade Quantity Calculation
In the second step, we will calculate the trade volume from our risk percentages.
- Firstly, type the following fields:
- Stock → List the company you want to buy or sell.
- Status → Short or Long.
- Start Price → The price of the stock on the starting date.
- Stop Loss → To limit your loss.
- Risk → The difference between the “Start Price” and the “Stop Loss”.
- Qty → We will calculate this from these values and the risks from the first step.
- Secondly, type the details of the probable trades.
- Thirdly, type this formula in cell F13 to find the risks related to Company A’s stock.
=D13-F13
- Now, our values are higher in the Start Price column, if yours are not, then you can use this formula to get positive values.
=ABS(D13-F13)
- Then, AutoFill the formula into the rest of the cells.
- Next, we will find the quantity (Qty) of the trades for each company’s stocks.
- Type this formula into cell G13.
=MROUND($C$7/F13,5)
- This formula divides the “Risk per Trade” amount by the “Risk of each company’s stock”. Then it rounds up or down to the nearest multiple of 5.
- Then, press ENTER and then AutoFill the formula to the rest of the cells.
- Lastly, this is what the final output of the second step will look like.
Read More: If a Value Lies Between Two Numbers Then Return Expected Output in Excel
Similar Readings
- How to Make a Forest Plot in Excel (2 Suitable Examples)
- [Fixed!] Up and Down Arrows Not Working in Excel (8 Solutions)
- How to Make a Box Plot in Excel (With Easy Steps)
- Make Sankey Diagram in Excel (with Detailed Steps)
Step 3: Adding Actual Trades
In this third step, we will add the values from a real trade to our money management Excel sheet. Moreover, we will be using the IF function to calculate profit or loss from the trades.
- At first, type the following data into the sheet:
- Stock → List the company you want to buy or sell, taken from the last step.
- Status → Short or Long, taken from the last step.
- Start Price → The price of the stock on the starting date, taken from step 2.
- Qty → obtained from step 2.
- Stop Loss → To limit your loss, obtained from the last step.
- Exit Price → The selling price of the stocks.
- Income → Profit or loss gained from selling the stocks, which we will calculate using a formula.
- Then, type in the relevant data.
- Afterward, type the following formula in cell H19.
=IF(C19="Long", G19*E19-D19*E19, (D19*E19-G19*E19))
- Then, press ENTER and AutoFill the formula into the rest of the cells.
Formula Breakdown
- The logical_test of this formula is C19 = ”Long”. If this is true, then the first part of the formula executes. Alternatively, the second part of the formula will be executed.
- Our formula reduces to → IF(TRUE,-1005,1005)
- Output: -1005.
- As this is true so the first part of the formula will be executed.
- Thus, we get the output.
Read More: How to Calculate Cost of Funds in Excel (with Easy Steps)
Step 4: Calculating Total Income from Trades
Now, in the final step, we will be using the SUM function to get the total profit or loss for the trade, and we will fill in the last two cells from the first step.
- Firstly, type the following formula in cell C8. This formula adds all the profit or loss from all the trades
=SUM(H19:H21)
- Then, press ENTER.
- After that, we will find the amount of “Current Capital” by typing this formula in cell C9.
=C5+C8
- Finally, this will conclude our steps for creating a money management sheet for trading.
Read More: How to Fix Formula in Excel (9 Easy Methods)
Things to Remember
There are a lot of factors that affect trading. So there is no definite way to manage trading. Therefore, you should not base your trading decisions on this Excel sheet. It is always best to consult with professionals about managing your hard-earned money.
Conclusion
We have demonstrated how to create a money management Excel sheet for trading in just four simple steps. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. Moreover, you can visit our site ExcelDemy for more Excel-related articles. Thanks for reading, and keep doing well!
Related Articles
- How to Calculate WACC in Excel (with Easy Steps)
- Add Trailing Zeros in Excel (2 Easy Ways)
- How to Make Flashcards in Excel (2 Suitable Ways)
- Add Signature in Excel (3 Quick Ways)
- How to Show Menu Bar in Excel (2 Common Cases)
- Make a Venn Diagram in Excel (3 Easy Ways)
- Descriptive Statistics – Input Range Contains Non-Numeric Data
