Canadian Money Forum banner

1 - 4 of 4 Posts

·
Registered
Joined
·
15 Posts
Discussion Starter #1
Hey guys,

Forgive me for this, I have searched everywhere online and can't seem to figure it out. The spreadsheet I use to track my investments is giving me different numbers than my tracker on Yahoo Finance.

Essentially I'm trying to determine my ROI on a recent ETH investment I made, details below:

Purchased 1.002 shares when the price per share was at $348.26
Sold 0.2 shares when the price per share was at $502.84

Currently have 0.8002 shares left and the price per share is at $514.00

Yahoo Finance is telling me its around a 66% return, but my spreadsheet is saying its around 48%.

Unfortunately, I can't for the life of me figure it out right now.

Thanks for the help, this is an embarrassing one haha.
 

·
Registered
Joined
·
1,023 Posts
I get 46.95% ROI. They are doing an IRR internal rate of return to get the 66% based on the cash flows. It looks like in one year you made the purchase (at the start) , a -ve cash flow) and sale (during the year) (+vs cash flow) and then had a positive cash flow balance at the end of the year (positive end cash balance)

In Excel cash flow period 1 cell A1 is -348.26 (your investment) + the $100.568 from the sale during the year = -248.39 net cash flow

end balance cash at start of period 2 cell A2 is .8002 X $514 = $412.23 positive cash flow

Then in A3 type = IRR (A1:A2) and you will get 65.96%
 

·
Registered
Joined
·
196 Posts
I get 46.95% ROI. They are doing an IRR internal rate of return to get the 66% based on the cash flows. It looks like in one year you made the purchase (at the start) , a -ve cash flow) and sale (during the year) (+vs cash flow) and then had a positive cash flow balance at the end of the year (positive end cash balance)

In Excel cash flow period 1 cell A1 is -348.26 (your investment) + the $100.568 from the sale during the year = -248.39 net cash flow

end balance cash at start of period 2 cell A2 is .8002 X $514 = $412.23 positive cash flow

Then in A3 type = IRR (A1:A2) and you will get 65.96%
Nice catch, I looked at this thread and searched a bit, I was assuming it was the XIRR but values didn't fit. You are right, seems like it's the IRR, but then Yahoo Finance is wrong, since IRR should be used only for periodical cash flows... They should provide TWRR & MWRR. I prefer MWRR (XIRR). I don't know why they wouldn't provide the current total P&L.
 

·
Registered
Joined
·
1,023 Posts
Nice catch, I looked at this thread and searched a bit, I was assuming it was the XIRR but values didn't fit. You are right, seems like it's the IRR, but then Yahoo Finance is wrong, since IRR should be used only for periodical cash flows... They should provide TWRR & MWRR. I prefer MWRR (XIRR). I don't know why they wouldn't provide the current total P&L.
Interesting. In his ex too it depends on when he received the proceeds from the sale. It had to be in yr 1 to get IRR = 66% If it was in yr2, IRR would have been the same as ROI - 48%. So IRR works here for just one period.

I use IRR to calculate my investment rate of return ongoing using the cells as yearly values. Contributions total for the year are -ve entry (as above) and my account balance as the +vs cash flow. It seems to work ok. TW or MW woudl be better but then you would have to go to a monthly period and have 0 in months when you made no contributions and a little more complicated vs putting yearly sums.
 
1 - 4 of 4 Posts
Top