3
$\begingroup$

I'm trying to understand how the performance of financial investments are measuring when the asset has multiple points of investment.

For example, say I invest $1000 in a stock. After 6 months, it's worth $1200. At this point, I invest an additional $2000, for a total value of $3200. If after an additional 6 months, the entire investment is worth $4000, how would I calculate the overall yield or effective interest rate? Normally, you'd do (final-initial)/initial, but in this example there are multiple initial values, but only one final. How would I reconcile this?

EDIT: Note, some values are supposed to be dollar amounts, but for some reason the site's markup is interpreting the dollar sign as the start of italics...

EDIT: Escaped dollar amounts (thanks KennyTM).

  • 2
    We use dollar signed to indicate equations, e.g. `$final-initial \over initial$` --> $final-initial \over initial$. You need to escape the money between backquotes, e.g. `\`$1000\`` to display it properly.2010-09-24
  • 1
    A bit more can actually be done here (note that the solutions of user1827 and Américo Tavares are equivalent). It is possible to find an "interest rate" $r$ such that 1000 is a accrued to 1200 after 6M **and** such that 1200 + 2000 is accrued to 4000 6m even later. (This is also very handy tool in finance when you know the a cash flows from start - invest in the cf giving highest $r$).2010-09-25
  • 0
    A comment to the above comment it is crucial that $r$ is an **effective interest rate**.2010-09-25

2 Answers 2

3

Excel has a function for this called IRR. To roll your own you just need to find the rate of return that produces the final value. You have investments i1, i2, ... that have been in the account for m1, m2, ... months. For a given return per month you should have i1*(1+r)^m1 + i2*(1+r)^m2 + ... Now just search for r that makes this equal to the final value. For some patterns of cash flow, there may be multiple values of r that satisfy the equation. You can use your favorite root finder, but bisection is quite robust.

  • 0
    I'm not sure I understand how IRR would apply. I tried creating a sample spreadsheet with three rows (1000,3200,4000), with =IRR(A1:A3) on the fourth row, and Excel gives me an iteration error. The docs state that it also assumes the cash flows occur at regular intervals, which I can't guarantee.2010-09-24
  • 0
    On further searching, it looks like XIRR allows a non-periodic calculation.2010-09-24
  • 0
    Or you can use an increment of time that is small enough that all the investments fall on an interval and put zero investment in some of the increments.2010-09-25
  • 0
    Thanks. I've confirmed XIRR does exactly what I need.2010-09-25
6

My interpretation of your question, my hand calculations and explanations are as follows. For periods of six months you have:

  1. At the beginning of period 1 you invest $C_{0}=1000$ (dollars).

  2. At the end of period 1 $C_{0}$ worths $F_{1}=1200$.

  3. At the beginning of period 2 you invest the additional capital of $% C_{1}=2000$.

  4. At the end of period 2 your investement worths $F_{2}=4000$.

alt text Figure: Cash Flows of -1000, -2000 and +4000 dollars

Let us denote by $r$ the anual nominal interest rate of your investement. In $n$ periods of six months the investement worths $(1+r/2)^{n}$ per currency unit of invested capital.

The initial investement $C_{0}$ worths $C_{0}(1+r/2)^{2}$ at the end of period 2.

The additional capital $C_{1}$ worths $C_{1}(1+r/2)$ at the end of period 2.

Then we have

$C_{0}(1+r/2)^{2}+C_{1}(1+r/2)=F_{2}$

$1000(1+r/2)^{2}+2000(1+r/2)=4000$

or

$(1+r/2)^{2}+2(1+r/2)=4$

$2r+\dfrac{1}{4}r^{2}+3=4.$

The solution is: $r=2\sqrt{5}-4\approx 0.47214\approx 47.214\%$ anual nominal rate

To determine the effective interest rate, you could find how much would you need to invest so that at a nominal rate of $r$ you yould have $4000$ in 1 year (2 periods):

$P\cdot 1.2361^{2}=4000$

$P=4000/1.2361^{2}=2617.9$

The anual effective interest rate is

$i_{eff}=\dfrac{4000}{2617.9}-1=0.52794\approx 52,794\%$.

Remark: The future value $F_{n}$ at the end of period $n$ of a present value $P$ is given by

$F_{n}=P\left( 1+\dfrac{r}{m}\right) ^{n}$,

where $r=i_N$ is the anual nominal interest rate compounded $m$ periods per year. In your case $m=2$.

Reference: Rigg, Bedworth and Randhawa, Engineering Economics, Mc Graw Hill, 4th ed., 1996.

All errors and omissions are of mine, of course.