How do you calculate SIP returns in Microsoft Excel or Google Sheets?

In Excel, use the future value function: =FV(rate/12, tenure*12, -monthly_investment, 0, 1). Note that the last argument (1) signifies payment at the beginning of each period (annuity due). For irregular cash flows, use the =XIRR() function.

Why is CAGR inadequate for measuring SIP performance?

CAGR assumes a single lump sum invested at time zero. Because a SIP involves multiple staggered cash flows at 60, 120, or 240 different dates, XIRR (Extended Internal Rate of Return) is required to calculate the true internal rate of return for each individual cash flow.

Mathematical Tutorial

How to Calculate SIP Returns: Formula & Excel Tutorial

Ever wondered how online calculators and financial software compute the maturity corpus of a Systematic Investment Plan? Here is the complete mathematical breakdown.

The Future Value (FV) Annuity Formula

Because monthly SIP contributions are made at the beginning of each monthly cycle, finance mathematics treats a SIP as an Annuity Due:

Annuity Due Formula

FV = P × [((1 + r)ⁿ − 1) / r] × (1 + r)

Variable Definitions

FV: The total future maturity value at the end of the investment tenure.
P: The monthly investment amount deposited every month.
r: Periodic monthly rate of return = Annual Return Rate ÷ 12 ÷ 100. (For a 12% expected return, r = 12 / 12 / 100 = 0.01).
n: Total number of monthly installments = Tenure in Years × 12. (For 10 years, n = 120).

Step-by-Step Calculation Walkthrough

Let us calculate the maturity value for ₹10,000/month invested for 1 year (12 months) at an expected return of 12% per year:

Monthly rate: r = 12 / 1200 = 0.01
Number of months: n = 12
Compounding factor: (1 + 0.01)¹² = 1.126825
Subtract one: 1.126825 − 1 = 0.126825
Divide by monthly rate r: 0.126825 / 0.01 = 12.6825
Multiply by P: 10,000 × 12.6825 = ₹1,26,825
Multiply by (1 + r): 1,26,825 × 1.01 = ₹1,28,093

Total invested: ₹1,20,000. Maturity value: ₹1,28,093. Profit: ₹8,093.

How to Calculate in Microsoft Excel / Google Sheets

Excel Formula:

=FV(12%/12, 120, -10000, 0, 1)

Where 12%/12 is monthly rate, 120 is months, -10000 is payment, 0 is PV, and 1 signifies deposit at beginning of month.

Test Our Instant SIP Calculator →

Frequently Asked Questions

The formula is the Future Value of an Annuity Due: FV = P × [((1 + r)^n − 1) / r] × (1 + r), where P is the periodic monthly deposit, r is the periodic monthly rate (annual return / 12 / 100), and n is the total number of monthly payments (Years × 12).