Search results
The first (and earliest) entry should then be January 1 of this year (meaning the portfolio value as of January 1). Then an XIRR return of 12.3% means that, at the current rate of return (since Jan 1), you'd make 12.3% over the entire 365-day year. If, however, only N days have passed since Jan 1, you can get a YTD return using: (1+0.123) N/365 ...
If R = 0.123 (meaning 12.3%) then 1.123 1/365 = 1.000317869 is the daily Gain Factor Then each dollar will grow to (1+ R ) D /365 in D days ... the D -day Gain Factor If D = 90 days then 1.123 90/365 = 1.029 is the Gain Factor over 90 days, meaning $1.00 will grow to $1.029 in 90 days
Yes, 8% quick-and-dirty and XIRR agree pretty well. In fact, if the Jan 1/00 portfolio was $50K and a $25K investment was made on July 1/00 (after six months) and the Dec 31/00 portfolio was $80K, then XIRR gives an annual return (for the year 2000) of 8.03%
This may (or may not!) agree with either IRR or XIRR ... especially if one of the months has a -100% return making (1+R) = 0. Example : if you begin on Jan 1, 2007 with $1K and invest another $1K every 30 days (Jan 31, Mar 2 etc. , the last being on Nov 27/07) , ending with $13K on Dec31/07, then:
XIRR: a bug? Excel's XIRR function is nice for calculating the annualized rate of return when there are various investments and withdrawals. See the calculation in Figure 1? Excel's XIRR says the return is 0.0%, if you use as your "guess" 10%. However, the "correct" return, for the numbers in Figure 1, is -4.81% so Excel has it wrong.
The XIRR command. It goes like this: =XIRR(A1:A50,B1:B50,0.10) where the range A1:A50 contains the cash flows and the range B1:B50 contains the dates and 0.10 is an initial guess (like 0.10 meaning 10%) Here's an example
Excel's XIRR function has the following format: =XIRR(cash flows, dates, guess) Somebuddy sent me the following problem: The XIRR return says 0.0% ... which ain't right. Now change the initial $200 investment to $200.01 (or even $200.00001) and the correct XIRR appears. Mamma mia! Does anybuddy here get the same curious behaviour
If you add or withdraw at random times, there's XIRR... sometimes called ROI. ('course, ya gott watch our for XIRR bugs) Both require sexy calculations which require a computer. There ain't no easy formula. Further, some people calculate a return as: [A] newPrice / oldPrice - 1 Example: $53/$50 - 1 = 0.06 or 6%. Some prefer:
That YTD return of 8.59% means that a single $100 deposit on Jan 1, in the same bank, would give you $108.59 by Mar 1. Figure 1A. Now consider Figure 2A. You put money into a bank that pays a constant 45.76% per year. You deposit $10K on Jan 1, withdraw $9K on Feb 1. At 45.76% per year you'd have $1500 by Jun 1.
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 ...
