annualised rate of return on an investment formula

[Trebor777]

Hi. I have some shares which originally cost me £2000 on 27/5/14.

Today have gained £200, partly capital growth, partly dividend income.

The total return is 10%. No problem. But what is this as an 'annualised' rate of return, given that i bought them about 2.5 years ago?

Excel 2007 has several formulas for these sorts of calcs, but I get confused as so many refer to mortgage interest and debt sums.

Many thanks as always

KK

[TheSilkCode]

Hi Trebor-

I am not aware of an annualized rate of return native function in Excel, however you can quickly create your own using a UDF- see below:

Please Login or Register  to view this content.

Hope this helps,
TheSilkRoad

[Trebor777]

Many thanks for this. I'm surprised there isn't native function, but it might explain why I couldn't find one !

The formula you suggest could easily be written directly into the cell I hope, avoiding the need for UDFs etc (I don't know how to use them sorry)

KK

[TheSilkCode]

Yes it could- to create a UDF simply pull open the VBA editor, add a module, paste the code in. Done! Give it a try- UDFs are pretty powerful tool, and if you have any issues post them here and the community will help you through them..

TheSilkCode

[JohnTopley]

Transforming "TheSilkCode" formula:

Starting value in C3
Ending Value in C4

Number of days in C6
Annualised Return:

=(1+(C4-C3)/C3)^(365/C6)-1

[Trebor777]

This helps, mega-thx