How to Create Money Management Excel Sheet for Trading

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.

Money Management Excel Sheet for Trading


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.

Money Management Excel Sheet for Trading 2

  • 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%

Money Management Excel Sheet for Trading 3

  • 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%

Money Management Excel Sheet for Trading 4

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

Money Management Excel Sheet for Trading 5

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.

Money Management Excel Sheet for Trading 6

  • Secondly, type the details of the probable trades.

Money Management Excel Sheet for Trading 7

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

Excel Sheet for Trading 8

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

Money Management Excel Sheet for Trading 9

  • Then, press ENTER and then AutoFill the formula to the rest of the cells.

AutoFill

  • Lastly, this is what the final output of the second step will look like.

Money Management Excel Sheet for Trading 10


Similar Readings


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.

Actual Trades Blank

  • Then, type in the relevant data.

Money Management Excel Sheet for Trading 11

  • Afterward, type the following formula in cell H19.

=IF(C19="Long", G19*E19-D19*E19, (D19*E19-G19*E19))

Sheet for Trading 12

  • Then, press ENTER and AutoFill the formula into the rest of the cells.

Sample Dataset 1

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.

Actual Trades


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.

Calculating Overall Profit or Loss

  • After that, we will find the amount of “Current Capital” by typing this formula in cell C9.

=C5+C8

Finding Current Capital

  • Finally, this will conclude our steps for creating a money management sheet for trading.

Money Management Excel 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.


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

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I am passionate about all things related to data and MS Excel is my favorite application. I want to make people's life easier by writing easy-to-follow and in-depth guides here at Exceldemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo