--- tags: excel title: Measuring Returns - XIRR() and CAGR() --- # Intro So far, we've covered various computations, which mostly involved algebraic sum / multiplication / subtraction. Except, these are not enough. We need to add new tools in our arsenal to fully unlock the powers of excel, to aid with our day-to-day financial decision-making process. # CAGR CAGR (**C**ompound **A**nnual **G**rowth **R**ate) is a measure of how _fast_ a value has been growing, assuming it's result of a compounding process. Assume value at the beginning of a time period was $V_{initial}$, and if it changed to $V_{final}$ over a period of time $t$, then CAGR or the rate of this growth, can be formulated as: $$V_{final} = V_{initial}(1 + r/100)^t$$ Where, $r$ is the rate of growth or CAGR. This is well known formula of compounding, from your school days. Rearranging for $r$, we get $$r = 100((\frac{V_{final}}{V_{initial}})^{1/t} - 1)$$ In below diagram, we plot value against time ![](https://i.imgur.com/8gOe6fh.png) ![](https://i.imgur.com/tUTQjyD.png) Notice that the growth doesn't have to _look like_ compounded growth. This mathematical formula, just needs two values: - $V_{final}$ - $V_{initial}$ And on top of that, time it took to go from $V_{initial}$ to $V_{final}$ In fact, either of these two points in the Y-axis could be smaller / bigger than one another, or even be negative. The mathematical formula poses no restrictions on that front. However, in the above graph, we've no way of visualizing or placing $r$, the rate of growth (CAGR). It's a power-law formula. Instead of plotting value vs time, we can plot logarithm of value against time. :::info Usually, such plots are commonly known as log plots ::: Let's go back to original formula $$V_{final} = V_{initial}(1 + \frac{r}{100})^t$$ We can take `log` of both sides of the equation, **only if both sides are positive numbers**. :::warning Logarithm, or `log` for short, cannot be used on negative numbers - it results in _complex numbers_, which we don't want to deal with in context of investments and finance. ::: Taking `log` on both sides, $$ \log(V_{final}) = \log(V_{initial}(1 + \frac{r}{100})^t) $$ We can apply formula of logarithms (left to reader as an exercise), to finally arrive at this: $$ t \log{(1 + \frac{r}{100})} = [\log {V_{final}} - \log {V_{initial}}] $$ This is effectively the straight line equation, similar to $y = mx + c$. ![](https://i.imgur.com/P8qD9Ib.png) ![](https://i.imgur.com/pPuR7Oz.png) We have plotted $\log$ of values (Y-axix) versus time (X-axis). The slope of a straight line joining those two points $A$ and $B$, is $\log{(1 + \frac{r}{100})}$, and can be used to compute $r$. --- # Computing 3Y Return A common use of CAGR is to compute returns of a mutual fund or a stock portfolio watchlist (e.g. smallcase). Let's begin with an common one: 3 year return of a mutual fund. Most mutual fund platforms report annualized returns for time periods greater than 1 year, while absolute percentage growth for time periods smaller than 1 year. It's an accepted norm. At exact 1 year mark, either can be reported, since both would be same (annualized and absolute _return_). We know that index funds track the index. In rest of this chapter, we'd compute and compare CAGR of Nifty and that of a Nifty index fund over arbitrary periods of time. From Valueresearch Online, [UTI Nifty Index Fund Direct Growth](https://www.valueresearchonline.com/funds/15830/uti-nifty-index-fund-direct-plan) ([archive.org link](https://web.archive.org/web/20210122010723/https://www.valueresearchonline.com/funds/15830/uti-nifty-index-fund-direct-plan) | [archive.is link](https://archive.is/zSHxj)) has 3Y return of **13.72% p.a.**, as on 26th Mar 2021. ![](https://i.imgur.com/LS94uGU.png) ![](https://i.imgur.com/dErfLa7.png) This is effectively 3Y CAGR of the fund. Our task would be to do these: - validate this number, computing it ourselves - compare against CAGR of Nifty over same time period ### Validating Fund CAGR We need historical NAV data for this. One way we can obtain it via Google Finance. Ticker symbol for UTI Nifty Index Direct Growth is `"MUTF_IN:UTI_NIFT_INDE_1HPGBNK"`. Another option would be to use the AMFI endpoint for latest NAV, and ISIN of this fund to lookup the latest NAV. In this case, since we only need two data points, we can just manually look it up. Steps: - Head over to AMFI historic NAV page: https://www.amfiindia.com/net-asset-value/nav-history - Searching for dates around 26th March, 2021; we find the latest NAV as **96.7892**. - Searching for dates around 26th March, **2018** (we are going back 3 years, for computing CAGR of 3Y), we find that NAV as on 26th March, 2018; was **65.7954**. - Enter these info in a spreadsheet as follows: | Date | NAV | | -------- | -------- | | 26/03/2018 | 65.7954 | | 26/03/2021 | 96.7892 | - Below the last row, write `CAGR` - Spreadsheet doesn't have an in-built CAGR function, but we can make do with `RRI()` function. Invoke it as `RRI(3, <cellID for value from 3 years ago>, <cellID for value as on today>)`. ![](https://i.imgur.com/DtbXC2a.png) ![](https://i.imgur.com/7NRJpGH.png) We could have also used the mathematical formula directly to compute the same ![](https://i.imgur.com/LZbsAGc.png) ![](https://i.imgur.com/wfLfmDk.png) Notice that we've used 0.33 for $1/t$, to denote $t = 3$. This is **not** correct. The number of days between any two arbitrary dates cannot always be expressed as whole numer of years. It might have leap-years, for example. We see that computed value of **13.58%** is quite close to **13.72%** as reported, but we are not quite there yet. On the other hand, growth rate computed using in-built formula is close to the reported value of 3Y return on Valueresarch. :::info Based on today's date, you might get a different value of latest NAV. You should use 3Y return as on today, and search for NAV as on dates near today's date, or dates from 3 years ago. ::: :::warning At the time of writing this, AMFI historic NAV provides NAV data on a maximum of 90 day period, at once. If you're searching for NAV for a particular date, adjust the start date and end date in a way, that would select any 90 day or smaller duration window, which covers that particular date. ::: We've used `GOOGLEFINANCE()` to prepare this video for guidance {%youtube F2cLCI_Nwn0%} {%youtube EvugA04SAe8%} ### Checking Nifty CAGR Now that we've verified CAGR of the index fund over 3 years, independently, and matched with reported CAGR on an aggregator portal; it's time to compare with Nifty CAGR over same period. Once again, we can use `GOOGLEFINANCE()` for getting historic Nifty price data. The ticker for Nifty can be `"NIFTY_50"` or `"INDEXNSE:NIFTY_50"`. {%youtube 3LD3Pesl0eY%} {%youtube 2xbL4UwQ9og%} Final results ![](https://i.imgur.com/ZJDw6u0.png) ![](https://i.imgur.com/CrcYCgN.png) As you can see from your computation, or above image(s); the CAGR numbers of UTI Nifty Index Fund - Direct Growth, and CAGR numbers for Nifty itself; **do not match**. In this case, 3Y CAGR as on 26th March 2021 is 13.73% p.a. for UTI Nifty Index Direct Growth. While, for Nifty itself, it's slightly lower, 12.52% p.a. This is expected! An index fund tracks the TRI (**T**otal **R**eturn **I**ndex), and not the vanilla price index. TRI is price index + dividends. Therefore, over any given period of time - Nifty index funds can have slightly higher return than Nifty itself. - If no dividends were announced in that time period, it can be expected that Nifty index fund CAGR are close to that of Nifty CAGR itself. - As time periods get longer (10Y / 15Y / 20Y), this difference becomes more and more stark between CAGR of Nifty and a Nifty index fund. These have nothing special to do with Nifty itself. If you'd instead picked an S&P500 index fund, or a NASDAQ index fund - similar observations would've held true. We can satisfy our curiosity, by comparing against Nifty Total Return Index CAGR. --- ### Nifty Total Return Index CAGR Similar to last two computations, we just have to switch the ticker. Except, to the best of our knowledge, Google Finance has **no ticker for Nifty Total Return Index**. In other words, we've to manually find and enter these values in our sheet, to compute CAGR. We can obtain these from official NSE India website for historic data on total return index: https://www1.nseindia.com/products/content/equities/indices/historical_total_return.htm Plugging in values for the two dates manually, we get this ![](https://i.imgur.com/RUIKCnd.png) ![](https://i.imgur.com/sWUKDUe.png) 3Y CAGR of Nifty TRI, as on 26th March 2021, stands at **14.09% p.a.** This is much closer to the CAGR of UTI Nifty Index - Direct Growth. However, the CAGR is slightly higher. An index fund is _supposed to_ mimic the total return index, but there can be inefficiencies. Hence, CAGR over a given period can be somewhat different in practice, with some _tracking error_. We won't discuss tracking error in detail here. But we'd emphasize tracking error can be both positive and negative in value. In other words, due to inefficiencies in tracking the index, the CAGR of the index fund can be both higher or lower than CAGR of the total return index, over any given period. --- # Visualization We had mentioned earlier how CAGR relates to slop of a straight line joining two points in a log-plot. We shall now plot these price points in a log graph, and try to validate that our visual idea about CAGR holds true. :::info Since the Y-axis is in log scale, but X-axis is in normal scale; such plots are called _semi-log plots_. For it to be a log plot, both X and Y axes have to be in log scale. ::: Spreadsheets have fantastic chrating / plotting abilities built in. Refer to this video on how to get this working {%youtube JXSn2dZ0Qek%} {%youtube eEAseRxyaAk%} ![](https://i.imgur.com/mFWNLVl.png) <iframe loading="lazy" width="600" height="371" seamless frameborder="0" scrolling="no" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vQ1A0ZY1OU6sWC-8reKrISWdZ46homj4W82eh5BrqD9CfU0JeGw_plmnmlNrC-KvHY3dSJmGm-RmeKM/pubchart?oid=1235068923&amp;format=interactive"></iframe> We see from the videos / images and published chart above, that once we switch the Y-axis to logarithmic values, the three lines start to look parallel to each other. Parallel lines have same slope, and since slope of these lines relate to CAGR of the underlying data set, through a logarthmic relationship; it stands to reason that these would seem parallel to the naked eye. Notice that right up until the time we didn't convert Y-axis values to logarithmic scale, the line for UTI Nifty Index fund was nearly flatlining, kissing the X-axis closely. This was because compared to absolute values of Nifty or Nifty TRI, which are above 10k in this dataset; NAV of this fund was barely even 100. It'd practically be dwarfed in a vanila line graph. However, once we switched to semilog mode, all three lines started reflecting real growth over time. It should intuitively make it clear that absolute value of an asset is immaterial when you purchase it, or the range of values it moves between; only thing that matters is the growth rate _after_ you purchase the asset. The downside of such visualization is unless the difference is stark, or a larger time periods are chosen; most such semi-log plots would result in near-parallel lines in a line graph. --- # Recap and Wrapping Up When it comes to spreadsheet / excel functionalities, in this chapter, we learned how to draw semi-log plots. We have also gained new insights into a demystified process of _return calculation_. Next time we see CAGR reported on any portal such as MoneyControl, ValueresearchOnline, MorningStar, CRISIL, BSE India, Kuvera, Coin, PayTM Money, or even Smallcase etc.; we'd know how to compute and validate these numbers ourselves. ### A word of caution CAGR is a point-to-point metric. Given any two points in a plane, a straight line can be drawn to connect those two points. It says nothing about the journey in-between. For instance, the computation above might have painted a rosy picture, that one might falsely assume _Nifty can easily achieve 12.5%-14% p.a. return over a 3 year time period_. **This is wrong, and dangerous to assume**. The actual journey of Nifty 50 index between those two dates, can be viewed as this (plotted using real Nifty price data, which can be obtained from Google Finance). This is also a semi-log plot, where the price axis (Y-axis) is consisted of log values. ![](https://i.imgur.com/OYMTWEz.png) ![](https://i.imgur.com/0UWuA4g.png) As Nifty TRI and Nifty index fund move closely with Nifty 50 itself; plotting these would lead to similar looking graphs, with lot of volatlity. What we're doing when we compute CAGR, is to connect the starting point and last point on the graph with a straight line, then compute slope of that line. Since two points on a plane can be connected with so many different lines and line segments; there are infinite possibilities of the _journey within that time period_, which CAGR computation won't capture. We've alluded to this in one of our previous chapters, where we had computed NASDAQ-based portfolio's final value. And we've shown that NASDAQ's point-to-point return of near zero, didn't show up exactly as that in a DCA / SIP portfolio in NASDAQ. Similarly, CAGR alone cannot produce any information, on the price movement of an asset within a time period. In next chapter, we'd be introduced to a more generalized measure, that captures more relevant information.