# Thread: Regression of some sort

1. ## 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?

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

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

4. ## 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?

Originally Posted by JeffM
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.

5. ## Re: Regression of some sort

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.

Originally Posted by GhostAccount
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

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

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

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