First published 25 April 2016
The formula in Excel is (A/B)^(1/C)-1 expressed as a percentage; where A is the current value, B is the original value, ^ is the Excel symbol for ‘to the power of’ and C the number of elapsed years. For example the following is taken from the spreadsheet for a Momentum endowment. The YEARFRAC Basis 3 function was used for years elapsed but this is easy enough to calculate separately. The answer of 13.60%pa compares to Momentum’s own figure of 13.61%. [There may be an Excel function for this but I haven’t looked too hard for it and the formula as set out can be applied without using Excel]
Years | Investment | |
elapsed | Amount | |
07-Jul-10 Inception | 103,529.50 | |
31-Mar-16 | 5.74 | 215,148.02 |
CAGR from inception | 13.60% |
Calculating the return where there are multiple additions and withdrawals (eg a living annuity with an annually changing monthly payout or with capital additions) is inevitably a little more complicated. For my living annuity I do it by setting up a monthly table of additions, withdrawals and interest / investment return and using the Goal Seek function to determine the rate of interest which applied to the other two variables produces the current value of the annuity. This gives a monthly compounded rate which can then be easily converted to an annual compound rate. (Please refer to me if in doubt on either of these!)