2

I'd like to calculate an investment's IRR by looking at the NAV/share of the investment and also by looking at the cash flows as a check. The results should be the same.

The following case works fine. I calculate the IRR by the product of the single period returns (monthly) where the single period returns are calculated from the change in NAV. The IRR is 7.98. If I use Excel's XIRR function with the starting outflow and ending inflow I also get 7.98.

End Date       # Shares  Ending NAV/Shr Period Return  +1   IRR       Cash Flows     IRR
6/25/2014      52.537    1.82025802                                  -95.63106889      
7/25/2014      52.537    1.83192383     0.006409  1.006409                        
8/25/2014      52.537    1.84367069     0.006412  1.006412                        
9/25/2014      52.537    1.85549583     0.006414  1.006414                        
10/25/2014     52.537    1.86739956     0.006415  1.006415                        
11/25/2014     52.537    1.87938219     0.006417  1.006417                        
12/25/2014     52.537    1.89144404     0.006418  1.006418                        
1/25/2015      52.537    1.90358546     0.006419  1.006419                        
2/25/2015      52.537    1.91580681     0.006420  1.006420                        
3/25/2015      52.537    1.92810846     0.006421  1.006421                        
4/25/2015      52.537    1.94049078     0.006422  1.006422                        
5/25/2015      52.537    1.95295416     0.006423  1.006423                        
6/25/2015      52.537    1.96549902     0.006424  1.006424  7.98%     103.2616091    7.98%

But the next case below doesn't work. By using the NAVs I get 6.06% and by using the cash flows I get 6.45%. The only meaningful difference I can see in these two examples is that in this second example the number of shares held increases.

End Date       # Shares  Ending NAV/Shr Period Return  +1   IRR       Cash Flows     IRR
6/25/2014      8.333     1.000000                                     -8.333333      
7/25/2014      16.646    1.002457       0.002457  1.002457            -8.333333      
8/25/2014      24.926    1.006471       0.004003  1.004003            -8.333333      
9/25/2014      33.169    1.011020       0.004520  1.004520            -8.333333      
10/25/2014     41.372    1.015850       0.004778  1.004778            -8.333333      
11/25/2014     49.535    1.020862       0.004933  1.004933            -8.333333      
12/25/2014     49.535    1.026003       0.005036  1.005036            0.000000      
1/25/2015      49.535    1.031692       0.005545  1.005545            0.000000      
2/25/2015      49.535    1.037413       0.005545  1.005545            0.000000      
3/25/2015      49.535    1.043164       0.005544  1.005544            0.000000      
4/25/2015      49.535    1.048947       0.005544  1.005544            0.000000      
5/25/2015      49.535    1.054762       0.005543  1.005543            0.000000      
6/25/2015      0.000     1.060609       0.005543  1.005543  6.06%     52.537095      6.45%

If anyone can tell me what I'm doing wrong, I would really appreciate it.

Thanks in advance.

Improve this question

1 Answer 1

Reset to default
1

You are calculating using different methods. For example, to obtain 6.45%

e = 2.71828182;
r = x /. FindRoot[8.333333 e^x +
      8.333333 e^(11/12 x) +
      8.333333 e^(10/12 x) +
      8.333333 e^(9/12 x) +
      8.333333 e^(8/12 x) +
      8.333333 e^(7/12 x) == 52.537095, {x, 0}];
100 (e^r - 1)

6.44647

This is effectively the same as the money-weighted return calculation.

In arriving at 6.06% you have calculated the true time-weighted return.

Both answers are right, but they are different measures.

To use time-weighted returns you need to know the value of the investment at the time of every cash flow. Modified Dietz uses a simple approximation to avoid that requirement. Money-weighted return gives results that are more accurate for back calculating than Modified Dietz, (also without requiring interim valuations), but the calculation is more complex.

See How to Calculate your Portfolio's Rate of Return for a decent reference.

Improve this answer
7
  • Thank you very much. Could you explain the calculation syntax in your code block please? What is r=x/.?
    – mchac
    Jun 26, 2014 at 2:14
  • @mchac - hi, that's specifically Mathematica syntax. It just sets r as the solution root, x. Hopefully the rest of the calculation is clear. It's just the log form of the money-weighted return formula.
    – Chris Degnen
    Jun 26, 2014 at 6:19
  • Ok, got it. Thank you again. Last question on the above, using TWROR how is a 2 year rate interpreted? I.e. the product of 24 net monthly returns results in a 13.85% return. If I want to express the average annual return over the 2 years is it as simple as 13.85/2 since there is no compounding (so no solving for an IRR)?
    – mchac
    Jun 26, 2014 at 11:56
  • @mchac - you should refer to this section on annualisation. If your example is a continuation of the table in your question then it would seem to involve compounding, so r = (1 + R)^(1/t) - 1 = (1 + 0.1385)^(1/2) - 1 = 0.067 = 6.7%
    – Chris Degnen
    Jun 26, 2014 at 12:18
  • Ok. Yes, it is a continuation of the table in my question. I was thinking about this incorrectly. It's a theoretical fund where an inv is placed on day 1 and redeemed at a future date with no intermed cashflows to the investor. But you are correct, practically, there are intermediate cashflows monthly (inc and exp and ultimately a period end value) but they are retained in the fund and that's where I went wrong. I had calc 6.7 then thought the investor isn't getting the year 1 value to reinvest so it must be simpler but they are getting that value (cash retained). Thanks very much!
    – mchac
    Jun 26, 2014 at 12:40

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .