To know the rate of return from your investments for a period of more than one year, you need to calculate the compounded annual growth rate (CAGR). Here’s how Step 1 Open a fresh excel sheet and type out the investment amount (PV), the future value (FV) and the duration of the investment (NPER) as shown below. For example, Rs 1 lakh invested 5 years ago is now Rs 6 lakh, what is the rate of return per year? PV -100000
FV 600000
NPER 5
Rate –
Tip: Remember to put a minus sign when you key in the amount invested (PV) or the formula won’t work.
Step 2
Click on the empty cell in front of rate. Go to ‘fx’ function at the top of the screen. Click on it for the ‘Paste Function’ box.
Step 3
In the ‘Paste Function’ box go to ‘All’ in ‘Function category’. Then scroll down in ‘Function name’ till you find ‘Rate’, click on it and then click on OK.
Step 4
Tip: The ‘rate’ box may appear over the typed out text. You can click and drag the box away from here to anywhere on the screen.
You don’t need to type the figures in the slots, just click on the correct cell and that figure will appear in the slot.
For our example, first click in the empty slot of NPER and then click on the cell with 5 in it. Click on the empty slot in front of PV and then click on the cell with -100000. Click on the empty slot in front of FV and then click on the cell with 600000 in it.
Leave the ‘Pmt’ and ‘Type’ slots blank. Click on OK. 43% will appear in front of ‘rate’ in your excel sheet. This is the compounded annual growth rate (CAGR) of return if the money grew at a steady rate each year.
Source: Outlook Money