Managing money
can involve calculations to understand the worth of an investment. To
arrive at a result, calculations can be done in a different way or by
using a different formula.
Even the same
formula can be used differently to arrive at a certain result. Here
are a few commonly used money management formulas. Use an excel sheet
to do these.
1. Compound
Interest
I want to take
a loan of Rs 1 lakh to buy a used car. How much will the car cost me
at an annual interest rate of 8 per cent for four years?
The compound
interest formula can be used here to calculate the final cost, which
would include the loan amount and the interest paid. The amount that
is actually paid for Rs 1 lakh is Rs 1,36,048.90. The total amount of
interest charged for borrowing Rs 1 lakh is Rs 36,048.90.
Formula:
Future value = P(1 + R)^N
Type in:
=100000(1+8%)^4 and hit enter. P: amount borrowed; R: rate of
interest; N: time in years.
Also used for:
Calculating the maturity value on lumpsum investment (bank fixed
deposits and National Savings Certificate, for example) over a fixed
period at a certain rate of interest.
2. Compound
Annualised Growth Rate
I had invested
Rs 1 lakh in a 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?
Compound
annualised growth rate (CAGR) will be used here to calculate the
growth over a period of time. 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). However, it should not be construed as 50 per cent average
return over five years.
Formula: CAGR
= {[(M/I)^(1/N)] ? 1} * 100
Type in:
=(((70/20)^(1/5))-1)*100 and hit enter. M: maturity value; I: initial
value; N: time in years. CAGR here is 28.47%.
Also used for:
Calculating the annualised returns on a lumpsum investment in shares.
3. Internal
Rate of Return
I paid Rs
18,572 every year on a money back insurance policy bought 20 years
back. Every fifth year, I received Rs 40,000 back and Rs 4.5 lakh on
maturity. What was my 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 and inflows at the
same regular periods.
In the excel
page type Rs 18,572 as a negative figure (-18572), as it is an
outflow, in the first cell. Paste the same figure till the twentieth
cell.
Then, as every
fifth year has an inflow of Rs 40,000, type in Rs 21,428
(40,000-18,572) in every fifth cell. In the twentieth cell, type in
?18572. In the twenty first cell, type in Rs 4,50,000, which is the
maturity value of the policy.
Then click on
the cell below it and type: = IRR(A1:A21) and hit enter.
5.28% will
show in the cell. This is your internal rate of return.
Also used for:
Calculating returns on insurance endowment policies.
4. XIRR
I bought 500
shares on 1 January 2007 at Rs 220, 100 shares on 10 January at Rs
185 and 50 shares at Rs 165 on 18 May 2008. On 21 June 2008, I sold
off all the 650 shares at Rs 655. What is the return on my
investment?
XIRR is used
to determine the IRR when the outflows and inflows are at different
periods. Calculation is similar to IRR's. Transaction date is
mentioned on the left of the transaction.
In an excel
sheet type out the data from the top most cell as shown here.
Outflows figures are in negative and inflows in positive. In the cell
below with the figure 4,25,750, type out
=XIRR
(B1:B4,A1:A4)*100
Hit enter. The
cell will show 122.95%, the total return on investment.
Also used for:
Calculating MF returns, especially SIP, or that for unit-linked
insurance plans.
5. Post-Tax
Return
My father
wants a bank FD at 10 per cent return for five years. He pays income
tax. What will be the returns?
The post-tax
return has to be calculated here. The idea is to know the final
returns on a fully taxable income. Interest income from the bank is
taxed as per your tax slab.
Formula: ROI ?
(ROI * TR)=Post-tax return
Type in: =10 ?
(10 * 30.9%) and hit enter. You will get 6.91%
ROI: rate of
interest; TR: tax rate (depends on tax slab)
Also used for:
Calculating post-tax returns of national savings certificates,
post-office time deposits, and Senior Citizens' Savings Scheme.
6. Pre-Tax
Yield
My brother
says that the investment in public provident fund (PPF), which gives
8 per cent, is the best. Isn't 8 per cent a low rate of return?
An
investment's pre-tax yield tells us if its return is high or low. The
return on PPF (8 per cent) is tax-free. Also, this has to compared
with returns of a taxable income to estimate its worth. For someone
paying a tax of 30.9 per cent, the pre-tax yield in PPF is 11.57 per
cent. At present, there is no fixed, safe and assured-return option
that has 11.57 per cent return and a post-tax return comparable to
PPF's 8 per cent.
Formula:
Pre-tax yield = ROI / (100-TR)*100
Type in:
=8/(100-30.9)*100 and hit enter. You will get 11.57%. ROI: rate of
interest, TR: tax rate, (depends on tax slab)
Also used for:
Calculating the yield on an Employees' Provident Fund or any other
tax-free instrument.
7. Inflation
My family's
monthly expense is Rs 50,000. At an inflation rate of 5 per cent, how
much will I need 20 years hence with the same expenses?
The required
amount can be calculated using the standard future value formula.
Inflation means that over a period of time, you need more money to
fund the same expense.
Formula:
Required amt.=Present amt. *(1+inflation) ^no. of years
Type in:
=50000*(1+5% or .05)^20 and hit enter. You will get Rs 1,32,664 as
the answer, which is the required amount.
Also used for:
Calculating maturity value on an investment.
8. Purchasing
Power
My family's
monthly expense is Rs 50,000. At an inflation rate of 5 per cent, how
much will be the purchasing value of that amount after 20 years?
Inflation
increases the amount you need to spend to fetch the same article and
in a way reduces the purchasing power of the rupee. Here, Rs 50,000
after 20 years at an inflation of 5 per cent will be able to buy
goods worth Rs 18,844 only.
Formula:
Reduced amt.= Present amt. / (1 + inflation) ^no. of yrs
Type in:
=50000/(1+5%)^20 and hit enter. You will get Rs 18,844, which is the
reduced amount.
9. Real
Rate of Return
My father
wants to make a one-year bank FD at 9 per cent. On maturity, he says,
the capital will be preserved and he would get assured return on it.
It is true
that fixed deposit is safe and gives assured returns. However, after
adjusting for inflation, the real rate of return can be negative.
Formula: Real
rate of return=[(1+ROR)/(1+i)-1]*100
Type in:
=((1+9%)/(1+11%)-1)*100 and hit enter. -1.8% is the real rate of
return. ROR: Rate of return per annum; i: rate of inflation (11 per
cent here).
10. Doubling,
Tripling of Money
I can get 12
per cent return on my equity investments. In how many years can I
double or even triple my money?
Formula: No.
of years to double = 72/expected return
Type in:
=72/12 and hit enter. You will get 6 years. For tripling, type in:
=114/12 and hit enter. You will get 9.5 years. For quadrupling, type
in: =144/12 and hit enter to get 12 years.
or
Rule
of 72: -
Money will be doubled
72/rate of return
Ex:
Rate of return is 8%
72/8=9 (i.e if the rate of interest 8%
Principal amount will be doubled with in 9 years)
Rule
of 112: - Money
will be Triple
112/rate of return
Ex: Rate of
return is 8%
112/8=14 (i.e if the rate of interest 8%
Principal amount will be Tripled with in 14 years)
