Home > Insights > Housing Finance > Complete Guide on How to Calculate SIP in Excel

Complete Guide on How to Calculate SIP in Excel

Housing Finance Guide 29-11-2022 | 5 mins read

Looking for a way to invest in mutual funds or any monthly investment option? Want unique features like Rupee Cost Averaging and a reduction in risks? Want to know about something that compares to lump sum investing in mutual funds or stocks? SIP Calculator will help you to calculate the returns from mutual fund investments.

In this article, we learn about mutual funds SIP, benefits and ways to calculate SIP in excel.

What are Mutual Funds SIP?

You can choose SIP to invest regularly. One approach to investing regularly in mutual funds(MF) or other options is through a systematic investment plan or SIP. For brevity and clarity, this essay will treat SIPs as mutual fund investments. You can use the SIP method to invest in stocks and shares of corporations.

As such, let’s examine the value of SIP and why you should begin using it. Let’s look at some numbers before we get into the meat of the case for SIP investing.

The total monthly SIP receipts were INR 43.921 crores in 2016–17 and are projected to reach INR 1,00,800 crore by the end of the upcoming fiscal year (2021–22). That’s an increase of almost 100% in less than five years.

Additionally, as of January 2022, there were 5.05 billion SIP accounts in India used for investing in mutual funds.

So, why do Indians seem so keen on SIP mutual funds? This is because of the benefits of SIPs over traditional investing methods, such as rupee cost averaging and lower overall risk compared to making a single large investment in a stock or mutual fund all at once.

Benefits of SIP:

Rupee Cost Averaging:

This concept is equivalent to dollar cost averaging, but let’s consider it in Indian rupees for now. Since a SIP invests in MF on a predetermined schedule, it allows you to buy fewer units of mutual funds when the market is doing well (and thus when their prices are high) and more units of mutual funds when the market is doing poorly (and (price goes down).

Lowering risk:

Your SIPs allow you to invest regularly despite market fluctuations, which helps to maintain a more stable investment value than would be possible with a lump sum investment in which the full amount would be invested all at once following a slight decline in the market. For SIP, this means less danger.

Mental Strength:

Unlike with a lump sum investment, you won’t need a sizable amount of cash to get started with a systematic investment plan. A small percentage of the total cost is paid upfront, but the long-term advantages more than justify the initial investment.

Achieving goals:

“Slow and steady wins the race” is a cliche you’re probably familiar with. It’s not about how fast you go but about how patiently and persistently you finish the race. A SIP is a great way to save money for the future and reach your goals more quickly.

How to calculate SIP in an Excel sheet?

Now you know the difference between a systematic investment plan (SIP) and a net asset value (NAV) in a mutual fund. Let’s now study ways to calculate SIP returns in Excel.

To reiterate, figuring out a SIP and keeping tabs on your investments is a breeze in Excel.

  •  Step 1: I will use a mutual fund I’ve been following for a year to demonstrate how I would track the NAV. The monthly SIP investment amount we’ll use as an example is Rs. 5,000.
  • Step 2: To use the units SIP calculation formula: Units = Amount / NAV, to determine how many units will be issued for each monthly investment. Similarly, we determine the monthly SIP calculated payments.
  • Step 3: We sell our MF units (redeem funds) based on the recent NAV of the mutual fund. The amount earned from selling all units is calculated as follows: Total Amount = Total Units * Most Recent NAV.

When does SIP get matured?

It’s worth noting that you have complete liquidity over your units when you invest in a mutual fund through a SIP. As of yet, we have no established SIP expiration date. You are not required to maintain your SIP investments for a minimum of one year or a maximum of five years. You can trade whenever it suits you.

But, depending on the mutual fund you choose, you might not be able to sell too soon because of fees or minimum holding periods, which we call exit rules or criteria.

The phrase “0.1% charged for units redeemed in less than a year” describes one set of rules for early withdrawal. For example, you buy some units first. Now due to any reason, you sell them within a year. You need to pay 0.1% of the profits after selling them.

Each month’s SIP allocation will have a 1-year duration with its departure conditions. With such exit rules in place, however, you can withdraw the funds within a year for the price of 0.1% of your total profits of a year (if you’re okay with that).

Units of mutual funds are subject to a 5% redemption fee upon request or sale, although this fee is waived if you choose to hold them for above a year.


This article intends to do just that: examine the SIP Calculator in great detail. You should carefully consider whether or not an investment is the best choice for frequent, long-term use. The SIP Calculator will help you determine how much money you should invest in various stocks or mutual funds with a particular sum of money. Before investing, it is also important to be aware of the other side.

Investment Reliable does not offer financial advice, but we provide unbiased information and evaluations on trading, investing, and finance. Users ought to always carry out their research. Also, visit Piramal Finance, which has more in-depth, educational, financial-related articles.

Also Read:

    Get insights on WhatsApp

    Enter WhatsApp number to receive valuable insights on finance and loan offers