# Thread: Very complex return on investment problem to solve

1. ## Very complex return on investment problem to solve

In non mathematical terms here is the problem. I have clients who ask me to review their current investment or pension portfolios. The inputs I usually receive are the dates of lump sums invested and the amounts, then the dates of regular monthly sums invested, their respective dates and amounts. From those figures and dates I am asked to work out how a portfolio has performed.

example case
a) Lump sum invested in December Jan 2011 of £55,186 plus
b) Regular monthly contributions made since Jan 2011 amounting to £64,220. Monthly contribution amounts have varied between £1394 per month and £1878 per month.
c) Total value of contributions is a+b (£120,036) however b has been invested over a period of time.
d) Value today is £149,000

I would be happy taking average value of monthly contributions as a guide if that would make the problem easier to solve.

Any help would be appreciated.

2. ## Re: Very complex return on investment problem to solve

This can be solved in excel using the IRR function, but frankly excel's IRR function looks meaningless, and the logic behind it is a tiny bit convoluted. So you can get your answer that way, but you will need to present that answer to your clients in a more intuitive way.

You really need to be more specific about the timing of the investments. I am going to assume that the initial investment was made at the end of December, 2010, and that additional investments were made at the end of each succeeding month. I am also going to use the average (because I really have no information that is usable except for that). And I am going to assume that the final value was as of June 30, 2014, and that there was an investment made at the end of that month.

Under those assumptions, I get an annual return of approximately 8.92% compounded monthly using the IRR function in excel.

I cannot explain how to use excel on this site. If you want to send me a private message with your email address, I can send you a copy of the excel spreadsheet that shows how to use excel's IRR function and how you can make it comprehensible to your clients.

3. ## Re: Very complex return on investment problem to solve

Hi, that is a revelation to me. I had thought of using IRR on excel but did not think it relevant and I suppose it makes sense as there have been cash outflows at certain periods and the rest must be the inflows. I will provide the actual figures below and would be interested to see if we get the same result using excel.

Todays value £149,000

Historic contributions

Date contributions
Mar-14 £1,879
Feb-14 £1,879
Jan-14 £1,879
Dec-13 £1,879
Nov-13 £1,879
Oct-13 £1,879
Sep-13 £1,879
Aug-13 £1,879
Jul-13 £1,879
Jun-13 £1,746
May-13 £1,746
Apr-13 £1,746
Mar-13 £1,746
Feb-13 £1,746
Jan-13 £1,746
Dec-12 £1,514
Nov-12 £1,514
Oct-12 £1,514
Sep-12 £1,514
Aug-12 £1,514
Jul-12 £1,514
Jun-12 £1,514
May-12 £1,514
Apr-12 £1,514
Mar-12 £1,514
Feb-12 £1,514
Jan-12 £1,514
Dec-11 £1,401
Nov-11 £1,401
Oct-11 £1,401
Sep-11 £1,401 £55,816 (additional lump sum)
Aug-11 £1,401
Jul-11 £1,401
Jun-11 £1,401
May-11 £1,401
Apr-11 £1,401
Mar-11 £1,394
Feb-11 £1,394
Jan-11 £1,394

4. ## Re: Very complex return on investment problem to solve

It is way too cumbersome to try to explain in English how to use a highly unintuitive excel formula. I have created an excel spreadsheet using the data given on the assumptions that all investments or liquidations occur at end of month, no investments or liquidations occurred during April, May, and June of 2014, and that the final value you provided was as of June 30, 2014. Under those assumptions, I get an approximate annual average yield of 10.87%.

As I explained yesterday, I can send you the spreadsheet so you can see the mechanics of how to calculate the IRR in excel and how to present it in a way comprehensible to clients, but I am not going to try to explain the mechanics. That would take many hours, and I have other things to do. It is so much simpler to just send you the spreadsheet. If you want me to do that, please give me your email address in a PRIVATE MESSAGE.

What I can explain here (if you want) is the logic behind the IRR calculation. I doubt you will be able explain it to most clients, which is why I show how to present the results in a more intuitive way in the spread sheet.

5. ## Re: Very complex return on investment problem to solve

Warning: Beer soaked rambling ahead, may be unsuitable for the occasional drinker.

Assuming first deposit was on January 1, 2011 and "Today" is April 1, 2014, then we have

solve 149000=1394(1+x )^39+1394((1+x)^2-1)/x*(1+x)^37+1401((1+x)^9-1)/x*(1+x)^28+1514((1+x)^12-1)/x*(1+x)^16+1746((1+x)^6-1)/x*(1+x)^10+1879((1+x)^9-1)/x*(1+x)^1+55816(1+x)^31 - Wolfram|Alpha Results

Thus an effective rate of about 11.18%

With a few adjustments in assumptions, you can probably arrive at the same result as that of Sir JeffM.
You need to be more specific about the exact date of your deposits/investments.
Were they made at the beginning or at the end of the month? You also need to specify when your "today" is.

6. ## Re: Very complex return on investment problem to solve

I have sent the spreadsheet to the OP by email. It is far easier to discuss excel with reference to the actual spreadsheet and formulas than in the abstract.

But it occurs to me that it may be helpful to others to understand why IRR is pertinent to this problem.

In actuality, an portfolio will have changes in value from day to day or month to month as a result of additional investments, liquidations, intesret define a period's return as the ratio of the sum of interest and dividends received and the change in portfolio value during that period over the starting value for that period. Assuming additional investments and liquidations for each period happen at onlt the end of that period, we get

$\displaystyle F = S\left(\prod_{i=1}^P(1 + R_i)\right) + \left(\sum_{i=1}^PN_i * \prod_{j = (i+1)}^P(1 + R_j)\right)$ where

F = final value;
S = starting value;
Ri = the return during period i;
Ni = net incremental investment (additional investments minus liquidations) for period i; and
P = number of periods.

Now we can simplify this mess a bit by saying S = N0.

Then we get $\displaystyle F = \left(\sum_{i=0}^PN_i * \prod_{j = (i+1)}^P(1 + R_j)\right).$

But frequently what we want to know is what is the relevant average return, R. In other words we want to know what R we can put into the formula above that gives virtually the same result.

$\displaystyle F = \left(\sum_{i=0}^PN_i * \prod_{j = (i+1)}^P(1 + R)\right) = \left(\sum_{i=0}^PN_i * (1 + R)^{(P - i)}\right) \implies$

$\displaystyle \left(\sum_{i=0}^PN_i * (1 + R)^{(P - i)}\right) - F = 0 \implies \left(\sum_{i=0}^{P-1}N_i * (1 + R)^{(P - i)}\right) + (N_P - F)(1 + R)^{(P - P)} = 0.$

Solving that final expression for R gives the internal rate of return per period.

EDIT: Jonah posted while I was writing this post. Notice that this computation is sensitive to the assumptions about timing. (That of course assumes I did not make some stupid error in getting to 10.87%, which is another possibility.)

7. ## Re: Very complex return on investment problem to solve

Originally Posted by jonah
Warning: Beer soaked rambling ahead, may be unsuitable for the occasional drinker.

Assuming first deposit was on January 1, 2011 and "Today" is April 1, 2014, then we have

solve 149000=1394(1+x )^39+1394((1+x)^2-1)/x*(1+x)^37+1401((1+x)^9-1)/x*(1+x)^28+1514((1+x)^12-1)/x*(1+x)^16+1746((1+x)^6-1)/x*(1+x)^10+1879((1+x)^9-1)/x*(1+x)^1+55816(1+x)^31 - Wolfram|Alpha Results

Thus an effective rate of about 11.18%
The 11.18% is the nominal rate,

Using this How to find IRR in Excel 2007, 2010 and 2013

The effective rate is 11.77%

Code:
-1394 -1394 -1394 -1401 -1401 -1401 -1401 -1401 -57217 -1401 -1401 -1401 -1514 -1514 -1514 -1514 -1514 -1514 -1514 -1514 -1514 -1514 -1514 -1514 -1746 -1746 -1746 -1746 -1746 -1746 -1879 -1879 -1879 -1879 -1879 -1879 -1879 -1879 -1879 149000
If the date for evaluation is moved to July then the IRR is 10.87%

Code:
-1394 -1394 -1394 -1401 -1401 -1401 -1401 -1401 -57217 -1401 -1401 -1401 -1514 -1514 -1514 -1514 -1514 -1514 -1514 -1514 -1514 -1514 -1514 -1514 -1746 -1746 -1746 -1746 -1746 -1746 -1879 -1879 -1879 -1879 -1879 -1879 -1879 -1879 -1879 0 0 149000

8. ## Re: Very complex return on investment problem to solve

Originally Posted by GhostAccount
The 11.18% is the nominal rate
Indeed.
How careless of me. Should have taken in more beer. Typing in a semi-drunk state can be dangerous to your health.
Equivalently, effective rate = (1+x)^12-1

Good call Sir GhostAccount. Or is it also Sir AbrahamA?

9. ## Re: Very complex return on investment problem to solve

Originally Posted by jonah
Good call Sir GhostAccount. Or is it also Sir AbrahamA?
@Jonah

Someone toasted my account for AbrahamA and when I asked for the reason they said it was an error and they will be unable to resurrect the dead and suggested that I should continue with the new GhostAccount

10. ## Re: Very complex return on investment problem to solve

Deposits made end of months; so 1st on Jan.31/2011, last on Mar.31/2014

Cpd. monthly (Effective) rates if 149,000 on Mar.31/2014:
11.64% (12.28%) ; if on Jun.30/2014: 10.37% (10.88%)

So yer all wrong and I'm (of course) right !

Hey Jonah and Jeff: how ya doin' you 2 mudders???!!!

11. ## Re: Very complex return on investment problem to solve

Originally Posted by Wilmer
Deposits made end of months; so 1st on Jan.31/2011, last on Mar.31/2014

Effective rate if 149,000 on Mar.31/2014: 11.64% ; if on Jun.30/2014: 10.37%

So yer all wrong and I'm (of course) right !

Hey Jonah and Jeff: how ya doin' you 2 mudders???!!!
Bon soir mon ami. If I'm wrong please please do not tell lookagain

12. ## Re: Very complex return on investment problem to solve

Originally Posted by JeffM
Bon soir mon ami. If I'm wrong please please do not tell lookagain
Should be "Bonsoir". Promise I won't. Bon soir!

13. ## Re: Very complex return on investment problem to solve

What a difference there is between people in this forum and people in my industry. You guys strive for the right answer, most in my industry do not even consider it worth asking the question. Thanks all.
Originally Posted by Wilmer
Should be "Bonsoir". Promise I won't. Bon soir!