We'd like to remind Forumites to please avoid political debate on the Forum... Read More »
Annualised Return

JohnRo
Posts: 2,887 Forumite

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?
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
0
Comments
-
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.0 -
Sounds like a job for XIRR:
https://support.office.com/en-za/article/XIRR-function-2ecd4c99-7245-472a-93c2-60ab1ce60dd0Stompa0 -
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.' - WB0
-
JohnRo,
I have sent you a PM.0 -
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.' - WB0 -
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%0 -
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 Bogart0 -
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.' - WB0 -
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 Bogart0 -
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.' - WB0
This discussion has been closed.
Confirm your email address to Create Threads and Reply

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