# How to Create SIP Interest Calculator in Excel (with Easy Steps)

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

`=XIRR(C5:C17,B5:B17)`

Entering this formula will return the SIP rate value of the investment made in the span of 12 months.

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

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

## Related Articles

<< Go Back to Finance Template | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!
Rubayed Razib Suprov

Rubayed Razib, holding a BSC degree in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a devoted member of the ExcelDemy project. He has contributed significantly by authoring numerous articles and showcasing proficiency in VBA. Razib efficiently automates Excel challenges using VBA macros and actively participates in the ExcelDemy forum, providing valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel,... Read Full Bio

1. 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.