Financial Mathematics -Calculating rate of returns before investment - Let's learn how

It's a common practice in financial industry that products are sold by projecting maturity value. Reason is human mind always looks at maturity value in today's term, but forget to measure impact of inflation and rise in expenses due to standard of living. We just get obsessed with the higher absolute value and forget the actual return it generates. Instead of focusing on maturity value if we start concentrating on returns we can take much better decision. Illustrating how just looking at maturity value can make our plans a haywire and that too when we actually in need of it.

One of my friend Vivek bought an Life Insurance policy for his son's education in the year 1999 for an 18 year term on becoming a proud father. The TOTAL cost for a good engineering course was almost Rs.40000 at that time.Let me remind you during late 90's a person at senior executive level used to get salary in range of Rs.20000 to 40000 pm. Household exps. for a family of 4 for an average middle class person was in the range of Rs.4000 to Rs.5000 pm. 

With an intention that his son should not compromise during his engineering studies he planned for an maturity of Rs.1 Lac, for which was required to pay premium of Rs.3000 equal to his one month Exp. or 75% of his monthly salary.

Inspite of planning for double the amount required. When his son will reach in STD 12th i.e in the year 2017, I doubt whether he would be able to pay even the tuition and entrance examination fees for engineering courses with maturity of above policy.

Biggest reason was he thought everything in 1999's context and he thought by paying Rs.3000 which was a big amount for him at that point in time he has secured his child's education.  As against that if he would have calculated the returns he might have understood its meager 7% per annum.

Let's learn how to calculate returns so that we do not face such problems.

Open an excel sheet and go on "Insert" select 'Function' under category select "Financial" and in window search for function type "Rate". The following box will appear after clicking OK.

Inputs ;

NPER is no. of period - In Vivek's case NPER is 18 years i.e tenure of payment. In case frequency of payment is Monthly multiply it by 12,in case of Quarterly by 4 and for Half yearly by 2.

PMT Payment - periodic payments or regular payment e.g Insurance Premium, EMI Installment, Monthly Post recurring Amt. etc.In case of an FD (Its Only 1 time Payment) so leave this filed blank.
In case of Vivek it is Rs.-3000.00. In case we are making payment that should be shown as (-) and receiving money should be shown as (+) or nothing.

PV - This field should be used in case of single or one time payment like for FD,Bonds, MF investment etc.In our case as it is periodic investment we will keep it blank.

FV - This is the desired corpus or the maturity amount product offers or required.As funds are flowing in we need not put any sign before it. 

Type - Applicable only in case of periodic or regular payment. In case you are paying in advance then you should put 1 and in case of end put 0.In our example it is 1 as we pay premium in advance.

Guess - It is not visible in above picture but in excel sheet when you will scroll down you will find it, no need to put anything in it. But in case any error comes then you should put approx. return which you think it would give. Most of the time you do not require to provide anything in this field.

Let's put above data and find the result which would be as follows :

You can see the Formula result as 7% at the bottom. In case frequency is taken as Half Yearly you need to multiply result with 2, for Quarterly by 4 and in case of Monthly by 12.

In this way you can calculate Interest on EMI and returns on FD, SIP, Insurance Policy etc...

While planning do not forget to consider effect of inflation and rise in exps. due to standard of leaving.