Using IRR to calculate your rate of return

With the rise of private equity investments and the associated lack of standardized reporting such investments contain, it becomes more important for wealthy investors to calculate their own rates of return. One way to do this is using an internal rate of return (“IRR”) method.

An Internal Rate of Return (“IRR”) is a widely accepted way to calculate an investment’s rate of return.

Stock market investors have easy access to real-time valuation of their returns. Regulations also require registered investment advisors to provide you with standardized & regulated performance reports. The same isn’t true in the world of private equity.

Investors in most private equity and venture capital funds will need to calculate their own rates of return to evaluate performance. But this can be tough since most times, the only information private equity investors have are their cashflows. In other words, the amount invested, and the amount returned.

With only their cashflows, private equity investors can calculate performance using IRR as described in this post.

How to calculate IRR

Once you have the dates & cashflows associated with an investment, you can use an IRR formula in Microsoft Excel or Google Sheets to calculate your rate of return. Essentially what an IRR calculation will do is spit out an annualized discount rate related to a series of cashflows.

The example below uses the XIRR function in Excel to calculate the rate of return based on the cashflows of the following investment:

Column A (dates)	Column B (cashflows)
01-06-2021	-$500,972.40
01-09-2021	$3,766.50
01-12-2021	$3,766.50
01-03-2022	$3,968.00
01-06-2022	$3,968.00
01-09-2022	$3,968.00
01-12-2022	$3,968.00
01-01-2023	$314,216.00

In this case, an initial investment of $500,972 was made on June 1^st, 2021. Then, distributions from the investment were received quarterly until the balance of the capital worth $314,216 was returned on Jan 1^st, 2023. Using the XIRR function in Excel, we write =XIRR(dates,values), or =XIRR(A2:A20,B2:B20) and Excel tells us the IRR is -22.57%.

Here’s another example using an uneven series of cashflows into and then out of an investment:

Column A	Column B
30-06-2019	-$160,748.37
30-06-2019	-$10,000.00
30-09-2019	-$63,127.26
31-10-2019	-$153,145.59
31-01-2022	$167,861.15
31-03-2022	$115,075.24
30-04-2022	$25,319.62
30-06-2022	$77,651.17
30-09-2022	$179,468.62
31-12-2022	$36,298.78

In this case, the IRR is +17.34%.

The main benefit of using the XIRR function is that it will account for unevenly timed cash flows.

What if you haven’t got your capital back yet? Can you still use IRR to calculate your rate of return?

Many times, we invest in something like a fund or stock, but we want to determine our rate of return even though our capital is still invested.

In other words, say you purchased shares in Toronto-Dominion Bank for $100,000 in 2015. You receive dividends quarterly, and you want to determine your rate of return since that time, but you haven’t sold your shares yet.

You can still calculate your rate of return using an IRR method like the XIRR function in Excel. Simply determine the cost & date your investment was made, the amounts & dates of dividends you received, and the current market value of your investment:

Column A	Column B
30-06-2019	-$100,000.00	Initial Investment
28-09-2019	$1,000.00	Dividend
27-12-2019	$1,000.00	Dividend
26-03-2020	$1,100.00	Dividend
24-06-2020	$1,100.00	Dividend
22-09-2020	$1,100.00	Dividend
21-12-2020	$1,100.00	Dividend
21-03-2021	$1,200.00	Dividend
19-06-2021	$1,200.00	Dividend
17-09-2021	$175,000.00	Current Value

In this case, your IRR is 32.61%.

What about evaluating my entire stock portfolio or the value of an entire account?

You can also use an IRR method to calculate the returns of an entire investment account.

Simply, assume a negative number as the account value on the date of inception (or from when you want to begin measurement). Then add the amounts you withdrew from the account and subtract the amounts you deposited. Finally, assume a positive number for the closing value of the account:

Column A	Column B
30-06-2019	-$100,000.00	Beginning Value
28-09-2019	-$10,000.00	Deposit
27-12-2019	$10,000.00	Withdrawal
26-03-2020	$25,000.00	Withdrawal
24-06-2020	-$10,000.00	Deposit
22-09-2020	-$15,000.00	Deposit
21-12-2020	$11,000.00	Withdrawal
21-03-2021	$12,000.00	Withdrawal
19-06-2021	$10,000.00	Withdrawal
17-09-2021	$90,000.00	Ending Value

In this case, your IRR was 10.82%, even though the ending value of the account was lower than the amount you initially invested. In between, you made a few deposits and withdrawals. The XIRR formula takes these into consideration and weights them by time.

Our Family Office

Most investors don’t measure or evaluate their financial rates of return. This is true for working class investors and wealthy investors alike.

The result is most investors are overpaying for investment advice and not aligning their returns with their objectives.

Our family office provides regular reports to our clients that clearly evaluate their financial performance (independent of their financial advisors). This reporting will not only evaluate your financial returns but will also measure & evaluate your philanthropic impact too.

For more information about how our family office can help you measure & evaluate your returns, please click here to contact us.