Components of Calculating SIP of a Mutual Fund
The basic target of calculating the SIP of a Mutual Fund is to get the final value of the investment. Based on this value investors will going to decide whether they should invest in this venture or not.
Formula:
The main components are:
P =Investment Amount
i = Rate of Return(Compounded)
n = Investment Time
r = Desired rate of return
2 Suitable Examples of Creating SIP Interest Calculator in Excel
For demonstration, we will use the below dataset. We’ve used the Microsoft 365 version, but these methods should work in most newer Excel versions.
1. SIP Interest Calculator for Even Cash Flow
Steps
- In the below dataset, put the input data for cash flow that is given as installation.
- All of those values are actually negative signs because in all those cases, money is actually getting out of the system, instead of coming in.
- Now we need to enter the return that the investor is going to receive at the end of the investment period. Let’s say the return is $10,750.
- Select the cell E5 and enter the following formula:
=XIRR(C5:C17,B5:B17)
Entering this formula will return the SIP rate value of the investment made in the span of 12 months.
Read More: How to Develop CD Interest Calculator in Excel
Method 2 – SIP Interest Calculator for Uneven Cash Flow
Steps
- Fill in the dataset with erratic installations, some of which are positive and the others being negative.
- For negative values, money is actually getting out of the system instead of coming in.
- Now we need to enter the return that the investor is going to receive at the end of the investment period. Let’s say the return is $10,750.
- Select the cell E5 and enter the following formula:
=XIRR(C5:C17,B5:B17)
Entering this formula will return the SIP rate value of the investment made in the span of 12 months.
- Go to Home tab, select the Number group, and click on Percentage.
- The range of cells is now in Percentages format.
Read More: How to Generate Overdraft Interest Calculator in Excel
How to Calculate Profit of a Mutual Fund Using SIP
Steps
- Enter the rate of SIP investment each month and the amount of money that is going to be inserted.
Then we need to evaluate the rate of the Net Asset Value.
- Select the cell E6 and enter the below formula:
=(E5*C6/12/100)+E5
Entering this formula will calculate the rate value of the Net Asset Value of the month.
- Drag the Fill Handle down to cell E17. This will fill the range of cells E5:E17 with the Net Asset Value for each month.
- Select cell F5 and enter the following formula:
=D5/E5
Doing this will calculate the Units for the month of January.
- Drag the Fill Handle down to cell F17. This will fill the range of cell F5:F17.
We will also estimate the amount of money that is actually deposited by the investors over the course of 12 months.
- Enter the following formula in cell I5.
=SUM(D5:D16)
- Select cell J5 and enter the following formula:
=SUM(F5:F16)
- To get the total amount of output value, enter the following value in K5:
=J5*E17
- We also need to calculate the Profit value of the investment. Select cell L5 and enter the following formula:
=K5-I5
Download Practice Workbook
Download this practice workbook below.
Related Articles
- Create a Daily Loan Interest Calculator in Excel
- How to Create a Daily Compound Interest Calculator
- Create Reverse Compound Interest Calculator in Excel
- Make Quarterly Compound Interest Calculator in Excel
- Bank Interest Calculator in Excel Sheet – Download Free Template
<< Go Back to Finance Template | Excel Templates
Get FREE Advanced Excel Exercises with Solutions!
Referring to: SIP Interest Calculator for Even Cash Flow
I entered the same figures in Excel and I did not get the same results of 12% return that you are showing.
If you invest $1,000 per month for 12 months (i.e. total invested = $12,000) and your ending value is only $10,750 then you have actually a loss of 18.6% (or a return of -18.6%) per Excel.
Please clarify or correct. Thanks!
Hello Raj
Thank you for reaching out with your comment. You encountered a different result than what was described in the post. I assume that you missed inserting positive numbers in the range C16:C17. However, once those values were included, I discovered a result identical to what is described in the post. Therefore, It’s essential to ensure all intended data is inputted correctly.
Regards
Lutfor Rahman Shimanto