How do I calculate yearly rate of return on my investments?

Question

I have been investing since May 2013 and I wanted to calculate my average yearly rate of return.

For example:-

Date

May - 2013 - Total investment made ($1200)

January - 2014 - Total investment made($8300)

July - 2014 - Total investment made ($9100)

October- 2014 - Total investment made ( $ 1400)

TOTAL AMOUNT INVESTED - $20000

Current Value of portfolio - $22000

Total gain - 10%

My question is, what formula can I use to see how much was my yearly rate of return? Shouldn't it be more than 10% as a lot of money was invested only recently?

Answer 1

Since the deposits into the investment fund are irregular in their timing, there isn't really any single formula that will give the information you want. Your only hope is a spreadsheet.

Start by guessing at the rate of return. Yes, GUESS. Assume that the rate is the annual rate, compounded monthly.

So, you throw in the $1200; it grows, compounded, for 8 months, and then you throw in another $8300. The new total grows at that same rate monthly until the next payment, and so on. In the end, you'll have the current value of the investments, assuming that the interest rate guess is correct. (Not too likely!)

This screen clip

shows the result of this process for a guess of 4%. The formula displayed is for Cell C5; it's copied into each cell in column C.

The Error is the difference between the calculated value and the actual value; 4% is too low a guess. Since the investment is actually worth more, you need to hike the interest rate and try again. Eventually you'll zero in on the correct rate...

Fortunately, most spreadsheets have a "What-If" function. In Excel, you can tell the program to fiddle with one cell until another cell has the value you want. When I do this, this is what I get:

Note that the error is a tiny fraction of a cent, and the interest rate is a very nice 18.4% nominal annual rate, compounded monthly.

EDIT: this solution is equivalent to 20.06% effective annual rate...

Answer 2

With these irregular deposits and no valuations at the deposit times the most accurate method to work out the rate of return is the money-weighted return calculation. It is used in this case by equating the net present value (NPV) of the cash flows to the NPV of the final value.

Taking n= 1 as the span of the whole investment period. From the start of May 2013 to the start of October 2014 is seventeen compounding intervals.

Since the last deposit was in the same month as the final valuation we can disregard its compounding contribution.

The return over the whole 17 month period is found by solving this equation:-

giving 29.56%.

This has to be annualised:-

giving a return of 20.06% effective annual interest.

Alternatively, the nominal annual rate compounded monthly is the effective monthly rate times twelve, i.e.

monthly x 12 = 0.015351 x 12 = 0.184212

so 18.42% nominal interest compounded monthly.

Edit

The monthly rate can also be found using Excel's IRR function like so:

The annual rate can be calculated from the monthly rate as shown previously.

Answer 3

Annual return = 20.05%

Using the Solver in Excel will arrive at the same conclusion, but it is long-winded. Use XIRR instead as it is the easiest and better solution: it accounts for the timing of cash flows (IRR assumes all cash flows are equally spaced, which is not your case) and you don't need to run the Solver each time you change your cash flows and their corresponding dates.

In essence, XIRR is the discount rate that produces a net present value of the cash flows of zero (NPV = 0); it is an annualised rate of return. The timing of cash flows is critical to the result.

Not knowing specific dates, I have assumed that payments are made at the beginning of the months (as negative numbers) and earn a return until the beginning of Oct.'14. I have also assumed that the last investment and the valuation of the portfolio occurred on 1 Oct. 2014 and are offset against one another.

So, for Oct.'14 cash flow, assume $20,600 = $22,000 (portfolio value) minus $1,400 (for lack of information, assumed to have been invested in Oct.'14 at the date of the valuation of the portfolio).

Create a table of data with input dates in one column and cash flows to its right. If you want to enter only month/year, Excel will default to the first day of that month (05/2014 => 01 May 2014). This will impact your results.

Select a cell outside that table, type =XIRR( ... and follow the instructions. The resulting number is an annualised rate (0.2005 = 20.05%).

For XIRR to work, consider the investments are negative numbers and the portfolio valuation is a positive number (assume that you could sell your portfolio at that price, which would return cash to you, whilst investments take cash away from you).

---

Proof, using the future value (FV) at Oct.'14 of the cash flows:

(1) 1,200*(1+20.05%)^(17/12) = Future value of $1,200 = $1,555

Note: 17/12 = number of months from investment to Oct.'14 over 12 months per year

(2) 8,300*(1+20.05%)^(9/12) = FV of $8,300 = $9,519

(3) 9,100*(1+20.05%)^(3/12) = FV of $9,100 = $9,525

Sum of (1), (2), (3) = 20,599 ~= 20,600 (Oct.'14)

The difference of $1 is due to rounding errors.

I couldn't paste a spreadsheet to show the calculations (I cannot make this HTML interface work properly).

Answer 4

Use the XIRR in Excel. It takes two ranges – a range of dollar values and a range of dates.

Here's how to use it:

For the range of dollar values, the first value is the starting balance or contribution. Each subsequent value is another contribution (or withdrawal), except for the final value which is the ending balance, but written as negative.

The corresponding dates show the dates of the contributions and of the ending balance.

In your case, the annualized rate of return is 20.05%.