Suppose a bank offers to pay you Rs.4500 every year for 3 years if you deposit Rs.10,000 today. So what is your return?
This is calculated by way of Internal Rate of Return (IRR). IRR is defined as the rate at which the net present value of a series of cash flows is zero i.e. you reduce all future cash flows to their present value at a rate such that net inflows match with the outflows.
It is very easy to calculate IRR by using the IRR formula in MS EXCEL. We have to just type the outflows and inflows in a column and put the IRR formula at the end i.e. = IRR(cashflow, guess) as shown in the excel picture below:
This is calculated by way of Internal Rate of Return (IRR). IRR is defined as the rate at which the net present value of a series of cash flows is zero i.e. you reduce all future cash flows to their present value at a rate such that net inflows match with the outflows.
It is very easy to calculate IRR by using the IRR formula in MS EXCEL. We have to just type the outflows and inflows in a column and put the IRR formula at the end i.e. = IRR(cashflow, guess) as shown in the excel picture below:
(When the dates are uneven, you will have to use the modified formula called XIRR, as shown in the above excel picture.)
Thus this IRR formula is very important as it enables you to calculate your effective returns or effective cost for any given investment scheme or loan. Write down all the numbers in the form of cash inflows and outflows and apply the IRR formula.
For example, you may find a bank offering you fixed deposit at 10% p.a. payable annually. At the same time, the bank is offering a loan at 8% p.a. “flat” rate of interest with monthly EMIs. It is but logical that something is wrong here. How can bank pay 10%, if it earns only 8%? Surely bank is not a fool to do such a thing. The jugglery lies in the way the two interests are calculated. See blog post Beware!! 8%=14.68%.
To calculate effective cost, put loan received as 200000 for month zero and EMI payout as -9666.67 from Month 1 to 24 and apply the IRR formula (multiply this by 12 because the periods here are in months). Lo and behold, you get the true cost i.e. 14.68%.
So in reality, on a comparable basis, a person is paying 14.68%, but will earn only 10%. This 4.68% is the bank’s profit.