Greenwood2
Member of DD Central
Posts: 4,377
Likes: 2,780
|
Post by Greenwood2 on Jan 11, 2019 21:17:27 GMT
Well, that is a way of approximating your rate of return but given XIRR is so easy to use (all you need is a list of transactions and a final balance), why not use that? Depends how many transactions you have and if you have them in a spreadsheet already. If you can just use the monthly summary information on the site to give a good approximation this may be good enough for most purposes. Edit: I do have all transactions in a spreadsheet as well but only bother to do an XIRR very occasionally.
|
|
aju
Member of DD Central
Posts: 3,500 
Likes: 924
|
Post by aju on Jan 11, 2019 22:39:30 GMT
Not sure I should say this but I have all transactions in a database too for our 4 accounts, 2 Invest and 2 ISA. Thing is though there's getting on for a total of over 1 million records in the statements data since I started in 2005. It's all in Excel too so that's not ideal either.
I'm guessing we are meaning online Statements data though rather than transactions data as such. I have spreadsheet that contains the monthly statements and this is much more manageable and I use both methods that are being discussed XIRR and monthly calcs. I agree with Benaj the empirical methods data is a little bit skewed when one is either building up an investment (ISA transfers say) or running things down on a monthly basis. The XIRR does have more stable results but sadly the Statements data is monthly and for us the data increases and decrease can be daily in many cases.
I've not tried XIRR on any of our transaction files it could be quite slow perhaps on the amount of records that I have in my all-time tables for Invest alone. For XIRR purposes I guess I could just use a years worth but even that is a lot of data these days with the amounts I am working with. That said I know its an approximation for a given month but XIRR for the year must be getting close to the correct value.
|
|
|
|
Post by GSV3MIaC on Jan 12, 2019 8:24:02 GMT
For xirr all you need is in/outs to the account(s) from wherevere else you kept the funds, plus current balances for each of the 4 accounts. You do not need the million internal 2p transaction records. Most folks only deposit/ withdraw weekly or monthly .. some only annually.
|
|
zlb
Member of DD Central
Posts: 1,422
Likes: 333
|
Post by zlb on Jan 12, 2019 15:01:22 GMT
Well I gave up on XIRR in google sheets - it kept on returning very strange figures, and I've used formulae for years.
(this way will do won't it?) Running a calculation of exact earnings per month, compounded to add up earnings and one deposit, as it went, = 3.09% interest over Jan 2018 to Dec 2018, ranging from -0.9% annually to 6.01% annually. See no mode as pasted below. It's what I suspected and nothing like that advertised, particularly given that I have about 25% in Plus. Losses each month are stabilising at at least 33% of earnings.
Jan 2018 monthly 0.04% *12 = 0.49%
2.30%
0.00%
6.01%
1.62%
4.13%
3.83%
4.00%
5.00%
2.72%
-0.90%
5.66%
Dec 2018 2.28%
|
|
