3

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.

2

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.

enter image description here

∴ 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
  • Thank you. I appreciate the detail. If I've understood you correctly, what I need to do is: * Divide my portfolio into timeslots, and ensure that for the duration of each timeslot the investment is constant. * Calculate the rate of return over each timeslot. * Use the Time-Weighted Rate of Return formula to calculate an overall rate of return. – Mark Barnes Jun 20 '18 at 11:24
  • 1
    @MarkBarnes Yes, that's the most accurate method because it's using the real returns and taking the geometric mean. The periods don't need to be equal, e.g. for 10% per quarter: calculating from a six-month return plus two quarters is (1.21*1.1*1.1)^(1/4) - 1 = 0.1 – Chris Degnen Jun 20 '18 at 11:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.