Keeping track of gains with cost averaging

ValiantSon

ValiantSon wrote: »

Try this in Excel:

=(((Current Value-SUM(Range of Cells with Investment Amounts))/Initial Value)-1)

Given that nobody has commented on this formula, and the best way to work out the return is still being debated, could someone explain if there is a flaw in the formula I have given, because it seems to work for me?

Thanks.

msallen

ValiantSon wrote: »

Given that nobody has commented on this formula, and the best way to work out the return is still being debated, could someone explain if there is a flaw in the formula I have given, because it seems to work for me?

Thanks.

I'm not sure what you expect it to produce?
Is the "initial value" your initial investment, and the "range of cells with investment amount" extra contributions?

If so then imagine you start with £10,000 and add £1,000 per month for another 11 payments and at the end of the year your "current value" is £23,000

That would translate to

(((23000-11000)/10000)-1)

which equals 0.2. What do you think that means, or what do you want it to tell you?

As I said above I use the XIRR function

If I plugged the above figures into that (i.e. invest £10K on 1/1/17, and £1K on the the first of the next 11 months, then if the lot was worth £23K on 31/12/17 the XIRR function would give me a rate of return of ~5.37%

Alternatively you could just simplistically say that you have invested £21K and now have £23K so your value has increased by ~9.5%, but that means nothing without saying how long the cash was invested for (unless you invested it all in one go a year ago).

msallen

To give you a concrete example ...

For each of my p2p platforms I have a spreadsheet which lists all my deposits and withdrawals with the date.

Column B is the date and column C is the amount (a negative value for a withdrawal).

The first entry is on row 3.
I leave space for entries up to row 100 (I extend this later if I need to)
I then add a dummy entry on row 101 and in cell B101 I have ...
= TODAY()
... and in cell C101 I have
= 0-J2
(where J2 is the current contents/value of my account)

I then use the formula ...
=XIRR(C3:C101, B3:B101)*100
... to give me the annual internal rate of return I have earned to date on that platform.
(It doesn't matter if I only have entries on rows 3 to 70, as the empty rows 71-100 are ignored by XIRR)

ValiantSon

msallen wrote: »

I'm not sure what you expect it to produce?
Is the "initial value" your initial investment, and the "range of cells with investment amount" extra contributions?

If so then imagine you start with £10,000 and add £1,000 per month for another 11 payments and at the end of the year your "current value" is £23,000

That would translate to

(((23000-11000)/10000)-1)

which equals 0.2. What do you think that means, or what do you want it to tell you?

As I said above I use the XIRR function

If I plugged the above figures into that (i.e. invest £10K on 1/1/17, and £1K on the the first of the next 11 months, then if the lot was worth £23K on 31/12/17 the XIRR function would give me a rate of return of ~5.37%

Alternatively you could just simplistically say that you have invested £21K and now have £23K so your value has increased by ~9.5%, but that means nothing without saying how long the cash was invested for (unless you invested it all in one go a year ago).

Thanks for the reply.

The initial value is the starting point you wish to work from. This may be your initial investment, or it could be the value at the start of a new year. It all depends on the time period you want to work out your return over. The range of cells are the extra contributions.

It returns a percentage if you format the cells.

If I put some figures in (arbitrarily for illustrations sake) of a starting value of £10,000 and twelve further investments of £750 each, and then an ending value of £30,000, the formula returns a figure of 117.5%. That looks pretty correct to me: the value of the pot has grown by 200%, but part of that is due to £9000 of new money coming in.

msallen

ValiantSon wrote: »

Thanks for the reply.

The initial value is the starting point you wish to work from. This may be your initial investment, or it could be the value at the start of a new year. It all depends on the time period you want to work out your return over. The range of cells are the extra contributions.

It returns a percentage if you format the cells.

If I put some figures in (arbitrarily for illustrations sake) of a starting value of £10,000 and twelve further investments of £750 each, and then an ending value of £30,000, the formula returns a figure of 117.5%. That looks pretty correct to me: the value of the pot has grown by 200%, but part of that is due to £9000 of new money coming in.

Hmmm. If I use your figures that simplifies to..
((30-9)/10)-1
... and that equals 1.1 for me (although I admit I haven't tried it in a spreadsheet) so I don't know how you get to 117.5. I also don't really know what its telling me, but if it suits your purposes fair enough.

Edited to add. OK if you have 11 lots of 750 rather than 12, and then multiply the result by 100 you get 117.5. I'm still not sure I see the value though (no pun intended )

ValiantSon

msallen wrote: »

Hmmm. If I use your figures that simplifies to..
((30-9)/10)-1
... and that equals 1.1 for me (although I admit I haven't tried it in a spreadsheet) so I don't know how you get to 117.5. I also don't really know what its telling me, but if it suits your purposes fair enough.

Okay.

That's the percentage returned in my spreadsheet. I don't really know why you get a different rate.

What it should be telling you is the per centage gain/loss in the value of the portfolio minus the cash injections.

It works for me.

Audaxer

msallen wrote: »

To give you a concrete example ...

For each of my p2p platforms I have a spreadsheet which lists all my deposits and withdrawals with the date.

Column B is the date and column C is the amount (a negative value for a withdrawal).

The first entry is on row 3.
I leave space for entries up to row 100 (I extend this later if I need to)
I then add a dummy entry on row 101 and in cell B101 I have ...
= TODAY()
... and in cell C101 I have
= 0-J2
(where J2 is the current contents/value of my account)

I then use the formula ...
=XIRR(C3:C101, B3:B101)*100
... to give me the annual internal rate of return I have earned to date on that platform.
(It doesn't matter if I only have entries on rows 3 to 70, as the empty rows 71-100 are ignored by XIRR)

Thanks msallen, that looks a useful Excel formula which I hadn't previously come across.

Thrugelmir

Wassa123 wrote: »

If I do this regularly, won't the gain number be continually warped by the new money coming in?

There'll be periods of time when markets will decline. You'll wonder why are you are investing as in effect (on paper) you'll have incurred a loss. Stock markets are far from an upward escalator.

Nor lose sight of the fact that reinvestment of income is what generates the return over an extended period. Not the underlying company share prices.

AlanP_2

msallen wrote: »

To give you a concrete example ...

For each of my p2p platforms I have a spreadsheet which lists all my deposits and withdrawals with the date.

Column B is the date and column C is the amount (a negative value for a withdrawal).

The first entry is on row 3.
I leave space for entries up to row 100 (I extend this later if I need to)
I then add a dummy entry on row 101 and in cell B101 I have ...
= TODAY()
... and in cell C101 I have
= 0-J2
(where J2 is the current contents/value of my account)

I then use the formula ...
=XIRR(C3:C101, B3:B101)*100
... to give me the annual internal rate of return I have earned to date on that platform.
(It doesn't matter if I only have entries on rows 3 to 70, as the empty rows 71-100 are ignored by XIRR)

Thanks for this, I had looked at XIRR once before but gave up as I got a meaningless answer - my Excel is not good.

A couple of questions if you don't mind.

Where do you put opening balance or do you need to run it from Day 1?

Do you treat fund sales to cover fees as a withdrawal?

Is it ways a annual with a restart each year?

Audaxer

AlanP wrote: »

Thanks for this, I had looked at XIRR once before but gave up as I got a meaningless answer - my Excel is not good.

A couple of questions if you don't mind.

Where do you put opening balance or do you need to run it from Day 1?

Do you treat fund sales to cover fees as a withdrawal?

Is it ways a annual with a restart each year?

I've just looked at an example earlier tonight, but I think at the start of the year, you could just use the first cells for the date, 1st January, and the value of your fund or portfolio on that date. Then in the subsequent cells below input the date and amount of any further investments and a minus figure for any cash withdrawals following sales, and possibly platform charges as well. I need to test it out but it looks like that would give you a true percentage gain or loss for the year to date as the year progresses.

I assume you just need to restart it every year with the current value of the fund/portfolio, and at each year end it will give you a true annual percentage gain/loss after taking into account all transactions during the year.