How do we calculate Returns / CAGR in Portfolio ?

How do we calculate Returns / CAGR in Portfolio ?

Mathematically,

XIRR is that single rate of return, which when applied to every installment (and redemptions if any) would give the current value of the total investment.

XIRR is your personal rate of return. It is your actual return on investments.

XIRR stands for Extended Internal Rate of Return is a method used to calculate returns on investments where there are multiple transactions happening at different times.

Mutual Fund investments are not as evenly spaced as you saw above in case of mutual funds. In the case of mutual funds, you tend to invest and redeem investments at irregular intervals.

It will cause cash inflows and cash outflows at different points in time. In this type of case, in addition to the invested amount, the time of such investment also assumes significance to yield a certain outcome.

Here you may use the concept of Extended Internal Rate of Return (XIRR).

So, XIRR is a good function to calculate returns when your cash flows (investments or redemption) is spread over a period of time.

Calculation

XIRR can be easily calculated using Microsoft Excel. Excel provides an inbuilt function to calculate XIRR.

XIRR is a more powerful function in excel for calculating the annualized yield for a schedule of cash flows occurring at irregular periods.

XIRR formula in excel is:= XIRR (value, dates, guess)

Step by Step Process to Calculate in Excel

  1. Enter all your transactions in one column. All outflows like investments, purchases will be market negative while all inflows like redemption’s while are marked positive.
  2. In the next column add the corresponding date of the transaction
  3. In the last row mention the current value of your holding and the current date
  4. Now Use XIRR function in excel which is something like this =XIRR (values, date, Guess)
  5. Select values to a series of cash flows that corresponds to a schedule of payments in dates and date columns stand for the date when the first investment was made and when the cash flows were received, guess parameter is optional ( if you do not put any value Excel use a value of 0.1.

Example of How to Use the Function in Excel :

For this calculation you need is with an example of six-month SIP. Let

SIP amount = ₹ 5000

SIP investment dates = start-01/01/2017, end-01/06/2017

Redemption date = 01/07/2017

Maturity amount = ₹ 31000

Assume we have a set of cash flows like those in the table below :

01-01-2017

-5000

03-02-2017

-5000

01-03-2017

-5000

11-04-2017

-5000

01-05-2017

-5000

25-06-2017

-5000

01-07-2017

31000

 

11.92429



In the above table, the cash flows are occurring at irregular intervals. Here, you can use XIRR function to compute the return for these cash flows. Remember to include the ‘minus’ sign whenever you invest money.

Open an excel sheet and follow these steps :

  • In column A, enter the transaction dates on the left side.
  • In column B, enter SIP figure of 5000 as a negative figure as it’s an outflow cashflow.
  • Against the redemption date (Column A), enter the redemption amount (Column B) (31000).
  • In the box below 31000, type in: “ =XIRR (B1: B7, A1: A7)*100 ” and hit enter
XIRR value of 11.92 % will be display as a result.

So XIRR makes this simpler by calculating one return for your investments. So, if you are looking to calculate returns on your mutual fund investments XIRR is the right way to go.

    • Related Articles

    • Why to upgrade to FinnSys 360 - Plan & benefits !

      As you know that software and technology is an ever evolving process, wherein we all need to keep upgrading. Under a normal software plan the basic issue or dispute between the customer and the vendor comes at the point of payments /charges. A vendor ...

    • How to assign editor role to Finnsys in your WIX panel ?

      You need to invite Finnsys to Collaborate on Your website if it is being made on WIX . Follow the steps below to Invite our Team to collaborate and assign them permissions to design and edit your WIX Website To invite Finnsys : Go to Roles & ...

    • How to get BSE Star MF API for integration in Finnsys ?

      MFD Model (For ARN Holders/IFAs) SEBI vide its circular CIR /MRD/DSA/32/2013 dated 4th October, 2013 has allowed Mutual Fund Distributors to use re-cognized stock exchanges' infrastructure to purchase and redeem mutual fund units directly from Mutual ...

    • How to create and use Google reCAPTCHA ?

      Prevent Bots from spamming your query or signing up forms with fake emails. Are you getting hit with notifications of spammy accounts being created on your website? Did you know that malicious computer programs called “spambots” search the internet ...

    • How to buy a Business E mail ?

      What is the importance of Business E mail ? If you use the free E mal service within your FinnSys back end, then you may get delayed delivery for your bulk E mail jobs, like - Broadcast, Reminder mailings, evaluation mailings etc Hence we recommend ...