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:

Formula of SIP calculator in Excel

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.

SIP interest Calculator in Excel


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.

SIP Interest Calculator for Even Cash Flow

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

Target value of return for the SIP interest calculator

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

SIP Interest Calculator for Uneven Cash Flow

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

Unformatted cashflow value for the investment

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

Calculate Profit of a Mutual Fund Using SIP calculatior

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

Amount of profit calculated from the SIP calculator from an Investment


Download Practice Workbook

Download this practice workbook below.


Related Articles


<< Go Back to Finance Template | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!
Rubayed Razib Suprov
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

2 Comments
  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.

    Please clarify or correct. Thanks!

  2. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jan 26, 2023 at 2:03 PM

    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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo