7

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?

  • When is "current"? – DJohnM Oct 15 '14 at 5:39
8

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 enter image description here

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: enter image description here

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...

  • 1
    Hey, thanks a lot. That was a really good answer and it really helped! – user19894 Oct 15 '14 at 14:07
  • 1
    +1, but, doesn't Excel's IRR function do this for real, instead of your brute force method? – JTP - Apologise to Monica Oct 15 '14 at 16:39
  • 1
    I believe IRR treats irregular payments made at regular intervals. So if I had padded the investment record with 14 months of zero investment at the appropriate rows, IRR would indeed have done the job I brute-forced... – DJohnM Oct 15 '14 at 17:05
  • Further: Excel's XNPV handles a list of irregular payments made at irregular intervals, but you'd still need to use What_If to extract interest rate... – DJohnM Oct 15 '14 at 17:11
  • Doesn't Excel have "goalseek" functionality? – barrycarter Oct 15 '14 at 22:43
4

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.

enter image description here

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:-

enter image description here

giving 29.56%.

This has to be annualised:-

enter image description here

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:

enter image description here

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

  • Why not just use the XIRR function? – Vaddadi Kartick Nov 21 '15 at 9:33
  • As I recall XIRR does not treat the months as equal periods of time so the answer is different to the above. – Chris Degnen Nov 21 '15 at 10:21
  • 1
    I thought it does. If you have equal periods, you use the IRR function, which is a special case of XIRR. – Vaddadi Kartick Nov 21 '15 at 11:35
1

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).

1

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.

Excel XIRR Example

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.