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 1^{st} Jan 2013. On 31^{st }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 1^{st} 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 1^{st} 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 1^{st} May let us assume that I am adding $100 on 31^{st} 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 2^{nd} 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 **2 ^{nd} 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

- Timing of cash inflows and outflows are ignored.
- 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 thepresent time is worth more than the same amount in the futuredue 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 2^{nd} Feb which is 32 days after 1^{st} 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.863198057039761000 != 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.

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

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.

Regards,

Jana

Thanks Jana. I know, not many value investors promote the use excel but it does come handy occasionally 🙂 Thanks for sharing this !!

Thanks Anshul.

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

Regards,

Jana

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

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?

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 🙂