Money Excel - Personal Finance Blog

Menu
MENUMENU
MENUMENU
  • Home
  • Business Ideas
    • 225 Small Business Ideas
    • 40 Online Business Ideas
    • 20 Business Ideas for Beginners
    • 20 small manufacturing business
    • 30 Food Business Ideas
    • 100 Business Ideas low investment
  • Stock Market
    • Best Demat & Trading Accounts 2020
    • Best Stocks 2020 by Experts
    • Best Virtual Trading Apps
    • Best Stock Screener
    • Best Discount Brokers in India
    • Multibagger Stocks of LockDown
    • Rakesh Jhunjhunwala Portfolio
  • Mutual Funds
    • Best Mutual Funds 2020-21
    • Best Direct Mutual Fund Platform
    • Best Mutual Fund Mobile Apps
  • Incometax
    • Income Tax Slab FY 2020-21
    • Income Tax Calculator FY 2020-21
    • Income Tax Refund Request
    • Pay Advance Tax Online
    • Download New 15G -15 H Forms
  • Investment
    • Best Investment Ideas 2020
    • Long Term Investment Options
    • Best Investment Regular Income
    • Best Investment Options NRI
  • Credit Card
    • Best Credit Cards 2020-21
    • Best credit card for online shopping
    • Best Forex Card in India
    • Best Business Credit Cards
    • Credit Cards against Fixed Deposit
  • Insurance
    • Best Term Insurance Plans 2020
    • Best Health Insurance 2020
    • Best LIC Policy 2020
    • Best Car Insurance Companies
    • Best Mobile Insurance Companies
  • Downloads
Home
Investment
Calculating Returns of Investment – Formulas & Examples – 2

Calculating Returns of Investment – Formulas & Examples – 2

Raviraj Parekh August 2, 2020

Return Calculation

We have discussed about absolute returns, simple annualized returns & compounded annual growth rate in pervious article Calculating Returns of Investment – Formulas & Examples – 1. Return calculations looked so fare in previous article was done using value of investment at beginning and at the end of period. But it does not tell actual story in terms of comparison with other investment option. So we are herewith article explaining Rolling returns, relative returns, peer returns, IRR and XIRR.

Rolling Returns:-

Suppose someone has purchased two mutual funds, A and B, both purchased with NAV of 12 and both sold seven years later at NAV of 32 as shown in figure below.

Rolling Return

Do not miss below posts -
  • Saving for Child education is not child’s play
  • Investment Options of Middle Class – Worth for investment?
  • NPS for NRI – Can NRI invest in NPS?

Absolute Returns and CAGR in this case is same

  • Point-to-point returns: Both have generated an absolute return of 100* (32-12)/32 =166.67%.
  • CAGR :CAGR calculation would also yield identical results, that is, 10.41%

Although CAGR is same you can see their respective performance-A is consistently rising whereas B, after showing a robust growth for the first four years, declines consistently after 2009. Clearly, A is a better choice, which you’d never know by relying on the point-to-point/CAGR return measures.

Rolling returns is nothing but dividing time frames in equal but small part and taking average of small period returns. This gives information about performance of asset class over time period.

We have come across HDFCFund: Sensex rolling return calculator this calculator can calculate rolling returns of Sensex from any date between 12 Nov 1997 to 12 Nov 2012.

But rolling period statistics have their limitations, too. By their very nature, they tend to put more weight on returns in the middle of the measurement period and less on those at either end.

Relative Return:-

Relative returns enable us to know the true return earned by the fund over and above the benchmark. It determines how the return of a given stock or fund compares to that of benchmark. This can be useful in making investment decisions. For example, if the stock you are holding achieves a return of 20 per cent, while the benchmark index say Nifty managed 15.58 per cent, then the stock has achieved a relative return of a +4.42 per cent. A stock that falls less than the benchmark in a falling market is considered to have done well, as it manages to contain losses for the investor.

Peer Returns:-

Suppose you invest in State Bank of India (SBI) and the stock increases in a year by 7%. Other banking stocks rise like HDFC Bank by 9% ICICI Bank by 6.5%, Punjab National Bank (PNB) by 5% and Banking Index by 6%. It means SBI has given better returns as compared to Banking Index and among its peers better than PNB but less than HDFC Bank and ICICI bank. Peer return helps in selection of investment within a particular sector or sub-group of an asset class such as banking stock in above example.

So far we focused on investing only one time. But How do you calculate your returns when you invest some amount every year/or at some time period and at the end you receive your money back or in between you get some dividend? That’s where you required IRR & XIRR.

Do not miss below posts -
  • What are Chit Funds? How do Chit Funds Work?
  • Suppandi Making Investing Simple – Video Series
  • Top Rated Hybrid Funds to Invest in India

Internal rate of Return(IRR):-

Suppose your invest 3,000 , 6,000 , 5,000 , 4,000 and 6,500 in 5 yrs and Get 36,000 at the end of 5 yrs , what is your Return ? Or you invested Rs 10,000 in the stock which gave 10% annual dividends. At end of 5 years you sell stock for 12,000 Rs. The return that is used in such cases is IRR or Internal Rate of Return.

IRR is Internal Rate of Return and it is used to calculate the returns given some amount at a fixed interval i.e. after every 3 months or after every 1 yr. The only thing which matters is that there should be equal distance between two installments.

IRR Calculation:-

Let’s try to find IRR in an Excel sheet, first enter the original amount invested. The amount invested should be represented by a ‘minus’ sign. In each cell enter the cash flows which received each year. Remember to include the ‘minus’ sign whenever you invest money. Now find out IRR by mentioning =IRR(values,[guess])

IRR

IRR for investment in this case is equal to 14% as shown in picture above.

External Internal Rate of Return (XIRR):-

If you make an investment in the beginning of the investment period and you receive cash flows that are ‘not’ necessarily periodic such as dividends offered on mutual fund units. In this case how would you calculate the return on your investment?  You can calculate returns by means of using powerful function XIRR of Excel.

XIRR Calculation:-

Suppose you invest 50,000 Rs in mutual funds and you get 500 Rs dividend at different interval and you sold these mutual funds at value of 51,000 Rs. Let’s try to find XIRR of this example using Excel.

In an Excel sheet, first enter the original amount invested. The amount invested should be represented by a ‘minus’ sign. In each cell enter the cash flows which received each period. Remember to include the ‘minus’ sign whenever you invest money. Now find out XIRR by mentioning =XIRR(values, dates,[guess])

XIRR

XIRR for investment in this case is equal to 4.89% as shown in picture above.

I hope I have done my job by explaining various ways to calculate returns. So next time before doing any investments please remember what you read here and apply your wisdom to calculate returns. Be smart investor, & take investment decision based on calculation and not assumptions.

Comments ? I would love to hear if these concepts mention here are of any use to you.

Prev Article
Next Article
Tags:formulas investment irr peer returns period relative returns return return calculation returns Rolling Returns stock xirr

About The Author

Raviraj Parekh

Raviraj is the man behind moneyexcel.com. He is PGDBA, engaged in blogging for 10 years. Moneyexcel blog is ranked as one of the Top 10 Personal Finance Blog in India. He is not affiliated with any financial product, service provider, agent or broker. The purpose of this blog is to spread financial awareness and help people in achieving excellence for money. Please note that the views expressed on this Blog/Comments are clarifications meant for reference and guidance of the readers to explore further on the topics. These should not be construed as investment advice or legal opinion.

One Response

  1. Peter

    Would this work if you considered the cash flows as a re-investment by purchasing additional shares in a mutual fund? So you would have a base investment with any dividends purchase new shares (cash flow) and ending value is the market current value? What about if you sold shares during the period ? Is that dividend ?
    Thanks !

    March 19, 2013

Leave a Reply

Cancel reply

IIFL Demat Account

Search

Top Posts & Pages

  • Rakesh Jhunjhunwala Portfolio Holdings - January 2021
  • Income Tax Calculator FY 2020-21 (AY 2021-22) – Excel Download
  • Cash Deposit Machine Locator – How to find CDM Near You?
  • How to check Property Records and Land Records online in India?
  • 20 small manufacturing business ideas with low cost
  • 225 Small Business Ideas with Low Investment in 2021-22
  • LIC Premium Paid Certificate Download For Income Tax Purpose - Online
  • 10 ways to Convert Black Money to White
  • Top 60 Manufacturing Business Ideas
  • 40 Small Business Ideas with low investment

Subscribe to Blog via Email

Join 5,474 other subscribers

Follow MoneyExcel

Money Excel – Personal Finance Blog

Personal Finance Blog
Copyright © 2021 Money Excel - Personal Finance Blog
| About |Downloads | Contact Us | Sitemap | Disclaimer | Privacy Policy |