Keeping track of gains with cost averaging

msallen

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?

Hi AlanP

1. If you have an opening balance just treat that as the first deposit - there's no difference in the result if you deposit £1000 to an empty account on 1st Jan, or you start calculating from 1st Jan with £1000 that's been in the portfolio for 10 years!

2. No. Don't concern yourself with the minutiae of what happens within the account, just track deposits/withdrawals to/from it.

3 (of a couple! ) I'm not totally sure what you're asking here so I'm going to give you two answers and hope one is appropriate....

A. This will give you an annual rate regardless of how long a period is actually covered.

B. My spreadsheets are actually a bit more convoluted than my example above but I didn't want to confuse the essence of what I was explaining before. In reality I record the return I've achieved over the past year at the end of every month so that I can see a trend. To do this I need a starting figure from a year ago (you'll see this is tied in with your first question). Hence what I actually do is not only record deposits/withdrawals as explained above, but also the balance at the end of every month. I do this with two entries being the negative of the current balance followed by the current balance, so for example I might have the following hypothetical entries from around the end of January last year ...

Row 45: . 05/01/17 . £1000 . Deposit
Row 46: . 18/01/17 . £250 . Deposit
Row 47: . 31/01/17 . -£17,238.23 . End of month
Row 48: . 31/01/17 . £17,238.23 . Start of month
Row 49: . 03/02/17 . £200 . Deposit
Row 50: . 17/02/17 . -£2000 . Withdrawal

and then at the end of January this year I might have ...

Row 82: . 15/01/18 . £500 . Deposit
Row 83: . 29/01/18 . £500 . Deposit
Row 84: . 31/01/18 . -£19,381.30 . End of month
Row 85: . 31/01/18 . £19,381.30 . Start of month
Row 86: . 13/02/17 . £100 . Deposit

...etc

These matching negative/positive markers at the end of every month allow me to calculate the return between any two of them as described below, but because they also look like equivalent deposit/withdrawals on the same date they have zero effect on the result when just taken amongst a list of transactions.

To calculate the rate from the end of Jan 17 to the end of Jan 18 I would use ...

=XIRR(C48:C84, B48:B84)*100

.. i.e. the first row in the calculation (48) would be a "Start of month" row, whilst the last row in the calculation (84) would be an "End of month" row.

I hope that makes sense. I'm sure there's a far more elegant way of doing it, but I'm no whizz on Excel so just came up with something that worked.

Audaxer

msallen wrote: »

Hi AlanP

1. If you have an opening balance just treat that as the first deposit - there's no difference in the result if you deposit £1000 to an empty account on 1st Jan, or you start calculating from 1st Jan with £1000 that's been in the portfolio for 10 years!

2. Yes

3 (of a couple! ) I'm not totally sure what you're asking here so I'm going to give you two answers and hope one is appropriate....

A. This will give you an annual rate regardless of how long a period is actually covered.

B. My spreadsheets are actually a bit more convoluted than my example above but I didn't want to confuse the essence of what I was explaining before. In reality I record the return I've achieved over the past year at the end of every month so that I can see a trend. To do this I need a starting figure from a year ago (you'll see this is tied in with your first question). Hence what I actually do is not only record deposits/withdrawals as explained above, but also the balance at the end of every month. I do this with two entries being the negative of the current balance followed by the current balance, so for example I might have the following hypothetical entries from around the end of January last year ...

Row 45: . 05/01/17 . £1000 . Deposit
Row 46: . 18/01/17 . £250 . Deposit
Row 47: . 31/01/17 . -£17,238.23 . End of month
Row 48: . 31/01/17 . £17,238.23 . Start of month
Row 49: . 03/02/17 . £200 . Deposit
Row 50: . 17/02/17 . -£2000 . Withdrawal

and then at the end of January this year I might have ...

Row 82: . 15/01/18 . £500 . Deposit
Row 83: . 29/01/18 . £500 . Deposit
Row 84: . 31/01/18 . -£19,381.30 . End of month
Row 85: . 31/01/18 . £19,381.30 . Start of month
Row 86: . 13/02/17 . £100 . Deposit

...etc

These matching negative/positive markers at the end of every month allow me to calculate the return between any two of them as described below, but because they also look like equivalent deposit/withdrawals on the same date they have zero effect on the result when just taken amongst a list of transactions.

To calculate the rate from the end of Jan 17 to the end of Jan 18 I would use ...

=XIRR(C48:C84, B48:B84)*100

.. i.e. the first row in the calculation (48) would be a "Start of month" row, whilst the last row in the calculation (84) would be an "End of month" row.

I hope that makes sense. I'm sure there's a far more elegant way of doing it, but I'm no whizz on Excel so just came up with something that worked.

Am I right in thinking that you would use this formula to calculate the returns on your total portfolio value as a whole rather than on each individual fund?

msallen

Audaxer wrote: »

Am I right in thinking that you would use this formula to calculate the returns on your total portfolio value as a whole rather than on each individual fund?

You could do it over whatever range of investments you like, but of course all the deposits/withdrawals would have to appear in the same list. It is easy to do per account as you just record all the payments into the account and withdrawals from it (so you might have two separate ones if you had an AJBell account and HL account for example).

I have a separate one for each p2p platform where I select individual loans as a way of calculating the return I get combined, but if I used any p2p platforms with a single balanced rate (such as ratesetter) then I wouldn't bother. This is analogous to using it for an S&S account where you hold different instruments, but not bothering where you only hold a single fund. [I do only hold single funds in separate S&S accounts so don't bother at all for S&S]