Results 1 to 13 of 13

Math Help - Very complex return on investment problem to solve

  1. #1
    Newbie
    Joined
    Jul 2014
    From
    UK
    Posts
    11

    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.
    Follow Math Help Forum on Facebook and Google+

  2. #2
    Super Member
    Joined
    Feb 2014
    From
    United States
    Posts
    549
    Thanks
    254

    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.
    Follow Math Help Forum on Facebook and Google+

  3. #3
    Newbie
    Joined
    Jul 2014
    From
    UK
    Posts
    11

    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
    Follow Math Help Forum on Facebook and Google+

  4. #4
    Super Member
    Joined
    Feb 2014
    From
    United States
    Posts
    549
    Thanks
    254

    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.
    Last edited by JeffM; July 8th 2014 at 10:39 AM.
    Follow Math Help Forum on Facebook and Google+

  5. #5
    Member jonah's Avatar
    Joined
    Apr 2008
    Posts
    122
    Thanks
    3

    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.
    Last edited by jonah; July 8th 2014 at 01:52 PM.
    Follow Math Help Forum on Facebook and Google+

  6. #6
    Super Member
    Joined
    Feb 2014
    From
    United States
    Posts
    549
    Thanks
    254

    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.)
    Last edited by JeffM; July 8th 2014 at 02:37 PM.
    Follow Math Help Forum on Facebook and Google+

  7. #7
    Banned
    Joined
    Jul 2014
    From
    Village
    Posts
    12
    Thanks
    2

    Re: Very complex return on investment problem to solve

    Quote Originally Posted by jonah View Post
    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
    Follow Math Help Forum on Facebook and Google+

  8. #8
    Member jonah's Avatar
    Joined
    Apr 2008
    Posts
    122
    Thanks
    3

    Re: Very complex return on investment problem to solve

    Quote Originally Posted by GhostAccount View Post
    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?
    Last edited by jonah; July 8th 2014 at 11:27 PM.
    Follow Math Help Forum on Facebook and Google+

  9. #9
    Banned
    Joined
    Jul 2014
    From
    Village
    Posts
    12
    Thanks
    2

    Re: Very complex return on investment problem to solve

    Quote Originally Posted by jonah View Post
    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
    Follow Math Help Forum on Facebook and Google+

  10. #10
    MHF Contributor
    Joined
    Dec 2007
    From
    Ottawa, Canada
    Posts
    3,102
    Thanks
    68

    Thumbs up 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???!!!
    Last edited by Wilmer; July 23rd 2014 at 09:38 PM.
    Follow Math Help Forum on Facebook and Google+

  11. #11
    Super Member
    Joined
    Feb 2014
    From
    United States
    Posts
    549
    Thanks
    254

    Re: Very complex return on investment problem to solve

    Quote Originally Posted by Wilmer View Post
    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
    Follow Math Help Forum on Facebook and Google+

  12. #12
    MHF Contributor
    Joined
    Dec 2007
    From
    Ottawa, Canada
    Posts
    3,102
    Thanks
    68

    Re: Very complex return on investment problem to solve

    Quote Originally Posted by JeffM View Post
    Bon soir mon ami. If I'm wrong please please do not tell lookagain
    Should be "Bonsoir". Promise I won't. Bon soir!
    Follow Math Help Forum on Facebook and Google+

  13. #13
    Newbie
    Joined
    Jul 2014
    From
    UK
    Posts
    11

    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.
    Quote Originally Posted by Wilmer View Post
    Should be "Bonsoir". Promise I won't. Bon soir!
    Follow Math Help Forum on Facebook and Google+

Similar Math Help Forum Discussions

  1. return on investment if initial investment not counted
    Posted in the Business Math Forum
    Replies: 0
    Last Post: October 23rd 2012, 06:19 AM
  2. Please help w/ return on equity problem
    Posted in the Business Math Forum
    Replies: 1
    Last Post: March 14th 2010, 02:49 PM
  3. Solve investment question using matrix
    Posted in the Advanced Algebra Forum
    Replies: 1
    Last Post: May 13th 2009, 06:36 AM
  4. Replies: 1
    Last Post: November 29th 2007, 06:13 AM
  5. Replies: 4
    Last Post: August 14th 2007, 10:52 PM

Search Tags


/mathhelpforum @mathhelpforum