Measuring Portfolio Performance

We invest our hard earned money in stocks and bonds. Year 2013 is coming to an end and we want to know how our portfolio performed. But how do we measure the performance of a portfolio? In this post I will show you how.

Given below is a simple portfolio. $1000 was invested on 1st Jan 2013. On 31st Dec 2013 it is valued at $1500.

Date Value
1-Jan-13 $1000
31-Dec-13 $1500

The portfolio return is 50%

Let 

MVB = Market value of the portfolio at the beginning
MVE = Market value of the portfolio at the end

Portfolio Return = ((MVE - MVB) / (MVB)) * 100
                 = ((1500 - 1000) / (1000)) * 100
                 = (500 / 1000) * 100
                 = (0.5) * 100
                 = 50%

In reality we do not have a simple case like this. To complicate the situation slightly I am adding $100 to the portfolio on 1st May.

Date Value
1-Jan-13 $1000
1-May-13 $100
31-Dec-13 $1500

The original formula will no longer work. Why? Since it does not account for the addition of $100 on 1st May. We need to modify the formula to handle this case.

Let 

MVB = Market value of the portfolio at the beginning
MVE = Market value of the portfolio at the end
NIF = Net cash inflow

Portfolio Return = ((MVE - (MVB + NIF)) / (MVB + NIF)) * 100
                 = ((1500 - (1000 + 100)) / (1000 + 100)) * 100
                 = (400 / 1100) * 100
                 = (0.3636) * 100
                 = 36.36%

The return is 36.36%. But there is one problem with this approach. Instead of 1st May let us assume that I am adding $100 on 31st Dec. What is the portfolio return?

Date Value
1-Jan-13 $1000
31-Dec-13 $100
31-Dec-13 $1500

The return is the same 36.36%. This does not make any sense. Why? Adding $100 on the last day of the year is not going to affect the portfolio returns. Hence we need to adjust the formula again by removing it from the denominator.

Let 

MVB = Market value of the portfolio at the beginning
MVE = Market value of the portfolio at the end
NIF = Net cash inflow

Portfolio Return = ((MVE - (MVB + NIF)) / (MVB)) * 100
                 = ((1500 - (1000 + 100)) / (1000)) * 100
                 = (400 / 1000) * 100
                 = (0.4) * 100
                 = 40.00%

The return is 40%.

Like cash inflows we can also take cash out of the portfolio. On 2nd Feb I took out $300. In order to differentiate cash inflows and outflows, I have used -ve sign for inflows and +ve for outflows.What is the portfolio return?

Date Value
1-Jan-13 -$1000
2-Feb-13 $300
5-May-13 -$100
31-Dec-13 $1500
Let 

MVB = Market value of the portfolio at the beginning
MVE = Market value of the portfolio at the end
NIF = Net cash inflow
NOF = Net cash outflow

Portfolio Return = (((MVE + NOF) - (MVB + NIF)) / (MVB + NIF)) * 100
                 = (((1500 + 300) - (1000 + 100)) / (1000 + 100)) * 100
                 = (700 / 1100) * 100
                 = (0.6363) * 100
                 = 63.63%

The return is 63.63%. I took out $300 on 2nd Feb but still we are counting it in the numerator as a return for the entire year.  This artificially boasts the portfolio return.

There are two issues in all the cases we have seen so far

  1. Timing of cash inflows and outflows are ignored.
  2. Length of the holding period is not taken into account. Doubling the money in an year is better than doubling it in 10 years. But none of the above formula considers holding period into account. The formula does not have date portion included.

Before proceeding any further we need to understand the time value of money. It is defined as

The idea that money available at the present time is worth more than the same amount in the future due to its potential earning capacity. This core principle of finance holds that, provided money can earn interest, any amount of money is worth more the sooner it is received.

$100 today received today has more value than receiving it one year. Why? If I can invest it today for 10% return then in one year I can get $110.

$100 invested at 10% will become $110 in one year

Value after 1 year = $100 + $100 * 0.1
                   = $100 + $10
                   = $110

Applying the same logic for $100 received 1 year from now is worth $90.90

$100 received 1 year from now will be valued $90.90 today at 10% discount rate

Value today * 1.1 = $100 after 1 year
Value today = $100 / 1.1
            = $90.90

Let us apply the time value of money concept to find out the return on the portfolio given below.

Date Value
1-Jan-13 -$1000
2-Feb-13 $300
31-Dec-13 -$100
31-Dec-13 $1500

In one year there are 365 days. Since cash flows can happen any time of the year, we need to give weight for the day on which it happened. $300 cash outflow happened on 2nd Feb which is 32 days after  1st Jan. Given below are the days when all the cash flows happened.

Date Cash Flow happened on
1-Jan-13 0 days
2-Feb-13 32 days
31-Dec-13 364 days
31-Dec-13 364 days

We need find out the rate in the equation given below. This rate is your portfolio return. Take some time to understand. This equation is called as the internal rate of return (IRR).

1000 = (300 / (1 + rate)(32 / 365)) - 
       (100 / (1 + rate)(364 / 365)) + 
       (1500 / (1 + rate)(364 / 365))

We cannot use algebra to solve for rate. We need to solve it by using trial-and-error. Let us guess the rate to be 50%

1000 = (300 / (1 + rate)(32 / 365)) - 
       (100 / (1 + rate)(364 / 365)) + 
       (1500 / (1 + rate)(364 / 365))

Let rate = 50%

1000 = (300 / (1 + 0.5)(32 / 365)) - (100 / (1 + 0.5)(364 / 365)) + (1500 / (1 + 0.5)(364 / 365))
1000 = 300 / (1.5)0.08767123287671 - 100 / (1.5)0.9972602739726 + 1500 / (1.5)0.9972602739726
1000 = (300 / 1.036186996308119) - (100 / 1.498334630205529) + (1500 / 1.498334630205529)
1000 != 1223.8937463 (Does not match)

The value 1223.89 is higher than 1000. To bring this down we need to increase the rate. Let us guess the rate to be 90%

1000 = (300 / (1 + rate)(32 / 365)) - 
       (100 / (1 + rate)(364 / 365)) + 
       (1500 / (1 + rate)(364 / 365))

Let rate = 90%

1000 = (300 / (1 + 0.9)(32 / 365)) - (100 / (1 + 0.9)(364 / 365)) + (1500 / (1 + 0.9)(364 / 365))
1000 = 300 / (1.9)0.08767123287671 - 100 / (1.9)0.9972602739726 + 1500 / (1.9)0.9972602739726
1000 = (300 / 1.057885517994901) - (100 / 1.896661778781891) + (1500 / 1.896661778781891)
1000 = 283.58456080258582 - 52.72421320380265 + 790.86319805703976
1000 != 1021.7235456558 (Does not match)

Still it does not match. But we got closer to 1000. Continuing this way the solution for this equation is 0.9558. Hence the portfolio return is 95.58%.

This is a lot of work for just 4 cash flows. Our portfolio might have a lot more entries than this.

Luckily excel has a function called as XIRR which will do the calculations for us. You need to enter the dates and the cash flows that happened. Cash inflows are negative entries and cash outflows are positive entries. Do not worry if you do not have Microsoft Excel. Google Docs also has this function and it is free.

I found the book Investment Performance Measurement by Bruce J. Feibel really useful in understanding this concept.

xirr

Advertisements

10 thoughts on “Measuring Portfolio Performance

  1. This is super helpful!! I have a masters in finance and not one professor ever taught me the xirr function. Thank you for sharing and keep up the great work!

    • Thanks Charlie.
      I have been using XIRR for 7 years and I wanted to share how it actually works.

      Regards,
      Jana

  2. This is excellent. I was thinking about how to handle this for a project I am working on. Such timely knowledge, its scary. Serendipity, perhaps.

    Cool stuff.

    • Thanks Anshul.

      As long as we are not doing any future extrapolations excel should be fine.

      Regards,
      Jana

  3. XIRR will work even when signs were reversed i.e. cash outflows with -ve sign and inflows with +ve sign. Do you see any issues?

    • Praveen,

      As you consistently flipping the sign I believe it should be ok.

      Regards,
      Jana

  4. How about the inverted problem?
    1/1/14: 1
    2/3/14: 3
    3/7/14: 4
    4/17/14: 1
    5/8/14: 2
    This is the amount invested and the corresponding dates. If the annual return were to be 12%, what should the amount be worth today 7/15/14?
    Note, “xnpv” will not work as its for future payments. No?

  5. Thanks a ton Jana,
    Even though I’ve been investing in stocks since 2010 i was never able calculate CAGR returns of my stocks till date as almost all of my stocks purchases were not one time investment rather they’re purchased different times at different price. hence only was able to calculate absolute performance now thanks to you’re article i was able finally calculate CAGR.

    BIG THANKS 🙂

Comments are closed.