1 of 100

r/IndiaInvestments

Disclaimers and Disclosures

Disclaimers and disclosures that you should read and understand before reading this wiki

The content of this website has been prepared by contributions from multiple authors from the India Investments community on Reddit and Discord.

The content on this website, including but not limited to text, images, videos, spreadsheets, hyperlinks, and computer source code; is written and created solely for educational purposes. It is NOT meant to be fiduciary financial advice. When in doubt, a legally registered fiduciary financial advisor may be contacted. In India, a fee-only financial advisor registered with Securities and Exchange Board of India (SEBI) may be contacted.

The authors of this website may or may not hold positions, either long or short, in any mutual fund, ETF, stock, bond, or any another investment instrument or securities mentioned on our website. These positions may or may not change in the future without any notification whatsoever. The act of an investment instrument being mentioned on our website does NOT constitute a recommendation or endorsement for or against investment in that instrument.

Any and all numerical calculations made on our website in any form may be incorrect and subject to changes without any notification whatsoever. India Investments community and / or the authors of content on this website, cannot be held liable in any form or manner if anyone incurs financial losses in the securities markets based on information, either directly or indirectly, from our website.

The content on this website may include material, including but not limited to excerpts, images, and videos, which has been modified with respect to the original source material. Such modifications have been made only for the purposes of illustration, education, and aesthetics. The original source material should be considered as the definitive source of truth.

The content on this website may include recommendations for certain software, apps, platforms, portals, services, and websites. The authors from India Investments may or may not hold positions, either long or short, in the respective companies who own or invest in these software , apps, platforms, portals, services, and websites. These positions may or may not change in the future without any notification whatsoever. The content on this website might also recommend methods about how to use these software and websites. These methods may or may not be in compliance with the terms of service of the software, apps, platforms, portals, services, and website. India Investments cannot be held liable for any damages or loss anyone suffers because of using the methods shown in our content. The user is advised to read the terms and conditions of the software and websites they use before following the methods and recommendations mentioned in our content.

The content on this website may include external 3rd party hyperlinks which we do not own, control, or recommend in any form or manner. Although we have included web archive links for these 3rd party hyperlinks wherever possible, the content in the original 3rd party hyperlink may or may not be the same as it was when it was mentioned in our website. India Investments does not bear any responsibility whatsoever about the content on these 3rd party hyperlinks and it cannot be held liable for damages or loss anyone suffers as a result of using these 3rd party hyperlinks.

The content on this website is provided “as is”, without warranty of any kind, express or implied, including but not limited to the warranties of merchantability, fitness for a particular purpose and non-infringement. In no event shall India Investments and its contributors be liable for any claim, damages or other liability, whether in an action of contract, tort or otherwise, arising from, out of or in connection with the content on this website or the use or other dealings in the content on this website.

FAQs

Frequently asked queries in our community, and their answers.

Mutual Funds and ETFs

FAQs on all things mutual funds

What’s the best mutual fund I can invest in?

No one can predict how a mutual fund would perform in the long run. Data also shows consistently chasing best mutual funds result in behavior gap. Pick one that you can stay with for long term.

If you google top mutual funds, you'd find plenty of communities or online publications tossing you some names of well performing funds.

No one can predict how markets would perform in near future (short term), or over long periods of time. And when we say no one, we really mean no one at all.

Amsterdam is the oldest stock exchange, founded in the 1460s. London stock exchange is one of the oldest, founded in 1801, with nearly ~220 years of history. US markets have been around for ~200+ years, since the early 1800s (NYSE was established in 1817). Indian markets are relatively young, been around for only a little over 40 years, in any meaningful way. BSE was founded in 1870s, but it's hard to find reliable data since before Sensex was created in 1979. Some even suggest to ignore data from before NSE was created in 1994, which gives us ~25 years worth of market data to analyse.

Despite all these data, there's no prediction algorithm or deep-learning solutions; that can predict future market behavior accurately or near-accurately. Markets would surprise you from time-to-time, and new events would keep on happening in all markets, all over the world.

Which date(s) is/are best for SIP in a month?

There's no such thing. It's rare for markets to move up / down so much in a single month, that a single SIP instalment being on a different date would make a sizeable difference in your long term.

There's no such thing.

It's rare for markets to move up / down so much in a single month, that a single SIP instalment being on a different date would make a sizeable difference in your long term corpus.

If you start investing today via SIP, and analyse 10 years later, you'd find that, say for example, 23rd of the month was the best day to invest for your funds. That's something you can know only if you know the NAV history of next 10 years, which no one does.

What about the apps that show you best date of SIP?

These platforms or services are showing you, the "best" date, to prevent an analysis-paralysis. It's "one less thing to worry about", therefore prompting it to the user would mean higher conversion rate (assuming this is a metric these platforms are measuring - how many people complete setting up their SIPs).

This is good for the platform, as it's helping the user feel psychologically safe that they're able to see some analysis, no matter how useless it is.

However, the only thing they've done is take past NAV data, and found out what has been best date of SIP for a particular fund over a period.

Since past performance is not indicative of future performance, anything derived from past performance is also not indicative of same metric for future.

If between 2001-2010, 5th of the month had been best for a particular fund, say SBI Bluechip (not a recommendation, just using as an example) - that does NOT mean for next 10 years (2011-2020) same exact date was best for the same fund.

For instance, some people believe that the last Thursday of the month coinciding with options expiry date, can be best for investment. This is obviously not backed by any sound analysis.

What does a sound analysis here look like?

You might find articles on the internet that draws some conclusions.

Take the 2010-2020 period. It's a 10 year period, with 120 months. That's 120 transactions. Assuming each month to be of 28 days (to account for February); this would require comparing across 28 time-series data sets, with each having 120 entries; for every fund.

But that's not all! Imagine someone investing on 28th of a month, instead of 1st of a month after salary credit (assume this person gets paid by employer on last day of previous month). Then these 28 days, this amount has been parked in savings account or overnight fund or even short term FD.

A proper analysis on best date for SIP would also have to involve the interest income from this and tax paid on these gains, which again, have varied greatly over any meaningful period.

As you can see, there are too many variables; and that's just with one single fund. India has more than a two thousand mutual funds, as of now.

Finally, consider that people don't invest the same amount via SIP over a ten year period. Some people increase the amount of SIP that they do every year as their salary increases. Some of them pause their SIPs from time to time, or skip a few installments here and there.

To sum it all up; there might be a best date for SIP in a month, and it depends on lot of factors that one cannot easily simulate with past data. But even if they did and found one, that date might not be best for your investment pattern for next 1-2 decades.

Invest on a date of month you're comfortable with.

I’ve to invest in ELSS for 80C tax saving. Which fund(s) should I pick?

Pick a fund that you like. No point having more than one ELSS fund. But know why you're investing in ELSS in the first place, because it's equity, and you should give it longer than 3 years to perform

When it comes to ELSS, present categorization gives the fund managers wide berth in picking stock from Nifty 500 universe of stocks. They can pick any stock from top 500 stocks listed in NSE / BSE - this is different from funds that are categorized as large-cap, mid-cap, small-cap, or multi-cap funds. Only flexi-cap category of equity funds is similar.

This makes it tricky to pick an ELSS fund, because all funds from these category might not be on the same footing.

You can look at past returns, or metrics derived from past returns (alpha, beta, sortino ratio, and other greeks); but all these won't indicate how a fund would perform in next time period of similar duration.

If a fund had 4% alpha on its benchmark over last 3 years; it's not an indication of what kind of alpha it would have going into next 3 / 5 / 7 years.

Active fund picking is hard, and ELSS having widest latitude can be really hard to pick the right fund that'd perform great after you invest in that.

For instance, until 2015-16, Nippon ELSS used to be one of the popular recommendation on many mutual fund portals. Since then, next 3-4 years haven't been kind to this fund, owing to this fund's huge bets on infrastructure / manufacturing sectors.

Conversely, Axis Long Term Equity (not a recommendation, using as illustration) has done better than expected over the same period.

In 2020, Canara Robeco ELSS fund looked like a great fund, doing much better than its peers over last 3-4 years. But 3-4 years ago, or even a year ago, it wasn't doing anything extraordinary, that it'd get into anyone's radar.

The harsh truth is, you can pick an ELSS fund, and at best hope for it to do well-enough. We've discussed in these FAQs how picking the right fund isn't exactly a big requirement - if you pick something that does ok enough, and give it time, that's fine.

ELSS investment which would be countable under 80C deductions is capped at 1.5L per year (though you can invest more than that amount in one/multiple ELSS schemes, the amount available for 80C will remain at 1.5L as per the current IT laws). On top of this, most salaried workers have PF, insurance etc., limiting ELSS investments to much lower value. If the investment principal is small, return difference isn't that important.

Just pick one, you're as likely to be wrong as you could be lucky.

Should I get a Demat Account to buy units in Mutual Funds?

No! You do not need demat account / trading account, to invest in mutual funds.

Intro💫

No! You don't need demat account / trading account, to invest in mutual funds.

Mutual funds are sold by AMC (Asset Management Companies) directly to you. RTA (Registrar and Transfer Agent) of the relevant AMC processes the transaction, and keeps records of your units purchase or selling.

Lumpsum investment vs SIP/DCA

If you're investing for the long term, time in the market beats timing the market. Invest the entire amount all at once, irrespective of market levels. Invest regularly if don't have lumpsum.

This is quite confusing to many people, since the finance world touts SIP (Dollar Cost Averaging in foreign terms) as the best way to invest.

Definitions

Insurance

All frequently asked queries on insurance

Should I invest in this LIC policy?

Avoid. LIC and the agent would take most of it. You'd be left with peanuts. It's one of the most opaque form of investments, and there's no guarantee. Govt. backs LIC, not your financial future.

LIC doesn't create money out of thin air. Over-simplified & shortened description of how it works: It collects premium from policyholders, then it invests that in market-linked securities (bonds, stocks, CP, CD, commodities etc.). Eventually, it pays out policyholders after keeping some cut for itself.

Unlike bank or post-office deposits, LIC returns are not legally guaranteed. Govt. of India is a major stakeholder in LIC, but that does not mean it's a major stakeholder in your future. Govt. would do its best to prevent LIC from going under. But your returns come at the cost of LIC's income - when those two are at odds with each other, Govt. would pick LIC over your financial future.

In practice, LIC returns money you'd paid as premium + some bonus. There are different categories of these bonuses: loyalty bonus, guaranteed addition etc. LIC wants to make you feel they're doing you a favor by giving you some bonus. Historically, a bank FD / RD at SBI would have had higher returns than LIC policy over a given period.

You can know your bonus after the policy matures.

Opinions on investing in smart wealth plan by bank?

Avoid. Insurance company and bank RM would make gains; but you could do much better not mixing insurance with investments. Insurance cover is inadequate, returns from ULIPs are lower than bank deposit

This is not that different from the query on LIC. Bank / Insurance company would take your money, invest in some market-linked securities, deduct insurance related charges; and give you back peanuts. Upside is limited, but bank would sell you on the idea that downside is also limited.

As a rule of thumb, any policy or plan sold by bank, especially one that has a smart in its name, can be avoided without further consideration.

ULIP (Unit Linked Insurance Policy), Endowment plan, Annuity Plan - these are all great plans for the banks & insurance companies. But they are terrible for you.

Before buying into any bank products that are sold with such hopes and optimism, ask the bank RM to put you in touch with an existing subscriber, so you can review returns-in-the hands of an investor who actually invested in that.

Note that just because a bank RM is selling you, doesn't mean its returns are guaranteed by the bank. Other than bank deposits, nothing else is guaranteed, even if a bank sells you that.

You can look at the brochure / policy doc, and you'd see nice fat projections. But that's all those are - projections. Per IRDA regulation, banks & insurers can create projections with 8% and 4% return as illustrations.

That's not a guaranteed amount.

Some banks do sell guaranteed annuity and similar products. Putting those in a single excel sheet, or Google Spreadsheet, and invoking XIRR() function would show you that returns would barely be anything close to long term FD returns. Here's an example from #insurance channel:

As said above, buy term cover if you need insurance. Invest in market-linked cost-efficient assets if you need returns. Most importantly, don't mix insurance with investment, where investment pays for the insurance.

Up to what age should I take term cover?

Till the age someone is financially dependent on your income. Ideally 60-65, no more.

What really is term cover?

A term cover is an income replacement plan. It should be covering you, till you've dependents who're reliant on your income, and miss it if something were to happen to you.

This money would never be yours. It's meant for your family, in the event of something highly unlikely.

Do I need my own health insurance? Employer already has group policy

Yes, you should. Employer provided group policy might have lower cover, or co-pay, or a cap on room-rent. You should get one on your own, to decouple your insurance from your employer

Intro 🍁

According to IRDA (Insurance Regulatory & Development Authority) of India

A group insurance policy gives you advantages of standardized coverage and very competitive premium rates

Should I take top-up policy or super top-up?

Super top-up is better, as the limit is reset on every claim. While for top-up, it's per policy year

Introduction ✨

Before we get to the answer for this, we need to define and establish common understanding around some domain specific terms.

A Base Policy, which could either be a group, an individual, or a family floater policy, covers you and/or your family members up to the Sum Insured.

This is for any hospitalizations; for a minimum period of 24 consecutive in-patient care hours, except for specified procedures/treatments where such admission could be for a period of less than 24 consecutive hours.

This base policy might have restrictions on Room Rent (all major hospital expenses are linked to this parameter, as discussed before) and ICU charges; require you to co-pay a specified percentage of the admissible claim amount and have other restrictions/exclusions under the policy.

Given all other conditions like your age, any pre-existing illnesses, number of family members being insured being the same, your base policy premium will increase with every increase in the sum insured. Hence a ₹2,500,000 (25L INR) sum insured policy will have a higher premium than a ₹1,500,000 (15L INR) sum insured policy.

Is my base policy enough? 🤔

Cost price inflation of medical services and medicines (closer to 10%-15% per annum) and lifestyle diseases can make a base policy of 25L inadequate in the next 5 years, since the time of subscribing in the policy.

Any new policy will also have a waiting period for pre-existing illnesses (PED’s) along with higher premiums due to the PED’s. If you want to future-proof your medical expenses, the base policy alone will not be enough.

The option you then have is to buy an additional top-up or super top-up policy. Both these policies are based on a clause called a deductible.

What is a deductible? 🌵

As per IRDAI’s guidelines on standardization in health insurance:

Deductible means a cost sharing requirement under a health insurance policy that provides that the insurer will not be liable for a specified rupee amount in case of indemnity policies and for a specified number of days/hours in case of hospital cash policies which will apply before any benefits are payable by the insurer.

When you buy a top-up or super top-up policy you must choose a deductible - the maximum cost you need to bear when making claim.

Assume you are buying this policy with a deductible of ₹500,000 (5L INR). What it means is that the first 5L INR of any eligible claim will first be paid by the insured either through a separate base policy or out-of-pocket.

Top-up vs Super Top-up? ☯️

In case of a top-up the deductible is applicable for each hospitalization, whereas in case of a super top-up the deductible is cumulative for the policy year.

Which one do I buy? 💰

Assuming you already own a base policy and are choosing between a top-up vs super top-up, it’s usually recommended you buy the super top-up.

The higher the deductible that you choose, lower will be the premium for the super top-up policy.

Ideally your super top-up policy should have the same renewal date as your base policy. This ensures that both policies overlap each other 100% and the benefits of having these two policies are maximized.

Super top-up policies are available for sum insured of up to 1 crore with some insurers.

Why is the premium low for Super top-ups? 🐤

Insurers can offer super top-ups at a cheaper rate compared to base policy. This is because of much lower probability of higher expenses, that’d require you to use your super top-up policy claim options.

Statistically, it's more likely that your hospitalization costs would be within the deductible limit of super top-up policy. Hence, insurers can afford to offer lower premium.

Stocks

FAQs on direct equity investments

The Stock Market Has Crashed. Which Stocks Should I Buy?

Corrections in the stock market aren't unusual. Do your own due diligence before buying stocks.

Market Corrections Aren't Unusual

A correction or a crash in the stock market isn’t an unusual event, despite what one might think during such an event. ,

Since 1920, the S&P 500 has, on average, experienced a 5% pullback 3 times a year, a 10% correction once a year, and a 20% decline every 7 years.

Foreign Investing

FAQs on Investing in the US and overseas markets

Tax

Frequently asked queries around taxes

I don't have any tax to pay. Do I still have to file ITR?

File a return, it is an important piece of paperwork that can come in handy later. Even if you have no tax to pay. It would take 5-10 mins at worst.

Filing an Income Tax Return (ITR), and paying taxes are two different things. A return filing is a declaration. Even if you're not eligible to pay taxes in a particular financial year; we recommend filing a return nonetheless.

An ITR is a piece of document that might need to be used in the event of a loan application, credit card application, or even visa interviews.

In fact, until you review your form 26AS, you won't know if there's an entity out there who's deducted TDS against your PAN. If so, you can claim a refund from IT dept. only via completing a return filing.

There's no harm in filing one, it clears up a lot of things and get those on the record.

These days, there are services like ClearTax, Quiko etc., that would let you file most common type of ITRs for free of cost. You can prepare and submit directly from these apps.

Miscellaneous

Various frequently asked queries, all in one place

Is Gold a good investment now? It has gone up ~50% this year

If that is indeed true, best time to buy gold was a year ago. It can be a great investment, but one cannot judge good or bad investment only the basis of recent returns.

Past performance is not indicative of future returns. If you pick assets after they have performed well, you'd most likely miss out on assets that performs well after you have invested in it.

If Gold has gone up 50% this year, right time to invest in it would've been 1 year ago.

Instead, question your asset selection process: is your selection criteria good enough for it to have picked Gold as an investment a year ago?

In other words, what were Gold's 1Y / 3 Y / 5Y / 10Y returns, 1 year ago on this date? Most likely, it won't have been good, and you'd have passed on that.

Chasing past returns, hoping for a repeat of performance, is called return chasing. It's common among new investors, but even veteran investors fall prey to this. As a rule of thumb, avoid greed in decision making.

This is not to say Gold is a bad investment for next 1Y / 3Y / 5Y. It might be, it might not be. But the fact that Gold has surged in price in recent times; is not a good reason to invest in Gold.

How To

How to achieve something specific in context of investments, finance, and economics

How to transfer shares from one demat account to another How to move from one mutual fund platform to another How to switch a Mutual Fund from Regular to Direct Plan How to file SEBI SCORES complaint?How to Update Nominee Details?How to rematerialize mutual fund from demat form How to Pay Advance Tax

How to move from one mutual fund platform to another

You just need to stop the existing SIP, import the CAS statement to the new platform and restart the SIP there under the same folio number

Intro👋

There are 2 ways to hold mutual funds. One in the demat form and another in physical (rematerialized) format. The method of transfer will vary depending on the form that your mutual funds are stored in.

How to switch a Mutual Fund from Regular to Direct Plan

If your email ID is linked to the folio, you need to login to the portal of the fund house and select the switch option. If not, you will have to visit the office of the fund house

Intro👋

If you know which mutual fund you want, it is always better to buy a direct mutual fund since it eliminates the commission deducted by AMCs as fees for distributors (the person or entity who had facilitated the transaction(s) for you to invest in the fund).

The difference between TER of regular and direct plan normally varies between 0.5% to 1.5%.

How to rematerialize mutual fund from demat form

Step-by-step guide on how to re-materialize demat units of mutual fund.

Intro 💫

There can be some disadvantages and inconveniences for having the mutual funds in dematerialized (aka demat) form.

Some of these are

Difficult to change the investment platform or DP (Depository Participant)
Vendor lock-in, where you are locked to the brokerage platform and its subset of functionalities.
You will have to pay various charges like annual charges, depository charges etc.

Often, you might find it more cost effective to sell your demat holdings through your broker, and re-buy units in non-demat mode in same fund's direct plan; than going through this process.

You should consider the alternative course of action and estimate your costs for both these processes.

Below steps will help you to understand how to convert your mutual funds in demat form.

Steps to re-materialize 👩🏽‍🏫

Get 2 copies of Re-materialization Request Form for re-materialization from DP for each ISIN (International Securities Identification Number) / scrip / mutual fund.
Submit the form signed by all the holders along with SOA (Statement of Account), Self attested address proof and PAN to the DP.

Charges for re-materialization may apply (as decided by the DP).
Partial conversion may not be possible.

STOCKS

Introduction to the Stocks Series

This series on Stocks will go into detail about how to read an annual report, how to research industry sectors, financial ratios, preparing a due diligence checklist, and much more.

During the course of one's investing journey, one may come across their friends, relatives, colleagues, or simply hear about it in the newspaper or a website, about how profitable stock investing can be.

As on April 2021, there are 3,947 companies available for trade on BSE, the oldest stock exchange in India. Only 500 companies among these are qualified to be on the S&P BSE 500 TRI index while only 30 companies make it to the premier S&P BSE SENSEX TRI index.

Considering that there are approximately four thousands listed companies in the stock market in India, how does one go about deciding which companies to invest in? Where do we even begin? Which companies are "better" than their peer companies also listed on a stock exchange? Which parameters should we employ to judge companies and their stock? Perhaps we should also be asking ourselves — can we beat the market, benchmark indices like S&P BSE SENSEX TRI and Nifty 50 TRI, if we invest in stocks? If one isn't able to consistently beat easily investable indices after dabbling in stocks for several years, is stock investing even worth it?

We'll attempt to answer these questions in this series.

Financial Metrics and Ratios

Financial metrics and ratios are numbers which can help investors interpret the financial performance, health, and efficiency of a company.

Financial ratios are numbers which can help investors interpret the financial performance, health, and efficiency of a company.

Although financial ratios can seem like an attractive tool to make quick judgments about a company, in isolation, a financial ratio conveys little information and, in some cases, can even be misleading.

One of the most popular financial ratios, the price to earnings (PE) ratio, is used by a lot of investors to somehow judge the "value" of a company which may or may not always make sense. Avenue Supermarts, a chain of supermarkets across India, operating under the brand name DMart, made its debut on the NSE on 21st March 2017 at a PE ratio of approximately 105 and since then it has rarely exhibited a PE ratio of less than 90.

Of course, this doesn't mean that PE ratio isn't useful, but one should know how and, more importantly, when to use it. That goes for all financial ratios we're going to talk about.

All that being said, financial ratios can become a powerful tool in an investor's repertoire if

the method of calculation of the ratio in question is accurate
the ratio in question is calculated for a period of time (we suggest looking at, at least, the past 5 years of financial statements) to create a trend of how the company has performed
the ratio in question for a company is compared against its peers and an industry wide analysis is performed

When we say trend, think of Google Trends. Plotting financial ratio data points on a graph over a period of time would tell us a lot about how a company is evolving. Similarly, comparing these trends in financial ratios data against industry peers helps us gauge whether the evolution of the company is satisfactory and competitive.

In the following sections, we'll take a look at various financial ratios, their implications, and their relevance across different sectors.

Work in Progress

These are chapters in progress that may or may not be reviewed yet. Read with caution.

Content in these pages are being worked upon, and not yet ready for publishing. If you are reading these, expect grammatical and spelling errors, incomplete thoughts, and messed-up text in general.

Diving Deeper into Businesses Efficiency Liquidity and Solvency

Liquidity and Solvency

Solvency Ratios help investors evaluate the competence of a company to meet its long term debt obligations. Liquidity Ratios are used to evaluate the ability to pay off short term debt obligations.

Debt to Equity Ratio

The Debt to Equity ratio tells us how much a company’s debt weighs against its shareholders equity. It gives us an overview of a company’s capital structure by measuring the amount of employed debt against equity.

The Debt to Equity ratio can be defined as

We’ll consider total debt as the sum of financial obligations which bear interest and lease liabilities. This includes current and non-current borrowings, lease liabilities, current maturities of long term debt and lease obligations, and any other liabilities that can bear interest. We will

EXCEL

Using External Data : Working with CSV Format

A close look at how to parse CSV data dumps and process the same in your excel sheet or spreadsheet

Prelude

In the previous chapter, we've seen how to get historical market data from Google Finance API.

We used GOOGLEFINANCE() function to get NASDAQ ticker data, over a given period of time.

However, you might have also noticed that this is not a

Quantifying Returns: CAGR and XIRR

How to do returns in excel or spreadsheet? Read along to find out

In investment parlance, returns is a big deal. It probably gets more attention than almost any other metric or ratio out there, and occupies the largest mindshare among most investors.

So far, we've carefully avoided this topic to do it justice because returns also happen to be commonly misunderstood and are often erroneously focused upon.

To begin with, returns are not just some numbers on a screen and the goal is not to have as high returns as possible. It's instructive that as investors, we develop a good visual and mental model of what these numbers / metrics mean and interpret it in the right context.

This chapter's child pages are meant to enhance our knowledge, so we can have higher-level discussions about returns. At the same time, we aim to empower ourselves so we can compute these values independently on our own using spreadsheets rather than relying on external online tools. This would help us make better financial decisions.

BONDS

Bond Basics

A bond is an instrument of indebtedness of the bond issuer to the holders.

Basics

A Bond is a contract, between an entity taking the loan (issuing the bond) and another entity providing the loan. It denotes the indebtedness, and captures the necessary information on how much to pay back, and when.

Who issues bonds?

Companies that need capital (fancy way of saying access to large sums of money), can issue bonds. Central banks, like RBI or Federal Reserve or Bank of England, can and do issue bonds. Govt. of a country (GOI, for example) as well as state governments, can also issue bonds. Bonds are market-linked securities, just like stocks. Not unlike stocks, bonds also trade daily in exchanges (often called "secondary markets") such as NSE / BSE etc.

Why?

A company issues bonds to raise capital from the capital markets. It helps with their immediate need for cash. Which they would invest in growing their business. The belief here is they can generate much higher return than it'd cost to pay interest on that loan raised through bonds. Central banks, governments etc. do it for similar reasons as well; but instead of business growth, they're more focused on controlling flow of money within economy and issuing bonds in capital markets is how they start. The buyer of bonds is getting interest on money lent out against bonds.

Is currency a bond?

You might think that 10 rupee note in your pocket is a bond issued by RBI, because it clearly has the signature of RBI Governor, and some legalese printed on that. No, it's not a bond. Currencies are not bonds, even if they're issued by central banks.

History

Bonds were government debt instruments issued by the governments to finance government spending as an alternative to taxation. The first issued bond can be traced to Mesopotamia in around 2400 BC. The bond was backed by grain similar to how modern era secured bonds are backed by assets, as was customary of the time. Failure to repay the bond resulted in forfeiture of this surety. The staple currency in the era was corn. As loans were primarily taken to finance growing crop or herding cattle, interest payments were made in the form of the harvests of said crops. Over time silver became the dominant currency due to the perish-ability of other commodities. The modern form of bonds came to life when European merchants started lending to the aristocracy to fund sovereign spending.

What to look for?

Bonds can be issued by Governments (sovereign entities) or by corporates. Depending on that a lot of things will vary, including how risky the bond is but there are some things to keep in mind while looking at bonds which we will talk about in more depth as we progress in the series.

Coupon

Coupon is simply the rate of interest that the bond will pay out throughout its life. Depending on the type of the bond this can be fixed from the date of issue or keep on varying. Payment dates too can vary.

Yield

It is the rate of return. How does this differ from the coupon? Simply put not all bonds give out interest, some bonds instead are sold at a discount and you get a higher value back at maturity. Think of it like a cumulative FD if you may.

Yield to Call

A call option is a special provision in some bonds where the bond issuer has the special right to immediately pay off the bond. Think about how you might want to pay off a loan after getting a promotion with a large hike. Quite often a bond that offers a yield much higher than average will be "called" so it is prudent to check both the yield to maturity (yield which you will get if held till maturity) and yield until the date when the issuer has a right to call the bond. As an addendum there is another feature in some bonds called a put option which is the reverse ie it lets the bond holder force the issuer to pay the money back early, a premature FD closure if you may.

Credit Quality

As mentioned earlier not all bonds are made equal. Some bonds are riskier than others, this risk can be estimated by a "credit rating" which is a score that indicates how risky a bond is.

MISCELLANEOUS

Miscellaneous

This section is meant to hold entries which do not have a section of their own, yet.

US Investing Recommended Reading

New to Investing

Zero to Investing

Anyone who has no idea about how to start investing can start with this

WARNING: This section of the wiki needs an overhaul and the information presented in this section may not be up to date or reflect the current state of affairs. However, the concepts presented in this section should still hold true and valuable.

If you're interested in making this section better, send us a message on our Discord server.

Part Two - Defensive Setup

Now that we want to park the money into a more flexible instrument, we can proceed to next options. You focus on what can go wrong and take care of it first.

Part 2A is Health Insurance.

Take a decent amount of health insurance (more correctly called Sickness Insurance, since what it means is that when you get sick and get admitted in a hospital, then you get part or all of the medical expenditure you did).

To shortlist the options, I checked coverfox.com with the following options:

Investment Philosophy and Strategy

Basics of Investment Strategy Plan

If you're interested in making this section better, send us a message on our Discord server.

Basics of Investment Strategy Plan

THE Basic PRINCIPLES:

It should be fairly straightforward. No need for assessment of complicated risk tolerance (graphs/psychological tests etc). Follow the KISS principle (Keep it Simple and Stupid).

Various types of Risks in Investments

A common definition used is 'Risk is the uncertainty that an investment will earn its expected rate of return.” It includes both Upside and Downside Risk. Although, we like the Upside Risk (return much greater than expected), we abhor the Downside risk. One needs to understand that both are sides of the same coin and mostly cannot be separated. If you want an instrument which can give / has given higher returns than expected in the past, then in future, the opposite can happen too.

There are plenty of places in which one can find the various types and details of various types of Risks. References:

Are you a Stock or Bond?

Evaluate yourself as having income like a stock or bond or somewhere mixed and then accordingly plan out your asset allocation on a personal level.

One way to view human life is to see it as a process of converting human capital into wealth, both financial and real. When you start out, the human capital is high while wealth is low. Midway (around 40 years), there should be assets which show the result of past 15-20 years of use of the human capital. At 65-70 years of age, the human capital is almost spent and now the wealth capital has to take care of rest of the life.

Now it is important to understand how the human capital is being converted into wealth. If it is in the way of:

A solid government job, without firing, then it should be considered as a solid bond with a decent coupon rate.
A big firm with good HR practices, then consider yourself to be a low-risk blue chip stock.
A freelancer with variable pay and/or performance linked pay, then consider yourself as a non-bluechip stock.

Place yourself in the risk-return continuum according to above 3 examples.

How you should allocate your assets depends upon what you are. So if you are a bond, then you should have an increased allocation to the equities. While if you are the freelancer group / non-bluechip stock, it would be better to have more allocation to the bonds in your wealth portfolio. The more like a stock you are in your income flows and job security, the lesser should be your allocation to equity. This is asset allocation at a totally different level and makes the investing decision much simpler for an individual.

Critical Mass

Critical Mass is a place in which individuals enjoy their own personal financial nirvana. Differentiation between earned income and assets is a fundamental lesson to learn when thinking in terms of it

A state of freedom from worry and anxiety about money due to the accumulation of assets which make it possible to live your life as you choose without working if you prefer not to work or just working because you enjoy your work but don't need the income. Plainly stated, the Land of Critical Mass is a place in which individuals enjoy their own personal financial nirvana. Differentiation between earned income and assets is a fundamental lesson to learn when thinking in terms of critical mass. Earned income does not produce critical mass......critical mass is strictly a function of assets.

Taken from .

Critical Mass, as a concept appears to be slightly different from the Retirement corpus.

Initially, we grow our assets by savings. Investing these savings over time then starts to accumulate assets in terms of yearly incomes. A simple 10% saving rate, if not invested, will accumulate one year's income in 10 years. While with investing, it will do so in much lesser period depending upon the rate of investment return. Then one day, we see that we have assets worth income of multiple years. And after some years, the yearly growth of our assets can become equal to the yearly income. At this point in time, we have reached the Critical Mass. And now the possibility to retire from a day job / salaried job is a real possibility.

Asset Rebalancing

Main Ideas:

In a well-diversified portfolio, the individual investments are expected to generate a certain rate of return based upon their characteristics and risk profiles.

Insurance

Severe medical emergencies or sudden loss of income can be pretty ghastly to one's finances. An insurance policy acts as a shock absorber. Read more about various insurance policies here.

If you're interested in making this section better, send us a message on our Discord server.

Life

Life insurance and various forms of it. From ULIP to term insurance, check which one you need, and which one should avoid.

Some FAQs on Life Insurance

Commonly asked queries on term / life insurance

Q1. What happens if the company I have bought insurance from goes bust? Is there any mechanism to protect customers like Deposit insurance corporation?

A1. The IRDA regulations mandate that the solvency ratio (mainly indicates the assets versus liability adequacy) of the insurance company should have a decent margin of safety. Check this for more details. Currently, all the companies have decent solvency ratios. Hence, we do not need Deposit Insurance corp or any other such fallback for that.

Q2. How do insurance companies use funds to pay? Is it 'pay as you go' OR 'is it that they invest the money and pay accordingly when the claim arises'?

A2. In short, the latter. In the longer form the basic idea is that the mortality actuarial rates of the life insurance premium is calculated in such a way that probability wise, insurers make money on those. Eg, for a person of age of 30, the old mortality rate (used by LIC) was 1% (example) then they would price it as 2%. On a probability play, they are betting that if 100 persons aged 30 take Re 1 insurance per person, then only 1 of them dies (on an average because the mortality rate is 1%). They would then have to pay Re 1 to the dead person's family and keep the rest (Re 1) as profit. for regular term insurance, the company sets up a way to invest partially the extra premium (1% from the total 2% of the above example) and uses it to compensate in the later years. for single payment term insurance, the above is done in a much bigger way. for ulips / endowment policies, whether they are transparent or opaque, the mortality premia are deducted from the total amount regularly and work in a similar way as the first example.

Links to Answers related to Life Insurance

External links, to common queries and responses, regarding term or life cover

1. Short Primer on Life Insurance as a comment:

Health

All things health insurance

- this includes a pointer regarding Room Sublimits.

Varun Dua (Co-founder of Coverfox.com)

Post/Comment Section

Others: Disability / Home

Disability and critical illness related cover. And home insurance too.

HouseHolder Insurance

ULIP - Unit Linked Insurance Plan

An opaque form of investment, that's costly for you, and doesn't adequately cover you. More importantly, you cannot exit if you choose to, not until maturity.

BASIC DEFINITION

This is a single instrument / vehicle which gives Insurance and Investments. It uses the income tax laws to provide tax-free returns(most of them do, but changing laws sometimes changes the status of such plans – so always do check the actual laws and the relevance of the particular Ulip).

In other words, Ulip is a type of mutual fund (in the sense that they get your money, convert them into units and professionally manage the money while charging you a management fee) which uses the tax laws to make the entire instrument tax free or as tax-friendly as possible.

BASIC TENETS:

The insurance component: Usually it is kept to a minimum. Prior to 2-3 years, most of the Ulips had a minimum of 5x premium of insurance cover. Why? Because the tax laws then said that for any insurance plan to get included in 80C section, it had to have a premium of <20% of the insurance cover. eg. If the plan had to have a cover of 1 lakh, then the premium should be less than 20k. Then this limit was changed to 10x (=max. 10k premium for 1 lakh cover). However, you need to remember that the insurance cover provided is 10x on the lower side. On the higher side, it can be 20-30x also. There will be proportional increase in the Mortality Charges too. Compare this with the amount of cover which an online policy can give – around 1000x.
The Investment Component: The money which remains after deduction of the various charges is put into one of the fund(s) allowed in the particular fund of that company. Then that money behaves like being in a mutual fund (gives the name Unit-Linked).

VARIOUS CHARGES:

Mortality Charges- Since this is an Insurance policy, there are mortality charges which are deducted usually every month (not quarterly or yearly). Usually, it is done in terms of Total Cover – Fund Value and the relevant mortality rate is applied. Eg. For a male of 30 years, if the cover is of 10lakhs, while the fund value of the policy is 2 lakh, the rest 8 lakh cover incurs the mortality rate of 1.91 per thousand which means an yearly premium of 1,528 (or 127 per month) plus service tax (12.36%). If the fund value is 6lakh, the mortality rate will be 764 per annum. Only when the fund value is more than the Cover, the mortality charge becomes Nil. The charge is applied by removing the corresponding amount of Units from your total fund units.
Premium Allocation Charges- This is a variable rate and is mostly related to the Commission to the Insurance Agent. However, on the lower side, it is @ 2% while some years back it used to be 50-70% in the first year, then 20-30% in second year. Usually it is there in the first 5 years (basically the absolute or relative lock in periods). Prior to Sep 1, 2010, the Ulip structures were for 3 year lock in, so the major premium allocation charges were in those 3 years. Later on, the 5 year lock in was put, so the Ulips were redesigned to distribute the charges accordingly. Even the Top Up premiums undergo this charge but usually to a lesser amount. This amount never goes into Insurance or Investment at all.

In short, any type of benefit is charged accordingly, and nothing is free. The only major difference from the other traditional insurance policies is that everything is clear and written in Ulips while it is not so in those.

FLEXIBLE OPTIONS:

Top Up: Over and above the normal insurance premia, you can put extra amount of money as Top Up Premium. This now requires you to have additional corresponding amount of insurance (in the older Ulips, the additional insurance cover was not mandatory). Eg. If by 10k premium you get 1 lakh, an additional 10k top up premium can get you an additional cover which can be variable (eg from 1.1x to 5x to 10x). The other difference is that the Premium Allocation Charge is usually lesser than the normal premium. However, in the newer policies, the top up will have a lock-in of 5 years from the date of the top-up.
Switching: Out of the various fund options available in a policy (some have 5, some have 7, etc), you can allocate the entire money in a liquid fund, or a longer term debt fund, a large cap type of fund, mid cap or multi-cap, etc. In general, out of the various options, you can allocate your fund money in parts or in total to 1 or more funds of that policy. Some policies have Automatic Switching options in which according to age, the percentage of equity-debt will change or according to an increase in decrease in the fund allocations, an appropriate automatic change will occur. Mostly, such additional options do incur charges but this also depends on the policy.

RISKS:

The insurance risk part is borne by the company and the Mortality Charges are accordingly set. In this way, this part is not different from the plain vanilla term insurance.

The investment risk part is completely the responsibility of the policy holder. This is the same as in any Mutual Fund.

REVIVAL:

For Ulips, there are usually no Revival options. If the policy is discontinued or foreclosed, then the appropriate option is followed automatically. Compare this with traditional policies, in which a policy can be revived later too.

What is BAD about Ulips?

INSURANCE Part:

Firstly, Insurance is an expense and by combining it with a savings/investment option, the purpose of the entire instrument becomes kind of tug of war between two different aims. The primary way should be to check the eligibility and requirement of the life insurance for the person which includes:

(I) Whether life insurance is required or not? If there are no financially dependent persons, life insurance is not required.

(II) If it is required, how much is the requirement? For this calculation, check this .

The 10-30/40 times premium Sum Assured FOCUSES on the amount of premium which you can pay rather than the primary aim of The Total Sum Assured (Life cover amount). Eg. For a 30 year old male, the online plans give a sum assured of approx. 1000 times yearly premium. For offline the corresponding value is anywhere between 500-700. At higher age groups and with large term periods, there is corresponding lowering of the sum assured.

In short, the insurance component of a Ulip is 99% of times inadequate as a sole policy.

Secondly, the mortality charge table used in Ulips is higher as compared to mortality charge table for the same company in its own non-ulip term (offline / online) policies. In general, the inadequate insurance cover is expensive too.

INVESTMENT Part:

Part 1: CHARGES

The Premium Allocation Charge is a front-loading charge, which means the charge is levied first and then the rest of the money is put for investment. Compare this with ongoing charges like Fund Management Charges or Back-loading (eg Exit loads in which the charge is levied at the time of withdrawal). In older times, when there was an Entry Load on the General Mutual Funds, which now is not present. This charge as mentioned is basically a commission charge for the agent and/or company, and does not help the investment in any manner.
Policy Admin Charge- completely useless charge. Everything can be seen online and this charge is waste of money. Over that, this charge usually increases year on year.

Part 2: FUND MANAGEMENT

In older times, the insurance companies used to delegate the responsibility of fund management to the regular mutual fund companies and did not require a separate fund management team. Now they are required to do so. In most companies, the size of the investment team is small (sometimes even 1 or 2 main persons managing the various funds). The quality of fund management in insurance companies is thus lower than that of the regular MF companies. The 1.35 -1.5% FMC (adding service tax of 12.36% means this gets translated to 1.5-1.7% net) of most equity funds in the various Ulips is comparable with the charges in the Direct Plans of the regular equity mutual funds. Similar is the case with the debt mutual funds. If you will look in more detail, then presently the FMC of equity and longer debt Ulip funds is in general slightly lower than corresponding regular funds, while those of liquid/shorter term debt ulip funds is slightly higher.

As far as I know, simple index funds are not available in any of the Ulips (I have checked the insurance fund section of Morningstar.in and have not found any index fund). For some people, this is a very decent option with minimum charges (but then, Ulips are not cost-effective at all).

Part 3: The Various OPTIONS-

Switching Options:

This is touted as a major benefit of Ulips. The ability to move money from equity to debt at market tops, and then move money from debt to equity at market bottoms is extremely beneficial. The only problem with that is no one has been able to do that consistently ever (at least that is what all the major gurus say in the world all these years). Moreover, market tops and bottoms are only apparent in the hindsight and not when they are occuring. So, if all the smart people have never been able to do that consistently, how can that be expected from a relatively unknowledgeable / less knowledgeable person buying a Ulip. It may be helpful for a very select few, but not for the 99.9% people. Also, behavioral finance tells us that if given an option like that, most people would do it in reverse, that is put money into equities near tops and put money into debt near bottoms. This is even worse than not doing anything at all.

Part 4: ILLIQUIDITY and LOCK-IN

If the management team of a regular mutual fund is not performing to your satisfaction, you can remove your money from them and put that amount into a different company. OR you can remove your money within the fund company. In Ulips, neither you can change the fund management company nor you can transfer away from funds available in your particular Ulip to another fund within the same company.

The surrender / discontinuance charges are extremely high. Compare this with 1-3% exit loads in various regular mutual funds.

The Illiquidity costs in a Ulip are very high WITHOUT providing a corresponding degree of benefit.

MAJOR CONS:

Too many charges. = Expensive and Complex.
Not-so-great management teams as compared to regular Fund Companies. = Suboptimal Management with comparable management expenses.
Very Illiquid, without providing any corresponding benefit for that illiquidity.

What is GOOD about Ulips (=Pros)?

Ulip is probably the perfect option for those people who get utterly confused and get policy-paralysis or decision paralysis (No, this is not an uncommon thing. More and more options, after all there are thousands of mutual funds in India, just create a severe type of confusion and to prevent selection of a bad or suboptimal option, one just does not choose any).

The advantages of Ulips are:

They give you an equity based tax-free option in the 80C category. The other pure equity based option is ELSS (Equity Linked Savings Scheme). While private pension plans (like Templeton India Pension Plan and UTI Retirement Benefit Pension Plan) are hybrid debt oriented mutual funds with decent options) are also there. In case, ELSS get dropped in DTC, then these will remain as the only pure equity based plans (of course, one can choose an 80% or 50% equity option too).
Although, the insurance component is inadequate in these, but as an asset allocation instrument (and NOT as a market timing instrument), this provides free transfer from equity to debt or vice versa. Free both in the sense of a number of switches every year as well as no payment is required in terms of capital gains tax as would happen if this is done outside between mutual funds or direct stocks, debt instruments, gold and cash.

In short, presently Ulips are mostly good in terms of behavioral financial aspects.

Researching a Sector

How do we research a sector — in the Indian context

Disclaimer & Disclosures

The author of this section DOES NOT have any long or short position for companies used in this section for illustration purposes as of the date of being published. This may or may not change in the future.
The companies used as examples are mentioned solely for educational purposes, mention of any company is random and is certainly NOT an investment recommendation.

Introduction

As individual investors, our primary focus is to,

find disparities between a company's market price (the price at which it trades) and its intrinsic value (the underlying value of the company we find fair).
make an assessment on this disparity, and attempt to exploit it to make money.

Can't catch 'em all

A prerequisite to finding such a disparity is having a set of companies to work with. Collectively, the market is made up of thousands of companies; it is chaos, an unexplored territory with limitless potential, if you will.

It would be naive to take a shot at tracking all the listed companies. So, we need a method to carve out order from this chaos so to speak. Intuitively, arranging companies under different buckets based on some basis seems like a good starting point. One such basis is categorizing companies on the basis of the sector/industry under which it conducts business, then further categorizing them based on sub-sectors, and so on.

For example, a company 'A' selling chocolates and a company 'B' selling pencils would both broadly be part of the 'fast moving consumer goods' (FMCG) sector, however, company 'A' would come under Foods sub-sector, and company 'B' would come under Stationary sub-sector.

This categorization helps us keep track of a broader universe of stocks - it gives us an approach for thinking about companies based on the sector under which they operate before we even begin conducting our due-diligence on them. Once categorised, we can work with the sectors that we understand.

Categorization, sure — but then what?

There are two investing strategies with which we can use this categorization,

Top-down strategy: This strategy involves studying macroeconomic factors to narrow down preferred sectors to invest in, then finding the best opportunities in such sectors. The focus on individual companies is lower here, and sector conditions are the prime focus. Sometimes, top-down strategy investors also prefer buying baskets of 2-3 stocks in a sector rather than finding the strongest player in the sector.
Bottom-up strategy: In this approach, we first find potential opportunities in companies based on some set of criteria or a checklist, and then slowly move up to studying the sector and its players. The focus on sector conditions is lower here than in top-down strategy; the idea is to find companies that can remain healthy even in sector downturns, and so can outperform its peers over the long term.

Either of them does involve at least some sectoral research, so let's get to how we can do that.

A framework for Sectoral Analysis

Broadly speaking, a sectoral research should include,

Information about the product, and its applications,
Understanding the value chain of the sector,
Market history, key events, and an overview on current conditions, and,

To keep up with this section, I implore you to pick a sector, and try answering the questions I frame for each of these points. I will try to provide examples from different industries for each section for your understanding, but you can stick to the sector of your preference and pencil down the answers for the sector you picked.

Information about the product, and its applications

What is the sector selling?

Quite self-explanatory and perhaps overly simplistic, but noting down does help to answer a few forthcoming questions that are often ignored. For example,

Fast moving consumer goods sector, often abbreviated as FMCGs, sell products that are generally cheap, and sell quickly as a consequence of frequently being used, such as packaged foods, beverages, stationery products, etc.
Financial sector, often abbreviated as BFSIs in India, deals with financial products such as loans, insurance, brokerage services for capital markets, etc.
Pharmaceuticals sector sells medicines, drugs, vaccines and other health related products.

How is the sector sub-categorized?

Most, if not all sectors can further be divided into different segments, and you'll often find the economic dynamics of such segments differ from one another, but generally not contradicting that of the sector itself. Often, a company operating under a segment eventually forays into all sub-sectors. For example,

Fast moving consumer goods sector can be categorized into personal care, home care, packaged foods, beverages, and stationary/office segments.
Automobile & components sector can be categorized into pureplay automobile manufacturers, and auto-ancillaries.
Utilities sector can be categorized into Power, Gas, Water, etc.

What is the market size?

Market sizing refers to the estimation of a variable with little or no data available. In general, market size refers the potential revenue / volume a company can attain if it had 100 percent of estimated market share in the sector. Determining market size helps us perceive,

the growth potential of companies operating within a sector, and
the growth potential of the sector itself under conducive macroeconomic factors.

For example,

Domestic automobile sectors sold 21.5 million vehicles in financial year 2020.
Fast moving consumer goods sector was a 3.4 lakh crore market in India as of financial year 2018.

Is the sector prone to disruption?

There is great value in identifying potential disruptions in a sector. There are two kinds of disruptions a sector can face,

A change in business dynamics of the sector. The obvious example of this kind of disruption is the entry of Reliance's Jio into the telecom sector forcing the incumbent players to either match its prices, or shut shop.
A fundamental change in the product itself. An example of this is the introduction of affordable electric vehicles in the automobile sector.

Understanding the value chain of a sector

A value chain analysis refers to evaluating various aspects involved in running a business, such as,

Procuring required raw materials & machinery,
Recruitment of labor / workforce,
Conducting Research & Development (R&D),

We evaluate value chain of a company to,

Understand the value created by a company in a marketplace,
Understand potential inefficiencies and bottlenecks faced by a company in building & selling its product,
Take account of steps taken by a company to weed out such inefficiencies and bottlenecks.

If you think about it, a company's intrinsic value is some function of the additional value it exclusively creates in the marketplace, and the relative efficiency with which it conducts its business. I feel this is roughly what people mean when they're referring to a company's competitive edge, or what Warren Buffett calls moat, which Investopedia defines as,

a distinct advantage a company has over its competitors which allows it to protect its market share and profitability.

This additional value in the company's products can be many things - brand power, brand recall, pricing power, first movers advantage, technological superiority and so on.

You will find commonalities in value chain analysis of most companies working under the same sector. Thus, a broad understanding of value chain of a sector essentially gives you a bird's-eye view of any company operating in it. Paradoxically, the process of understanding value chain of a sector itself needs an assessment of value chain of a few companies in the sector. In due-diligence, there truly are no shortcuts.

Let us go through the mentioned aspects of a value chain with some examples—

Preferred geographical locations

Though this aspect doesn't necessarily belong to value chain analysis, the destination of production sites is often relevant in a company's business model. This can be in terms of keeping costs of building the product low, procurement of relevant raw materials, or recruitment of skilled labor.

For example, Ruchira Papers, a paper manufacturing company, prefers Himachal Pradesh as a preferred manufacturing destination as the energy costs in the state are favorable, and raw materials (softwood pulp, wheat straw) are readily available from Punjab and Haryana.

A sectoral example of this is a large number of cement production plants in Rajasthan due to the state being rich in limestone.

Procurement of raw materials

Companies like to keep their raw material landed costs as low / benign as possible without compromising on intended quality of their products. This is, of course, because volatility in raw material prices would affect the cost of production, which in turn affects the profitability of the company.

A company has few avenues to keep raw material costs benign, such as,

Using raw materials efficiently by optimizing usage and minimizing wastage.
Having favorable long term contracts with suppliers to safeguard against volatility and achieve security of supply at favorable prices.
Minimizing the proximity of the company's production site to the supplier, since transportation of raw materials is generally borne by the procurer, directly or indirectly.

For example, Tata Metalliks, a company that was initially engaged in producing and selling pig iron, has opportunistically pivoted to primarily selling ductile iron pipes in recent years. Note that one of the key raw materials needed for producing ductile iron pipes is pig iron, which the company has been producing for years.

Recently as you may have heard, pharmaceutical manufacturers have started relying on backward integration for the supply of active pharmaceutical ingredients (APIs), which is the raw material needed for production of drugs.

Procurement of machinery and equipment

Some sectors require frequent capital expenditures on machinery and equipment. This may be for,

Capacity expansions,
Maintenance, or
Replacement of older equipment / machinery

In such cases, it may be worthwhile to understand the costs and specifics associated with such capital expenditures.

Nascent sectors are a good example of this. As you may know, the Electric Vehicles (EV) sector needs constant investments for building charging stations — the adoption of the technology heavily depends on it. The CEEW Centre for Energy Finance estimates in its report that an investment of $2.9B is needed to develop 2.9 million public charging points in the country by FY30.

However, this is not to imply the capital expenditure needed for machinery and equipment is frequently done only in nascent companies. Some traditional sectors require constant upgrades too, and thus are capital intensive in nature.

For example, consider the telecommunications sector with evolving technology standards for their network infrastructure. It is estimated that Reliance's Jio, Airtel, and VI (formerly known as Vodafone Idea) will need to spend a combine of roughly $28B in capital expenditures in the next few years to build infrastructure of 5G.

Recruitment of labor / workforce

Different sectors require varied kinds of the workforce needed to conduct its business. The cost of recruiting a company's workforce would depend on,

The skills required of the labor to conduct business, as different qualifications would dictate different wages.
The availability of such labor, as the fundamental supply and demand principles apply.

For example, the IT sector would consider software developers and project managers as its workforce, which for reasons not in the scope of this section, requires skills that command higher wages. Employee costs are generally 50-60 percent of operating costs in these Tier-1 players of this sector. Thus, while tracking the IT sector, employee costs would be one of the key metrics to keep in mind.

Conducting Research & Development (R&D)

Research and Development (R&D) costs refer to expenses incurred in innovation & enhancements of products, services, and various activities of a company. Such investments are essential for a company to stay competitive in the market. Generally, sectors with higher rate of change in underlying technology have higher R&D costs, such as health care, telecommunications, and automobiles.

Building, testing, and releasing the product

While the various input costs we've discussed so far affect the margins (a measure of profitability) of a company, the real value addition of a company in the marketplace comes from its manufacturing or production activities. In other words, a company purchases inputs (such as raw materials, power, etc) from its suppliers, and transforms these inputs into finished and marketable goods.

Consider a company that purchases such inputs, and for some reason can't convert it into a finished good. What will the company do with these inputs? If it is sellable in the market, such as raw material, it would do that. But since there's no value added by the company, it would at best fetch the same prices for such inputs that it bought it for (considering there's no price arbitrage). The company wouldn't get any profits.

So, studying the production (or value addition) process of a company is essential to understand how a company generates its profits. Let us take an example here, that of manufacturing of solar modules. If you are not aware, solar module are components that convert solar radiations into electricity. A solar module manufacturing process is as follows,

The company would purchase inputs in the form of semiconductors (typically Silicon), tempered glass (specialized for solar modules), silver paste, and electrodes.
Silicon in crystalline form is melted and cast into blocks. These blocks are cut into thin wafers.
The wafers are then used as diodes, so that charges flow in only one direction. Thus, current is generated.

Suppose you had bought a company that sells solar modules. If you had not studied the manufacturing/assembling pipeline, you would probably not be aware of the existence (let alone importance) of solar glass in the modules. In an event where there is shortage of solar glass in the market, the company would have to either pay extraordinary prices, or in the rare occasion, shut production. You, as an investor in the company, would be caught off guard with the margins of the company being affected. Thus, it is important to understand the importance of studying every aspect of the value chain of a company.

Managing & storing inventory

Once inputs are transformed into finished products, a company has to store the products and the raw material that remained unused. The finished product will be packaged and delivered to customers, and the raw material will be used as input at a later production cycle. This is referred to as inventory management.

An example of this is the rice sector. As you may know, some kinds of rice are aged before being sold. The company would need to ensure that the rice is kept safely with proper hygienic measures for long durations to safeguard against the rice being ruined.

Marketing, distribution, & delivery of the product

Marketing, distribution, delivery, installation (if applicable), & post-installation services (if applicable) of the product are all customer-facing activities, associated with the part of value chain that is carried out after the company produces its finished goods. Essentially, marketing refers to a company's ability in effectively promoting the value of its products & creating an image of the brand, distribution refers to the various paths a product takes in reaching the end-user, and delivery refers to how the company hands over the product to the end-user.

You'll often find companies with moats create value in this part of the value chain.

A great example of a marketing campaign is the Ambuja Cement ad we've all seen at some point, in which after terribly failing to destroy a war between their houses, Boman Irani asks Boman Irani 'Bhaiya yeh deewar toot ti kyu nahi!?' and Boman Irani replies 'Kyuki yeh deewar Ambuja Cement se bani hai'. Fun times.
For fast-moving consumer goods, distribution channels are key to ensuring their brands have a consistent mind share with customers. We all know the brand value that Nestle's Maggi enjoys, but another competitive edge of the company lies in its extensive distribution channels. It doesn't matter where you are in the country, you are very likely to find stores that sell Maggi & Nescafe coffee.

Hopefully, the significance of understanding value chains has struck you by now.

Market history & key events

Any sector with a couple of years of existence has some past/forthcoming events that have a material financial impact on its players. This can be a major disruption, a government policy, a new player bent on capturing the position of incumbents, a black swan event and so on.

Take the airlines industry in the recent past as an example of a black swan event & intervention by the government. In April/May 2020, airlines had to ground their flights due to rampant spread of coronavirus. While domestic flights have resumed, operations are still at ~50% of pre-COVID levels. The Ministry of Civil Aviation (MoCA) has mandated fare bands, limiting the revenues earned by airlines.

For the telecom sector, a major event in its history dictates the fate of its player for the next few years; the Supreme Court of India asked 15 telecom players to pay a combine of roughly 90,000 crores to the government. For the coming years, two of the total four players have to take strategic decisions with this liability in mind.

Studying such events gives you a lot of context on how a sector operates.

Overview on current market conditions

Having a finger on the pulse is vital to concluding your research on a sector. This includes catching onto a sector's growth headwinds, market maturity (where the sector is in its cycle), consumption split between organized versus unorganized market, consumption split between domestic and international players, split between the consumption in domestic and international markets, the players responsible for setting the trend in the sector (in terms of pricing power, product innovation, etc), the emerging players that could disrupt the set trends, the barrier to entry faced by new players, and so on. Look, it's impossible to list such things down here, specially since different sectors can have different things to look at, but the gist is to have an eye on what the participants of a sector as a collective are doing.

Benchmarks & metrics to keep track of

Tracking every number associated with the sectors you're tracking becomes impossible very quickly. And so, the flip side of performing due diligence is the ability to keep track of companies / sectors within your circle of competence as simply as possible. In fact, performing thorough due diligence yourself essentially allows you to abstract what's relevant in a sector, and track that. For example, with the telecom sector, much of what you need to know apart from your research is encapsulated in one number - average revenue per user (ARPU). With fast-moving consumer goods, a lot of information that you would need to keep an eye on cuts down to value/volume growth.

Sahil Bloom, VP of Altamont Capital Partners describes this beautifully in his appearance on the We Study Billionaires podcast,

Occam’s razor is all about simplicity. The simplest explanation is often the best one. [...] And it really just enables you to the razor cut through the noise on an issue and just boil it down to the critical path towards an outcome. The best investors are able to boil down investment decisions to the fewest possible variables. [...] They have this unbelievable ability to aggregate all of this information about a company and just say the one or two things that actually matter. [...] They’re able to boil something complicated down to the simplest possible variable for them to look at. Gavin Baker [...] talks about this a lot in a number of interviews that I’ve heard with him, he talked about it as it related to electric vehicles, as an example. All that matters is the battery efficiency. And so, you just focus on that. You don’t need to get caught up in all the other metrics because battery efficiency is what matters for driving these businesses forward and for their success as a business model. And so you focus on that. That’s really what this is all about. Don’t add unnecessary assumptions, variables, and noise, when really only one thing matters. So identify that one thing, and then ruthlessly focus on it as a means to just bring simplicity into extremely complex companies.

Conclusion

To sum up what we've covered in this chapter, we essentially perform due-diligence over sectors to get a birds-eye view of its participants. A sectoral research includes the understanding of its products, an analysis of its value chain, looking at key events in its history, getting an overview on its current conditions, and an effective way to keep adding to it.

Using Screeners

How can we use various screener websites to our advantage and analyze stocks

Disclaimer & Disclosures

The author(s) of this section does not hold any position (long or short) in any of the companies used in this section at the time of writing this. The purpose of using these companies is purely illustrative and educational in nature. Mention of any company in this section is NOT financial advice.
The screens run in this section are purely for educational and illustrative purposes. The results of queries run in this section are NOT stock recommendations. The screens themselves are NOT financial advice.
The watchlists in the screenshot of this section are NOT the author(s) actual watchlist. They are just resultants of arbitrary screens run for illustration and educational purposes. Stocks under these screenshots are NOT stock recommendations.
The author(s) of this section did not get incentivised for recommending or not recommending any websites/tools/products mentioned in this section.

Don't trust numbers reported on any screener at their face value, always verify the numbers shown. Since calculations are done based on one-size-fits-all formulaes, factoring in nuances in input values is not possible. The methods of calculating these numbers may vary between different websites. Sometimes, the numbers can even be absurd and the methods used in calculating them can differ from generally accepted definitions. Making investment decisions solely based on the reported idea can be extremely risky.

Introduction

As of December 2020, the number of actively traded listed companies are nearly 4200 on the Bombay Stock Exchange (BSE), and nearly 1800 on the National Stock Exchange of India (NSE). This absolute abundance of companies to choose from makes the task of creating a watchlist challenging.

Fortunately, there are tons of screeners available, that can filter through these listed securities to build a universe of companies based on constraints set by an investor. These constraints can be,

based on financial criteria set by an investor
based on alternate market data based criteria set by an investor
based on circle of competence of an investor

Using different screeners to your benefit

In India, there are many screeners available to you for free. Some of these screeners stand out with distinct use cases, so it makes sense to use different screeners as per the need of the hour. These are,

differentiates itself from other screeners by providing company's alternate data (financial or operational information that is typically beyond the scope of a company's stock exchange filings), such as market share of its products, or number of stores the company operates. It also has some neat features, such as a to track economic indicators, sector indices, and sector-based market data. However, the platform has moved to a paid subscription-based model. Whether it's worth for you is going to be a personal call.
has a friendly interface for beginners, and explains relevance of figures presented in financial data. However, this is a double-edged sword, a screener's commentary on figures can eliminate its neutrality as a tool, and induce bias.

Screener.in

The two primary reasons we chose Screener (capital S will refer to subsequently) for illustration purposes of this section are,

Screener doesn't induce simplification, nudges, or potential biases.
We would recommend you to start building your own excel sheet for financial data once you're comfortable. Screener's Export to excel feature and the ability to customize the sheet's template helps as a transitional step towards that.

Outline of a company's page

You can land to a company's page on Screener by using the search bar located on the top right of any page. Once you are on a company's page, you will find,

Overview section

You can find quick access to frequently sought information here such as,

links to company's official website, NSE page, and BSE page,
market information (market cap, current price, shares outstanding), and key ratios (you can customize this to your liking!),
a short outline/history about the company,

Chart section

You can check financial graphs for a company under different timeframes in this section. There are a few options,

Price line chart with volume and a couple of daily moving averages.

Price to Earnings (P/E) line chart with trailing twelve months (TTM) Earnings per share (EPS), and a line indicating the median P/E for the selected timeframe.

Quarterly sales chart with gross profit margins (GPM), operating margins (OPM), and net profit margins (NPM) for the respective quarters.

Though the charts here are good enough to serve their purpose, they are not as customizable, or comprehensive compared to other tools available, such as .

Analysis section

This section can safely be ignored, as it lists pros and cons of a company based on some ratios. You might find your unbiased conclusion to be different from conclusions listed here, so shortcuts such as this section in a screener can induce potential bias in your investment decisions.

Peers section

This is where things start to get interesting! You can compare a company to its listed competitors in this section, on the basis of financial data selected by you --- say you want to check how a company's operating margins stack up against that of its peers to determine if its products have pricing power, or say you want to check if the company's quarterly results are in line with the sector --- you can do this here.

Don't let the ratios used in the screenshot above overwhelm you. We've covered tons of them under our Financial Ratios section if you haven't checked it out already. You can add, remove, or change the order of ratios shown here by clicking on the 'edit columns' button.

If you click on either the company's Sector or Industry, you can look at the complete directory of the company's listed competitors (in the peers section, screener only shows you a few).

You can also use the 'Detailed comparison with' option to get a head to head comparison of the company with one of its peer if you have a paid subscription.

If you're unsure what to look for, don't worry! We've covered how to compare a company to its peer in 'and ' chapter of this series.

Quarterly Results section

You can find the past few quarters' standalone & consolidated profit and loss (P&L) data in this section.

There's an option to view the revenue, profit, and return on capital breakup based on product segments and geographical segments for paid users, but you can get this data free of cost from the company's quarterly earnings reports.

Profit & Loss section

This section gives you past ten years & trailing twelve months (ttm) financial data from profit & loss (P&L) statements of the company, in both standalone and consolidated figures, for easy comparison --- for instance, to check the trend of operating margins (OMP) over the past few years, or to check interest on the company's debt it pays each year.

Again, there is an option for paid subscribers to view the revenue, profit, and return on capital breakup based on product or geographical segments, but you can get this data free of cost from the company's annual reports.

Below the P&L data table, this section shows you compounded sales, profit, & stock price growth, and average return on equity over the past 3, 5, & 10 years, and the trailing twelve months (TTM).

We've covered how to read a profit & loss (P&L) statement and related financial data under our chapter of this series.

Balance Sheet section

This section gives you the past ten years of financial data from the balance sheets of the company, in both consolidated and standalone figures, for easy comparison --- for instance, you can compare the company's debt over the past few years, or check how much cash the company holds.

There's also a handy option to check details of past corporate actions (this data goes back up to 2005) taken by the company, such as buybacks, distribution of dividends, allotment of bonus shares, stock splits, mergers, and rights.

Cash Flow section

This section gives you past ten years of financial data for cash flows of the company, in both consolidated and standalone figures, for easy comparison --- for instance, you can check cash outflows due to distribution of dividends by the company to its shareholders, or compare changes in working capital the past few years.

Figures for Free Cash Flows (est.) are unfortunately missing here, but can be calculated as shown in [Financial statements](https://www.indiainvestments.wiki/stocks/reading-an-annual-report#financial-statements chapter of this series.

Ratios section

You can find return on capital employed (in %), debtor days, and inventory turnover of the company for the past few years in this section. This section could be ignored, as it is recommended you calculate these and many other ratios yourself while conducting due diligence for company, as explained in chapter of this series.

Investors section

You can find the shareholding pattern of a company's promoter entity, and distribution of its shareholders between promoters, foreign institutional investors (FII), domestic institutional investors (DII), government entities, and retail shareholders for the past few quarters here.

Screener also allows you to view other listed companies held a shareholders of the company. For example, if you click on Life Insurance Corporation of India in the screenshot above, you'll find a list of other public companies held by LIC.

Documents section

This section gives you quick access to recent stock exchange filings and annual report of a company. As far as I've observed, they are usually linked to BSE's repository. Apart from stock exchange filings, Screener also provides links to credit rating reports of a company here.

Now that we have some understanding of a company's Screener page, let's move on to more interesting things.

Creating a screen

As remarked earlier, there are thousands of companies listed on the Bombay Stock Exchange, and the National Stock Exchange of India. We use screeners to make sense of this abundance of companies to choose from, based on criterias set by us. Primarily, Screener allows us to screen companies based only on financial data (you can however filter companies based on industries, so there is some form of a screening option to confine companies based on your circle of competence).

To screen companies on Screener, click on screens page at the top. You can find your previously saved, and some popular screens here.

Click on the Create new screen button. Here, you want to start adding your constraints, with the keyword AND separating them.
Suppose you want to get a list of companies with fairly large market cap, decent growth in revenue and earnings in the past few years, consistently good return on capital, and low debt. Syntax for this query would look something like this,

This query gives us a list of public companies and some ratios (customizable using the edit columns button) that match the financial constraints we've put. You have the option to save a query for conveniently running it again (it can be located at the screens page from the top).

Building custom ratios

If you find a financial ratio missing from the set available by default, Screener allows you to manually create your own . For example, to create a ratio for P/FCF, you can fill in the following information ---

The ratios you create can be added to Peers comparison section of a company's page, or on results returned by a query.

Building a watchlist & tracking them

You can add a company to your watchlist by following it from the company's page. There are a couple things you can do with your watchlist. One, you can view it as a query and manage it , and two, keep track of the company's filings on Screener's home (feed) page. On the left part of this page, you can see latest filings submitted to stock exchanges by the companies you have in your watchlist. On the right, you can check when the upcoming results of companies you have followed are expected.

Exporting financial data to an excel sheet

You can export a company's financial data to a spreadsheet using the 'Export to excel' option available on the top of any company's Screener page.

More importantly, you can set up a custom template for excel sheets downloaded by the 'Export to excel' option. To do this,

First use the 'Export to excel' option for any company -- it doesn't matter which one. Then, open the excel sheet and go to the 'Customization' tab. It should look something like this.

Delete everything on this tab. This is now a blank slate to put everything you want to see in your custom template. Use the financial data from other tabs of this excel sheet for calculations done in this tab. For ideas, you can refer to and chapters in this series and see what is missing on Screener by default.
For illustration purposes, I've used this tab to find estimated free cash flows of a company over the past few years. Free cash flows are calculated as FCF = Cash flows from Operations - Capital expenditure. I've used cash flow from operations data from the cash-flows tab of this sheet, and calculated capex using fixed assets

Now, save this excel sheet, and upload it . You can always upload an updated sheet with more customization done later using the same page.

All done! Try using the 'Export to excel' option for a different company -- you will find your custom ratios for this company added in the customization tab automatically.

Popular screens & further reading

If you're up for it, try running various stock picking strategies as a screen! Here are a few you can try,

Joel Greenblatt's Magic Formula, an investment technique explained in his book -- The Little Book that Beats The Market.
Benjamin Graham's net-net investing, in which a security is valued solely on the basis of its current assets.
Rob Kirby's Coffee Can investing, popularized by Saurabh Mukherjea's book on the subject

Profitability

Profitability Ratios, also known as Performance Ratios, help investors evaluate the ability of a business to generate profit considering its sales, assets, and shareholder's equity.

Gross Profit

There are three major types of profits -

gross profit
operating profit
net profit

Each of them are significant in their own way.

Gross Profit is the amount of money a company earns after deducting the costs directly associated with producing their goods/products/services from the revenue. These costs are collectively known as Cost of Goods Sold or COGS.

How do we define COGS though and what do directly associated costs mean? Interestingly, the answer can vary depending upon the company we're looking at. Let's look at some examples.

For a capital intensive company like Motherson Sumi which deals with a lot of tangible raw materials, it should be expected that the cost of materials consumed would be relatively high. Purchase of stock-in-trade measures the expenses incurred in buying products used as intermediates to create the finished products sold by the company. Change in inventories of finished goods, work-in-progress, and stock-in-trade, as the name implies, are the expenses incurred on creating the products left in the inventory of Motherson Sumi at the end of the financial year 2020.

These costs can be considered directly associated with creating the end product. Expenses like the finance costs or depreciation & amortization aren't directly related to the creation of the end product. We can ask the following question to think whether an expense qualifies as COGS — would this expense have emerged even if no sales were generated? If yes, then it probably shouldn't be included in COGS.

The labor costs incurred to produce the end product are also direct costs eligible for inclusion in COGS. However, this should only include actual labor costs paid to employees responsible for creating the product, not employees in marketing or HR, for example. This is a conundrum because the notes for employee benefit expenses looks like this

We don't know the details of how much of the salary, wages, & bonus are direct costs. The details are not mentioned possibly due to competitive and logistical reasons.

We leave the decision of inclusion of employee benefit expenses in COGS up to the reader and his/her judgement which will possibly be influenced by the company in question and its nature of business. Please note that these expenses would still be reported under the operating expenses and end up getting reflected in the operating profit.

In this case, we'll consider COGS as

This gives us crores.

Since TCS is a services company, it doesn't deal with or manufacture any raw materials or tangible goods. TCS' tangible material is its software licenses and employees who provide services worldwide. In this case, the COGS would be crores.

Now that we know how to calculate COGS, we can calculate the gross profit.

Motherson Sumi's gross profit for the financial year 2020 was crores. Similarly, TCS' gross profit for the year 2020 was crores.

The Gross Profit Margin of Motherson Sumi and TCS for the year 2020 are as follows

Earnings Before Interest, Taxes, (Depreciation and Amortization) (EBIT/EBITDA)

EBIT, as the name implies, is the amount of profit a company earns before interest and tax expenses have been subtracted. It is also known as the Operating Profit. We can also strip out depreciation costs of tangible assets and amortization costs of intangible assets from the expenses to obtain the EBITDA.

The revenue from operations for the year ended March 2020 was crores. We won't consider other income here because it isn't generated from operations. The total expenses excluding taxes are crores. If we exclude finance costs (interest) of crores from the total expenses, it gives us operational expenses of crores. Thus, the EBIT in this case turns out to be crores.

You can use the following formula to calculate EBIT.

To calculate the EBITDA, we can simply exclude the depreciation and amortization expenses. The formula then becomes

In this case, the EBITDA is crores.

Why is the EBIT and EBITDA of a company relevant?

EBIT and EBITDA present us with an ideal view of the companies' core operational performance by excluding non-operational expenses like interests and taxes. While the net profit might be skewed by variables like taxes, EBIT and EBITDA will present the true picture of how much the company actually earned from its operations.

EBIT and EBITDA are influenced by the ability of the company to earn revenue and the operational expenses it incurs. The EBIT margin (EBIT in terms of % of revenue) or the Operating Profit Margin of Abbott India for the financial year 2020 was while that of Pfizer India for the same period was . Using these numbers, we can infer that Pfizer India was operationally more profitable than Abbott India during the financial year 2020. Again, keep in mind that this is just one of many data points.

Like most financial ratios and metrics, EBIT and EBITDA are best used for comparison of companies in the same sector. Comparing the EBIT margin of a MNC pharmaceutical company with that of a capital intensive auto ancillary company doesn't make sense.

EBITDA can, however, be misleading and it may not be useful, especially in asset heavy companies with significant depreciation and amortization costs. Here's what Warren Buffet has to say about EBITDA.

People who use EBITDA are either trying to con you or they’re conning themselves. Telecoms, for example, spend every dime that’s coming in. Interest and taxes are real costs.

Here's what Charlie Munger has to say about EBITDA.

I think that, every time you saw the word EBITDA, you should substitute the word "bullshit" earnings.

Profit Before Tax (PBT) and Profit After Tax (PAT)

As the name implies, Profit Before Tax (PBT) is the amount of profit a company earns before subtracting the taxes it has to pay. Although PBT doesn't get the same amount of focus as gross profit, operating profit, and net profit do, it can still be useful in its own right. Corporate taxation laws can change and this can end up skewing the net profit that a company earns in a financial year. For example, ( | ) for domestic companies from to in September 2019. Some companies in certain sectors might also enjoy tax incentives from the government for a specific period of time. In such cases, focusing on PBT might be a good idea.

You should be able to find PBT in the profit and loss statement in an annual report.

Profit After Tax (PAT), also known as the Net Profit, is arguably one of the most important metrics in an annual report. It is what we usually mean when we ask "how much profit did the company earn?". PAT is the metric which tells how profitable a company is after all its expenses are considered and deducted from the revenue.

Like PBT, you should be able to find PAT in the profit and loss statement in an annual report.

The PAT margin, also called net profit margin, is a widely used metric to calculate and compare the ability of companies to generate profit.

For the financial year 2020, Abbott India has a PAT margin of and Pfizer India has a PAT margin of .

Unlike operating profit, note that we're using total income, which includes other income as well, to calculate the net profit and net profit margin.

Return on Equity (ROE) and Return on Assets (ROA)

Return on Equity is a widely used profitability ratio that measures how many rupees of profit are generated for each rupee of shareholders equity. It highlights how efficiently a company uses the shareholders equity to generate profits. It can be calculated using the following formula:

Depending on the source, we might find a slightly different formula for ROE.

You can calculate average shareholders equity by calculating the average of the shareholders equity at the beginning and at the end of a financial year.

One might ask — why consider the average of shareholders equity and not the total shareholders equity for a specific financial year?

Avenue Supermarts issued additional shares in the financial year 2020 to comply with SEBI regulations of bringing down the promoter holding below within 3 years of IPO. This is a one-off event which won't happen regularly. To avoid presenting skewed ROE numbers, we'll consider the average shareholders equity rather than the total shareholders equity. This gives us crores. The PAT for the year 2020 was . This gives us an ROE of .

Of course, if the difference between shareholders equity in successive financial years isn't driven by one-off events like buybacks or additional issue of share capital, considering the average shareholders equity may not be needed.

Similarly, Return on Assets can be defined as the amount of profit generated by a company relative to its total assets. We can use this metric to judge how well a company utilizes their assets to generate profits. It can be calculated by using the following formula:

Just like ROE, we can use average total assets instead of just total assets when needed to calculate ROA.

The ROE of Avenue Supermarts for the year 2020 is and the ROA is

Even though ROE is a popular metric, it's important to understand its quirks and limitations. Bombay Dyeing & Manufacturing Company Ltd, one of India's largest producers of textiles, had a ROE of in the year 2017, in 2018, in 2019, and in 2020. As you might have suspected, these ROE figures are misleading (but not incorrect). We'll attempt to showcase the limitations of ROE as a financial ratio and why it should be used with caution in cases where the net profit is negative or when the company is in significant debt.

Besides the definition that we shared above, ROE and ROA can be expressed using the DuPont Identity, also known as the DuPont Method, which breaks down ROE and ROA into several ratios and presents us with a detailed and an alternative view.

According to the DuPont method, ROE can be written as

Profitability Ratio is the PAT margin we've seen before. The Efficiency Ratio is Total Income / Average Assets and the Leverage Ratio is Average Assets / Average Shareholders Equity. If you observe closely, the DuPont Identity gives back the original definition we shared if we cancel out the relevant numerators and denominators from the above formula. However, by breaking up ROE into 3 different ratios, we can now know how the ROE figures of Bombay Dyeing ended up getting inflated.

Let's start with the year 2019. We can break down Bombay Dyeing's ROE as follows:

Profitability Ratio =

Efficiency Ratio =

Leverage Ratio =

Return on Equity =

The difference of may be ignored because ratios were rounded to 2 decimal places.

Although the profitability ratio and efficiency ratio (also known as asset turnover ratio) are decent, what ends up inflating ROE is the leverage ratio (also known as equity multiplier) which indicates that Bombay Dyeing's assets are mostly funded through debt rather than equity which is a point of concern.

Let's assume a hypothetical company called A Ltd. Its accounting equation looks like where assets are 10, equity is 8, and liabilities are 2. Its leverage ratio is . A Ltd decides to take on units of loan to finance its operations. The equation now looks like . The financial leverage now becomes . This would end up inflating the ROE of A Ltd but that doesn't necessarily mean that its a good thing. Taking on a lot of debt may or may not pay off. If, however, the profitability ratio and efficiency ratio increase in the subsequent financial years, taking on debt could be considered a worthwhile decision.

The DuPont formula for ROE ends up revealing ROA as well when we multiply the Profitability Ratio with the Efficiency Ratio. This means that ROA is a function of a firm's profitability and its asset turnover capability.

Besides the highlighted limitations in this section, the usual caveats apply. ROA and ROE are best compared against firms in the same sector with similar business models.

Return on Capital Employed (ROCE)

Return on Capital Employed (ROCE) is a measure of how much money can a company generate in the form of profits considering the amount of money invested in it for the long term.

After reading that definition, one might think, how do we calculate the "amount of money invested in a company for the long term"?

Let's say you decide to buy a house worth lakh. In addition to this cost, you need another lakh rupees for repair and renovation which means you need lakh. However, you only have lakh for down payment. You decide to borrow lakh from your nearby bank as a long term loan and lakh from an elder sibling for completing repair and renovations. The acquisition, repair, and renovation process takes about 6 months and you're able to pay back the lakh loan you took from your sibling within an year.

If we use the accounting equation in this case, we can say that

Assets = lakh (the house)

Shareholders Equity = lakh (the downpayment you made)

Non-current Liabilities = lakh (the long term bank loan)

Current Liabilities = lakh (the money borrowed from your sibling)

The long term investment in this company (the house) seems to be lakh — the sum of shareholders equity (your contribution) and non-current liabilities (the bank loan). This is what we call Capital Employed. It's the amount of money employed by a business to fund its assets and, ultimately, generate profits.

We can define ROCE using the following formula

where

One might find Capital Employed defined as Total Assets - Current Liabilities which is just another way of using the accounting equation. The former method of looking at capital is called the financing approach and the latter method is called the operating approach. We can also use Average Capital Employed to prevent looking at skewed ratios because of abrupt changes in Shareholders Equity or Non Current Liabilities.

The ROCE of Bombay Dyeing for the year 2019 is

EBIT = crores

Average Shareholders Equity = crores

Average Non-current Liabilities = crores

ROCE =

and for the year 2020 as opposed to its ROE of and for the years 2019 and 2020 respectively.

Abbott India's ROCE for the year 2020 is as opposed to its ROE which is . Avenue Supermarts' ROCE for the year 2020 is and its ROE is .

It should be apparent that ROCE is a "better" profitability ratio to use compared to ROE, especially in capital intensive sectors like telecom and auto where assets being funded by significant debt isn't unusual.

However, ROCE isn't free from limitations. Even though retained earnings is being counted as part of capital employed, it may not be employed for any financial activities. A company with high amounts of cash reserves would be negatively affected when calculating ROCE. This should be apparent in the case of Abbott India. If we exclude the retained earnings, we'll get a ROCE of for the year 2020.

Return on Invested Capital (ROIC)

One of the drawbacks of ROCE, as we saw earlier, was that it considers cash reserves as part of the capital employed which ends up subduing the ROCE of Abbott India. Instead of looking at capital employed, how about we consider the capital invested in a business? After all, the ability to generate cash isn't the only thing that matters. A company sitting on huge amounts of cash isn't necessarily a good thing since the returns generated from that cash is not going to exceed the cost of capital employed and the expected returns by shareholders. We'd want a company to make use of that cash effectively and grow its business better than its competitors. Efficient capital allocation matters just as much, if not more, than how capital is employed and it is one of the most important responsibilities of the management of a company. Its importance is perhaps best highlighted by the following excerpt from Warren Buffet's 1987 letter to shareholders.

The heads of many companies are not skilled in capital allocation. Their inadequacy is not surprising. Most bosses rise to the top because they have excelled in an area such as marketing, production, engineering, administration or, sometimes, institutional politics. Once they become CEOs, they face new responsibilities. They now must make capital allocation decisions, a critical job that they may have never tackled and that is not easily mastered.
In the end, plenty of unintelligent capital allocation takes place in corporate America. (That's why you hear so much about "restructuring.") Berkshire, however, has been fortunate. At the companies that are our major non-controlled holdings, capital has generally been well-deployed and, in some cases, brilliantly so.

The Return on Invested Capital (ROIC), also known simply as Return on Capital (ROC), is the measure of profit generated by a company relative to the amount of capital invested in its business.

Okay, so how do we define what capital invested is?

We defined capital employed as Shareholders Equity + Non-current Liabilities but it can also be written as Total Assets - Current Liabilities using the operational approach. We can exclude

cash and cash equivalents, bank balances, term deposits, and interest
income from cash
goodwill
unusual and one-time items

Excess cash sitting in a bank or in debt funds isn't invested in a business and isn't an operational asset. Goodwill is an intangible asset usually resulting from the acquisition of a company at a premium value. We exclude goodwill because it is the leftover premium from the past, not an investment for future growth. Similarly, other one-time items like asset write downs aren't included in the capital invested since they are not core operational assets.

This gives us

This gives the denominator in the formula for ROIC. Since we are focusing on the invested capital from an operational standpoint, we'll use operational profit in the numerator. However, instead of using just EBIT, we'll adjust EBIT for taxes to get a more standardized version of operating income. This is known as net operating profit after taxes (NOPAT).

ROIC can now be defined as

There's another caveat we should keep in mind when calculating ROIC. The NOPAT generated at the end of a financial year won't be because of capital invested at the end of the same financial year. To account for this timing difference, the amount of capital invested used in calculating ROIC will be as it was at the end of the preceding financial year.

Let's start with Abbott India. The EBIT for the year 2020 was crores. The tax rate can be considered as as mentioned in Note 18 of the financial statements. This gives us a NOPAT of

NOPAT = crores

The capital invested of Abbott India for the year 2019 is

Cash and Cash Equivalents (Note 10) = crores

Term Deposits (Note 11) = crores

Interest on Bank Deposits (Note 13) = crores

Non-operational Assets = crores

Capital Invested = crores

This gives us a ROIC of . The ROIC of Pfizer India for the year 2020 is .

Motherson Sumi, an auto ancillary company which is significantly more capital intensive, has a ROIC of for the year 2020. Of course, this doesn't mean we can compare Motherson's ROIC with Abbott's. An appropriate comparison would be with a company like Minda Corporation which is also a capital intensive auto ancillary company. It's ROIC for the year 2020 was . One may notice that although Minda Corporation had a negative PAT for the year 2020, its operational profit was positive. The reason for the negative PAT seems to an exceptional event which is not considered when calculating ROIC.

Of course, like any other ratio, ROIC has its limitations.

ROIC, and its constituents, are non-standard metrics that we may not find anywhere in an annual report. We have to calculate NOPAT and Capital Invested ourselves, manually, to avoid mistakes and incorrect calculations. Although we considered the timing difference when calculating capital invested, some stock screening websites may not do so and use capital invested at the end of the year in question which is incorrect.

ROIC can also mislead investors into preferring asset light companies with relatively higher ROIC than asset heavy companies with relatively lower ROIC.

( | )
( | )
( | )

Process for Estimating Tax

A peek into tax estimation process for redeeming units that are outside STCG zone

Intro

In the previous chapter, we've seen how to compute total number of LTCG taxation eligible units, if equity transaction history has been provided.

Picking up from where we’d left off in in part 1, we can start with planning for potential tax computations

Planning for Tax Computation

At this point, we’ve computed units outside of STCG zone (purchased more than a year ago, that’s still in investor’s portfolio, eligible for LTCG taxation if redeemed). STCG stands for Short Term Capital Gain.

We need to compute these:

tax liability, if investor were to sell all these units.
total number of units present, where net tax liability is equal to or below 1L, if investors were to sell those

How to compute Tax?

Tax is computed on realized gain, or booked profits. Realized gain, can be written as SUM(selling price of units - purchase price of units).

Say, an investor purchased

U1 units, on date t1, at price P1.
U2 units, on date t2, at price P2.

Assume selling price (NAV on day of selling) is P.

This investor has (U1 + U2 + U3) available for selling. Realized gain is:

Which can be written as:

In previous section, we’ve computed effective value of (U1 + U2 + U3), for any number of transactions.

We know P1, P2, P3; for all those transactions.

Only missing information is selling price, P.

What we need, is selling price as on today (the given day when investor is attempting the sell transaction).

In other words, we need a way to get access to latest NAV of these funds.

Because this NAV would change every day (equity mutual funds update NAVs at the end of the day, every business day); it’d be better to programmatically access it, like we used GOOGLEFINANCE() for historic data.

GOOGLEFINANCE() can be one option here. But as we’ve learned in the previous section, it has issues (remember seeing #NA all over the place?)

Instead, we’d go to the source!

Getting the latest NAV

Every AMC has a regulatory requirement to update their NAV daily with AMFI (Association of Mutual Funds in India). And AMFI makes this data available for parsing, in a CSV format, on their website.

This is the link:

Remember when you were thinking learning SPLIT() TRANSPOSE() would’ve been of no use? Well, as you’re about to see, not all CSV data come in text files.

Google Sheets has an IMPORTDATA() function, that’d do just fine. We provide it a URL for the AMFI NAV endpoint, and it’d fetch that latest raw NAV data.

Then we can split that raw NAV feed into cells, as we see fit.

Finding your fund

This AMFI URL has ~20k entries. To be able to find the funds as in the CSV transaction dump, our excel matchers would be fine.

But the problem is this: the name of fund can appear differently in that list, as opposed to how it appears in the CSV.

Finding fund by matching strings would lead to misses.

A better option would be to find fund the way it’s usually done - by a unique identifier.

This unique identifier is already present in the AMFI NAV feed URL: ISIN

ISIN (International Securities Identification Numbers) is unique across global securities market. Not only can there be no two funds with same ISIN in India; there can be no two securities with duplicate ISINs in regulated markets, across the world!

We have to find ISIN for each of the funds in CSV transaction, manually, and add a column in the second sheet, next to each fund, to add its ISIN.

List of ISIN is also present in AMFI NAV feed.

This is actually a straight-forward, one-time exercise. It should also make it clear to the reader why having too many funds to portfolio can be cumbersome.

Execution

Adding ISIN column

Let’s start by adding the ISIN column first

In the second sheet (not the one named data), add a column next to fund’s name. You can use Insert Column.

Insert ISINs as follows (copy-paste from below):

Final results, should look like this

Fund Name

ISIN

Fetch and Process Latest NAV from AMFI

Follow these steps

Create a new sheet, name it AMFI NAV (Raw)
In a cell, enter the AMFI URL: https://www.amfiindia.com/spages/NAVAll.txt
Invoke IMPORTDATA() function, and pass the cell ID of the URL above, as argument

Technically, you won't need to fill out all 18k+ rows. As long as you fill out enough number of rows, such that all funds in that other sheet appear in those rows, it's good enough.

Refer to the following video(s) for reference

Your sheets would now have latest available NAV from all mutual funds, in a nice table format.

We've introduced a few new Google Sheets functions in this section:

Computation of Value as on Today

In this section, we’d compute net redemption value of units older than 1 year.

This is total available units older than one year (already computed earlier), multiplied by latest NAV.

We can look up latest NAV using VLOOKUP with ISIN as search query.

VLOOKUP() stands for vertical lookup or row-wise lookup, across columns. It’s a way of searching for matching data.

There’s also HLOOKUP() and LOOKUP(); but for our use case, VLOOKUP() would work fine for now.

| |

Follow these steps:

Come back to the main sheet, which has information on available units and sold units.
Add a column at the end, titled Latest Value
Invoke VLOOKUP() for the top-most row as follows:

Refer to this video to follow along steps:

Final outcome in the newly added column, should be similar to these screenshots:

Depending on when you're looking at these, the exact value might differ. Because it depends on latest NAV as on that date. This computation was done with NAV of 22nd March, 2021

We did not explicitly set out to do this, but it's trivial to take a CSV and compute latest portfolio valuation! You already have total number of units investor has in portfolio for each fund, and latest NAV for each fund.

Adding up total cost of purchase

On the surface, this sounds easier than previous steps.

All one has to do, is go and sum on units and NAV column, where date is much before the date 1 year ago.

In fact, investment amount is provided in the CSV transaction dump, so multiplication isn’t even needed (invested amount = unit x NAV on that date).

Except, it’s not that simple.

We have realized that even if units were acquired more than one year ago, doesn’t mean some of those weren’t sold in last 1 year, or before.

Remember that available units is NOT sum of all units purchased more than a year ago. We had to subtract the sold units (sold at any point in time, not just more than a year ago) to arrive at LTCG-eligible units.

In other words, we need to find unit purchase price for only these units. Not that of all units.

Another way to look at it: keep adding purchase price, until total number of units added is same as LTCG-eligible units.

An Example

An example to illustrate this idea:

Say, an investor is buying 10 units every month, from 2018 January, to 2020 December. That's a total 36 month period.

At the end of December 2020, units purchased between January 2018 and December 2019, are eligible for LTCG.

Now assume that in 2020, at some point, this investor has also sold 54 units.

Then, at that point, they’ve units available for LTCG-eligible redemption.

What were the purchase price(s) for these 186 units?

More importantly, when were these 186 units acquired?

We know that investor purchased 10 units every month, from Jan 2018 to Dec 2020. Investor has also sold 54 units some time in 2020.

Redemption works in a FIFO (First In First Out) manner in folio. Meaning, earliest purchased units are to be redeemed first.

We first need to account for 54 units that were sold. These units were acquired as follows -

Month of Purchase

Number of units

Once we’ve depleted the earliest-purchased-earliest-sold scheme for already redeemed units, we can start to account for remaining 186 units.

These 186 LTCG-eligible units were acquired as follows:

Month of Purchase

Number of units

When computing purchase price for these 186 units, we need to use purchase price from these months in the table; and not purchase price from January 2018.

In other words, this is going to be the most complex part of the computation.

To recap, we compute purchase cost / acquisition cost as follows:

Account for existing sell transactions, depleting units, starting from initial purchase transaction; until total units add up to all units sold so far.
Start adding up units from this date, till it adds up to total available LTCG-eligible units.

A Special Case

Luckily, we have a special scenario on our hands.

In our case, if you notice the units sold and LTCG-eligible units in the column, investor has either never sold any units from their current holdings in a specific fund; or they’ve completely sold off all their units in that specific fund.

Which means, for funds where there are LTCG eligible units; we do NOT need to account for previous sell transactions.

In other words, one can start adding up the purchase transaction costs from the very first purchase.

This won’t be the case for a typical CSV transaction dumps for most investors.

Grandfathering LTCG Computations

We’ve so far carefully avoided discussing the grandfathering clause in equity LTCG computation.

Union budget 2018, defined purchase price of equity, for LTCG computation as higher of price as on 31st January 2018, or actual purchase price - if units were bought before this date.

To factor this in, we’d need information on NAV price as on 31st January, for each of the funds present in the CSV transaction dump, which has LTCG-eligible units.

The AMFI URL for latest NAV won’t do.

Instead, we can use this URL for fetching historic data: http://portal.amfiindia.com/DownloadNAVHistoryReport_Po.aspx?mf=<AMC Code>&tp=1&frmdt=31-Jan-2018&todt=31-Jan-2018

This URL returns data in CSV format as before. However, it’s specific to each AMC, identified by an AMC code.

An AMC code is a number, between 1 and 100. However, not all numbers between 1 and 100 correspond to a valid AMC code. You’d have to try out each number, and see which ones result in valid URLs that return data.

For example, Nippon AMC is identified by AMC code 21. This URL would return NAV history of all Nippon AMC funds, as on 31st January 2018: http://portal.amfiindia.com/DownloadNAVHistoryReport_Po.aspx?mf=21&tp=1&frmdt=31-Jan-2018&todt=31-Jan-2018

You can follow similar steps as earlier to incorporate data from this URL into your calculations.

An Exercise for You

We won’t be covering the last bit of calculation here, as it’d be quite complex.

It’s left as an exercise to the reader; as it won’t be of much value to do these with Excel / Spreadsheet, for most investors.

To achieve such conditional summing-up, one would most likely need to use QUERY() function, writing something akin to SQL queries.

| |

It’d also be much harder to update or understand. for another person going through same excel sheet or spreadsheet; which would have such esoteric constructs.

At that point, using a for-loop equivalent in commonly used programming languages (Python / Node / Java / Ruby / Go etc.) would be much easier to reason about.

Another option would be to use ( | ), in Google Sheets; which is basically JavaScript to program your sheets. However, we won’t be covering that here.

Wrapping Up

Phew! We covered a lot of ground in these three chapters.

We can only hope power of spreadsheets are becoming more and more apparent to you. Simultaneously, you've also started seeing the rough edges.

Excel / spreadsheets with complex formulas can be hard to maintain - it's tough to understand and update if needed.

We'd gradually address these in upcoming chapters and series.

CAGR: Point-to-Point Annualized Returns

We'll take a look at what CAGR is, how it works, and how we can compute CAGR of various listed assets.

Intro

So far, we’ve covered various computations, which mostly involved algebraic sum / multiplication / subtraction.

Except, these aren't 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.

Learning about CAGR is only the first step towards that.

CAGR

CAGR (Compound Annual Growth Rate) is a measure of how fast a value has been growing, assuming this value is probably a result of a compounding process.

Let's assume value at the beginning of a time period was , and if it changed to over a period of time , then CAGR or the rate of this growth, can be formulated as:

Where, is the annualized rate of growth or CAGR.

This is a well known formula for calculating compound interest and we've been taught this back in school.

Rearranging this equation for , we get

In the above formula, is greater than 1. If we want the value of to be between 0 and 1 (and not 0 and 100 as is usually expressed in percentage notation), we can remove multiplication by 100 from the formula. Either choice is fine.

Let's see how we can use this in the real world of investments.

3Y Return CAGR

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 a common one: 3 year (often written as 3Y, for short) 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).

Returns above 1 year, are reported as annualized rate of growth, often appended with per annum (p.a., for short).

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 an arbitrary period of time.

From ValueResearch Online, ( | ) has 3Y return of 13.72% p.a., as on 26th Mar 2021. Exact date is important, since this 3Y return value would change with date.

Notice the value under 3-Y column, noted as 13.72.

This is effectively 3Y CAGR of the fund. It means, as on 26th March, 2021; 3 year CAGR of this fund was 13.72% p.a.

Our task would be to do these:

validate this number, computing it ourselves
compare against CAGR of Nifty over same time period

Validating Fund 3Y 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, as we've discussed in a previous chapter.

But remember that CAGR needs only two data points. In this case, since we only need two data points, we can just manually look it up. And enter these two data point values into our excel sheet or spreadsheet manually.

Steps to follow:

Head over to AMFI historic NAV page URL: This is different from other AMFI links / URLs we've seen in the past. It's a dedicated URL, where AMFI offers the user an option to select date range, fund name etc.; and receive NAV of a given fund between those two dates, both inclusive.
Search for dates around 26th March, 2021 (select two dates, that are less than 90 days apart, and covers the given date); we find the latest NAV as 96.7892 for UTI Nifty Index Direct Growth. You could, for instance, select 1st March 2021 as one date, and 31st March 2021 as second date. These two dates are less than 90 days apart, and has the given date in its range.

But instead of using the RRI() function, we could directly use the mathematical formula as we'd discussed above: being the value on given date, is the value on given date 3 years ago; and is 3, for 3 years.

We'd get this

Notice that we've used 0.33 for , to denote . This is not correct. We could get more accurate estimation, directly using instead of using 0.33.

However, the number of days between any two arbitrary dates cannot always be expressed as whole number 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 ValueResearch Online portal.

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.

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

Cross-checking Nifty 3Y 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".

Final results as it can be

As you can see from this computation, the CAGR numbers of UTI Nifty Index Fund Direct Growth and the CAGR numbers for Nifty itself do not match.

In fact, this has nothing to do with the dates. You could select a different set of dates, 3 year apart and the return from Nifty Index fund would be slightly higher than Nifty 3Y CAGR.

In this case, 3Y CAGR as on 26th March 2021 is 13.73% p.a. for UTI Nifty Index Direct Growth. However, for Nifty itself, it’s slightly lower — 12.52% p.a.

This is expected!

An index fund tracks the TRI (Total Return Index), and not the vanilla price index, which is Nifty 50 in this case.

TRI is price index + dividends reinvested back to buy the index stocks.

Nifty TRI can be thought of as an answer to this query: what if we added a normalized version of dividend-per-share values back to the share prices of respective Nifty companies, and recomputed Nifty?

For more information on exactly how Nifty TRI is computed, you can refer to details on NSE India official website: | |

A Nifty index fund achieves this by reinvesting the dividends received from corporate actions, into buying more of Nifty stocks, in same proportion as these are in Nifty.

Therefore, over any given period of time

Nifty index funds can have slightly higher CAGR than Nifty itself.
If no dividends were announced by any of the companies in Nifty index, in that time period, it can be expected that Nifty index fund CAGR would be 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 TRI CAGR.

Nifty TRI 3Y 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 TRI.

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 TRI:

Plugging in values for the two dates manually, and computing as earlier, we get this

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 and replicate the TRI, but there are fees associated with managing an index fund, fees associated with purchasing and selling equities, and various logistical inefficiencies trying to replicate the index in real time during market hours.

Hence, CAGR over a given period for an index can be somewhat different in practice, there will be some tracking error with respect to the TRI.

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 TRI, over any given period.

Visualization

Let's recall the original mathematical equation that we'd written above

To build a mental model of what this formula means, in below diagram, we can plot value against time. Value can be price of an asset, based on our context and use-case.

Value can be price of an asset, based on our context and use-case.

Notice that the growth doesn’t have to look like compounded growth. It's the other way around - we can model it or think of it as compounded growth.

This mathematical formula, just needs two values:

And on top of that, time it took to go from to

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 above poses no restrictions on that front.

However, in the above graph, we’ve no way of visualizing or placing , the rate of growth (CAGR).

It’s a power-law formula.

Semi-log Plots

Instead of plotting value vs time, we can plot logarithm of value against time.

Discussing logarithm in detail, is out of scope for our wiki. However, if you'd like to brush up on basic logarithms, we highly recommend checking out this

Note that we can take of expressions on both sides of the equation only if both sides are positive numbers.

Logarithm, or for short, cannot be used on negative numbers — it results in complex numbers, which we don't need to deal with in context of investments and finance.

In the above equation, taking on both sides, we get

We can use logarithmic identities (left to reader as an exercise, we won't show detail computation for that here) to arrive at the following formula

This is effectively the straight line equation, similar to . In this scenario, is effectively the X-axis, while Y-axis is .

We can now update our diagram's Y-axis.

We have plotted of values (Y-axis) versus time (X-axis). Then we've joined the start point and end point with a dotted straight-line.

The slope of a straight line joining those two points and , is , and can be used to compute .

CAGR directly relates to slope or tilt of a straight line joining two points, in a semi-log plot of asset prices. If slope is known, can be computed; and vice-versa.

Also, higher and lower slope of the joining straight line correspond to higher and lower values of , respectively.

We shall now plot these price points in a graph where price values are logarithmic, and try to validate that our visual idea about CAGR holds true.

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 charting / plotting abilities built in. You could use the Charting button on toolbar, or just select an area of cells which has data, then use Insert →Chart

Refer to this video on how to get this working

We see from the videos / images 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 logarithmic relationship; it stands to reason that these would seem parallel to the naked eye.

Notice that right up until the moment we converted Y-axis values to logarithmic scale, the line for UTI Nifty Index fund was nearly flat-lining, kissing the X-axis closely.

This was because compared to absolute values of Nifty or Nifty TRI, which are above 10k in this dataset, the NAV of this fund was barely even 100. It’d practically be dwarfed in a vanilla line graph.

However, once we switched to semi-log mode, all three lines started reflecting real growth over time in their tilt (or slope).

It should intuitively make it clear that absolute value of an underlying asset's price value is immaterial when you purchase it, or the range of absolute values it moves between; only thing that matters is the growth rate after you purchase the asset.

An asset whose price moves between 10 and 20, is not that different from an asset whose price moves between 10,000 and 20,000.

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.

Maximum and Minimum Limits of CAGR

We can use our visualization of CAGR as slope of semi-log plot, to understand maximum and minimum limits of CAGR.

In the above image(s), the final value can be any one of , , , . There are more possibilities - in fact, there are infinite possibilities between that semi-circle.

Can it go backwards? No. As in, the point cannot have a lower X-axis value than , since X-axis is time, and we cannot go back in time for final value of the asset.

Best case scenario is depicted by the line starting at and ending at . This line has a slope or tilt of (positive infinity).

Plugging this in our formula, we get . Which resolves to being an infinitely large positive value.

Worst case scenario is depicted by the line starting at and ending at . This line has a slope or tilt of (negative infinity). Similarly, plugging this in the equation, we get to be a very large negative value, approaching negative infinity.

Theoretical limits of CAGR can be any value between and . Most of us wrongly assume, that annual compounded growth rate cannot be higher than 100% p.a. or -100% p.a. As we just saw, it can be any real number.

What if the slope of the line is zero? A straight line in the semi-log plot, that's parallel to X-axis or the axis of time. In this case, would also be zero. In other words, if there's no net growth over a certain period of time (starting and final value in Y-axis is same), then CAGR of that asset price is zero over that period of time.

We can build on this.

A 100% p.a. return denotes price doubling every year. What would slope of this line even look like for a 100% p.a. CAGR? Plugging in our above formula, we get slope to be .

If the logarithm base is 10, then the angle of slope is effectively , or . If the base of logarithm is , then angle of slope is , or .

In a semi-log plot where prices are logarithm of base-10, the realistic expectation would look like this. Most start and end points would be between the angular area to or to , while other areas outside of this region would remain largely unreachable for most common assets.

Recap and Wrapping Up

When it comes to spreadsheet / excel functionalities, in this chapter, we learned

how to draw semi-log plots
how to use RRI()

We have also gained new insights into a demystified process of return calculation.

The next time we see CAGR reported on any portal such as MoneyControl, ValueResearch Online, Morningstar, CRISIL, BSE India, Kuvera, Coin, PayTM Money, INDMoney, Piggy, ETMoney, or even Smallcase, we’d know how to compute and validate these numbers ourselves.

These platforms and portals are computing these numbers from historic NAV data they have obtained and now we can compute the same as well.

A word of caution!

CAGR is a point-to-point metric. Given any two points in a plane, a straight line can always be drawn to connect those two points. This is one of the fundamental axioms of geometry, known as one of the core axiom of Axioms of Euclidean Plane Geometry.

It says nothing about the journey or price-movement 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 of actual asset prices.

As Nifty TRI and Nifty index fund move closely with Nifty 50 itself, plotting these would lead to similar looking graphs, with lot of volatility.

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 (can even be curves), 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. Although the point-to-point return of NASDAQ was near zero in that case, the actual returns in a DCA / SIP portfolio were different and didn’t show up as one would expect looking at point-to-point returns.

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.

A Rigorous Introduction to XIRR

Mathematical formulation of XIRR, discounted cashflows, NPV, and decay.

Intro

Now that we’ve gained some ideas about XIRR (eXtended Internal Rate of Return), it’s time to formally introduce XIRR.

XIRR is tightly coupled with concept of discounting.

Discounting can be thought of as the opposite of compounding.

Discounting and XIRR

Popularly, compounding formula is written as .

Instead of , where is being multiplied with the value of ; in discounting, we’d do the opposite, to denote decay over time.

Discounted value of after a time duration , can be written as

In case of compounding, with time, the final value increases.

With discounting, as expected from the above formula, the decayed value gets lower as more time passes.

Sample Cashflow Table

Assume that we’ve a cashflow series, written like this:

Date

Cashflow

This format should be quite familiar. We’ve merely replaced the actual numbers with variables.

Discounted Cashflow Table

We’d add one more column to, to add discounted values

Date

Cashflow

Discounted Cashflow

Third column entries might look a bit cumbersome at first, but we’ve basically expanded on , where is .

This is effectively normalized time. Given two dates and , is equivalent to number of days in between those two dates. And is a common factor (commonly, 1 year or 365 days).

Then is number of years, where fractional values are allowed.

Notice the first row of the above table, it’s just . Because every discounting is being done corresponding to that date in first row, , so trivially, the discounted value against itself same as the original value.

Remember from your school days, that anything raised to the power of zero, is 1 in value.

Mathematically,

Net Present Value

NPV (Net Present Value) of the above cashflow is sum of all discounted cashflows:

Which can also be written as

This formula has summation symbol , which allows us to write sum of a series of numbers in a succinct manner.

Definition of XIRR

XIRR of a given cashflow time-series data, is defined as value of , the rate of discounting, such that NPV is 0.

Let’s see what this means.

As we’ve seen in the last chapter, in a cashflow, some entries would have positive and negative signs in front of those values; such that these values represent direction of cashflow.

If we take the cashflow from last chapter itself, it’s ₹100,000 (1L INR) for first 10 years, then after another 10 years, ₹200,000 (2L INR) for next 10 years.

Number of entries, is .

We fill the table up, mapping cashflow variables to values.

Date

Cashflow

If we use the NPV formula, we’d need to settle the value for power index in the formula, , where can be , , , , .

We could reasonably approximate these as whole numbers.

Plugging these in NPV formula, XIRR for this cashflow would be given by this equation:

Then XIRR is solution for this equation, where is the unknown variable.

We can verify that for XIRR, this equation results in zero.

Another way to think of XIRR, is it’s a value of rate, that makes sum of discounted cash outflows, same as sum of discounted inflows.

Verifying XIRR by Adding up Discounted Cashflows

We shall now go ahead and verify that this NPV equation indeed turns zero when we plug in the value of XIRR from in-built xirr() formula.

We had already created a table and verified the XIRR to be p.a.

Steps:

Create a new column in your original calculation from last chapter, next to Cashflow column.
In the first row, fill out the formula as follows:
Make sure to lock the cell with $ notation, for cell ID pointing to

This video should help with above steps:

Final result should resemble this:

You would be tempted to manually use 3.52% or 0.0352 instead of relying on output of xirr() formula. As you’d see, it would add up to a finite positive or negative value.

Due to formatting, we are only seeing two places after decimal point. But actual computed value is something slightly different from exact 3.52%.

How to Solve the XIRR Equation

Given a value of XIRR, we can verify if it’s indeed satisfying the equation of NPV being zero.

But given an equation, as above, can we derive the value of ?

Turns out, even if it’s a single-variable equation; it’s not as simple as solving an algebraic equation.

In essence, we need to use advanced approaches such as Newton-Raphson approximation to solve such equations, iteratively.

It starts with a guess for the right value of XIRR. Then it plugs that value back into the NPV equation, to extract a new approximate value.

After repeating this and extracting newer approximate values, it only gets more and more precise, through hit and trial.

Eventually, the value stabilizes reaching correct answer for the problem at hand.

A full treatment of Newton-Raphson method is beyond scope for this wiki, but even a cursory search in popular search engines would reveal enough materials that cover it in some depth.

The third argument in xirr() function is rate_guess, which if you provide, can speed up XIRR computation by reducing number of iteration it takes to reach correct value of XIRR.

But it’s best avoided; because if you plug in a value as guess for rate, which is far removed, it would only serve as a means to increase number of iterations it takes to find the value of XIRR.

Why is XIRR annualized growth rate?

Because the value is set to 1 year or 365 days.

We could, however, change that to 1 month or 1 decade; and get rates per month, or rates per decade.

Visualization

Now that we’ve a formal mathematical understanding of what XIRR is; it’s instructive to understand XIRR visually.

This would help us build a mental model of XIRR, intuitively, because it might not always be possible to have enough information to invoke xirr() function and obtain exact value of it.

Two Transactions - CAGR and XIRR

Let’s begin with a simple use-case, where a set of cashflows is basically only two transactions.

One purchase, and one sell or redeem-able account value.

It could be a lumpsum purchase transaction for a bond or a stock or even a mutual fund / ETF, and after the first purchase, there have been no transactions on that account.

Let’s consider an investor had purchased HDFC (NSE: HDFC) stock at a price of , on 1st April 2016, for quantity of 1 share.

Then total invested amount is same as share price at the time of purchase.

Also assume this investor has continued to hold on to this share of HDFC. As on today, it’d have a different value from its valuation at the time of purchase.

We can model it as one purchase transaction (negative cashflow, because money is going away from end user), and a potential redemption transaction of today’s value.

At the time of writing this, this value is per share, as on 1st April 2021.

Date

Cashflow

It effectively means, if the investor were to sell the share when price is ₹2515.20, they’d receive ₹2515.20 in liquid cash in their account. We’re ignoring STT (Security Transaction Tax), brokerage, and other fees for now.

Then XIRR of these transactions can be computed easily, by inserting these numbers from the table into a spreadsheet, invoking the xirr() formula.

We’d get XIRR as p.a.

At the same time, we could obtain CAGR of underlying asset, HDFC shares, over 5 years, which also comes out to be p.a.

They are the same!

You might have noticed that output of RRI() function is slightly different from output of XIRR function. This is because those two dates are not exactly 5 years apart, you’ve to also count the leap days in between.

CAGR of an asset between two dates, can be computed using XIRR function, by creating a purchase transaction at the start date, and an imaginary sell transaction on the end date.

This is why most spreadsheet or excel applications don’t include a dedicated CAGR function ****You only have to invert the sign of first row entry of value!

We can mathematically prove it as well.

Recall that formula of CAGR is:

is number of years, where we’re considering 1 year as unit of time.

Then equivalent transactions would be of values and

Plugging these in the NPV equation for XIRR, we get

Rearranging, and keeping in mind that power raised to means , we get

Therefore, CAGR and XIRR would have same values.

CAGR XIRR

We've simply found a case, where value of CAGR for an asset between two dates; can be same as XIRR of an imaginary portfolio of two transactions, on those two dates.

This would be true for all investible assets, for any two dates; as long as it can be modeled as two transactions (one purchase, one sell).

This makes sense intuitively as well.

XIRR of a portfolio where investor has invested only once and redeemed once (or can redeem full amount if they choose to), is same as rate of growth of underlying asset over same period - which is CAGR.

More than Two Transactions

What if an investor has more transactions, mix of buy / sell etc.?

This is usually how normal portfolios look like.

Even if one were to make a bank fixed deposit, there can be year-end TDS, resulting in cash outflow from the deposit account; which would end up having multiple transactions in that portfolio.

Dotted line shows actual numeric value, of each transaction’s cashflow.

While, solid lines indicate how these values would be after discounting using the decay formulation.

You can see the decay as a result of discounting.

To understand why the decay over time moves like that, we can plot similar graphs from our spreadsheets

Steps:

Select the three columns of data on dates, cashflow, and discounted cashflow that we’d worked on earlier, to validate XIRR.
Insert chart from menubar or toolbar
Switch to bar chart

Here’s a short video to help you out, though it’s quite straight-forward, if you’ve followed along thus far:

Final plot should look like this:

While the blue lines in the above plot are of same heights for positive cashflow values and negative values; the red lines are where we see values being discounted more and more over time.

Recap and Wrapping Up

We learned various aspects of XIRR in the last chapter, using an example and output of in-built xirr() function.

Now we can cross-validate these, how these follow from definition of XIRR naturally:

XIRR doesn’t depend on exact dates, rather relative differences between dates of transactions
This is because in NPV equation, the power terms are normalized differences in time, and not absolute values of time points (, , , etc.).
XIRR doesn’t depend on exact value of cashflow amounts, only relative scaling within those numbers.
In other words, if every cashflow amounts were multiplied by some arbitrary number, it won’t change the value of XIRR.

Similarly, we can refer to the NPV equation of XIRR formulation, and validate our erstwhile understanding of XIRR behavior.

You might have noticed that we didn’t do any semi-log plots with cashflow values.

This is because NPV equation is an equation that lists formulas as sum of decayed entities.

There’s no mathematical formula to simplify , that relates to or in any way, in general.

Next chapter we would learn about applying XIRR in financial decision-making, in the wild. We’d consider real-world scenarios, and approach decision-making on those, by computing their XIRR; and comparing with XIRR of alternative investment avenues.

Computing LTCG Eligible Equity Units

Step-by-step guide on how to compute equity units eligible for LTCG taxation, if redeemed, using Spreadsheet

Problem Statement

Now that we’ve seen how to import CSV data, and more importantly, how to massage that data for further use; we need to put this skill to use.

As an investor, you might have a portfolio of equity funds, and you’d want to know how many units you can sell without exceeding Long Term Capital Gain of ₹100,000 (1L INR, in colloquial terms).

Let’s back up a bit.

When you redeem units in equity-linked assets, you’ve gains or losses, depending on price of purchase / acquisition; and your selling price on the day of selling.

Here, we’re dealing with equity mutual funds, that invests in equity markets.

If holding period of your equity units exceed 1 year (365 days), then it is long term capital gain or loss.

In 2018 Union Budget, the taxation rules were changed in a way, that an investor doesn’t have to pay tax on long term capital gains, up to ₹100,000 or ₹1L.

If an investor is looking to exit some positions by selling some of their holdings, it might be beneficial for them to know

how many units are older than 1 year of holding period
total number of units older than 1 year, that they can sell

Consider a sample transaction history, plotted against time, for an investor who’s been investing for a few years (figures not to scale)

The Y-axis represents units purchased in every transaction. It’s positive for purchase transactions, and negative offshoots are for sell / redemption transactions.

X-axis is for time, in years.

Now, let’s assume today’s date is somewhere in 2021-22. Say, it’s 3rd July 2021.

Then any units purchased in last 1 year before that, from 4th July 2020 to 3rd July 2021; are less than 1 year old.

And all units purchased on or before 3rd July 2020, are older than 1 year.

Our task is to use excel / spreadsheet find these two:

number of units older than 1 year old (to avoid STCG tax or Short Term Capital Gain)
number of units, that are older than 1 year old, which has net gain of 100,000 INR or less.

This is a CSV of transactions from one of our community members. We’ve changed around a few data points, and removed PII (Personally Identifying Information).

Take a look at the CSV above, or after downloading the file; we can then begin with planning phase.

Planning

Right off the bat, we notice that unlike our previous sample CSV, this starts each line with a comma.

We could easily import it into our spreadsheet using in-built import system in place.

First part of the problem, is to compute total number of units purchased more than a year ago.

Notice the term, total number of units.

This is different from number of units purchased, added up all together.

Why?

A sell transaction reduces number of units.

Total number of units outside of 1 year period, is

Notice the difference. We’re adding up units purchased only up to a year ago. But we’re subtracting all units sold (and not just units sold up to a year ago).

What if this value is negative?

Then, there’s no unit older than a year.

In this case, we might want to wrap the result to be zero.

We can use MAX() function for that

| |

However, there remains one more problem: there are more than one fund!

We cannot just add units of two different funds!

In other words, we need a way to present this result (total units older than 1 year), for each fund.

But how do we get excel / spreadsheet to extract this unique set of values, then add up values only against each of those?

One simple observation is because the fund names are grouped together, we can manually segregate these.

But in a different CSV file, the grouping might not be there at all. Different funds can be mixed with transactions appearing one after another, to give it a chronological order.

We can use UNIQUE() function from Google Sheets, to get a list of unique fund names, and this would work for the case even when the names are not grouped together.

| |

Next task, is to add up transactions for each of the unique fund names that appear.

There are more than one ways to go about it. We could use FILTER() function, operate on a subset of the data. We could also use some combination of LOOKUP() with SUM() or SUMIF().

We use SUMIFS() to add up all units from purchase transactions up to a year ago, and subtract the unit balance for redemption or switch-out transactions.

| |

This function allows for more than one criteria to search, and sum up, within a range of data.

How do we find up to a year ago?

Instead of putting a hardcoded value for date, we can use a dynamically-deduced value. Reason being, this output would depend on which date you’re checking the spreadsheet.

TODAY() function gives us today’s date. And subtracting 365, gives date going back a year.

Remaining Units Older than 1 Year

Now that we’ve chalked out a plan, it’s time to execute this step by step.

Importing Transaction History

Follow these steps:

Open a new empty spreadsheet
Download the CSV file (or copy paste from above into a text file) in your machine.
Go to your spreadsheet and import with these settings
Select these options:

After importing this CSV file into your Spreadsheet, it should look like this

Extracting Unique Funds

We’d use this sheet as our source of data.

And in case you’re wondering, yes, excel / spreadsheet allows you to refer to data from a cell in different sheet.

Follow these steps to extract a list of unique funds

Create a new sheet in your workbook, clicking on the plus sign (+) at the bottom left of your spreadsheet application.
In this new sheet, add a table header

It should look like this

Next step is to use UNIQUE() function, with the range of all funds.

There are total 329 entries in the CSV imported transactions.

In the first sheet, if the first entry of fund name starts at B2, then the last entry would be at (329 - 2 + 1) = B330.

We are referring across sheet, the reference works out as <name of the sheet>!<cellId>.

Refer to this video for more guidance:

Final outcome of this process should look like as follows

Locking Cell IDs for Dragging Autofills

Before we proceed to compute the total units sold or purchased, a look at autofill drag behavior is warranted.

Say, a formula is FUNCTION(A2, sheet!C3:sheet!C300).

In this formula, if we drag it to next row, it’d change as follows: FUNCTION(A3, sheet!C4:sheet!C301).

However, we might not want all referred fields to change.

A2 → A3 ✅ sheet!C3:sheet!C300 → sheet!C4:sheet!C301 ❌

The range of data remains same for both functions, and we’d only want a part of formula to change, while other part of the formula remaining constant.

This is referred to as locking cells in excel or spreadsheet formula.

$ is used to lock a formula’s elements.

A cell ID consists of Column ID, and a Row ID. Using $ ahead of either of these, locks them or makes them immutable when an auto-fill dragging happens.

For instance, $A2 means if horizontal dragging is done, it’d remain as $A2. And won’t turn into $B2.

Similarly, A$3 means if vertical dragging is done, it won’t change to A$4. Rather it’d remain as A$3.

To lock or preserve the expression, both horizontally and vertically, we can use $A$2

In the above example, FUNCTION(A3, sheet!$C$3:sheet!$C$300) should do the trick.

We’d use this in the next step.

Total Number of Units Sold

Number of units sold in a fund, can be computed in the second sheet as sum of these cells:

where name of the fund match the fund column in data sheet
where unit column is negative

However, before we did that, we should update the data cells.

When importing, we imported everything as raw strings. And prevented spreadsheet application from formatting dates / numbers / strings as it saw fit.

This was to give us more control over how to format these strings.

Follow these steps:

In the data sheet where all CSV entries were imported, add a new column next to last column
Invoke VALUE() function for first entry against number of units.
This function takes raw string values of units, and convert those to floating-point numbers. This would help us compare against 0 or other numbers, or add these up.

Refer to the following video(s)

Final result should match this

Normalizing imported dates

To determine unit balance for only purchase transactions, about a year ago, we need to use SUMIFS() function again.

But this time, we’ve to add one more condition: date of purchase

Comparing dates adds some complexity.

As we had to convert raw strings to their numeric values earlier, we’d have to convert these raw string transaction dates to actual dates that would allow our spreadsheets to compare against other dates.

Follow these steps to achieve this:

Add an extra column next to the units value column, in data sheet.
Invoke DATEVALUE() function to compute date from raw strings in Purchase Date column.
It’d most likely print a number.

| |

The newly created column should contain similar-looking values, though under the hood, they’ve been converted to date objects.

Units purchased older than 1 year

It’s time to put all these together.

The SUMIFS() would get one more condition with 2 more arguments - that purchase date / transaction date were before 1 year ago.

In the second sheet (not the sheet named data), add a cell, to note down date exactly 1 year ago.
It should be written as TODAY() - 1 * 365. TODAY() is an in-built function that returns today’s date (we want to compute it, so no matter when someone checks the sheet, it gives correct value based on that date).
By default, subtraction assumes unit as 1 day, therefore subtracting 365 is enough to get the date from exactly a year ago.
Use date formatting if needed.

Refer to the following video:

Final result should match these

The numbers might not exactly match, because depending on today's date, you might have a higher value. Above computation is as on 21st March 2021.

When a cell range in a sheet is being referred, second reference to same sheet can be omitted. data!B4:data!B50 can be written as data!B4:B50

LTCG-Eligible Units

This part is straight-forward

It’s sum of total units purchased more than one year ago, and total units sold so far.

Effectively, we have to sum up the column entries. We can use auto-fill with drag. In fact, spreadsheet might actually prompt here, on how to autofill these cells, in last column of second sheet.

It also shows why keeping units sold in negative was a good idea.

A sample final result can look like this

Wrapping up

It’s easy to cross-check and verify that these complex computations and instrumentations actually lead to sane results

Based on the above computations, we see that investor has entered and exited multiple funds over the years.

Out of these, this investor hasn’t sold units in the following:

Parag Parikh Flexi Cap Fund - Regular Plan - Growth
Axis Focused 25 Fund - Regular Plan - Growth
Mirae Asset Large Cap Fund - Regular Plan - Dividend

This is evident from the Units Sold column - investor hasn’t sold any units in these funds.

Over the years, investor has switched their corpus into these above funds.

For the other funds in their portfolio, they’ve completely sold these off, more than a year ago. We can easily verify this, going through the redemption transactions, and see that those were before 21/03/2020, i.e. more than one year ago.

,Scheme Name ,Purchase Date,Transaction Type,Amount (Rs.),Price  (Rs.),Units
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,09/06/2017,New Investment ,5000,20.6793,241.788
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,11/07/2017,Additional Investment ,5000,20.843,239.889
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,09/08/2017,Additional Investment ,5000,21.0585,237.434
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,11/09/2017,Additional Investment ,10000,21.801,458.695
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,10/10/2017,Additional Investment ,10000,22.2062,450.325
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,09/11/2017,Additional Investment ,10000,22.6972,440.583
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,11/12/2017,Additional Investment ,10000,23.0909,433.071
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,09/01/2018,Additional Investment ,10000,23.8074,420.037
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,09/02/2018,Additional Investment ,10000,23.0323,434.173
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,09/03/2018,Additional Investment ,10000,23.2336,430.411
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,10/04/2018,Additional Investment ,10000,22.9069,436.55
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,09/05/2018,Additional Investment ,10000,23.4069,427.224
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,15/06/2018,Additional Investment ,10000,24.2186,412.906
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,10/07/2018,Additional Investment ,10000,24.6922,404.986
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,09/08/2018,Additional Investment ,10000,25.3014,395.235
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,11/09/2018,Additional Investment ,10000,25.178,397.172
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,09/10/2018,Additional Investment ,10000,23.3565,428.146
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,09/11/2018,Additional Investment ,10000,23.0313,434.192
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,11/12/2018,Additional Investment ,10000,23.1282,432.373
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,09/01/2019,Additional Investment ,10000,23.451,426.421
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,09/04/2019,Additional Investment ,10000,25.0255,399.592
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,09/05/2019,Additional Investment ,10000,24.6692,405.364
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,11/06/2019,Additional Investment ,10000,25.307,395.148
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,09/07/2019,Additional Investment ,10000,25.0506,399.192
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,09/08/2019,Additional Investment ,10000,24.7311,404.349
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,11/09/2019,Additional Investment ,10000,25.0192,399.693
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,10/10/2019,Additional Investment ,10000,24.9239,401.221
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,11/11/2019,Additional Investment ,10000,26.4092,378.656
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,10/12/2019,Additional Investment ,10000,26.1356,382.62
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,09/01/2020,Additional Investment ,10000,26.9707,370.773
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,11/02/2020,Additional Investment ,10000,28.1105,355.739
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,11/03/2020,Additional Investment ,10000,24.9975,400.04
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,13/04/2020,Additional Investment ,10000,22.6226,442.036
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,11/05/2020,Additional Investment ,10000,23.6931,422.064
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,09/06/2020,Additional Investment ,10000,26.2705,380.655
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,09/07/2020,Additional Investment ,10000,28.3438,352.793
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,11/08/2020,Additional Investment ,10000,30.2264,330.82
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,09/09/2020,Additional Investment ,10000,30.0601,332.65
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,09/10/2020,Additional Investment ,10000,31.8137,314.314
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,10/11/2020,Additional Investment ,10000,32.4878,307.792
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,09/12/2020,Additional Investment ,10000,34.5138,289.725
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,11/01/2021,Additional Investment ,10000,35.9973,277.785
,Parag Parikh Flexi Cap Fund - Regular Plan - Growth,12/02/2021,Additional Investment ,10000,38.2601,261.356
,Invesco India Multicap Fund - Growth,11/03/2015,New Investment ,1000,36.2,27.624
,Invesco India Multicap Fund - Growth,13/04/2015,Additional Investment ,1000,37.12,26.94
,Invesco India Multicap Fund - Growth,12/05/2015,Additional Investment ,1000,33.7,29.674
,Invesco India Multicap Fund - Growth,11/06/2015,Additional Investment ,1000,34.1,29.326
,Invesco India Multicap Fund - Growth,13/07/2015,Additional Investment ,1000,36.13,27.678
,Invesco India Multicap Fund - Growth,11/08/2015,Additional Investment ,1000,37.28,26.824
,Invesco India Multicap Fund - Growth,11/09/2015,Additional Investment ,1000,34.1,29.326
,Invesco India Multicap Fund - Growth,13/10/2015,Additional Investment ,1000,35.32,28.313
,Invesco India Multicap Fund - Growth,13/11/2015,Additional Investment ,1000,34.46,29.019
,Invesco India Multicap Fund - Growth,11/12/2015,Additional Investment ,1000,34.1,29.326
,Invesco India Multicap Fund - Growth,12/01/2016,Additional Investment ,1000,34.98,28.588
,Invesco India Multicap Fund - Growth,11/02/2016,Additional Investment ,1000,30.97,32.289
,Invesco India Multicap Fund - Growth,02/03/2020,Redemption ,-16808.13,48.73,-344.927
,Axis Focused 25 Fund - Regular Plan - Growth,05/11/2018,New Investment ,10000,25.6,390.625
,Axis Focused 25 Fund - Regular Plan - Growth,04/12/2018,Additional Investment ,10000,26.45,378.072
,Axis Focused 25 Fund - Regular Plan - Growth,04/01/2019,Additional Investment ,10000,26.33,379.795
,Axis Focused 25 Fund - Regular Plan - Growth,05/03/2019,Additional Investment ,10000,25.98,384.911
,Axis Focused 25 Fund - Regular Plan - Growth,04/04/2019,Additional Investment ,10000,27.28,366.569
,Axis Focused 25 Fund - Regular Plan - Growth,06/05/2019,Additional Investment ,10000,27.32,366.032
,Axis Focused 25 Fund - Regular Plan - Growth,04/06/2019,Additional Investment ,10000,28.92,345.781
,Axis Focused 25 Fund - Regular Plan - Growth,04/07/2019,Additional Investment ,10000,28.91,345.901
,Axis Focused 25 Fund - Regular Plan - Growth,05/08/2019,Additional Investment ,10000,26.98,370.645
,Axis Focused 25 Fund - Regular Plan - Growth,04/09/2019,Additional Investment ,10000,27.05,369.686
,Axis Focused 25 Fund - Regular Plan - Growth,04/10/2019,Additional Investment ,10000,28.82,346.981
,Axis Focused 25 Fund - Regular Plan - Growth,04/12/2019,Additional Investment ,10000,30.33,329.707
,Axis Focused 25 Fund - Regular Plan - Growth,06/01/2020,Additional Investment ,10000,30.19,331.236
,Axis Focused 25 Fund - Regular Plan - Growth,04/02/2020,Additional Investment ,10000,31.73,315.159
,Axis Focused 25 Fund - Regular Plan - Growth,04/03/2020,Additional Investment ,10000,30.41,328.839
,Axis Focused 25 Fund - Regular Plan - Growth,07/04/2020,Additional Investment ,10000,23.36,428.082
,Axis Focused 25 Fund - Regular Plan - Growth,05/05/2020,Additional Investment ,5000,24.31,205.677
,Axis Focused 25 Fund - Regular Plan - Growth,04/06/2020,Additional Investment ,5000,26.14,191.278
,Axis Focused 25 Fund - Regular Plan - Growth,06/07/2020,Additional Investment ,5000,28.01,178.499
,Axis Focused 25 Fund - Regular Plan - Growth,04/08/2020,Additional Investment ,5000,28.44,175.8
,Axis Focused 25 Fund - Regular Plan - Growth,04/09/2020,Additional Investment ,5000,29.49,169.541
,Axis Focused 25 Fund - Regular Plan - Growth,05/10/2020,Additional Investment ,5000,29.66,168.569
,Axis Focused 25 Fund - Regular Plan - Growth,04/11/2020,Additional Investment ,5000,31.02,161.178
,Axis Focused 25 Fund - Regular Plan - Growth,04/12/2020,Additional Investment ,10000,35.11,284.805
,Axis Focused 25 Fund - Regular Plan - Growth,05/01/2021,Additional Investment ,10000,38.12,262.316
,Axis Focused 25 Fund - Regular Plan - Growth,05/02/2021,Additional Investment ,10000,38.47,259.93
,Mirae Asset Large Cap Fund - Regular Plan - Dividend,01/04/2019,Switch In  -  Mirae Asset Emerging Bluechip Fund - Regular Growth ,118472.37,18.226,6500.185
,Mirae Asset Large Cap Fund - Regular Plan - Dividend,10/04/2019,New Investment ,10000,18.061,553.679
,Mirae Asset Large Cap Fund - Regular Plan - Dividend,10/05/2019,Additional Investment ,10000,17.602,568.117
,Mirae Asset Large Cap Fund - Regular Plan - Dividend,11/06/2019,Additional Investment ,10000,18.655,536.049
,Mirae Asset Large Cap Fund - Regular Plan - Dividend,10/07/2019,Additional Investment ,10000,18.049,554.047
,Mirae Asset Large Cap Fund - Regular Plan - Dividend,13/08/2019,Additional Investment ,10000,17.196,581.531
,Mirae Asset Large Cap Fund - Regular Plan - Dividend,11/09/2019,Additional Investment ,10000,17.516,570.907
,Mirae Asset Large Cap Fund - Regular Plan - Dividend,13/11/2019,Additional Investment ,10000,18.717,534.274
,Mirae Asset Large Cap Fund - Regular Plan - Dividend,10/12/2019,Additional Investment ,10000,18.761,533.021
,Mirae Asset Large Cap Fund - Regular Plan - Dividend,16/12/2019,Dividend Reinvestment  @ Rs. 1.3281 P/U,14518.68,17.615,824.223
,Mirae Asset Large Cap Fund - Regular Plan - Dividend,10/01/2020,Additional Investment ,10000,17.876,559.409
,Mirae Asset Large Cap Fund - Regular Plan - Dividend,11/02/2020,Additional Investment ,10000,17.746,563.507
,Mirae Asset Large Cap Fund - Regular Plan - Dividend,11/03/2020,Additional Investment ,10000,15.535,643.708
,Mirae Asset Large Cap Fund - Regular Plan - Dividend,13/04/2020,Additional Investment ,5000,13.32,375.375
,Mirae Asset Large Cap Fund - Regular Plan - Dividend,12/05/2020,Additional Investment ,5000,13.496,370.48
,Mirae Asset Large Cap Fund - Regular Plan - Dividend,10/06/2020,Additional Investment ,5000,14.8,337.838
,Mirae Asset Large Cap Fund - Regular Plan - Dividend,10/07/2020,Additional Investment ,5000,15.835,315.74
,Mirae Asset Large Cap Fund - Regular Plan - Dividend,11/08/2020,Additional Investment ,5000,16.785,297.87
,Mirae Asset Large Cap Fund - Regular Plan - Dividend,10/09/2020,Additional Investment ,5000,17.042,293.378
,Mirae Asset Large Cap Fund - Regular Plan - Dividend,12/10/2020,Additional Investment ,5000,17.545,284.967
,Mirae Asset Large Cap Fund - Regular Plan - Dividend,10/11/2020,Additional Investment ,5000,18.495,270.33
,Mirae Asset Large Cap Fund - Regular Plan - Dividend,10/12/2020,Additional Investment ,10000,19.642,509.088
,Mirae Asset Large Cap Fund - Regular Plan - Dividend,12/01/2021,Additional Investment ,10000,21.186,471.986
,Mirae Asset Large Cap Fund - Regular Plan - Dividend,10/02/2021,Additional Investment ,10000,21.963,455.288
,Mirae Asset Emerging Bluechip Fund - Regular Growth,12/03/2018,New Investment ,10000,48.56,205.931
,Mirae Asset Emerging Bluechip Fund - Regular Growth,10/04/2018,Additional Investment ,10000,49.532,201.89
,Mirae Asset Emerging Bluechip Fund - Regular Growth,10/05/2018,Additional Investment ,10000,49.978,200.088
,Mirae Asset Emerging Bluechip Fund - Regular Growth,11/06/2018,Additional Investment ,10000,49.185,203.314
,Mirae Asset Emerging Bluechip Fund - Regular Growth,10/07/2018,Additional Investment ,10000,48.706,205.314
,Mirae Asset Emerging Bluechip Fund - Regular Growth,10/09/2018,Additional Investment ,10000,51.373,194.655
,Mirae Asset Emerging Bluechip Fund - Regular Growth,10/10/2018,Additional Investment ,10000,46.757,213.872
,Mirae Asset Emerging Bluechip Fund - Regular Growth,12/11/2018,Additional Investment ,10000,48.201,207.465
,Mirae Asset Emerging Bluechip Fund - Regular Growth,10/12/2018,Additional Investment ,10000,47.717,209.569
,Mirae Asset Emerging Bluechip Fund - Regular Growth,10/01/2019,Additional Investment ,10000,50.069,199.724
,Mirae Asset Emerging Bluechip Fund - Regular Growth,11/03/2019,Additional Investment ,10000,51.563,193.938
,Mirae Asset Emerging Bluechip Fund - Regular Growth,01/04/2019,Switch Out  -  Mirae Asset Large Cap Fund - Regular Plan - Dividend ,-118472.37,52.99,-2235.76
,HDFC Mid-Cap Opportunities Fund - Growth,11/03/2015,New Investment ,1000,37.603,26.594
,HDFC Mid-Cap Opportunities Fund - Growth,13/04/2015,Additional Investment ,1000,38.728,25.821
,HDFC Mid-Cap Opportunities Fund - Growth,12/05/2015,Additional Investment ,1000,35.892,27.861
,HDFC Mid-Cap Opportunities Fund - Growth,11/06/2015,Additional Investment ,1000,36.095,27.705
,HDFC Mid-Cap Opportunities Fund - Growth,13/07/2015,Additional Investment ,1000,37.917,26.373
,HDFC Mid-Cap Opportunities Fund - Growth,11/08/2015,Additional Investment ,1000,39.696,25.191
,HDFC Mid-Cap Opportunities Fund - Growth,11/09/2015,Additional Investment ,1000,36.662,27.276
,HDFC Mid-Cap Opportunities Fund - Growth,13/10/2015,Additional Investment ,1000,37.649,26.561
,HDFC Mid-Cap Opportunities Fund - Growth,13/11/2015,Additional Investment ,1000,36.915,27.089
,HDFC Mid-Cap Opportunities Fund - Growth,11/12/2015,Additional Investment ,1000,36.611,27.314
,HDFC Mid-Cap Opportunities Fund - Growth,12/01/2016,Additional Investment ,1000,36.829,27.153
,HDFC Mid-Cap Opportunities Fund - Growth,11/02/2016,Additional Investment ,1000,33.233,30.091
,HDFC Mid-Cap Opportunities Fund - Growth,14/02/2017,New Investment ,10000,46.795,213.698
,HDFC Mid-Cap Opportunities Fund - Growth,14/03/2017,Additional Investment ,10000,47.445,210.77
,HDFC Mid-Cap Opportunities Fund - Growth,12/04/2017,Additional Investment ,10000,50.719,197.165
,HDFC Mid-Cap Opportunities Fund - Growth,12/05/2017,Additional Investment ,10000,52.274,191.3
,HDFC Mid-Cap Opportunities Fund - Growth,13/06/2017,Additional Investment ,10000,52.017,192.245
,HDFC Mid-Cap Opportunities Fund - Growth,12/07/2017,Additional Investment ,10000,53.096,188.338
,HDFC Mid-Cap Opportunities Fund - Growth,14/08/2017,Additional Investment ,10000,52.539,190.335
,HDFC Mid-Cap Opportunities Fund - Growth,12/09/2017,Additional Investment ,10000,54.472,183.581
,HDFC Mid-Cap Opportunities Fund - Growth,12/10/2017,Additional Investment ,10000,54.584,183.204
,HDFC Mid-Cap Opportunities Fund - Growth,14/11/2017,Additional Investment ,10000,56.603,176.669
,HDFC Mid-Cap Opportunities Fund - Growth,12/12/2017,Additional Investment ,10000,57.413,174.177
,HDFC Mid-Cap Opportunities Fund - Growth,12/01/2018,Additional Investment ,10000,61.258,163.244
,HDFC Mid-Cap Opportunities Fund - Growth,14/02/2018,Additional Investment ,10000,57.766,173.112
,HDFC Mid-Cap Opportunities Fund - Growth,13/03/2018,Additional Investment ,10000,56.725,176.289
,HDFC Mid-Cap Opportunities Fund - Growth,12/04/2018,Additional Investment ,10000,58.372,171.315
,HDFC Mid-Cap Opportunities Fund - Growth,14/05/2018,Additional Investment ,10000,58.219,171.765
,HDFC Mid-Cap Opportunities Fund - Growth,12/06/2018,Additional Investment ,10000,57.232,174.727
,HDFC Mid-Cap Opportunities Fund - Growth,12/07/2018,Additional Investment ,10000,56.713,176.326
,HDFC Mid-Cap Opportunities Fund - Growth,02/03/2020,Redemption ,-195192.83,53.724,-3633.289
,Franklin India Smaller Companies Fund - Growth,29/12/2014,New Investment ,5000,35.9135,139.223
,Franklin India Smaller Companies Fund - Growth,16/01/2015,Additional Investment ,5000,37.7375,132.494
,Franklin India Smaller Companies Fund - Growth,18/02/2015,Additional Investment ,5000,38.2927,130.573
,Franklin India Smaller Companies Fund - Growth,17/03/2015,Additional Investment ,5000,38.8945,128.553
,Franklin India Smaller Companies Fund - Growth,16/04/2015,Additional Investment ,5000,39.603,126.253
,Franklin India Smaller Companies Fund - Growth,18/05/2015,Additional Investment ,5000,38.4578,130.013
,Franklin India Smaller Companies Fund - Growth,16/06/2015,Additional Investment ,5000,36.5897,136.65
,Franklin India Smaller Companies Fund - Growth,16/07/2015,Additional Investment ,5000,39.2018,127.545
,Franklin India Smaller Companies Fund - Growth,18/08/2015,Additional Investment ,5000,40.1264,124.606
,Franklin India Smaller Companies Fund - Growth,16/09/2015,Additional Investment ,5000,37.6015,132.973
,Franklin India Smaller Companies Fund - Growth,16/10/2015,Additional Investment ,5000,39.5969,126.273
,Franklin India Smaller Companies Fund - Growth,17/11/2015,Additional Investment ,5000,38.8075,128.841
,Franklin India Smaller Companies Fund - Growth,16/12/2015,Additional Investment ,5000,38.8986,128.539
,Franklin India Smaller Companies Fund - Growth,18/01/2016,Additional Investment ,5000,36.1752,138.216
,Franklin India Smaller Companies Fund - Growth,16/02/2016,Additional Investment ,5000,35.2195,141.967
,Franklin India Smaller Companies Fund - Growth,16/03/2016,Additional Investment ,5000,36.5967,136.624
,Franklin India Smaller Companies Fund - Growth,18/04/2016,Additional Investment ,5000,40.238,124.261
,Franklin India Smaller Companies Fund - Growth,17/05/2016,Additional Investment ,5000,41.0136,121.911
,Franklin India Smaller Companies Fund - Growth,16/06/2016,Additional Investment ,5000,42.447,117.794
,Franklin India Smaller Companies Fund - Growth,18/07/2016,Additional Investment ,10000,44.4423,225.011
,Franklin India Smaller Companies Fund - Growth,16/09/2016,Additional Investment ,10000,47.3914,211.009
,Franklin India Smaller Companies Fund - Growth,17/10/2016,Additional Investment ,10000,48.4465,206.413
,Franklin India Smaller Companies Fund - Growth,16/11/2016,Additional Investment ,10000,44.2113,226.187
,Franklin India Smaller Companies Fund - Growth,16/12/2016,Additional Investment ,10000,44.4986,224.726
,Franklin India Smaller Companies Fund - Growth,02/03/2020,Redemption ,-171366.86,48.0475,-3566.655
,Mirae Asset Large Cap Fund - Regular Plan - Dividend,15/10/2018,Switch In  -  Mirae Asset Large Cap Fund - Regular Plan - Growth ,68541.61,17.951,3818.261
,Mirae Asset Large Cap Fund - Regular Plan - Dividend,06/12/2018,Dividend Reinvestment  @ Rs. 1.4167 P/U,5409.15,16.59,326.049
,Mirae Asset Large Cap Fund - Regular Plan - Dividend,16/12/2019,Dividend Reinvestment  @ Rs. 1.3281 P/U,5504.11,17.615,312.467
,Mirae Asset Large Cap Fund - Regular Plan - Growth,04/04/2018,New Investment ,10000,45.101,221.725
,Mirae Asset Large Cap Fund - Regular Plan - Growth,03/05/2018,Additional Investment ,10000,46.989,212.816
,Mirae Asset Large Cap Fund - Regular Plan - Growth,04/06/2018,Additional Investment ,10000,46.39,215.564
,Mirae Asset Large Cap Fund - Regular Plan - Growth,03/07/2018,Additional Investment ,10000,46.486,215.119
,Mirae Asset Large Cap Fund - Regular Plan - Growth,03/08/2018,Additional Investment ,10000,49.189,203.297
,Mirae Asset Large Cap Fund - Regular Plan - Growth,04/09/2018,Additional Investment ,10000,50.047,199.812
,Mirae Asset Large Cap Fund - Regular Plan - Growth,04/10/2018,Additional Investment ,10000,46.144,216.713
,Mirae Asset Large Cap Fund - Regular Plan - Growth,15/10/2018,Switch Out  -  Mirae Asset Large Cap Fund - Regular Plan - Dividend ,-68541.61,46.155,-1485.046
,Franklin India Flexi Cap Fund - Growth,18/05/2017,New Investment ,10000,531.5921,18.811
,Franklin India Flexi Cap Fund - Growth,19/06/2017,Additional Investment ,10000,537.3628,18.609
,Franklin India Flexi Cap Fund - Growth,18/07/2017,Additional Investment ,10000,555.2843,18.009
,Franklin India Flexi Cap Fund - Growth,18/08/2017,Additional Investment ,10000,550.453,18.167
,Franklin India Flexi Cap Fund - Growth,19/09/2017,Additional Investment ,10000,568.3494,17.595
,Franklin India Flexi Cap Fund - Growth,18/10/2017,Additional Investment ,10000,564.1949,17.724
,Franklin India Flexi Cap Fund - Growth,20/11/2017,Additional Investment ,10000,578.9429,17.273
,Franklin India Flexi Cap Fund - Growth,19/12/2017,Additional Investment ,10000,590.9089,16.923
,Franklin India Flexi Cap Fund - Growth,18/01/2018,Additional Investment ,10000,608.2863,16.44
,Franklin India Flexi Cap Fund - Growth,19/02/2018,Additional Investment ,10000,574.911,17.394
,Franklin India Flexi Cap Fund - Growth,15/10/2018,Switch Out  -  Franklin India Smaller Companies Fund - Growth ,-97131.08,548.9394,-176.945
,Franklin India Smaller Companies Fund - Growth,15/10/2018,Switch In  -  Franklin India Flexi Cap Fund - Growth ,97131.08,51.4937,1886.271
,Franklin India Smaller Companies Fund - Growth,04/04/2019,Switch In  -  Franklin India Bluechip Fund - Growth ,300975.04,54.1568,5557.476
,Franklin India Smaller Companies Fund - Growth,11/04/2019,New Investment ,10000,54.2866,184.208
,Franklin India Smaller Companies Fund - Growth,13/05/2019,Additional Investment ,10000,51.38,194.628
,Franklin India Smaller Companies Fund - Growth,11/06/2019,Additional Investment ,10000,54.4227,183.747
,Franklin India Smaller Companies Fund - Growth,11/07/2019,Additional Investment ,10000,51.5779,193.881
,Franklin India Smaller Companies Fund - Growth,14/08/2019,Additional Investment ,10000,46.9668,212.916
,Franklin India Smaller Companies Fund - Growth,11/09/2019,Additional Investment ,10000,47.8012,209.2
,Franklin India Smaller Companies Fund - Growth,02/03/2020,Redemption ,-411040.57,47.6721,-8622.327
,Franklin India Bluechip Fund - Growth,01/09/2016,New Investment ,10000,390.993,25.576
,Franklin India Bluechip Fund - Growth,14/10/2016,Additional Investment ,10000,385.7494,25.924
,Franklin India Bluechip Fund - Growth,15/11/2016,Additional Investment ,10000,368.2658,27.154
,Franklin India Bluechip Fund - Growth,14/12/2016,Additional Investment ,10000,368.8564,27.111
,Franklin India Bluechip Fund - Growth,16/01/2017,Additional Investment ,10000,378.5611,26.416
,Franklin India Bluechip Fund - Growth,14/02/2017,Additional Investment ,10000,395.3548,25.294
,Franklin India Bluechip Fund - Growth,14/03/2017,Additional Investment ,10000,406.0662,24.627
,Franklin India Bluechip Fund - Growth,17/04/2017,Additional Investment ,10000,409.7139,24.407
,Franklin India Bluechip Fund - Growth,16/05/2017,Additional Investment ,10000,426.1836,23.464
,Franklin India Bluechip Fund - Growth,12/06/2017,New Investment ,10000,426.2069,23.463
,Franklin India Bluechip Fund - Growth,14/06/2017,Additional Investment ,10000,425.8755,23.481
,Franklin India Bluechip Fund - Growth,11/07/2017,Additional Investment ,10000,430.7472,23.215
,Franklin India Bluechip Fund - Growth,14/07/2017,Additional Investment ,10000,435.6868,22.952
,Franklin India Bluechip Fund - Growth,10/08/2017,Additional Investment ,10000,432.034,23.146
,Franklin India Bluechip Fund - Growth,16/08/2017,Additional Investment ,10000,435.3714,22.969
,Franklin India Bluechip Fund - Growth,12/09/2017,Additional Investment ,10000,441.6172,22.644
,Franklin India Bluechip Fund - Growth,14/09/2017,Additional Investment ,10000,442.6595,22.591
,Franklin India Bluechip Fund - Growth,10/10/2017,Additional Investment ,10000,440.277,22.713
,Franklin India Bluechip Fund - Growth,16/10/2017,Additional Investment ,10000,448.5239,22.295
,Franklin India Bluechip Fund - Growth,10/11/2017,Additional Investment ,10000,455.7687,21.941
,Franklin India Bluechip Fund - Growth,14/11/2017,Additional Investment ,10000,450.7018,22.188
,Franklin India Bluechip Fund - Growth,12/12/2017,Additional Investment ,10000,450.429,22.201
,Franklin India Bluechip Fund - Growth,14/12/2017,Additional Investment ,10000,450.8708,22.179
,Franklin India Bluechip Fund - Growth,10/01/2018,Additional Investment ,10000,468.3489,21.352
,Franklin India Bluechip Fund - Growth,12/02/2018,Additional Investment ,10000,457.7254,21.847
,Franklin India Bluechip Fund - Growth,14/02/2018,Additional Investment ,10000,454.7569,21.99
,Franklin India Bluechip Fund - Growth,14/03/2018,Additional Investment ,10000,447.9042,22.326
,Franklin India Bluechip Fund - Growth,01/04/2019,Switch Out  -  Franklin India Smaller Companies Fund - Growth ,-300975.04,473.6336,-635.466
,Franklin India Prima Fund - Growth,29/12/2014,New Investment ,5000,620.3648,8.06
,Franklin India Prima Fund - Growth,16/01/2015,Additional Investment ,5000,655.0102,7.633
,Franklin India Prima Fund - Growth,18/02/2015,Additional Investment ,5000,672.8812,7.431
,Franklin India Prima Fund - Growth,17/03/2015,Additional Investment ,5000,674.2269,7.416
,Franklin India Prima Fund - Growth,16/04/2015,Additional Investment ,5000,679.9313,7.354
,Franklin India Prima Fund - Growth,18/05/2015,Additional Investment ,5000,656.7176,7.614
,Franklin India Prima Fund - Growth,16/06/2015,Additional Investment ,5000,638.7628,7.828
,Franklin India Prima Fund - Growth,16/07/2015,Additional Investment ,5000,685.4009,7.295
,Franklin India Prima Fund - Growth,18/08/2015,Additional Investment ,5000,688.7061,7.26
,Franklin India Prima Fund - Growth,16/09/2015,Additional Investment ,5000,645.2428,7.749
,Franklin India Prima Fund - Growth,16/10/2015,Additional Investment ,5000,672.374,7.436
,Franklin India Prima Fund - Growth,17/11/2015,Additional Investment ,5000,656.3437,7.618
,Franklin India Prima Fund - Growth,16/12/2015,Additional Investment ,5000,656.4382,7.617
,Franklin India Prima Fund - Growth,18/01/2016,Additional Investment ,5000,615.5887,8.122
,Franklin India Prima Fund - Growth,16/02/2016,Additional Investment ,5000,597.298,8.371
,Franklin India Prima Fund - Growth,16/03/2016,Additional Investment ,5000,618.4861,8.084
,Franklin India Prima Fund - Growth,18/04/2016,Additional Investment ,5000,676.6577,7.389
,Franklin India Prima Fund - Growth,17/05/2016,Additional Investment ,5000,686.2338,7.286
,Franklin India Prima Fund - Growth,16/06/2016,Additional Investment ,5000,708.6313,7.056
,Franklin India Prima Fund - Growth,15/07/2016,Additional Investment ,5000,744.8421,6.713
,Franklin India Prima Fund - Growth,17/08/2016,Additional Investment ,10000,773.2618,12.932
,Franklin India Prima Fund - Growth,15/09/2016,Additional Investment ,10000,782.6346,12.777
,Franklin India Prima Fund - Growth,17/10/2016,Additional Investment ,10000,797.1977,12.544
,Franklin India Prima Fund - Growth,15/11/2016,Additional Investment ,10000,729.1266,13.715
,Franklin India Prima Fund - Growth,15/12/2016,Additional Investment ,10000,736.9692,13.569
,Franklin India Prima Fund - Growth,15/02/2017,Additional Investment ,10000,798.5369,12.523
,Franklin India Prima Fund - Growth,15/03/2017,Additional Investment ,10000,828.7766,12.066
,Franklin India Prima Fund - Growth,17/04/2017,Additional Investment ,10000,877.2783,11.399
,Franklin India Prima Fund - Growth,16/05/2017,Additional Investment ,10000,907.096,11.024
,Franklin India Prima Fund - Growth,15/06/2017,Additional Investment ,10000,894.824,11.175
,Franklin India Prima Fund - Growth,17/07/2017,Additional Investment ,10000,902.7454,11.077
,Franklin India Prima Fund - Growth,16/08/2017,Additional Investment ,10000,906.4853,11.032
,Franklin India Prima Fund - Growth,15/09/2017,Additional Investment ,10000,932.6085,10.723
,Franklin India Prima Fund - Growth,17/10/2017,Additional Investment ,10000,943.9366,10.594
,Franklin India Prima Fund - Growth,15/11/2017,Additional Investment ,10000,945.6373,10.575
,Franklin India Prima Fund - Growth,15/12/2017,Additional Investment ,10000,980.6321,10.198
,Franklin India Prima Fund - Growth,11/01/2018,New Investment ,10000,1038.8211,9.626
,Franklin India Prima Fund - Growth,14/02/2018,Additional Investment ,10000,983.9771,10.163
,Franklin India Prima Fund - Growth,13/03/2018,Additional Investment ,10000,973.9265,10.268
,Franklin India Prima Fund - Growth,11/04/2018,Additional Investment ,10000,987.189,10.13
,Franklin India Prima Fund - Growth,11/05/2018,Additional Investment ,10000,992.1258,10.079
,Franklin India Prima Fund - Growth,12/06/2018,Additional Investment ,10000,974.8691,10.258
,Franklin India Prima Fund - Growth,11/07/2018,Additional Investment ,10000,953.3073,10.49
,Franklin India Prima Fund - Growth,13/08/2018,Additional Investment ,10000,972.6508,10.281
,Franklin India Prima Fund - Growth,11/09/2018,Additional Investment ,10000,956.1179,10.459
,Franklin India Prima Fund - Growth,11/10/2018,Additional Investment ,10000,859.6945,11.632
,Franklin India Prima Fund - Growth,13/11/2018,Additional Investment ,10000,893.875,11.187
,Franklin India Prima Fund - Growth,11/12/2018,Additional Investment ,10000,892.4636,11.205
,Franklin India Prima Fund - Growth,11/01/2019,Additional Investment ,10000,920.3008,10.866
,Franklin India Prima Fund - Growth,02/03/2020,Redemption ,-446485.92,938.2031,-475.899
,ICICI Prudential Bluechip Fund - Growth,11/03/2015,New Investment ,2000,30.23,66.159
,ICICI Prudential Bluechip Fund - Growth,13/04/2015,Additional Investment ,2000,30.51,65.552
,ICICI Prudential Bluechip Fund - Growth,12/05/2015,Additional Investment ,2000,28.22,70.872
,ICICI Prudential Bluechip Fund - Growth,11/06/2015,Additional Investment ,2000,27.99,71.454
,ICICI Prudential Bluechip Fund - Growth,13/07/2015,Additional Investment ,2000,29.62,67.522
,ICICI Prudential Bluechip Fund - Growth,11/08/2015,Additional Investment ,2000,30.19,66.247
,ICICI Prudential Bluechip Fund - Growth,11/09/2015,Additional Investment ,2000,27.75,72.072
,ICICI Prudential Bluechip Fund - Growth,13/10/2015,Additional Investment ,2000,28.69,69.711
,ICICI Prudential Bluechip Fund - Growth,13/11/2015,Additional Investment ,2000,28.12,71.124
,ICICI Prudential Bluechip Fund - Growth,11/12/2015,Additional Investment ,2000,27.59,72.49
,ICICI Prudential Bluechip Fund - Growth,12/01/2016,Additional Investment ,2000,27.13,73.719
,ICICI Prudential Bluechip Fund - Growth,11/02/2016,Additional Investment ,2000,25.03,79.904
,ICICI Prudential Bluechip Fund - Growth,29/01/2019,New Investment ,10000,39.06,256.016
,ICICI Prudential Bluechip Fund - Growth,28/02/2019,Additional Investment ,10000,39.72,251.762
,ICICI Prudential Bluechip Fund - Growth,28/03/2019,Additional Investment ,10000,42,238.095
,ICICI Prudential Bluechip Fund - Growth,30/04/2019,Additional Investment ,10000,42.6,234.742
,ICICI Prudential Bluechip Fund - Growth,28/05/2019,Additional Investment ,10000,43.46,230.097
,ICICI Prudential Bluechip Fund - Growth,28/06/2019,Additional Investment ,10000,43.2,231.481
,ICICI Prudential Bluechip Fund - Growth,30/07/2019,Additional Investment ,10000,40.73,245.519
,ICICI Prudential Bluechip Fund - Growth,28/08/2019,Additional Investment ,10000,40.32,248.016
,ICICI Prudential Bluechip Fund - Growth,30/09/2019,Additional Investment ,10000,42.31,236.351
,ICICI Prudential Bluechip Fund - Growth,28/11/2019,Additional Investment ,10000,44.29,225.785
,ICICI Prudential Bluechip Fund - Growth,30/12/2019,Additional Investment ,10000,44.78,223.314
,ICICI Prudential Bluechip Fund - Growth,28/01/2020,Additional Investment ,10000,44.32,225.632
,ICICI Prudential Bluechip Fund - Growth,28/02/2020,Additional Investment ,10000,41.13,243.132
,DSP Tax Saver Fund - Regular Plan - Growth,02/05/2018,New Investment ,10000,46.601,214.588
,DSP Tax Saver Fund - Regular Plan - Growth,29/05/2018,Additional Investment ,10000,45.755,218.555
,DSP Tax Saver Fund - Regular Plan - Growth,29/06/2018,Additional Investment ,10000,44.168,226.408
,DSP Tax Saver Fund - Regular Plan - Growth,31/07/2018,Additional Investment ,10000,47.182,211.945
,DSP Tax Saver Fund - Regular Plan - Growth,29/08/2018,Additional Investment ,10000,48.482,206.262
,DSP Tax Saver Fund - Regular Plan - Growth,01/10/2018,Additional Investment ,10000,44.173,226.383
,DSP Tax Saver Fund - Regular Plan - Growth,30/10/2018,Additional Investment ,5000,41.902,119.326
,DSP Tax Saver Fund - Regular Plan - Growth,29/11/2018,Additional Investment ,5000,44.535,112.271
,DSP Tax Saver Fund - Regular Plan - Growth,31/12/2018,Additional Investment ,5000,44.952,111.23
,DSP Tax Saver Fund - Regular Plan - Growth,01/03/2019,Additional Investment ,5000,44.684,111.897
,DSP Tax Saver Fund - Regular Plan - Growth,29/03/2019,Additional Investment ,5000,48.352,103.408
,DSP Tax Saver Fund - Regular Plan - Growth,30/04/2019,Additional Investment ,5000,48.445,103.21
,DSP Tax Saver Fund - Regular Plan - Growth,29/05/2019,Additional Investment ,5000,49.323,101.373
,DSP Tax Saver Fund - Regular Plan - Growth,01/07/2019,Additional Investment ,5000,49.191,101.645
,DSP Tax Saver Fund - Regular Plan - Growth,30/07/2019,Additional Investment ,5000,46.45,107.643
,DSP Tax Saver Fund - Regular Plan - Growth,29/08/2019,Additional Investment ,5000,46.387,107.789
,DSP Tax Saver Fund - Regular Plan - Growth,01/10/2019,Additional Investment ,5000,48.926,102.195
,DSP Tax Saver Fund - Regular Plan - Growth,29/11/2019,Additional Investment ,5000,51.499,97.089
,DSP Tax Saver Fund - Regular Plan - Growth,31/12/2019,Additional Investment ,5000,51.619,96.864
,DSP Tax Saver Fund - Regular Plan - Growth,29/01/2020,Additional Investment ,5000,52.606,95.046
,DSP Tax Saver Fund - Regular Plan - Growth,02/03/2020,Additional Investment ,5000,48.921,102.206

Why should I invest in Direct Plans instead of Regular Plans?

Direct plans of mutual funds have no commission, lower fees compared to its Regular plan counterpart. Returns are higher with no extra risk.

A mutual fund is not free from costs. An asset management company (AMC) is a for-profit entity who aim to make some profit from their venture. They also have to pay their security analysts, operations teams, pay brokerage fees for market participation, and record keeping fees to registrars like CAMS and KFintech, formerly known as Karvy.

A regular plan of a mutual fund has one more additional expense — distributor commission.

There are various types of commissions. In this FAQ, we'll only focus on recurring trail commissions, and ignore upfront one-time commissions.

Why is distribution commission bad for an investor?

There are many reasons why regular plans shouldn’t exist.

Erosion of Value
Regular Plans add no value to the portfolio of an investor. All it does is eats away at your portfolio valuation and make the distributor richer.
This cost scales with your portfolio valuation. The more your investments grow in value, the more money is taken out of your investment.
It’s a classic case of tyranny of compounding costs, words made famous by Jack Bogle.

What difference does an extra 1% make?

Let’s try to understand, with real world scenarios, exactly how much one can stand to lose investing in regular plans. However, before we get there, let’s discuss what direct plans are.

The only difference between a direct plan and a regular plan of a mutual fund is that direct plans do not have distributor commissions. The word direct must be present in your NSDL / CDSL / AMC account statements if you’re investing in a direct plan of a mutual fund.

Although it may not be straightforward to calculate the difference in returns between a regular plan and a direct plan of a mutual fund from AMC account statements, we can calculate it by simulating the same transactions on the same date in the direct plan of a fund.

Lumpsum Purchase in Axis Long Term Equity and Tata Large Cap

As of 9th April 2021, the 5 year return (CAGR) of Axis Long Term Equity Direct Growth Plan stands at and that of the regular plan is . Let’s assume an initial investment of lakh on 9th April 2016.

Fund Type

5Y CAGR

Final Value

The distributor of this plan has made approximately of the original investment as commission in the last 5 years. And 5 years is a small time period in equity markets. Of course, this commission will only increase with time. However, this cost could’ve been easily avoided, just by investing in the direct plan of the same fund, 5 years ago.

One might say that the commission looks high only because the returns are high enough to more than double the original investment. After all, annualized returns over 5 year periods aren’t common.

Keeping this in mind, let’s take a different fund with a lower 5Y CAGR, where we do the same comparison, and see if losses to commission really goes down with the returns. As of 9th April 2021, the 5 year return (CAGR) of Tata Large Cap Direct Growth Plan stands at and that of the regular plan is .

Fund Type

5Y CAGR

Final Value

Although the returns and nominal profit of the investor have been reduced by a lot, the distributor commission has increased! In this case, it’s approximately of original investment, eroded over 5 years. Your distributor would make bank, whether your returns are higher or lower. They’d make even more, if your portfolio does well.

Next time, when a distributor tells you to keep your SIPs going, you have to wonder whether it’s because they don’t want to see their income disrupted, which is tied to total portfolio value.

Total Expense Ratio from an AMC’s Perspective

Let’s consider Kotak Standard Flexi Cap, one of the largest (in terms of AUM) equity mutual fund in India with an average AUM of crore at the end of March 2021. Its direct plan had a TER of and the regular plan had a TER of . From the average AUM disclosure provided by Kotak AMC for the month of March 2021, we know that Kotak Standard Flexi Cap had crores from its regular plan and crores from its direct plan.

We can use this data to get some insights about the expenses generated by this fund.

Kotak AMC gets to keep crores from both the regular and direct plan. Since distributors get on the regular plan, their commission comes out to be crores.

In a mutual fund with over crores in AUM, of its AUM comes from regular plans and mutual fund distributors end up taking away more expense income than the AMC of the fund itself even though the fund manager does the job of managing the mutual fund portfolio while your distributor does nothing except earn commissions and help the AMC inflate its AUM.

We’ve got the data about AUM distribution from link on page.

The miracle of compounding returns has been overwhelmed by the tyranny of compounding costs.

The recurring cost model of regular plans can unleash this mythical tyranny right on your portfolio, without you even realizing it.

We saw earlier that the difference in TER difference between the direct and regular plan of a mutual fund, or , can be thought of as an indicator of regular plan’s distribution costs.

Let’s assume that investing a lumpsum amount of in an asset generates a CAGR of . This asset also has a regular plan variant which generates a CAGR of . At lower CAGR, this is how the valuation would change over time.

If the difference in CAGR is assumed to be , this is how the corpus changes.

Eventually, an investor could end up losing of their portfolio to distributor commissions over a long period of time.

Falsehoods and Myths about Regular Plans

As you’d imagine, regular plans are a juicy proposition for every participant involved, except for the investor whose capital is being eroded.

An investor might come across some of the following arguments in favor of regular plans. We’ll go ahead and debunk them.

But why would AMCs purposefully lower their returns in regular plans? It doesn’t make sense.

AMCs don’t have a reason to compete on performance, when they can just offer more commissions on their funds. The distributors would then sing praises of such funds over other available options.

AMCs like Mirae Asset, have been artificially reducing the TER of the direct plan of their funds well below that of their competitor fund houses, creating large TER differences between their funds’ direct plan TER and regular plan TER.

This incentivizes distributors to recommend Mirae Asset equity funds over other funds and lures in direct plan investors with unusually low expense ratios. Of course, the unusually low TER of direct plans can simply be increased later once Mirae Asset’s AUM targets are achieved. It’s unfortunate that there are no regulations on frequent TER changes made by AMCs.

As on 9th April 2021, this is how the TERs look like for flagship funds from Mirae Asset AMC:

Of course, Mirae Asset isn’t the only AMC which is doing this. You might come across unsustainably low TERs in direct plans of other AMCs, with reasonably high TER in the regular plan of the same fund.

From our previous calculations, it should be clear that additional expenses would erode the corpus of an investor significantly in the long run.

If that person really did have your best interests at heart, he wouldn’t recommend funds with a high . If he’s doing that, he has his own interests on greater priority than yours. This wouldn’t be a problem in some other case but here, it’s about your money, not his.

Regular Plan distributors don’t have to recommend good funds, because if it doesn’t perform well, they could just tell you that the markets were bad this year, keep your SIPs going, and buy at lower prices. Your uninterrupted inflows in regular plans makes the regular plan distributor richer.

To understand how deep this nexus is, take a look at AMFI disclosure reports on commission to distributors, from past years:

Let’s start with the latest report, from 2019-20. It’s a PDF that lists the names of distributors and the gross amount paid in multiples of .

As expected, big banks like HDFC, ICICI, Kotak, and SBI are easily making hundreds of crores, just from rent-seeking and not adding any meaningful value to the portfolio of investors. However, relatively small distributors have been doing great as well. Based on that list, it’s not unusual to make over crore (a pretty large milestone in annual income for most Indians) by selling regular plans of mutual funds.

But the NAV of Regular Plans are lower than Direct Plans. Shouldn't I buy low and sell high?

The NAV of regular plans is lower because it includes distributor commission and it'll always be lower than the NAV of its direct plan counterpart. In this case, your purchase price doesn't matter. What matters, is the rate of growth after you purchase.

Let's consider the NAV of Axis Long Term Equity as on 12th April 2021. It was for the regular plan and for the direct plan. If we invest in the regular plan, we'll get units in the regular plan, and units in the direct plan. Let's assume that we get CAGR after 5 years in the regular plan and CAGR in the direct plan. The final portfolio value of the regular plan would be and that of the direct plan would be .

As we just saw, the NAV purchase price and the number of units that you get are irrelevant and do not affect the final portfolio value. The CAGR, however, does, and it should be evident that the CAGR of a direct plan will always be higher than its regular plan counterpart.

But it’s just a difference of 1%. It won’t have much impact.

As we’ve already shown above, a of can erode more than of your portfolio value over the long term. Even , let alone , is a significant loss to your portfolio. Are you okay with losing lakh from your corpus of crore and end up having lakhs? How about losing lakhs?

I know regular plans are bad but if I switch, I'd have to pay taxes. I don't want to pay taxes unnecessarily.

Taxes are on realized gains. Commission is on entire corpus.

Just like the largest ant is no match for the smallest elephant, your taxes might be low enough, if any, compared to your potential commission losses over next few years. Even if it’s not, tax is a one-time headache. Commissions are forever, till the date you’ve zero units to your name in that regular plan of a fund.

You could also switch out partially, and not all at once. That’d save you some taxes by spreading your redemptions over more than one financial year.

In a scenario like this, it’s best to compute the potential values for tax liabilities, project losses in commissions, and make a choice on a case-by-case basis.

If not for my distributor, I wouldn't have started investing in mutual funds. I'm grateful to him. I can't just switch to direct plans now.

It's your decision to make but we'd advise you to think rationally about this, and keep your emotions aside when making decisions about your money.

Your distributor may have introduced you to mutual funds but if he signed you up on regular plans, he probably didn't sign you up because he was thinking of your welfare first.

At this point, you have the power to make decisions about your own money and switch to direct plans to save expense costs. You don't even have to find different funds.

If you're not comfortable choosing funds, you can start investing in the direct plans of the same funds offered by your distributor. Even in that case, you'd save on a lot of expenses.

Would you rather be grateful to your distributor for the rest of your investing journey and lose up to of your corpus in the long term, or switch to direct plans and not lose that money?

But my distributor also gives advice. I don’t know anything about mutual funds. Investing in direct plans is only for advanced investors.

As we saw in the previous section, in the last 8 years, regular plans of large cap funds haven’t been able to achieve for the average SIP investor, what the direct plan of a low-cost Nifty index fund has generated.

Clearly, the so-called financial advice from regular plan distributors hasn’t been good enough to even make-up for the cost.

Investing in direct plans is a risk-free way of improving returns. A good advisor would recommend direct plans to their clients.

Also, when you invest in a mutual fund, the fund manager manages your portfolio. They are responsible for managing the investments in the you’ve invested in and adapt it according to market conditions. Your distributor has no role to play there.

SEBI has already issued notices saying that mutual fund distributors cannot be advisors. A person can either sell or advise but not do both.

Unlike a few years ago, we have many platforms today that offer investment in direct plans to every investor in the country. It has become much easier to invest in direct plans and manage your portfolio these days. One doesn’t need to visit an office, or use clunky AMC websites with bugs and errors.

If you’re wondering which website / app you should use to invest in direct plans, check out the following article.

If you’re wondering which mutual fund should you invest in, check out the following article.

We’d like to mention that choosing a “good” or a “best” fund isn’t important or required. Pick a decent actively managed fund and stick with it unless something fundamental changes about the fund. Or, if you’re still confused, pick an index fund and call it a day. The choice of the fund isn’t as important as sticking to that fund and investing in it in the face of market turbulence.

Wrapping Up

The real takeaway here is that recurring costs can affect your investment corpus in a really bad way over the long term. As an investor, avoiding regular plans is important. But in the same spirit, we should also avoid other recurring costs as well, which add up over the years, such as (but not limited to):

brokerage commission, in percentage terms (and not fixed), for equity delivery
rebalancing costs
frequent selling leading to capital gains taxes

Unlike regular plans, some of the above aren’t always avoidable. But do keep the adverse effects of costs from repeatedly doing these in mind, when engaging in, say, rebalancing.

Quantum AMC started offering direct plans of their flagship funds, Quantum Liquid and Quantum Long Term Equity, back in 2006. They were the first AMC to do so in India. They chose to not tie up with distributors, but rather offer a direct purchase option to the investors via offline mode (CAMS or KFinTech offices) and on their own website.

Since January 2013, SEBI regulations have forced all AMCs to launch direct plans for each and every mutual fund that they had and would come up with in the future.

However, despite the ease of access, most retail and HNI (High Net-worth Individual) investors still invest in regular plans. This is evident by the fact that most of the mutual fund industry AUM is still in regular plans, and not direct plans. ( | ), only of the industry AUM is in direct plans, while rest of it are in regular plans.

PayTM Money maintains listing difference in TER between direct plan and regular plan of mutual funds.

If you haven’t done it yet, take a look at your NSDL / CDSL CAS, and see if the word “direct” appears in the name of the funds that you’re invested in. If not, you’re most likely invested in the regular plan variant instead. Ideally, all your funds should be in direct plan mode, and no fund should be in regular plan mode. You might be losing money to a rent-seekers without receiving anything of value in return. If that’s the case, start the process of switching to direct plan today.

r/IndiaInvestments

Disclaimers and Disclosures

FAQs

FAQs

Mutual Funds and ETFs

What’s the best mutual fund I can invest in?

Which date(s) is/are best for SIP in a month?

I’ve to invest in ELSS for 80C tax saving. Which fund(s) should I pick?

Should I get a Demat Account to buy units in Mutual Funds?

hashtagIntro💫

Lumpsum investment vs SIP/DCA

hashtagDefinitions

Insurance

Should I invest in this LIC policy?

Opinions on investing in smart wealth plan by bank?

Up to what age should I take term cover?

hashtagWhat really is term cover?

Do I need my own health insurance? Employer already has group policy

hashtagIntro 🍁

Should I take top-up policy or super top-up?

hashtagIntroduction ✨

hashtagIs my base policy enough? 🤔

hashtagWhat is a deductible? 🌵

hashtagTop-up vs Super Top-up? ☯️

hashtagWhich one do I buy? 💰

hashtagWhy is the premium low for Super top-ups? 🐤

Stocks

The Stock Market Has Crashed. Which Stocks Should I Buy?

hashtagMarket Corrections Aren't Unusual

Foreign Investing

Tax

I don't have any tax to pay. Do I still have to file ITR?

Miscellaneous

Is Gold a good investment now? It has gone up ~50% this year

How To

How To

How to move from one mutual fund platform to another

hashtagIntro👋

How to switch a Mutual Fund from Regular to Direct Plan

hashtagIntro👋

How to rematerialize mutual fund from demat form

hashtagIntro 💫

hashtagSteps to re-materialize 👩🏽‍🏫

STOCKS

Introduction to the Stocks Series

Financial Metrics and Ratios

Work in Progress

Liquidity and Solvency

hashtagDebt to Equity Ratio

EXCEL

Using External Data : Working with CSV Format

hashtagPrelude

Quantifying Returns: CAGR and XIRR

BONDS

Bond Basics

hashtagBasics

hashtagWho issues bonds?

hashtagWhy?

hashtagHistory

hashtagWhat to look for?

hashtagCoupon

hashtagYield

hashtagCredit Quality

MISCELLANEOUS

Miscellaneous

New to Investing

Zero to Investing

Part Two - Defensive Setup

hashtagPart 2A is Health Insurance.

Investment Philosophy and Strategy

Basics of Investment Strategy Plan

hashtagTHE Basic PRINCIPLES:

Various types of Risks in Investments

Are you a Stock or Bond?

Critical Mass

Asset Rebalancing

hashtagMain Ideas:

Insurance

Life

Some FAQs on Life Insurance

Intro💫

Definitions

What really is term cover?

Intro 🍁

Introduction ✨

Is my base policy enough? 🤔

What is a deductible? 🌵

Top-up vs Super Top-up? ☯️

Which one do I buy? 💰

Why is the premium low for Super top-ups? 🐤

Market Corrections Aren't Unusual

Intro👋

Intro👋

Intro 💫

Steps to re-materialize 👩🏽‍🏫

Debt to Equity Ratio

Prelude

Basics

Who issues bonds?

Why?

History

What to look for?

Coupon

Yield

Credit Quality

Part 2A is Health Insurance.

THE Basic PRINCIPLES:

Main Ideas:

1. Short Primer on Life Insurance as a comment:

HouseHolder Insurance

What really is term cover?

Intro👋

Prelude

1. Short Primer on Life Insurance as a comment:

HouseHolder Insurance

Demat Mutual Fund transfer 🚧

Rematerialized mutual fund transfer 💻

Introduction ✨

Is my base policy enough? 🤔

What is a deductible? 🌵

Top-up vs Super Top-up? ☯️

Which one do I buy? 💰

Why is the premium low for Super top-ups? 🐤

Basics

Who issues bonds?

Why?

History

What to look for?

Coupon

Yield

Credit Quality

Intro 💫

Steps to re-materialize 👩🏽‍🏫

Intro💫