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