Use this Excel sheet to automatically calculate rolling upside capture, downside capture and rolling returns for equity mutual funds by comparing them with 72 BSE/NSE Indices. While the rolling returns provide a consistency measure for overall reward (return), the upside/downside captures provide a consistency measure for outperformance during up-market periods and protection during down-market periods respectively.
In simple terms, an upside capture ratio of 120% implies that the fund has captured 20% more gains than the index has. Therefore a consistently high upside capture is desirable.
A downside capture of 80% implies that the fund has captured only 80% of the index losses a consistently low downside capture (including negative values) is desirable.
Please refer to these articles for a deeper understanding:
An introduction to Downside and Upside Capture Ratios
It is not practical to expect mutual funds to outperform the index during both up-market and down-market periods. Typically, an aggressive fund will beat the market during up-market periods but will lose more than what the market does during falls.
A conservative fund will contain losses well, but may not beat the market when it moves up as much as aggressive funds.
A look at rolling upside/downside capture will give us an insight about the investing style of the fund.
The excel sheet linked at the bottom of this post will help you do just that.
For example, this is the 5Y rolling upside/downside capture for Quantum Long term Equity vs Nifty 50 TRI.
Notice the red dots. The downside capture is typically less than 100%. Thus the fund is efficient in containing losses (when compared to losses of the Nifty 50).
The blue dots – upside capture – are also less than 100% most of the time. This means that the fund is not aggressive when it comes reacting to market up moves.
The capture ratio (green dots) = upside(blue)/downside(red) is also shown. A capture ratio of close to or above 100% is “good” regardless of investing style.
Now what you make of HDFC Top 200 vs BSE 200 (price index), 3-year rolling from April 3rd 2006.
The rolling returns performance is not good.
Neither the upside nor the downside has been consistently low. Hmm ….
I would suggest that you try it for 5Y or 7Y and see how it looks.
Here is a screenshot of the main inputs page
Note: Step 8 has to be executed to refresh the rolling upside and downside calculation.
Let me know if there are any issues. Next month I shall include upside/downside consistency to the monthly screener data. This hopefully will give me a framework to build the robo-advisory template.
GameChanger– Forget Startups, Join Corporate & Live The Rich Life You want
My second book, Gamechanger: Forget Start-ups, Join Corporate and Still Live the Rich Life you want, co-authored with Pranav Surya is now available at AmazonOpens in a new window as paperback (₹ 199) and Kindle (free in unlimited or ₹ 99 – you could read with their free app on PC/tablet/mobile, no kindle necessary).
It is a book that tells you how to travel anywhere on a budget and specific investment advice for young earners.
You can Be Rich Too with Goal-Based Investing
My first book with PV Subramanyam helps you ask the risk questions about money, seek simple solutions and find your own personalised answers with nine online calculator modules.
The book is available at:
Amazon Hardcover Rs. 271. 32% OFF
Infibeam Now just Rs. 270 32% OFF. If you use a mobikwik wallet, and purchase via infibeam, you can get up to 100% cashback!!
Flipkart Rs. 279. 30% off
Kindle at Amazon.in (Rs.271) Read with free app
Google PlayRs. 271 Read on your PC/Tablet/Mobile
Now in Hindi!
Pre-order the Hindi version via this link
Connect with us on social media
- Twitter @freefincal
- Subscribe to our Youtube Videos
- Posts feed via: Feedburner
- We are also on Google Plus and Pinterest
Do check out my books
Get it now. The Kindle edition is only Rs. 199.
Gamechanger: Forget Startups, Join Corporate & Still Live the Rich Life You WantMy second book is now only Rs 199 (Kindle Rs. 99) Get it or gift it to a young earner
The ultimate guide to travel by Pranav SuryaThis is a deep dive analysis into vacation planning, finding cheap flights, budget accommodation, what to do when travelling, how travelling slowly is better financially and psychologically with links to the web pages and hand-holding at every step. Get the pdf for ₹199 (instant download)
Free Apps for your Android PhoneAll calculators from our book, “You can be Rich Too” are now available on Google Play!
Install Financial Freedom App! (Google Play Store)
Install Freefincal Retirement Planner App! (Google Play Store)
Find out if you have enough to say "FU" to your employer (Google Play Store)