How To Calculate Returns of Mutual Fund SIPs Using MS Excel

The full form of a SIP is a systematic investment plan. It helps you invest small amounts regularly at fixed intervals. This makes it very easy for people to invest. Just decide on the right amount from your salary and keep it aside. It is very good for long-term investments. You can decide the time. It could be a week, a month, or a quarter of a year. We can use the mutual fund SIP calculator for complex calculations.

How to calculate SIP using MS Excel?

Before this, let’s understand the term “NAV.” Its full form is “net asset value.” It means the cost of a single unit in a mutual fund. For example, the cost of one pen is 10 rupees. The cost of three pens is 30 rupees. So the cost of a single unit is NAV. It can vary with mutual funds. It is very important to understand NAV before investing.

Mutual Fund SIP Calculator

The following is the process for calculating SIP returns in MS Excel:

1. Decide on the fixed SIP amount.
2. Decide on the period.
3. Note down the NAV values for that period.
4. Calculate the number of units.
5. Calculate the total amount based on units.

Detailed explanation and formula:

1. Take three columns in Excel.
2. In the first one, mention the time intervals.
3. Write NAV values in the second one.
4. Write the decided value of SIP in the third one.
5. Use the following formula to calculate the number of units:

Formula: (no. Of units)=Amount/NAV.

6. Add all the units of every month.
7. Note its value.
8. Calculate the total amount using the formula:

Formula (Total amount)=Total no. of units × Latest NAV values.

9. The last step is to calculate the profit earned.
10. The formula used to calculate the profit earned is

Formula (profit earned)= Maturity amount – Total amount.

Now, let’s understand the maturity amount:

It simply means “future value.” It is the amount to be received based on the time interval. Your funds are in your hands. You decide to keep or sell them. You can sell them any time you want. However, there are exit criteria linked to the sale of some funds. Some funds cannot be sold before the deadline. You will also get charged under the exit criteria. It’s almost 0.1% of the amount. This is a negligible amount of your profits. You can make decisions related to time. Or according to your comfort. Doing some research before buying and selling is a good option.

How to calculate the maturity amount or future value (in MS Excel):

We can use formulas in Excel to calculate the maturity amount. This is a very easy process.

For calculations, we don’t have NAV values for the future. It is not possible to have exact NAV values for the future. But we calculate the expected NAV values. This step is needed for further processing. This value is termed “yearly expected returns.” Steps to calculate

1. Open a spreadsheet in MS Excel.
2. Write the number of months in one column.
3. Mention expected yearly returns in the second column.
4. Write the value of NAV for the first month.
5. Keep track of the expected annual returns.
6. Calculate the next month’s NAV value using the yearly expected return.
7. Calculate the current month’s NAV value.
8. The formula to calculate the current month’s NAV value is [(Yearly expected return / 12 × NAV of last month / 100) + NAV value for the previous month] 
9. Use the above-given formula in Excel.
10. All the formulas in Excel are written in the formula tab only.
11. It is user-friendly software.
12. Using the above formula, calculate all the NAV values for the remaining months.
13. Calculate all the NAV values for the remaining months using the above formula.
14. The formula to calculate the number of units is: Number of units = Amount / NAV 
15. Add units for each month.
16. Note the sum of the units.
17. After getting all the values above, we need to calculate the maturity amount.
18. The formula used to calculate the maturity amount (mutual fund SIP calculator) is: Formula (Maturity Amount) = Total number of units × Latest values of NAV 
19. Hence, we get the maturity amount.
20. Now, the profits for the year can be easily calculated.
21. The formula to calculate the profit is:Maturity amount (Future Value) – Total amount which was investedBy following the above steps, we can easily figure out the returns of a mutual fund SIP with MS Excel.

Benefits of SIP

1. Little investment: The SIP requires very little investment. It is there for long-term investments. This makes it open for everybody. The user is free to decide the time interval as well. It has a user-friendly interface. People can invest a small amount of their monthly salary. This can benefit them in multiple ways. It can even open new doors for them. This introduces people to a new world of finance. A mutual fund SIP calculator can be very helpful for this. It helps to calculate the correct amount to be invested. 
2. Simple to use: Calculating the returns of mutual funds SIP is very simple. There is no need for a financial background. Anyone with basic knowledge of MS Excel can use it to calculate the returns on SIPs. All of the steps for calculating mutual fund SIP returns using MS Excel are detailed above in the blog.
3. Enables Cost Averaging: One of the most important benefits of SIP is that it enables cost averaging. You can buy units based on the NAV value. If the NAV value is high, buy less. If the NAV value is less, buy more. This helps in proper planning. It is also useful because you can make changes. Everything here is in your own hands. 
4. Achieving goals: It is very useful for people who have knowledge but don’t have a lump sum to invest. They can invest small amounts at regular intervals. This adds up to a great profit. Good investments can surely help in achieving long-term goals.

Conclusion

In the end, investing is useful. The returns of mutual fund investments can be easily calculated. This is done in MS Excel. It is an easy process. It can motivate the youth to learn, grow, and invest. For more information, visit Piramal Finance.