motivated by email from Gregg B
Calculating Year To Date returns, given a starting portfolio value on January 1 as well as a series of additional investments or withdrawals
(them's the Cash Flows) can be done with this spreadsheet ... if'n you're careful
Click on the picture to get the spreadsheet
>Huh? What's the green x's?
There's an "Explain" sheet ... like so:
>Then pray that it works?
Of course ... else you get your money back
motivated by email from Steve N
In the above spreadsheet (called YTD.xls) , the XIRR function is used.
Alas, Excel's XIRR works by starting with an initial "guess" of 10% then making a jillion corrections to that guess.
Usually that works great, but if the true XIRR value is large and negative ... say 75%, then starting with 10% ain't gonna do it.
For that reason, the latest spreadsheet (called YTD2.xls) provides an initial guess to help XIRR do its thing.
>Is the initial guess any good?
Uh ... good enough to get XIRR started on the right track (I think).
It just counts how much money you've stuck into your portfolio and compares to the current balance.
Actually, it uses the "linear approximation" (similar to the modified Dietz) to estimate the return.
>Where's the spreadsheet?
Here it is:
Click on the picture to get the spreadsheet
There's an Explain sheet that looks like this.
I might also point out that the original YTD.xls assumed you'd use no more than 100 rows.
This version, YTD2.xls, lets you use up to 500 rows of data.
>And it works?
Of course.
