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.
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
