How can I calculate the annualised growth rate for an irregular investment?

Question

I have a portfolio, which I invest in monthly. Depending on my circumstances, the monthly amount changes from time to time (perhaps every two years or so).

I want to calculate the annualised growth rate of my investment. At the moment I'm using Excel's RATE function, like this:

=RATE(num_months,-average_monthly_investment,-present_value,initial_value)

This is OK as an approximation but I'm looking for a more accurate result.

Answer 1

Recapping your method with a simple example

initialvalue = 1000
m1start = 100
m2start = 100
m3start = 100
v3end = 1500

=RATE(3,-100,-1000,1500,1)

0.0528704

The calculated rate is 5.29% per period.

This is equivalent to solving the equation below.

∴ v3end = (100 (1 + r) ((1 + r)^3 - 1))/r + initialvalue (1 + r)^3

∴ r = 0.0528704

The most accurate method is to use the time-weighted return. However, this requires valuations at the end of each period.

initialvalue = 1000
m1start = 100
v1end = 1158
m2start = 100
v2end = 1325
m3start = 100
v3end = 1500

(1158/(1000 + 100)*1325/(1158 + 100)*1500/(1325 + 100))^(1/3) - 1 = 0.0528726

If you have varying payments you can put them into the time-weighted return calculation.

initialvalue = 1000
m1start = 90
v1end = 1147
m2start = 130
v2end = 1344
m3start = 80
v3end = 1500

(1147/(1000 + 90)*1344/(1147 + 130)*1500/(1344 + 80))^(1/3) - 1 = 0.0527103

If you don't have periodic valuations you can use the money-weighted return. This method discounts all the amounts to present value.

Solving for r

(1000 + 100)/(1 + r)^0 + 100/(1 + r)^1 + 100/(1 + r)^2 = 1500/(1 + r)^3

r = 0.0528704

And with varying payments.

(1000 + 90)/(1 + r)^0 + 130/(1 + r)^1 + 80/(1 + r)^2 = 1500/(1 + r)^3

r = 0.0527379

Note this may well differ from the time-weighted return.

For further reading see How to Calculate your Portfolio's Rate of Return.

Annualise by

12*r for nominal annual return compounded monthly

or

(1 + r)^12 - 1 for effective annual rate of return