Prajna Capital - An Investment Guide

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.

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.