Most investors have the wrong idea about what the real performance of their investments is. The difficulty is that most approaches to calculating returns involve a simple formula:

  • R ={A}/{B}
  • A-revenue received
  • B-initial investment

Let’s imagine a scenario in which a person invested $10,000 in January and $70,000 in December. By the end of the year, the investment account was $80,000 (securities increased in price). What is the return on investment? How should you calculate it? If we take the income of 10,000 and divide it by the sum of all investments, 80,000, we get very difficult-to-interpret results – 10%. After all, there was only $10,000 in the account for most of its term, and the extra balance was added only a month before the end of the year.

Now let’s consider another example:

In February, an investor put $100,000 into a broker account, and in December, he took out $90,000. By the end of the year, the broker’s account had a total of $15,000. If you just add up the deposits and withdrawals, it turns out that the total investment is equal to 100,000 – 90,000 = 10,000. Dividing the income by the total investment, we get an overly optimistic 50%. Obviously, you can’t do that.

That is why we suggest using Excel for portfolio management.

How to Make an Excel Project Portfolio Management Template

excel investment template

The main parameters are used on the main page in the table. To fill in the table and use it correctly, you need to know the following parameters. 

Indicator– usually the ISIN, the international identification code of the security.

Currency — filled according to the currency codes: USD, EUR, GBP.

Purchase date– used to calculate the yield and determine the value in USD (you can change to your local currency) at the time of purchase. 

Quantity — can be measured in pieces or in square meters.

The purchase price is recorded together with the broker commission (sometimes you have to pay a significant commission for a transaction, ignoring this cost would be a mistake).

IRR for Excel Investment Tracking

One of the simplest and most common ways to measure the performance of investments is to calculate the IRR- which is not exactly a return. Formally, the IRR (Internal Rate of Return) is the interest rate at which the present value of cash receipts (in – offs) is equal to the size of the initial investment.

An IRR is very common in business and finance. This value is used to calculate, for example, the profitability of projects in business. Similarly, the yield to maturity is calculated for bonds. IRR can be considered a kind of standard for measuring performance.

Another important advantage is that an IRR is easily calculated in Excel and other spreadsheets.

If the IRR is less than the Deposit Rate in City Bank, then you need to think about whether everything is normal with the investment strategy.

NOTE: Please leave a comment if you want to receive our EXCEL TABLE with automatic calculations for the IRR

How to Show Rebalancing in the Portfolio

excel for portfolio management

Rebalancing is one of the central topics in investments. Without it, you will not be able to maintain the right balance of risks and returns from investments. If the portfolio was correctly compiled, then rebalancing becomes an additional source of profit. You should not expect any miracles, but your “half income %” per year can be obtained only by rebalancing.

The process of rebalancing a portfolio is quite simple – but despite its simplicity, you must ask yourself the following questions:

  • How often should I rebalance my portfolio?
  • How do I calculate the price of selling and buying the assets?

It is quite easy to calculate the necessary sales and purchase amounts yourself in Excel or any other form of spreadsheets. We made a simple Excel project portfolio dashboard that allows you to calculate all the necessary amounts for a diversified multi-currency portfolio.

The screenshot of the calculator shows a situation in which the target asset ratio has already been set. The current scenario is that the investor has only 4139.86 YUAN. The column in the “Purchase Size” table shows how much each of the assets needs to be purchased in order to get the target ratio.

It is easy to see that the calculator has two sections:

  • Securities in Yuan
  • Securities in US dollars

NOTE: If you want, you can add other sections in the desired currency.

Conclusion

We can share any of these tables and give you more details on how to use them. Just contact the Einvestment team; if you need assistance in portfolio and investments, our professional team will help you. Creating an investment portfolio can be easily done, even if you are just a beginner!