How to Calculate Annualised Rate of Return — a what-if goal seek approach using Excel
For long-term investment, an annualized rate of return is often reported as it is a good benchmark with the interest rate per annum of say long term bonds. Since most investment returns and interest rates are usually reported in a per annum (annually) metric, it is crucial to estimate an annualised rate of return of a long term investment, such as MPF (mandatory provident funds).
The diagram below shows, for example, the annual actual rates of return of S&P 500 in blue bars from 1970 to 2015. There were ups and downs, with a wide range from almost -40% to +40%! Yet, if a long-term investor who holds the index for the whole period without selling, then he/she would be interested to know the equivalent annual rate of return for his/her investment in S&P 500 for the 45 years. People usually take the arithmetic average (the orange line) as a representation of the average rate of return, but it does not take the compounding effect (principal + interest) into account. Thus, a compounded annual rate of return (the red line) is the metric that we want to estimate.
Annualised Rate of Return (ARR) is defined as the equivalent annual return an investor receives over a given period. (Investopedia) In other words, it is a fixed annual rate of return over the period in compound interest to achieve the designated investment performance.
For example, if the initial value of the investment is $240,000, and the final value of the investment is $1,000,000 in an investment period of 20 years. Then the overall rate of return of the investment can be calculated simply by (1,000,000–240,000)/240,000 = 317%. But it takes 20 years to achieve the 317% return, what is the equivalent annual rate of return (ARR)?
ARR Formula
For such a simple case with one initial lump sum, then it can be calculated easily either by compound interest formula of the ARR formula below extracted from CPI. The ARR of the above example is found to be 7.4% as shown:
However, this approach is not good enough to have the flexibility of having periodic contributions or withdrawals, which is quite common for MPF investment. In MPF, we usually contribute our investment principal in the beginning or the end of each month. Then the above formula would not be applicable.
ARR Calculator
Fortunately, there are ARR calculators available online. Here I use the Bank Plus ARR calculator, which allows inputs of periodic contributions/withdrawals as well as many other parameters. Let’s try using a similar example, but instead of an initial lump sum of $240,000, we contribute $1000 each month for 20 years (240 months) and the ARR is calculated to be 12.69%!
The ARR calculator is much more flexible than the ARR formula, yet it still does not allow varying periodic contributions/withdrawals, which is quite common in MPF scheme. For example, the monthly contribution can be increased from $1000 per month to $1500 per month since the 11th year of the investment.
What-if Goal Seek Approach
Then, we may use the What-if Goal Seek function provided in the Excel Worksheet to estimate the ARR. As shown in the diagram below, using Data — What-if — Goal Seek function in Excel, it allows a trial-and-error estimation approach to identify the equivalent annual rate of return which can achieve the goal of having $1,000,000 after 20-year investment period with a monthly contribution of $1000 in the first ten years and $1500 in the second ten years, assuming a yearly lump sum contribution in the beginning of each year. Then the ARR is estimated to be 11.3%.
The diagram shows the three parameters required to be inputted in the what-if goal seek command, they are (a) Set Cell: V3 [the cell of the final value of investment], (b) To Value: $1,000,000 [the amount of the final value of investment], and (c) By Changing Cell: C4 [the ARR cell].
The idea is similar to a DCF (Discounted Cash Flow) model, with the first row showing the year numbers, the second row is the annual contribution amounts, i.e. $12,000 in the first ten years, and $18,000 in the second ten years. The third row shows the sum of the principal and interests. Since each year’s principal is the sum of the previous year’s sum of principal and interests (i.e. one column left) and this year’s contribution (i.e. one row above). Then this principal of year y is multiplied by (1+ARR) to identify the sum of the principal and interest of year y. For example, in Year 2, the principal is ($13,355.10+$12,000)x(1+11.3%) = $28,218.32.
This approach can cater for a wider flexibility on varying contributions/withdrawals and change of other parameters. For example, people may suspend their investment in a particular period.
Exercise:
Let’s try for a 20-year investment period exercise, with a final value of $800,000, monthly $2000 contribution in the first 12 years and $3000 contribution in the 13–20th year. The answer is 3.3%.
Youtube (English) : How to Calculate Annualised Rate of Return, Nov 21, 2021. https://www.youtube.com/watch?v=ehuae6JQyKQ&t=22s
