Skip to content

Equity Mutual Fund Rolling Upside/Downside Capture Calculator

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

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

 

 

Free Apps for your Android Phone

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)


About Freefincal

Freefincal has open-source, comprehensive Excel spreadsheets, tools, analysis and unbiased, conflict of interest-free commentary on different aspects of personal finance and investing. If you find the content useful, please consider supporting us by (1) sharing our articles and (2) disabling ad-blockers for our site if you are using one. We do not accept sponsored posts, links or guest posts request from content writers and agencies.

Blog Comment Policy

Your thoughts are vital to the health of this blog and are the driving force behind the analysis and calculators that you see here. We welcome criticism and differing opinions. I will do my very best to respond to all comments asap. 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.

Do let us know what you think about the article