**Internal Rate of Return**

In case anyone else in interested in performing this same computation, here are the details.

It is possible to compute a rate of return known as the "internal rate of return" or IRR. Those familiar with the PIA software will be familiar with this concept, since IRR forms a good basis for making "apples to apples" comparisons of different property investments.

I will state nice and early, before the explanation, and before you are thinking that you need to be a rocket scientist to understand this computation, that Microsoft Excel can easily compute this value for you (an example spreadsheet is attached), **as long as you give it the right numbers to work with**.

What is IRR?

The IRR is a computation which takes a series of cash inflows and cash outflows at particular dates and then computes a **single rate of return** (the internal rate of return) which produces a nett present value of 0.

Whoa... seems like gobbledegook, eh? Well I'll start with a simple explanation.

Let's say that 10 years ago you paid $100 into a bank account. Four years later (ie. 6 years ago), you contributed another $300 to the account. Today you decide to close the account and withdraw the current balance of the account, which is $740. Did we invest well? We want to calculate the internal rate of return.

There are 3 cashflows in this example:

A cashflow of $-100 ten years ago.

A cashflow of $-300 six years ago.

A cashflow today of $+740.

Cash outflows are shown negative here and cash inflows are shown positive. Note that in order to compute IRR there must be at least one positive and one negative cashflow.

The three cashflows shown occurred over a period of 10 years. What is each cashflow worth today? Obviously the $740 cashflow hasn't changed value because it actually occurred today. As for the other cashflows, that would depend on the interest rate that the cashflow was "earning" at the time. The whole point of the IRR calculation is to compute what interest rate they **were** earning.

This brings us to the subject of present value. If we invested an amount some time in the past at a particular constant interest rate, we can compute what it's present value is today. For example, if $100 was invested 10 years ago at a rate of 10%, then:

I (Interest) = 0.1 (10%)

V (Value) = 100

Y (Years) = 10

G (Growth) = ( 1 + I ) ^ Y

PV = V * G

In our case G = 2.59, so the $100 invested 10 years ago is today worth $259.

Had the $100 instead been invested at a rate of 20%, then G = 6.19 and the $100 is today worth $619.

The IRR calculation is an iterative calculation. It must be solved by an iterative sequence of "guesses" until we are sufficiently close to the result. The first iteration takes an educated guess as to what the rate of return is, and then uses that rate of return to compute the present value of each cashflow. The sum of the present values is then computed. If the sum equals zero, we have found the correct rate. If the sum is above zero, we [probably] need to increase our guess. If the sum is below zero we [probably] need to decrease our guess.

Let's illustrate this with an example. We take an educated guess that the rate of return is 5%.

$-100 ten years ago: G = 1.62, so $-100 is now worth $-162.

$-300 six years ago: G = 1.34, so $-300 is now worth $-402.

$+740 today: is now worth $740.

Sum: (-162) + (-402) + (+740) = 176

Since our Sum > 0, we obviously need to increase our guess. Let's try 10% instead:

$-100 ten years ago: G = 2.59, so $-100 is now worth $-259.

$-300 six years ago: G = 1.77, so $-300 is now worth $-531.

$+740 today: is now worth $740.

Sum: (-259) + (-531) + (+740) = -50.

Since our Sum < 0, we need to reduce our guess. Let's try 9% instead:

$-100 ten years ago: G = 2.36, so $-100 is now worth $-236.

$-300 six years ago: G = 1.68, so $-300 is now worth $-504.

$+740 today: is now worth $740.

Sum: (-236) + (-504) + (+740) = 0.

The sum is now 0, so 9% is the internal rate of return.

When calculating IRR it is useful to consider all cash inflows and outflows from the perspective of your purse/wallet (or your bank account). If money does not physically enter your wallet or leave your wallet then it is not a cash inflow or outflow.

In the case of a managed fund you have a period of cash outflows (your contributions) and at the end you have a cash inflow (the redemption of your units in the managed fund). Along the way the managed fund might also pay you income, which are additional cash inflows.

**Note:** income which is paid to you is different to reinvested earnings. Reinvested earnings are neither cash inflows or outflows because they didn't end up in your wallet. Incorrectly including these types of distributions as a cashflow gives the impression that you have received more cash inflows than you really did, and as such the IRR will accordingly show a higher rate.

Understanding the IRR is all well and good, but what about making the computation practical? Microsoft Excel has three different functions for computing IRR, but the one I have found most useful is the XIRR( ) function, which can handle arbitrary dates and arbitrary values.

The attached spreadsheet illustrates the use of Microsoft Excel for computing IRR based on series of uneven cashflows. Each cashflow is dated. Cash outflows are negative (out of your wallet), and cash inflows are positive (into your wallet). The example only shows one cash inflow which is the amount expected on redemption on a given date. The spreadsheet could be extended to add additional cash inflows relating to income.