Usually, the investor deposits or withdraws various amounts to/from the account during the investment process. In this case, a reasonable question is how to calculate profitability.
There are multiple approaches to calculating the amount of profitability in such cases, but the fastest and the most accurate is to use a function in Excel called XIRR.
The IRR is a metric used in finance to measure the profitability of potential investments and compare the attractiveness of each of them.
IRR is a time based concept. The IRR is the net annual return earned by the investor over a period of time and calculated on the basis of the incoming and outgoing cash flows.
The function takes two arrays as arguments: an array of cash flow values (deposits/withdrawals) and an array of dates on which these flows were received (with a plus sign) or paid (with a minus sign).
Let’s consider an example. On January 1, 2020, an investor invested 1 million USD, then on June 1 he invested 600k USD more and on September 1 of the same year he withdrew 400K USD.
As of January 1, 2021, the portfolio is worth 1.37 million USD. Let's calculate the profitability of the investor's portfolio.
We enter the data into an Excel spreadsheet. Deposit of funds — with a minus sign, withdrawal of funds and the final amount — with a plus sign. Next, we apply the XIRR() function as follows:
This function provides the result as a yearly percentage. If the investment period is less than a year, then the resulting number is recalculated considering the investment period, that is, the result is divided by 365 days and multiplied by the number of days in the period.
Only deposit and withdrawal operations, as well as other income and expenses, considered if they did not affect the currency balance in the portfolio (for example, you received a tax deduction and decided to spend it rather than put it to the brokerage account).
As the final value (the current value of the portfolio), the sum of all holding values AND the cash balance are added together.
Current balance/Not tracked
Transactions of purchase, sale, dividends, commissions, expenses and income are considered.
The final value (the current value of the portfolio) would be the sum of all holding values, excluding cash balances.