Table of contents:

How to calculate the return on investment
How to calculate the return on investment

This is easier than it sounds.

How to Calculate Return on Investment to Earn More
How to Calculate Return on Investment to Earn More

Why calculate profitability

It is quite easy to buy securities and earn some money on them. The investor does not even need to go anywhere - brokers have moved to mobile applications through which you can purchase different assets in different markets.

Until the investor knows exactly how much he earns, it is difficult for him to save money or to invest it profitably in the future. A person may think that he has excellent results, but a careful calculation will show: not so great, especially in comparison with other instruments, so you need to think about changing assets. Or vice versa: the real profitability is good, and it is worth continuing in the same spirit.

In order to understand all this, you need to understand the relative profitability of the portfolio and calculate how many percent the investor receives per annum.

How to calculate the annual return on investment

Professionals use complex formulas like Sharpe Ratio or Trainor Ratio. For a private investor, this can be useful, but for a start, a table in Excel and a few numbers from the broker's application will be enough.

If the investor has set up a sign and enters all the movements of money, dates, dividend payments and commissions there, then he will be able to calculate everything conveniently. The basic formula looks like this:

Profit (or loss) on the deal + dividends - commissions = profitability

Record assets

Let's say an investor has been buying and selling securities for nine months in a row. He knows how much money came and went, remembers the dates of the operations and did not forget to sign everything. As a result, he has a simple table:

How to calculate return on investment in Excel
How to calculate return on investment in Excel

The investor bought and sold assets, deposited money into the account and withdrawn it, so it is correct to first calculate the net profitability. You just need to substitute the PERFECT formula (or XIRR, it's the same).

How to calculate return on investment in Excel
How to calculate return on investment in Excel

It turns out that the investor earned 18.66% per annum. This is not bad, because the S&P 500 index has grown by 19.6% over the same period.

Pay commissions

Brokers take a percentage from each operation, unless the specific amounts vary - it is best to clarify this in your agreement with a specialist. Often commissions are already "sewn" into the reports, but sometimes they go as an additional line. In this case, it is better to prescribe them separately on the plate.

Suppose an investor pays 0.3% after each purchase or sale of an asset. If he took into account the indicator in advance, then he will not have to use new formulas, the same PERFECT will come down. It turns out that they have earned less.

How to calculate return on investment in Excel
How to calculate return on investment in Excel

Calculate Annual Percentages

But the investor calculated the portfolio's return only for the time that he invested money. This is less than a year, and it is customary to compare the volumes of profit as a percentage per annum. One more formula needs to be added:

Net profitability × days per year / days of investment = annual profitability

In our case, the investor traded securities for 236 days. Let's apply the formula:

How to calculate return on investment in Excel
How to calculate return on investment in Excel

The annual return on investment is 26, 49%. If an investor compares it, for example, with deposits, it turns out that the profitability of his assets is four to five times higher, so it is more profitable to continue to place money in this way. At the same time, the S & P 500 index brought 30.3% per annum over the same period in 2021 - it may be more expedient to invest in the funds that follow it.

How to calculate the return on investment in the future

No analyst, professional investor, or clairvoyant can answer for sure. But you can at least try to estimate this indicator using historical profitability.

So, the investor earned 18.66% per annum in 2021. He studied the profitability of his assets over the previous 5-10 years and realized: on average, such a portfolio brought in 13% per annum.

It's not a fact that everything will happen again in the future. Economic trends are changing, companies are subject to tight regulation, and there is always a threat of a crisis.

But the investor took everything into account and assumes that in the next 10 years the profitability will remain at the average level.

The investor's money remains in the account, because he is saving up for an apartment for his children. All received dividends are reinvested by a person back. In this case, the magic of compound interest is connected:

Account Amount, rubles Profitability Annual profit, rubles
2022 90 400 13% 10 400
2023 102 152 13% 11 752
2024 115 431, 76 13% 13 279, 76
2025 130 437, 89 13% 15 006, 13
2026 147 394, 81 13% 16 956, 92
2027 166 556, 14 13% 19 161, 33
2028 188 208, 44 13% 21 652, 30
2029 212 675, 54 13% 24 467, 10
2030 240 323, 36 13% 27 647, 82
2031 271 565, 39 13% 31 242, 03

If the investor took profit every year and re-invested the same amount, then in 10 years he would have earned 104,000 rubles. But his actions earned him 191,565 rubles - almost twice as much. This is called compound interest, or interest capitalization.

How not to count profitability

Net profitability formulas and commissions allow you to see "fair" numbers. Because the intuitive way of calculating - dividing the current value of the portfolio by investment - will not help. This is only suitable if the investor purchased the assets and sold them exactly one year later.

In reality, a person almost certainly buys something new in a portfolio or sells paper. It will be easy to calculate the profitability of each individual investment, but for the entire portfolio, and even taking into account commissions, it is easier to use formulas and a table.

How to account for taxes on investments

Taxes for an investor in Russia are paid by a broker - so you may not even immediately notice that they were written off. But it is still useful to know how much the state will have to give. It depends on what assets and for how long to acquire.

If a stock, bond or ETF share was purchased more than three years ago, then you can safely sell them and not pay income tax. Let's say an investor with plans for an apartment that he wants to buy in 10 years can invest and not worry that mandatory contributions will affect profitability.

But if the assets have to be sold earlier, then the tax on them will still be withheld - 13%. Except for those cases when the investor recorded a loss: he sold for less than he bought. If there is a profit, then the tax must be paid, but only on the difference between buying and selling.

For example, an investor bought shares of the company "Pervaya" for 80,000 rubles, and the next year sold them for 100,000. For both operations, he gave 0.3% commission to the broker, which is also taken into account in the calculations. You will have to pay so much:

(100,000 - 300 - 80,000 - 240) × 0.13 = 2,529.8 rubles

In addition, there will be payments to the state on dividends and coupons, the same income contribution of 13%. Let's say the dividends of the company "Pervaya" amounted to 7,000 rubles - 910 will be withheld from the investor, which will also affect the profitability.

How to calculate return on investment in Excel
How to calculate return on investment in Excel

Taking taxes into account, the investor will lose 3% of the return on his portfolio - quite a lot, and now investing in the S&P 500 index with an annual return of 30.3% looks even more reasonable. Although this value will be slightly less - due to the fees of funds and taxes.

What is worth remembering

  1. If the investor does not consider the profitability, then he does not understand whether he has successfully invested money and whether it is worth changing the investment portfolio.
  2. It is easier to calculate profits, losses on commissions and taxes if you keep a simple diary table in Excel.
  3. It is best for an investor to determine the profitability using the PERFORMANCE formula - it will take into account the irregular movements of money in the brokerage account.
  4. Sometimes it is more profitable to invest in several funds or indices than to build your own investment portfolio.