4

I've invested in a number of mutual funds, and I've calculated the annualised return of each of them, using a spreadsheet (and the xirr function).

I want to calculate the annualised return of the entire portfolio, starting from the time I started investing (at which point my portfolio was zero rupees) till today. I'm looking for a single number like: 12%. Presumably I have to take a weighted mean, but that's the weight?

Keep in mind that the investment style is irregular and doesn't fit textbook assumptions:

  • The amounts are irregular: There were some months where I invested 40x as in other months.
  • The frequencies are also irregular. For a while, I invested every month, and then went a few months without investing a single rupee.
  • The split across funds is also irregular. I've chosen a fund, pumped in all my money into it for a year or so, then chosen another one, and repeated the process. So the percentage of a particular fund in my overall corpus varies significantly over time.
  • After I stopped investing in a fund, I may have withdrawn some, all or none of the money. And if it's some, either a small fraction, or a large one. And over one transaction or many.
  • I'm hesitant to accept an approximation given the irregular style of investment I mentioned above. An approximation may be significantly off. So I don't want to accept it unless I know by how much it's off. In other words, I'll accept 8±1%, but not 8%.

I have the statements of the funds with me. Each row has a date, an amount invested or redeemed, the NAV as on that date, the number of units bought or redeemed, the unit balance and the rupee balance. Note that for each date, only one fund it listed: if on Jan 2, I invest in fund A, only fund A is listed for Jan 2. If the next investment is in fund B on March 5, then only fund B is listed for March 5.

In addition to this, I can also find out the NAV for any fund as on any required date.

Improve this question
11
  • The weight would be the weight of the asset in the portfolio. Then just basic sum. So investment a = 10,000 and total portfolio = 100,000 then the weight is .1
    – Ross
    Nov 18, 2015 at 14:34
  • The weight of the asset as of what date?
    – Kartick Vaddadi
    Nov 18, 2015 at 14:47
  • See the article How to Calculate Your Portfolio's Return
    – Ross
    Nov 18, 2015 at 14:58
  • @KartickVaddadi: Your comments seem to indicate that you want some number that won't change over time, but that is impossible. The return is a number reflecting the value of your portfolio relative to what you paid for it. As its value changes over time, your rate of return will also change.
    – BrenBarn
    Nov 18, 2015 at 17:36
  • No, I know the value will change as of time. I just want a number that's accurate as of today. The thing to keep in mind is that I've moved money between funds over the years that I've been investing, so "what percentage of your money is in this fund?" doesn't have a single answer.
    – Kartick Vaddadi
    Nov 19, 2015 at 3:35

2 Answers 2

Reset to default
4

The way I do it is to take each individual return and form a weighted mean of them, the weighting being the value of the individual investment.

For example, suppose you have two investments of values $100,000 and $30,000. The larger one returns 5% and the smaller one returns 10%.

Find the value of the return of each (e.g. 100,000 X 5/100), sum the returns ($8,000) and divide by the total investment ($130,000).

The figure 6.15% below the "Weighted Average" label is the weighted average (8000/130000).

enter image description here

Thank-you for the clarification, I think I see your issue now. Below is an attempt to explain what I do.

enter image description here

The rows beside the merged rows 1 and 2 show what I do with each individual investment. The idea for the 6/1/2015 row is that I haven't actually liquidated the investments, I'm just checking on their progress.

For the total return, I just (effectively) do as I explained above: I total the returns / investments in my portfolio and apply the same XIRR formula to them.

Note, for example, in April I had no investments. The returns (losses) I made are the same as when I liquidated my assets in March.

I tend to be a simpler than this example here: I am a buy and hold investor, so I don't tend to liquidate much except for rebalancing very occasionally.

Improve this answer
7
  • This won't work because the value of a particular investment keeps fluctuating over time. You may have 60% of my money in a given fund at a certain point in time, and 35% at a different point.
    – Kartick Vaddadi
    Nov 18, 2015 at 15:21
  • 2
    If that's true, then you'll never be able to do the calculation you're asking for. Just pick a point in time and do the calculation then, based on the numbers then.
    – Peter K.
    Nov 18, 2015 at 15:53
  • 2
    @KartickVaddadi Your investment fluctuates over time and so does the rate of return. You pick a date and use those returns, the values of the assets, and the total portfolio value at that time. Think of it as a snapshot.
    – Ross
    Nov 18, 2015 at 17:25
  • 1
    I do basically the same thing, but do it over time, so any single value is a snapshot as of a particular date, but I have a whole list of dates and values, so I can track how my portfolio is performing and how that performance changes over time.
    – blm
    Nov 18, 2015 at 17:45
  • Sorry is I was unclear. I know the value keeps fluctuating over time. I'm just asking for a number that is accurate as of today. In the years I've been investing, I've moved money between funds. One particular fund was 100% of my corpus when I started, and is now 60%. So I'm just looking for a way to calculate what my annualised return has been from the day I started investing till today. For example: Your annualised return from 2008 (when you started investing) till Nov 2015 has been: 12%.
    – Kartick Vaddadi
    Nov 19, 2015 at 3:38
2

Here's an example using quarterly returns to illustrate annualisation.

The portfolio has two assets with returns R1 and R2. X1 and X2 are the fractions of the portfolio's value held in each asset. RP is the quarterly portfolio return. The annualised average return is calculated by geometric averaging because the returns are compounded. In ...^(4/9) the 4 annualises the quarterly returns and the 9 is for geometric averaging over 9 periods.

enter image description here

The portfolio return is 6.2% per annum.

Improve this answer
7
  • a) Does this handle investments and withdrawals during the timeframe? In other words, the fraction of the portfolio's value held in a particular fund can change due to different returns for different funds, rebalancing or due to fresh investments (or withdrawals). Does this handle all those cases? b) When I look at my statements, the points in time are different for each fund. In other words, t2 - t1 ≠ t3 - t2. Further, at each point in time, I know the R and X values for only some of the funds. Can this work with missing data? If not, I can find out the missing data; it's just more work.
    – Kartick Vaddadi
    Nov 19, 2015 at 16:38
  • Re: a) If you have rebalancing mid-period, from the asset perspective these are cash inflows or outflows. These should be accounted for in the calculation of the common end-period asset returns, (either by money-weighted return or better, time-weighted return if you have the mid-period valuation for the asset.
    – Chris Degnen
    Nov 20, 2015 at 12:09
  • A) Am I correct in understanding that if the first fund outperformed the second in a particular quarter, and nothing else changed, then X1 for the next quarter > X1 for the previous quarter? B) The same thing would happen if you moved money from the second fund to the first at the end of the quarter, and nothing else changed? C) The only case your spreadsheet doesn't handle correctly, I think, is that of an additional investment made mid-period, like on Dec 31 2014 in your example. I think you need to use a WEIGHTED geometric mean, with rows Q1 2015 and below having more weight.
    – Kartick Vaddadi
    Nov 21, 2015 at 9:49
  • As an example, let's say there are only two quarters, Q1 and Q2. I start Q1 with a corpus of ₹1. At the start of Q2, I make an additional investment of ₹1 million. If I want to calculate my annualised return for the entire period (Q1 + Q2), my return in Q1 is practically irrelevant, given that it gets drowned out by the huge amount of money invested at the start of Q2. The geometric mean treats both quarters as having equal importance, while in reality the second is a million times as important as the first.
    – Kartick Vaddadi
    Nov 21, 2015 at 9:57
  • As far as I know, each quarterly return is independent. Say, return for Q1 is 10% so at the end of Q1 you have ₹1.1. Q2 starts with ₹1.1 + ₹1m = ₹1,000,001.1. Suppose the Q2 return is 5%, so Q2 ended with ₹1,050,001.155. The cumulative return is still (1 + R1)*(1 + R2) -1 = 1.1*1.05 - 1 = 15.5% over the two quarters. Return is simply about performance. You might find this interesting: How to Calculate you Portfolio's Rate of Return.
    – Chris Degnen
    Nov 21, 2015 at 12:22

You must log in to answer this question.

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