Calculating Internal Rate of Return and Time-Weighted Return

In my article on annual returns, I suggested you save time by looking at online calculators for both internal rate of return (IRR) and time-weighted (TWR). However, many people want a spreadsheet showing their investment performance over time.  To create one from scratch, read on.

Internal Rate of Return

As you remember, the IRR is what many fund companies or brokers (if they provide this information) refer to as personal performance. It evaluates not only the performance of the investment manager, but also the effect of your deposits and withdrawals on the investment.

There are several ways to set up a spreadsheet to calculate IRR; I use the one that makes the most sense to me. Feel free to condense this or use other sources if they make more sense to you.

To set this up, I just have two columns of information – Cash Flow and Date. Here I list any deposits into or withdrawals from the investment and the date on which they happened.

Let’s look at a simple example:

You decide to invest $1,500 in ABC Company on January 2, $1,000 on March 3, and $1,700 on July 8. On September 14, you withdraw $500. At the end of the year, your investment is worth $4,580. What’s your IRR?

What you’re actually finding here is your XIRR, which takes the different dates into account (instead of assuming you made the investments at the same intervals throughout the year as IRR does).

So your formula would read: =XIRR(A2:A6,B2:B6). For this example, your Internal Rate of Return is 29.97%.

Time-Weighted Return

When you calculate time-weighted returns, you are in essence calculating the returns for each of the time periods when you made a deposit or withdrawal. In other words, using the above example you would have four periods above when you would calculate the return.

Let’s use the same example as above but add in the ending value just before you invested more money into the account (or withdrew money from the account).

	A	B	C	D	E	F
1	Date	Ending Value	Cash flow	Value after Cash Flow	Return	Return +1
2	01/02/2020	$1,500	0	$1,500	n/a
3	03/03/2020	$1,557	$1,000	$2,557	3.80%	1.0380
4	07/08/2020	$2,702	$1,700	$4,402	5.67%	1.0567
5	09/14/2020	$4,308	-$500	$3,808	-2.14%	0.9786
6	12/31/2020	$4,580	0	$4,580	20.27%	1.2027

The great thing about this is that you only need to input the ending value and cash flow. The rest is done for you. (Date is not necessary for the equation, but it makes life simpler when I have several capital flows and I’m going through trade confirmations to determine the ending value).

For the Value after Cash Flow, it’s a simple formula adding ending value to cash flow. So for the January 2^nd time block, it would be =B2+C2.

I start the calculations for Return and Return +1 during the next time period.

For Return, you’re looking at the ending value divided by the previous time period’s value after cash flow. So for the first return (March 3), you would use the formula: =B3/D2-1.

Return + 1 simply adds one to the return, so for March 3: =E3+1.

Finally, to get the time-weighted average, you multiply the Return + 1 column and subtract 1: =PRODUCT(F3:F6)-1.

In this example, the TWR=29.11%.

Looking at these two numbers

Sometimes your IRR and TWR calculations will provide results that are very close. Other times, TWR may be positive when in fact you lost money. It’s all about when your cash flows hit the market. In this example, you were fully invested before the major run up in stock prices. In my original article, I noted an example where TWR was negative because the person had invested a large sum just before the stock price dropped.

If you have any questions or feel the numbers you calculate are incorrect, find an online calculator to double-check your work. There may be minor discrepancies – an online calculator might return a TRW for the example above of 29.20% depending on how many decimal points they allow in their calculations – but they can provide a sanity check if you’re unsure of your work.

Photo by Oleg Magni

Disclaimer