|
|
Post by davids on Mar 11, 2019 17:21:57 GMT
Sorry for a potentially dumb question...
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?
|
|
|
|
Post by mrclondon on Mar 11, 2019 18:39:54 GMT
|
|
|
|
Post by dan1 on Mar 11, 2019 21:26:57 GMT
Sorry for a potentially dumb question... 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!
|
|
|
|
Post by davids on Mar 17, 2019 9:43:52 GMT
I tried to do something different,
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
|
|
|
|
Post by Ace on Mar 17, 2019 9:51:24 GMT
I tried to do something different, 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.
|
|
|
|
Post by spareapennyor2 on Mar 17, 2019 10:08:24 GMT
|
|
|
|
Post by davids on Mar 17, 2019 15:34:37 GMT
Using that spreadsheet and inputting all my transfers in and out gives me a value of 8.47%
Don't get why this sheets works and mine doesn't the formulas and stuff are the same ... Some investigating to be done
|
|
registerme
Member of DD Central
Posts: 6,189 
Likes: 5,996
|
Post by registerme on Apr 1, 2019 17:49:16 GMT
Using that spreadsheet and inputting all my transfers in and out gives me a value of 8.47% Don't get why this sheets works and mine doesn't the formulas and stuff are the same ... Some investigating to be done I hate XIRR as well. 99.99999% of the time the fault will be with the format some of your data is in, most likely the dates.
|
|
benaj
Member of DD Central
Posts: 4,857
Likes: 1,591
|
Post by benaj on May 1, 2019 12:07:02 GMT
On unbolted, we don't usually receive interest monthly on standard loans and we don't pay tax on accrued interest. So based on the actual interest received from repayments, my XIRR is 5.22% compared to 8.05% when accrued interest is included in the calculation.
|
|
n
Member of DD Central
Yet another Nick
Posts: 879
Likes: 461
|
Post by n on May 1, 2019 13:34:04 GMT
Until the money is back in your bank account you haven't actually received anything. XIRR relies on your assigning a value to the funds on the platform (which might never materialise due to defaults and/or platform failure) so using accrued interest will be the closest you can get to the current value assuming no future defaults or platform failure.
But that's only my opinion and I am no expert.
|
|
|
|
Post by df on May 3, 2019 21:17:49 GMT
Until the money is back in your bank account you haven't actually received anything. XIRR relies on your assigning a value to the funds on the platform (which might never materialise due to defaults and/or platform failure) so using accrued interest will be the closest you can get to the current value assuming no future defaults or platform failure.
But that's only my opinion and I am no expert.
I wonder what my current Col XIRR could be? Crystal ball calculation would probably work better...
|
|
n
Member of DD Central
Yet another Nick
Posts: 879
Likes: 461
|
Post by n on May 4, 2019 8:41:25 GMT
Until the money is back in your bank account you haven't actually received anything. XIRR relies on your assigning a value to the funds on the platform (which might never materialise due to defaults and/or platform failure) so using accrued interest will be the closest you can get to the current value assuming no future defaults or platform failure.
But that's only my opinion and I am no expert.
I wonder what my current Col XIRR could be? Crystal ball calculation would probably work better... I expect it would be exactly the same as mine. 
|
|
