Compound annual growth rate calculation

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!)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s