Rate of Return for Managed Fund

I would like to think I am quite good at mathematics (and Excel) but this one has me a little beat.

When our daughter was born we immediately set up a managed fund to which we shall contribute $100 per month until she turns 18. (How we'll use that money for her I'm not sure !)

That was in Aug 2001. In Aug 2002 we increased the contributions to $105 per month as a vague way of keeping up with inflation (and will continue to do so each year hopefully). The fund pays distributions quarterly revolving around 30th June. Distributions are reinvested by buying additional units.

(As an aside, the stock-market has taken a battering and based on the amount we have deposited since then and the current unit price, we are behind by around $300 (ouch). But, 18 years is a very long term time frame, so this volatility in the market is nothing I wasn't expecting).

How do I calculate the Rate of Return for an investment of this type, given that the contributions vary (including the distributions)?

My thinking goes something like this:

If I look at each individual purchase of units (ie. each monthly investment, and each distribution amount which is reinvested), then I know the date it occurred, I know the amount, I know the current (todays) date, and I know the current (todays) unit price. For that one "parcel" of units I can therefore say something like:

"I bought $100 of units on 20th Oct 2001 at $3.3582 per unit, giving me 29.77 units. Today's date is 20th Oct 2002, current sell price is $3.6591 per unit. Therefore that $100 has increased to $108.96, over exactly 1 year, which is 8.96% return.

Whether that's appropriate or not, I don't know - but I sure can't figure out a way to use this information to calculate some kind of overall return.

The exact same issue would apply to anyone who purchases irregular parcels of shares for the same company.

Anyone able to help me?



I can't answer your direct questions, but I have a query on your strategy.

Whilst it is noble and sensible to put money away towards your children's future, it is IMHO, only worth doing so in a managed fund or similar, if all your non-deductible expense is expired. That is, unless the managed fund is making greater than twice the interest rate of any non-deductible debt then you would be much better retiring non-deductible debt (eg mortgage) with these payments.

If there is no home mortgage, then next element of strategy would be to have a trust and use said monies ($100/mth??) for more IPs (or shares) or offset to reduce IP interest such that net equity increases and flexibility of trust is maintained to distribute funds.

Bottom line I suspect, is that your kids will end up with all your assets in the long run anyway!

Just my thoughts.

Joe D

If you are not maximising your returns, you are stealing from your family :)
Hi Joe,

Your suggestions were certainly issues I had considered. In our case the managed fund is in my wife's name (rather than in trust) and since she is only a part-time worker the tax implications are relatively small.

Therefore rather than having to achieve a return equal to double our current mortgage interest a return of 8% would exceed the benefit derived from our mortgage.

Most managed funds this year have gone backwards, and everyone says not to rely on past performance as an indicator of future performance, but with the 18 year time span in place on this investment (possibly even 21 years) I think we have a good chance of getting a better return than our mortgage interest rate.

For us, however, there is also a psychological satisfaction in seeing a balance grow and any small loss we *might* incur (we are, after all, only talking $100 per month) is outweighed by that satisfaction (at the moment anyway).


Steve Navra is setting up a managed fund next year, and based on very strong returns in previous years, and looking at his methods, it should be able to return far better than the returns you've managed to get so far.
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.


  • irr.xls
    13.5 KB · Views: 117