How to calculate equivalent AER of a share holding

ericpode

Several years ago I bought some shares, and they have increased in value since then. I am wondering how this compares with the increase in value of a cash savings account if I had instead just paid the original amount into a savings account instead of buying the shares.

So I would like to answer the following question: If I had paid the money into an ordinary cash savings account instead of buying the shares, what would the AER of the savings account have been to produce the same final value as the shares today?

I've grappled with the compound interest formula using Excel, but without much success. The difference between the start date and today will have to be measured in days (not years), and I believe that savings accounts are compounded daily regardless of whether the interest is "paid" monthly or yearly. But the AER percentage that I'm after will obviously be based on years.

So, I have start and end dates (to the exact day), the principle, and the final amount. How do I find the annual percentage rate (AER) ?

To simplify things, ignore the tax on savings interest.

I have share holdings in several companies, so I would just apply the formula to each one separately. Actually, for one company I bought two lots of shares on different dates, so working out the equivalent AER would be even more difficult, but I guess we can ignore this case.

Linton

Neglecting dividends:


Let t= number of years between buying and selling as a decimal: P1= price at start of period. P2=price at end of period.



%annual rate of return= ((P2/P1)^(1/t)-1) * 100


So if a share increased in price from £1.50 to £2 over 3 years 3 months(=3.25 years approx):
%annual rate of return=((2/1.5)^(1/3.25)-1)*100=9.255%.

eskbanker

ericpode wrote: »

I've grappled with the compound interest formula using Excel, but without much success. The difference between the start date and today will have to be measured in days (not years), and I believe that savings accounts are compounded daily regardless of whether the interest is "paid" monthly or yearly. But the AER percentage that I'm after will obviously be based on years.

So, I have start and end dates (to the exact day), the principle, and the final amount. How do I find the annual percentage rate (AER) ?

To simplify things, ignore the tax on savings interest.

The Excel RRI function calculates the CAGR (compound annual growth rate) in the way you want:

Description

Returns an equivalent interest rate for the growth of an investment.

Syntax

RRI(nper, pv, fv)

The RRI function syntax has the following arguments.

Nper Required. Nper is the number of periods for the investment.

Pv Required. Pv is the present value of the investment.

Fv Required. Fv is the future value of the investment.

The only tweak you'll need to apply is to convert days into years, but division by 365.25 should be adequate.

Using the figures from the above post, RRI(3.25,1.5,2) = 0.092553, i.e. 9.2553% when expressed as a percentage.

Note that savings accounts don't compound interest daily - they earn it daily but compounding can only happen after it's actually paid.

Heedtheadvice

....and now Linton will be using Excel :eek::rotfl::):j





......sensible chap so probably already does


As pointed out by both posts above, for a correct comparison there is more to it than the fairly simple calculation/formula. Dividends really should be taken into account. Also you will calculate the mean rate of return for the shares. The comparison you are trying to make, should it not be compared to mean APR over the same period. If it is a long duration I am not sure where you get that info from. If it is just for a recent shorted period then the change is downwards but quite small (as is the interest on most accounts!).



I would add that buying and selling costs (possibly quite small) of the shares should also be taken into account plus any DRIP plus cost spread between buying and selling.