For example, if the beginning value of your portfolio was $100,000 and your ending value was $105,000, your simple rate of return for that year would be 5%:(105,000−100,000)100,000=0. 05x100=5%{\displaystyle {\frac {(105,000-100,000)}{100,000}}=0. 05x100=5%} If you earned any dividends, include those in your ending value. In the previous example, if you’d also earned $50 in dividends, your ending value would be $105,050.
For example, suppose you’ve had your portfolio for 4 years and your simple rates of return are 5% (0. 05), 7% (0. 07), 2% (0. 02), and 4% (0. 04). Your total return would be 1. 19 (rounded): (1+0. 05)x(1+0. 07)x(1+0. 02)x(1+0. 04)=1. 1918{\displaystyle (1+0. 05)x(1+0. 07)x(1+0. 02)x(1+0. 04)=1. 1918}
Continuing with the previous example, plug 1. 1918 into your calculator and multiply by the exponent 1/4. Your answer should be 1. 044. This calculation gets you a geometric average, which is simply an average of all the simple rates of return that also takes into account the compounding that occurs year after year. [5] X Research source
To continue with the example, your annualized rate would be 4. 4%:(1. 044−1)x100=4. 4%{\displaystyle (1. 044-1)x100=4. 4%} The full formula is (((1+R1)x(1+R2)x(1+R3)x(1+R4))1n−1)x100{\displaystyle (((1+R_{1})x(1+R_{2})x(1+R_{3})x(1+R_{4}))^{\frac {1}{n}}-1)x100}, where “R” is the rate of return for each investment period and “n” is the number of years.
For example, suppose your portfolio’s initial value was $100,000 and the final value after 10 years is $150,000. Divide 150,000 by 100,000 to get 1. 5. Then multiply 1. 5 by the exponent of 1/10 to get 1. 04. Subtract 1 to get 0. 04, then multiply by 100. Your annualized rate of return is 4%: ((150,000/100,000)110−1)x100=4%{\displaystyle ((150,000/100,000)^{\frac {1}{10}}-1)x100=4%} The full formula is ((final value of investmentinitial value of investment)1n−1)x100{\displaystyle (({\frac {\mathrm {final\ value\ of\ investment} }{\mathrm {initial\ value\ of\ investment} }})^{\frac {1}{n}}-1)x100}
Put each contribution or withdrawal in a new cell. There’s no need to combine cash flows for specific periods. For example, if you made 2 contributions and 1 withdrawal in a single year, you’d have 3 entries in 3 cells rather than just 1.
In Excel, the date function is =DATE(Year,Month,Day). For example, if you made a contribution on January 15, 2020, you would enter “=DATE(2020,1,15)”.
The values you enter refers to the range of cells containing the contributions or withdrawals you made. For example, if you used column A, rows 1 - 20, you would enter “A1:A20”. For the dates, use the range of cells in the column containing your dates, using the same formula as you used for the values. For example, “B1:B20”. The third value is your guess as to what you think the IRR will be. If you don’t have a guess, you can leave this blank. Excel defaults to 10% if no guess is given.
The result Excel and other spreadsheet programs reach is accurate within 0. 000001%, so it’s a result you can rely on.
Your value and date arrays are different lengths Your arrays don’t contain at least 1 positive and at least 1 negative value One of your dates comes before the first date entered in your array The calculation failed to converge (find a result) after 100 iterations
