XIRR and its uses
motivated by e-mail from Gordon K.

Suppose you get reports from your money manager which gives you quarterly returns and Year-To-Date returns etc., and you want to check those numbers yourself ... using the XIRR function in Excel.

>Why?
Because it'll give you a warm feeling ... if your calculations agree

So, first we note the following (where, for a return of 12.3% we use R = 0.123)

  • If R is an annual return, then each dollar grows to $(1+R) in a year ... we'll call 1+R the Gain Factor over 365 days
        Example: If R = 0.123 then 1.123 is the annual Gain Factor
  • Then each dollar will grow to (1+R)1/365 in one day ... this is the one-day Gain Factor
        If R = 0.123 (meaning 12.3%) then 1.1231/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.12390/365 = 1.029 is the Gain Factor over 90 days, meaning $1.00 will grow to $1.029 in 90 days
  • That means that the return, over D days, is (1+R)D/365 - 1 ... and you'd multiply by 100 to get the percentage return, over D days
        That'd be 1.029 - 1 = 0.029 is the return over 90 days, and multiplying by 100 gives a 90-day return of 2.9%
  • That means that, if D days has passed since the start of the year, then the YTD return would be:
    YTD Return = (1+R)D/365 - 1
    where R is the Annualized Return (since the start of the year)
    and D days have passed since the start of the year.

>And how am I supposed to calculate that Annualized Return?
Aah, that's where XIRR comes in! The Excel function always gives an Annuallized Return !
For example, suppose we start the year with $56,789 and make a bunch of deposits into our account and by the end of the first quarter we have $67,890 (which, according to XIRR rules, we enter as a negative amount).

Further, we continue to make deposits for another quarter (to June 30) and calculate the annualized return over just this second quarter.

Then (in column D) we calculate the YTD Return ... as described above.

>I thought you needed some kind of guess, for that XIRR?
I'd normally use XIRR(A3:A11,B3:B11,0.1) where that 0.1 (or 10%) is my guess ... but if I leave it out Excel puts in 0.1 anyway

If you'd like to play with the spreadsheet, just click on the picture above
(or, if'n you like, you can RIGHT-click and Save Target to download the spreadsheet.