December 25, 2011

How to calculate returns on your investments?

Today I will show you how to calculate returns from your investments and will make you familiar with few financial terminologies, which usually used in calculating returns.

1) Absolute Return: This is mainly used when the investment period is less than one year. To calculate this, simple formula is:-

Absolute Return= [Dividends (Receivables) ± (PE-PB)]/PE

Here PE-Price at the beginning of investments

PB-Price at the end of investments

2) CAGR-This is mainly used calculating your returns when holding period of your investment is more than one year and either you are investing lump sum or investing equal amount and frequency of investment too is equal (either Monthly, Qly, Hly or Yly). For example Rs.1,000 investment either one time, monthly, quarterly, half yearly or yearly. This is called Compounded Annual Growth Rate.  This is the same compound interest calculation, which you and I did during our school days. We can calculate the same using Excel. I will show you how to do this.

  1. A)     Open Microsoft Excel
  2. B)      Go to Formulas
  3. C)      Click on “fx” or Insert Function
  4. D)     Popp Up will appear, in that select category “Financial”
  5. E)      From drop down Menu select the option “Rate”
  6. F)      “NPER” means Total number of Payments. It should be of same frequency means, suppose your payment is monthly for around 15 Yrs then it should be 180 not 15. Suppose your investment is quarterly for 15 years then it should be 60 (15*4).
  7. G)     “PMT” means what you are going to pay for each period of investments
  8. H)     “PV” means present value i.e. if you have any investments already, then you need to insert that value (current value) here.
  9. I)        “FV” means Future Value of your investment which you can assume or you can calculate by inserting required rate
  10. J)       “TYPE” means- is your payment will be at the beginning of the period or end. Suppose you are investing monthly and if your investment is on the beginning of the month then insert “1” and if it is at the end of the month then”0”

This is very important in calculating, because if you are investing in the beginning of each month interest calculation will start from the beginning only.

  1. K)      “Guess” will give you the result from your investments.

With this function you can calculate any desired value by putting at least three values. Also remember that whatever going out of your pocket will be “-“value and whatever you will receive will be “+”. Remember that, your all values like Payment, Period and Interest should be in same frequency. Means if your contribution is monthly then period and interest should be in monthly.

3) IRR- It is used to calculate your returns when your contributions are not same but with equal intervals and you may be receiving lump sum in between. Suppose you are investing First month Rs.20,000 and next month Rs.25,000 and you are receiving Rs.2,00,000 in fourth month. For such type of transactions, you can use IRR function but period must be equal. It can be calculated again by selecting function and IRR. Remember to put outgoing as “-“and receivable as “+”. Here if the investment periods are beginning of the period then directly you can enter the amount. But your payment or receivable at the end of period then you need to enter into next year. Means if you are not investing at the beginning of the month then your first value should be “0” and second value must be your first month contribution. Logic is, it will consider the period from the beginning, suppose your investment is not in the beginning then you need to put “0” and need to put your value to next month.

4) XIRR-This is used when your payments or withdrawals and frequency are irregular. It is same as IRR but only difference is you need to key the dates of transactions. For Example-You are investing on 15th March 2011 Rs.30,000, on 24th April 2011 Rs.28,000, on 30th June 2011 Rs.45,000 and receiving Rs.1,30,000 on 25th Dec 2011.

I hope this will make you familiar with few of the Terminologies, which relates to returns and make you to calculate your returns.

2 Comments

  1. hi can you help creating an xl sheet to calculate irr or xirr for a mutual fund investment where the investments vary every month for a period of 5 years

    Reply
    • Naresh-I refer you “Free personal Finance Calculators” where you will find lot of calculators than what you expected, that too at free of cost. Hope you enjoy 🙂

      Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

eleven − eleven =

Share This