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.

hdfc top 200 returns new 650x264 - Equity Mutual Fund Rolling Upside/Downside Capture CalculatorThe rolling returns performance is not good.

hdfc top 200 upside new 650x274 - Equity Mutual Fund Rolling Upside/Downside Capture CalculatorNeither 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

rolling upside screenshot 650x292 - Equity Mutual Fund Rolling Upside/Downside Capture CalculatorNote: 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.

GameChanger review 4 650x477 - Equity Mutual Fund Rolling Upside/Downside Capture Calculator

2 mini Cover pink - Equity Mutual Fund Rolling Upside/Downside Capture Calculator

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.

mini Final cover - Equity Mutual Fund Rolling Upside/Downside Capture Calculator

You can be rich too Review1 650x254 - Equity Mutual Fund Rolling Upside/Downside Capture Calculator

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!

Hindi You can be rich too - Equity Mutual Fund Rolling Upside/Downside Capture Calculator

Pre-order the Hindi version via this link

 

 

Create a "from start to finish" financial plan with this free robo advisory software template


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