Topic: Using XIRR (excel) to calculate your Portfolios annualized ROI (Read 307 times)
Yeah, I'm in the same boat. My ROI was north of 3000% of on Friday, now it's around 1000%. But that wild swing is largely due to me being new to the game, and the extrapolation effect i meantioned for accounts less than 1 year old. Annualized ROI will become more stable after years of investing ... And hey, who can complain about 1000%, I'll take it!
September 04, 2017, 06:47:26 PM
Thanks.
It works and even after the dip, I am getting a 484% ROI now. Not bad!
Calculated it using the "value" from Saturday and it is showing 6366% :O ...I earnestly hope it comes back to that in a few weeks.
I also tried using the value a month ago (when my portfolio value is at max return) and it is showing error (#NUM)
It works and even after the dip, I am getting a 484% ROI now. Not bad!
Calculated it using the "value" from Saturday and it is showing 6366% :O ...I earnestly hope it comes back to that in a few weeks.
I also tried using the value a month ago (when my portfolio value is at max return) and it is showing error (#NUM)
September 04, 2017, 06:39:35 PM
Hey man, thanks for this! I had never heard of it and its a great tool to keep track of progress. Will definitely check it out when i get some time!
September 04, 2017, 06:16:24 PM
A few years ago I learned a great and simple trick for calculating real ROI across multiple investments from an different investment forum, and I thought I'd return the favor by sharing it here. All you have to keep track of is your deposit amounts/dates and withdrawals amounts/dates. You can trade inside your accounts all you want, and you don't have to track individual investment, buys/sells, etc. It's basically tracking cash in/cash out from the account, not trades made inside the account.
The first image is how you set it up in excel (it also works in open office). Each row represents a deposit/withdrawal and the date it was made. The last row is today's date, and the current balance of your account, entered as a negative.
This shows the formula.
This is what it looks like calculated.
If you have withdrawals along the way, enter those as a negative, like this.
Again, this is annualized, so if you've been investing a few months, it will extrapolate your returns out for a full year. If you've been investing for a few years, you get an average yearly ROI. It's kind of a "big picture" of how well you are doing overall. I've made all my deposits through coinbase (and not withdrawals yet), so I easily got this info from my account history.
The first image is how you set it up in excel (it also works in open office). Each row represents a deposit/withdrawal and the date it was made. The last row is today's date, and the current balance of your account, entered as a negative.
This shows the formula.
This is what it looks like calculated.
If you have withdrawals along the way, enter those as a negative, like this.
Again, this is annualized, so if you've been investing a few months, it will extrapolate your returns out for a full year. If you've been investing for a few years, you get an average yearly ROI. It's kind of a "big picture" of how well you are doing overall. I've made all my deposits through coinbase (and not withdrawals yet), so I easily got this info from my account history.
Jump to: