Equity Mutual Fund Rolling Upside/Downside Capture Calculator

Published: June 24, 2017 at 1:06 pm

Last Updated on

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:

Simplify Mutual Fund Analysis with Upside/Downside Capture Ratios

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.

rolling-downside

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.

Download the rolling upside and downside capture ratio calculator

Hate ads but would like to support the site? Subscribe to our ad-free newsletter and get beautifully formatted full articles delivered to your inbox!

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 wantco-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

 

 

Do share if you found this useful
Hate ads but would like to support the site? Subscribe to our ad-free newsletter and get beautifully formatted full articles delivered to your inbox!

About the Author

M Pattabiraman author of freefincal.comM. Pattabiraman(PhD) is the founder, managing editor and primary author of freefincal. He is an associate professor at the Indian Institute of Technology, Madras. since Aug 2006. Connect with him via Linkedin
Pattabiraman has co-authored two print-books, You can be rich too with goal-based investing (CNBC TV18) and Gamechanger and seven other free e-books on various topics of money management.  He is a patron and co-founder of “Fee-only India” an organisation to promote unbiased, commission-free investment advice.
He conducts free money management sessions for corporates and associations on the basis of money management. Previous engagements include World Bank, RBI, BHEL, Asian Paints, TamilNadu Investors Association etc. For speaking engagements write to pattu [at] freefincal [dot] com

About freefincal & Content Policy

Freefincal is a News Media Organization dedicated to providing original analysis, reports, reviews and insights on developments in mutual funds, stocks, investing, retirement and personal finance. We do so without conflict of interest and bias. We operate in a non-profit manner. All revenue is used only for expenses and for the future growth of the site.
Freefincal serves more than one million readers a year (2.5 million page views) with articles based only on factual information and detailed analysis by its authors. All statements made will be verified from credible and knowledgeable sources before publication. Freefincal does not publish any kind of paid articles, promotions or PR, satire or opinions without data. All opinions presented will only be inferences backed by verifiable, reproducible evidence/data. Contact information: letters {at} freefincal {dot} com (sponsored posts or paid collaborations will not be entertained)

Connect with us on social media

Our Publications


You Can Be Rich Too with Goal-Based Investing

You can be rich too with goal based investingMy first book is meant to help you ask the right questions, seek the right answers and since it comes with nine online calculators, you can also create custom solutions for your lifestyle! Get it now.  It is also available in Kindle format.
Gamechanger: Forget Startups, Join Corporate & Still Live the Rich Life You WantGamechanger: Forget Start-ups, Join Corporate and Still Live the Rich Life you wantMy second book is meant for young earners to get their basics right from day one! It will also help you travel to exotic places at low cost! Get it or gift it to a young earner

The ultimate guide to travel by Pranav Surya

Travel-Training-Kit-Cover This 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 Phone

All 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)

Comment Policy

Your thoughts are the driving force behind our work. We welcome criticism and differing opinions.Please do not include hyperlinks or email ids in the comment body. Such comments will be moderated and I reserve the right to delete the entire comment or remove the links before approving them.

Leave a Reply

Your email address will not be published. Required fields are marked *