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.
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.
How to Create Money Management Excel Sheet for Trading: Step-by-Step Procedures
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.
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.
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.
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.
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.
Download Practice Workbook
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. Thanks for reading, and keep doing well!
<< Go Back to Money Management | Finance Template | Excel Templates
Get FREE Advanced Excel Exercises with Solutions!