USEFULL CALCULATIONS

Personal finance is all about managing, investing and saving money. Other than these, one other thing needed is to know the exact value of your money because various external factors can have an impact on the value of your money. For instance, Rs 1,000 now would be worth less five years later.

Another reason is that gains and losses are not simple functions and can change under different circumstances. Therefore, it is important to know how to calculate certain figures to understand the actual worth of an investment.

Sometimes, calculations may be done in a different way or using a different formula. Even the same formula can be used differently to arrive at a certain result.

How to use

These calculations can be done manually also, but doing them in an MS excel sheet is much easier. The first step would be to open an excel sheet. Do remember to type ‘=’ in any box and then type out the formula. Without the ‘=’ sign, the formula will not work. Once you have inserted the values, hit the ‘enter’ key to view the result.

Section I

Maturity value on term deposits

Q:- I want to invest Rs 75,000 in a bank fixed deposit for 10 years at 9 per cent interest per annum. How much will I get on maturity?

When you invest in a fixed deposit for a certain period, the final maturity amount can be calculated using the compound interest formula. The interest earned is a fixed percentage per annum but banks usually compound it every quarter. Therefore, one gets more on quarterly compounding rather than from annual compounding.

Formula: Maturity value = P*(1+R%)^N

* Here, P is the amount invested (Rs 75,000)
* R is the rate of interest charged per annum (9 per cent)
* N is the time duration in years (10 years)

This formula is for annual compounding. But, since banks compound quarterly, the formula becomes:

=P*(1+R/4)^(4*N)

What you need to type is =75000*(1+9%/4)^(4*10)

When you hit enter the value 1,82,639 will show in the cell. This is the maturity value.

Maturity value on recurring deposit

Q:- My daughter is 10 years old. I want to start a recurring deposit in a bank of Rs 2,000 a month. The interest rate is 8 per cent per annum compounded quarterly. How much will I get when she turns 20?

The formula used is:

M =
P [(1+R)n–1] 1-(1+R)-1/3

Formula: Maturity value = P*((1+R)^N-1)/(1-(1+R)^(-1/3))

* P is the amount invested each month (Rs 2,000).
* R is the interest rate (8 per cent). However, as it is compounded quarterly the interest rate would be = 8 / 400 which gives 0.02
* N is the number of quarters over the duration, that is 40 quarters over 120 months.

So, you need to type in =2000*((1+0.02)^40-1)/(1-(1+0.02)^(-1/3))

When you hit enter, you will get Rs 3,67,233 as the maturity value.

Compounded annualised growth rate

Q:- I had invested Rs 1 lakh in mutual fund five years back at an NAV of Rs 20. Now, the NAV is Rs 70. How should I calculate my returns on an annual basis?

The gain of Rs 50 over five years on the initial NAV of Rs 20 is a simple return of 250 per cent (50/20*100). This, however, does not mean 50 per cent average return over five years. The compounded annualised growth rate (CAGR) needs to be calculated here to find out what the growth has been over a period of time.

Formula: Returns = (((M / I)^(1 / N))-1)*100

* Here, M is the maturity value (Rs 70),
* I is the initial value (Rs 20) and
* N is the time duration in years (5 years).

So, you need to type in =(((70/20)^(1/5))-1)*100

When you hit enter, the CAGR comes to 28.47 per cent.

Annualised yield

Q:- My fixed deposit of 10 per cent interest per annum is compounded quarterly. What is its actual yield? What if it has been compounded monthly? What if the deposit is of more than a year?

Here, 10 per cent is the nominal rate of interest. As interest is compounded every quarter, the effective interest amount, and thus the yield, increases. Higher the frequency compounding, more is the yield.

Formula: Yield = ((1+R/N)^n-1)*100

* Here, R is the rate of interest,
* N is the number of compounding periods per year and
* n is the number of quarters in the total period.

So, if compounding is quarterly: Yield = ((1+0.10/4)^4-1)*100= 10.38%

If compounding is monthly: Yield = ((1+0.10/12)^12-1)*100= 10.47%

A deposit of Rs 1 lakh at 10 per cent simple interest per annum would yield Rs 1,10,000. However, because of compounding it yields Rs 1,10,380. A longer period of deposit will enhance the yield.

However, if the deposit is for more than a year, say, for two years, then there will be eight quarters.

Formula: =(((1+R/4)^n-1)*100)/2

=(((1+10%/4)^8-1)*100)/2 = 10.92%

Discounted rate of interest

Q:- My bank is giving 5 per cent per annum interest rate. But, if I make a deposit asking for monthly income, the return is lesser. Why?

Under the monthly, quarterly or half-yearly option the amount received will be lesser than under the annual option because banks use discounted rate of interest for shorter periods of deposit. Here’s how to work it out.

Formula:
R/400 (1+R/1200)2 + (1+R/1200) + 1

* R is the interest rate per annum.

Formula for discounted rate per month: =(R/400)/((1+R/1200)^2+(1+R/1200)+1)

=(5/400)/((1+5/1200)^2+(1+5/1200)+1)= .00415

So, on a deposit of, say, Rs 1 lakh at 5 per cent, instead of receiving Rs 416.66 each month, the discounted income would be Rs 414.93.

Home loan EMI

Q:- I have a home loan of Rs 30 lakh for a period of 20 years at a floating interest rate of 9 per cent. I want to ensure that my bank is charging me the right EMI. How to calculate my EMI?

The loan being on floating interest rate, the EMI would keep changing as rates change. On a given date and on known parameters like rate of interest and the loan amount, you can calculate the EMI.

Formula: EMI =(A*R)*(1+R)^N / ((1+R)^N-1)

* Here, A is the loan amount and
* R is the rate of interest.
* Convert R into monthly rate (= 9%/12 or =9/1200). This will be 0.0075

The EMI using the formula will be:

= (3000000*0.0075)*(1+0.0075)^240 / ((1+0.0075)^240-1)= 26,992

So, your EMI is Rs 26,992.

Internal Rate of Return

Q:- I paid Rs 27,300 every year on a money back insurance policy that I had bought 20 years back. After every five years I received Rs 50,000 back and Rs 7 lakh on maturity. What is my overall rate of return?

The internal rate of return (IRR) has to be calculated here. It is the interest rate accrued on an investment that has outflows as well as inflows at the same regular periods. Follow the steps mentioned below carefully.

In an excel sheet type 27300 as a negative figure (-27300), as it is an outflow, in the first cell as shown on the right. Paste the same figure till the twentieth cell. So, all 20 cells will have -27300 in them.

Now, as every fifth year has an inflow of Rs 50,000, type in 22700 (inflow-outflow or 50000-27300) in every fifth cell. However, in the twentieth cell, type in -27300. In the twenty first cell, type in 700000, which is the Rs 7 lakh maturity value of the policy.

Then click on the empty cell below 700000 and type = IRR(A1:A21) and hit enter. You will get 5.17%, which is the IRR or simply the overall rate of return on your money back insurance policy.

XIRR

Q:- I bought 1,000 shares on 18 May 2006 at a price of Rs 250. On 18 July, I bought another 500 shares of the same company at Rs 160. On 3 April 2007, I bought another 300 shares at Rs 120. On 12 December 2008, I sold off all the 1,800 shares at Rs 444 per share. What will be the return on my investment?

The XIRR function is used to determine the IRR when the outflows and inflows are made at different periods. It is calculated almost in the same fashion as IRR. The dates of the transaction are mentioned on the left side of the outflows and inflows before the calculation is made.

In an excel sheet, click on tools in the toolbar. Go to add-ins, check Analysis ToolPak and click ok. (You may have to re-install MS Office 2000 for this function to work). Type out the data from the top most cell as shown here. Outflow figures are in negative and inflows in positive. In the cell below the figure 800,000, type the formula

=XIRR(B1:B4,A1:A4)*100 and hit enter.

XIRR, or the overall return, will be 70.66%
A B
18 May 2006 -250000
8 July 2006 -80000
03 April 2007 -36000
12 Dec 2007 800000
70.66

Post-tax return

Q:- My father is looking to make a fixed deposit in a bank at 10 per cent annual return for five years. He is an income tax payee. How will his returns be impacted?

The post-tax return has to be calculated here. This formula is used for determining the return on income that is fully taxable. The interest income from a bank deposit is fully taxable as per your tax slab. Hence, the return comes down after factoring in the tax. For example, for someone who pays 30.9 per cent tax, the post-tax return on a bank FD of 10 per cent is 6.91 per cent.

Formula: ROI-(ROI*TR)

* Here, ROI is the rate of interest and TR is the tax rate.
* Hence, post-tax return =10-(10*30.9%)=6.91

So, post-tax yield will be 6.91 per cent.

Pre-tax yield

Q:- My brother says that Public Provident Fund (PPF), which gives 8 per cent return, is the best investment option. But, isn’t 8 per cent a low rate of return?

We need to see the pre-tax yield to understand if an investment’s return is high or low. PPF gives 8 per cent tax-free return. The pre-tax yield should be compared with a taxed instrument to estimate its worth.

Formula: Pre-tax yield =ROI/(100-TR)*100

Using this formula, you need to type in =8/(100-30.9)*100 =11.57%

For someone paying tax at 30.9 per cent, pre-tax yield from PPF would be 11.57 per cent.

In the current scenario, there is no fixed, safe and assured return instrument that could pay 11.57 per cent return and yield a post-tax return comparable to PPF’s 8 per cent.

Inflation

Q:- My family monthly expense is Rs 50,000. At an inflation rate of 5 per cent, how much will I need after 20 years with same expenses?

The required amount can be calculated using the standard future value formula. Prices of goods and services rise every year and over a period of time, you need more money to fund the same expenses.

Formula: = Present amount * (1+inflation rate)^ Number of years

= 50000*(1+5%)^20 = Rs 1,32,665

Purchasing power

Q:- My family’s monthly expense is Rs 50,000. At an inflation rate of 5 per cent, how much will be the purchasing power of this amount after 20 years (or how much will this buy in today’s money terms)?

Due to inflation, over the years, a certain sum of money is able to purchase a lesser amount that it used to in the past. Here, Rs 50,000 after 20 years at an inflation of 5 per cent is able to buy goods that Rs 18,844 can buy today. Here’s how.

Formula: Reduced amount =present amount/(1+inflation rate)^Number of years

= 50000/(1+5%)^20= Rs 18,844

Real Rate of Return

Q:- My father wants to make an FD in a bank at 9 per cent for a period of one year. On maturity, he says, his capital will be preserved and he would be getting assured return. What will be the real rate of return (or inflation-adjusted return) for him?

It is true that a fixed deposit is safe and gives a fixed and assured return. However, after adjusting for inflation, the real rate of return can be negative. Very simply, if the nominal rate of interest on a bank FD is 9 per cent and inflation is 11 per cent, the real rate of return is a negative 2 per cent. A more correct way, however, is to use the formula below.

Formula: Real rate of return =((1+ROR%)/(1+i%)-1)*100

ROR is rate of return per annum (9%) and i is rate of inflation (assuming it as 11%).
=((1+9%)/(1+11%)-1)*100

When you hit enter, you get -1.8%, which is the real rate of return.

Doubling of Money

Q:- I would be able to generate 12 per cent return on my investments in equity. In how many years can I see my investment double or even quadruple?

There is a simple thumb rule to calculate the number of years in which the investment will double. It is known as the rule of 72. To calculate it, simply divide 72 by the rate of return that you can generate.

No. of years= 72/12 = 6.

So, at 12 per cent return, you can double your money in six years.To find out the time needed to quadruple your money, divide 144 by your expected return. Hence, 144 / 12 will give 12 years.