Results 1 to 8 of 8

Math Help - Regression of some sort

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

    Regression of some sort

    I had tried to solve this problem by using excel and when I carried out a simple check to see if the formula worked well I could not get it to work.

    Here is the problem in lay terms.

    I run financial illustrations for my clients and obtain the figures from a number of life companies. The illustrations are based on either a single lump sum, a series of regular payments or a combination. By running illustrations I ought to be able to compare costs of each company based on a portfolio I had selected with them. Assume for a second the following. The portfolios are identical in terms of proportions of equities, bonds, cash and property. Then assume I am using the same funds for each asset class. All that remains then are the charges for the life company providing the wrapper (pension or bond).

    e.g

    Lets say company a produces an illustration which shows that at 3.3% and after 10 years an investment grows to 97,400, at 5.12% the same investment grows to 117,000 and at 6.93% the investment grows to 139,000

    Company B then shows illustrations at 4%,6% and 8% and so I need to convert company A figures to 4%,6%,8% to enable me to compare like with like illustrations. So I used power regression on excel (simply because I do not know how to do it using mathematical formula) and came up with a formula which read y=11.459X +59.168 and r2 = 9991.

    I assumed r2 was suggesting there was a high correlation between the figures and so I took 4 (as in 4%) multiplied it by 11.459 and added 59.168 and got 105,000 which looked about right. I did the same at 6 and 8 (6 and 8%).

    I then went back and did the same for 3.3% and 5.12% to check the formula back and it came back with a different answer. 3.3% became 96,980, 5.12% became 117, 836 and 6.93 became 138,576.

    Is that because r2 is not 1?

    is there a better way to do this?



    Follow Math Help Forum on Facebook and Google+

  2. #2
    Super Member
    Joined
    Feb 2014
    From
    United States
    Posts
    825
    Thanks
    418

    Re: Regression of some sort

    I'm thinking about this. I'm not quite sure what you mean by "power regression." In any case, I am not sure that regression analysis is the best way to attack this (I may be wrong about that of course). What kind of rate are the illustrative rates supposed to be, long-term corporate bond rates, short-term government bond rates, stock market returns including capital gains and dividends, some sort of blended rate, or what? If the illustrative rates for different companies are different kinds of rates, the analysis is likely to get complex (maybe Monte Carlo method).

    Now I may be greatly complicating the question. It's possible that the companies are simply showing what the future value of a contract will be if returns on the contract are at various rates. In that case, pairwise comparison is not difficult at all with a spreadsheet.
    Follow Math Help Forum on Facebook and Google+

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

    Re: Regression of some sort

    You have the discount rates and future worth of the investments

    Given this information, you may want to find the present value of such investments at those rates that will give you the base value

    From those base values finding future values at "any" rate will give you the answer

    For single sum investments you can find the present value and for annuities you can find the annuity payment

    I can direct you to a set of tools that may be used but I have noticed in past my posts/account was disabled when someone took those links as SPAM
    Follow Math Help Forum on Facebook and Google+

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

    Re: Regression of some sort

    Good questions. I have no idea what I mean by power regression either but it seemed to get me to an answer quite close. The illustrative rates are blended by the provider. e.g. if the bond that I have selected is part government bonds, part commercial bonds and part high yield then they may give that a 4% illustrative yield. The next provider using exactly the same bond may take a different view and give it a 4.5% illustrative yield. These are normally unit trusts/OEICs which I am selecting and which sit on any investment platform. They also assume in fixed interest payments and dividends are reinvested.
    What we are talking about though is where the same portfolio is being given different rates.

    Your last point is most encouraging. If each company used the same illustrative rates for the same funds then the only other variable is their internal costs and any negotiated discount with the fund managers. The purpose therefore of the illustration is to show that. By giving different illustrative rates the providers make it difficult to make like for like comparisons. See?

    Going back to the power regression, basically excel draws a scattergram and shows a line through the centre of the dots. It then estimates the trend line and each returns relationship to the trend. I believe the power regression allows for curved trends. Does that help?



    Quote Originally Posted by JeffM View Post
    I'm thinking about this. I'm not quite sure what you mean by "power regression." In any case, I am not sure that regression analysis is the best way to attack this (I may be wrong about that of course). What kind of rate are the illustrative rates supposed to be, long-term corporate bond rates, short-term government bond rates, stock market returns including capital gains and dividends, some sort of blended rate, or what? If the illustrative rates for different companies are different kinds of rates, the analysis is likely to get complex (maybe Monte Carlo method).

    Now I may be greatly complicating the question. It's possible that the companies are simply showing what the future value of a contract will be if returns on the contract are at various rates. In that case, pairwise comparison is not difficult at all with a spreadsheet.
    Follow Math Help Forum on Facebook and Google+

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

    Re: Regression of some sort

    Let me think about this.

    What the illustration attempts to give is a future value based on certain growth rates (gross). It is not discounted for such things as inflation but it takes account of internal costs which may be affected by volume of investment and fund manager charges. If for example provider A has on its platform 200,000 of Liontrust special sits funds and provider B has 100,000 then a discount may be offered to provider A which may be passed onto the investor resulting in lower charges. Internal costs may be high due to technology, staff headcount, salary, inefficiencies etc or simply they are buying the market.

    There is a figures sometimes quoted by some of the providers called the Reduction in Yield but there is confusion among compliance teams about whether this can be used to demonstrate our due diligence.

    I may have both single sum and regular payments and so that is taking me back to an earlier post where JeffM enlightened me with the IRR formula in excel.






    Quote Originally Posted by GhostAccount View Post
    You have the discount rates and future worth of the investments

    Given this information, you may want to find the present value of such investments at those rates that will give you the base value

    From those base values finding future values at "any" rate will give you the answer

    For single sum investments you can find the present value and for annuities you can find the annuity payment

    I can direct you to a set of tools that may be used but I have noticed in past my posts/account was disabled when someone took those links as SPAM
    Follow Math Help Forum on Facebook and Google+

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

    Re: Regression of some sort

    If the Company A is giving three illustrative yields for the same portfolio for the same term (10 year) then it should follow that all such growths are for a certain present value

    But it does not look so

    Code:
    Rate	N	FV	PV
    3.30%	10	97,400.00	70,397.26
    5.12%	10	117,000.00	71,012.10
    6.93%	10	139,000.00	71,124.48
    If all you have in your portfolio is a collection of zero and coupon bearing bonds then a better way to evaluate the investment is to find the IRR or yield of the portfolio and then to find effective yield that you may compare with any of the offerings by Company A, B or any one else
    Follow Math Help Forum on Facebook and Google+

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

    Re: Regression of some sort

    All three illustrations represent low medium and high growth illustrations (different scenarios). I am trying to I could use the mean average of each company across low medium and high to select three figures to work with and apply those to all of them. There is a very crude way of course and that is to take the return difference between 5.12 and 3.3 (in this case 19600) and work out what each 10 basis points represents (in this case 1960) and so if I want to recalculate company A results to 4% instead of 3.3 I would simply multiply (70/182)X19600)=7538 to be added to 97400 and I have 104938. However the difference between low to med to high may not be the same due to volume discounts and so I needed something to look at the curve (I think)

    e.g.
    Follow Math Help Forum on Facebook and Google+

  8. #8
    Super Member
    Joined
    Feb 2014
    From
    United States
    Posts
    825
    Thanks
    418

    Re: Regression of some sort

    Hi

    I think (but am not sure) that I understand what you want to do: you want to compare the value of different packages on a consistent basis at various presumed rates of return. This is not as complex a problem as I originally made it out to be. It is still subtle.

    The first thing I will say, which you almost certainly have already recognized, is that different packages will typically not generate approximately equal returns under approximately equal market conditions unless they are indexed funds. (I'm a big believer in the logic behind indexed funds, but that is irrelevant to your problem.) So the comparison will be unrealistic to some degree. If, however, the packages represent quite similar risk characteristics and quite similar management styles, the returns before cost of management should vary around an average (hopefully without too much deviance from the average). However, what you seem to be worrying about are differences in costs, and the differences in costs may be outweighed by fairly small differences in before-cost return. In short, an analysis of this kind may be useful but is unlikely to be dispositive.

    Second, the math formulas for present and future value in essence assume that the person involved has unlimited funds. This of course is never true. So package A may be better for one person because it requires small investments over time, and package B may be better for a different person who can afford a large up-front, lump-sum investment. If you are familiar with the language of the Austrian School of economics, what is right or wrong for the individual depends on a comparison of "subjective value" (which is individually determined and unrelated to market prices) with "objective value" (which is simply market prices and so socially determined). My point here is that I doubt that you can necessarily determine which package is "better" in the abstract. A brief personal anecdote may be pertinent. When I retired, I was the beneficiary of three pension plans (long irrelevant story), each of which provided six options. So there were 106 possible options to choose from. I modeled the entire set. Many, indeed most, of those options were unlikely to be good given the situation that will probably pertain to my wife and me. The output of the model showed (if I remember correctly) three options that seemed to fit. People in a different situation would have found different options more appropriate. But all 106 had virtually identical expected present values because present value calculations ignore personal situations.

    Last point. If I am understanding what you want to do, you can probably answer the problem (ignoring personal differences) using future value formulas. I wouldn't bother. Depending on the level of detail, the use of the formulas would itself get involved. In any case, such formulas will mean nothing to your customers. Once again, I would use spreadsheets. Maybe use a relatively simple model to determine two good lump sum packages, two annuity type packages, and two lump sum plus annuity packages. And then for the individual client, you could use a more personalized model that compares all six using numbers that make sense for the individual client.

    Again, I don't feel comfortable trying to design or analyze excel models unless I can look at the spreadsheet. You have my email address. If you want to pursue that line, let's do it by email.
    Last edited by JeffM; July 17th 2014 at 06:19 PM.
    Follow Math Help Forum on Facebook and Google+

Similar Math Help Forum Discussions

  1. Replies: 1
    Last Post: February 5th 2013, 10:50 PM
  2. Ncaaf multiple regression/ stepwise regression
    Posted in the Advanced Statistics Forum
    Replies: 0
    Last Post: August 10th 2011, 07:21 AM
  3. What sort of DE is this?
    Posted in the Differential Equations Forum
    Replies: 6
    Last Post: November 1st 2010, 03:24 AM
  4. More max/min (sort of)
    Posted in the Calculus Forum
    Replies: 4
    Last Post: October 23rd 2009, 03:39 PM
  5. Need help. Sort of Urgent!
    Posted in the Algebra Forum
    Replies: 9
    Last Post: January 2nd 2008, 08:52 AM

Search Tags


/mathhelpforum @mathhelpforum