Annualised Return

Time to pick some better brains than mine.

Can I calculate, elegantly, an annualised return on an investment that has had new capital added at random intervals?

I'm trying to avoid the spreadsheet ballooning into something resembling the blueprint for all of creation.

I have all the dates, amounts, performance figures etc. but cannot see a simple formula that will provide anything close to an accurate result without somehow combining the calculations for each new amount added and time held, or am I barking up the wrong tree?
'We don't need to be smarter than the rest; we need to be more disciplined than the rest.' - WB
«13

Comments

  • jimjames
    jimjames Posts: 18,549 Forumite
    Part of the Furniture 10,000 Posts Photogenic Name Dropper
    What you might find useful is the guide to unitising your portfolio.

    Saw this the other day on monevator

    http://monevator.com/how-to-unitize-your-portfolio/
    Remember the saying: if it looks too good to be true it almost certainly is.
  • JohnRo
    JohnRo Posts: 2,887 Forumite
    Tenth Anniversary 1,000 Posts Combo Breaker
    Very good Jim, thank you, I'll look into this method.. further reading required.
    'We don't need to be smarter than the rest; we need to be more disciplined than the rest.' - WB
  • JohnRo,

    I have sent you a PM.
  • JohnRo
    JohnRo Posts: 2,887 Forumite
    Tenth Anniversary 1,000 Posts Combo Breaker
    Ok so I unitised my portfolio which is effortless given the heaps of historic data I have stored, once I'd undertood what's involved.

    I chose £100 as the initial unit price which has fallen as low as £97.67 and currently stands at £112.80 showing a total gain of 12.8% on the sums invested over a period of 664 days.

    Everything appears to tally neatly but I cannot work out how to apply a formula that gives a running annualised return based on the unitised price at each point in time.

    I've used the weighted days method Mr. Stollwell kindly provided and that's showing my annualised return currently at 8.24% which seems entirely reasonable. I can also easily apply this calculation to all the historic data points which isn't particularly useful but something I've done anyway.

    Any ideas how to extract an annualised return from the unitised values?
    'We don't need to be smarter than the rest; we need to be more disciplined than the rest.' - WB
  • AndyT678
    AndyT678 Posts: 757 Forumite
    Part of the Furniture Combo Breaker
    I think you want a CAGR.

    The Excel formula using named ranges =POWER(EndValue/StartValue,1/Period)-1

    StartValue = 100, EndValue = 112.8, Period = 644/365 gets 6.8%
  • redbuzzard
    redbuzzard Posts: 718 Forumite
    Part of the Furniture 500 Posts Combo Breaker
    edited 15 April 2015 at 12:10PM
    Try the XIRR function.

    Put the cash flows in a column with the dates next to them. Put the current value of the investment at the foot of the column with today's date.

    Make purchases negative, and sales and current value positive.

    Use the formula =XIRR([cash flows range],[dates range],[guess])

    You can omit the guess which defaults to 0.1, or try something else if the formula gives a silly answer.

    Note: Divis if paid out you can put in as return cash flows with the dates they occurred. If reinvested, they are not cash flows so leave them out, but they will of course be included in the current value.

    Sorry must have overlooked that this had already been suggested. Very easy though.
    "Things are never so bad they can't be made worse" - Humphrey Bogart
  • JohnRo
    JohnRo Posts: 2,887 Forumite
    Tenth Anniversary 1,000 Posts Combo Breaker
    edited 15 April 2015 at 12:16PM
    If I use a function in open office called RRI which calculates the rate of return from an investment, it gets close.

    This requires

    p number of periods used in the calculation
    pv present value
    FV future value

    If I use the current unitised value as the future value and the present unitised value as a fixed 100 (ie the starting sum)

    then the number of days from the start for the entry divided by 365.25 gives the period in years.

    date column A, unitised value column D

    =RRI(DAYS(A10;A$280)/365.25;100;D10)*100

    this gives a current annualised return (interest rate) of 6.90%

    (I've used 664 days not 644)

    edit: I've tried XIRR using the method you mention and got a strange result but will take another look and check for errors.
    'We don't need to be smarter than the rest; we need to be more disciplined than the rest.' - WB
  • redbuzzard
    redbuzzard Posts: 718 Forumite
    Part of the Furniture 500 Posts Combo Breaker
    edited 15 April 2015 at 12:34PM
    Isn't RRI just for one in and one out?

    IRR in Excel works for a series of cash flows, but unless they are at equal intervals then the answer will be wrong.

    The rate returned will also be for the period between the cash flows, so if that is monthly (you could just assume all your cash flows occur at the beginning say of the relevant month, and put in zero cash flows for the months in between) then you will have to apply

    Annual rate = (1+IRR)^12-1

    to get a CAGR.

    Better to use XIRR, which will use the actual dates. You can just use the dates and amounts when you have added or withdrawn (as with income paid out) money, put the formula over it and it will calculate the annual rate directly.

    Open Office does have the XIRR function.
    "Things are never so bad they can't be made worse" - Humphrey Bogart
  • JohnRo
    JohnRo Posts: 2,887 Forumite
    Tenth Anniversary 1,000 Posts Combo Breaker
    edited 15 April 2015 at 12:59PM
    The thing is that the unitised value has already accounted for all the cash flows in and out.

    edit:

    I've entered the current portfolio value as the last entry inflow and the XIRR function gives me a rate of 7.75%
    'We don't need to be smarter than the rest; we need to be more disciplined than the rest.' - WB
This discussion has been closed.
Meet your Ambassadors

🚀 Getting Started

Hi new member!

Our Getting Started Guide will help you get the most out of the Forum

Categories

  • All Categories
  • 350.3K Banking & Borrowing
  • 252.9K Reduce Debt & Boost Income
  • 453.2K Spending & Discounts
  • 243.3K Work, Benefits & Business
  • 597.9K Mortgages, Homes & Bills
  • 176.6K Life & Family
  • 256.4K Travel & Transport
  • 1.5M Hobbies & Leisure
  • 16.1K Discuss & Feedback
  • 37.6K Read-Only Boards

Is this how you want to be seen?

We see you are using a default avatar. It takes only a few seconds to pick a picture.