## 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**

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