Can someone advise me on how to do an XIRR calculation for my unbolted portfolio.
I've tried putting all the loan amounts in a column and making these values negatives and then repayments below which are positives but gives me a value to the minus 9.
Then I tried filtering by complete loans and spent some time ordering them to be chronological order and this gives me 0.6
Can someone explain what I'm doing wrong?
Firstly, it's not a silly question at all, it can be difficult for the uninitiated.
Follow mrclondon's method to get the basics on some example data, e.g. two dates a year apart, start value of 100, end value of 108.4 and your XIRR should yield 8.4%.
I'm not sure why your second method doesn't yield the expected result but (assuming you fix it) it'll only account for money on loan. I'd suggest you should use external cash flows, i.e. to/from your current account, which will include cash drag. To do this you can record all of the "Bank Payment" entries from the transaction log (https://unbolted.com/uk/lenders/balance/ note: I have > 50 pages of the stuff!), and in the final row set the value to negative "Total Portfolio Value" from your dashboard. This includes accrued interest so, if you want to know your XIRR for paid interest simply replace this final row value with the sum of interest and other income from the tax statement page (https://unbolted.com/uk/lenders/tax-statement) - add in the current months interest too if you can be bothered!
I took all my payments into unbolted as positive values, and all my withdrawals as negatives, and then my overall account balance at the end as a negative on today's date and this returns 0.2
That's pretty much what I do, and mine comes out as 8.50%. I'm guessing that you've made an error in one of the entries. I'd be happy to take a look if you pm me the details, but obviously understand if you don't want to reveal the figures.
I'm a minor equity investor in Welendus, Brickowner, Crowdstacker, Proplend, Orca, Assetz Exchange and ABLrate, but I haven't a clue why! Just felt like a good idea at the time.