Break-even analysis for mutual funds
I am trying to get Excel to generate the break-even point between Class A and Class C mutual funds given a specified interest rate.
Class A has a front-end load and lower expenses.
Class C has no load but higher annual expenses
The given will be
-Class A load
-Class A expense ration
-Class C expense ration
-Investment return
The formula should generate the number of years until Class A funds will be worth more than Class B using a given interest rate.
I can do the math using a graphing calculator, but my algebra skills aren鈥檛 good enough to pull it into an excel formula.
My formula was:
(1 鈥?front-end load) (1- Class A expenses + investment return)^X = (1- Class B expenses + investment return)^X
I need an excel formula to solve for X.
I need it in excel so I can change the interest rates and fund info very quickly. I think your formula is slightly off. It should be:
Inv = Investment Amount in Dollars
AL = Class A Load Percentage
AX = Class A Expense Percentage
IR = Investment Return Percentage
BX = Class B Expense Percentage
X = Number of Years
[Inv - (Inv * AL)]*[1-AX+IR]^X = Inv * [1-BX+IR]^X
**************************************...
Note...I just solved this algebraically. The excel formula is:
= (LN(Inv-(Inv*AL))-LN(Inv)) / (LN(1-BX+IR) - LN(1-AX+IR))
where ln is the LN is the natural Logarithm formula and all the other variables correspond to those listed above.
**************************************...
If you wish to use goal-seek to solve the problem, follow these steps below...
Step 1: put all those variables into separate cells
Step 2: set up each side of the formula in separate cells.
(a) So... [Inv - (Inv * AL)]*[1-AX+IR]^X goes in one cell.
(b) Inv * [1-BX+IR]^X goes in a different cell
Step 3: In a separate cell insert a formula to divide Step 2(a) by Step 2(b).
Step 4: Go to the Tools menu and select "Goal Seek..."
Step 5: In the dialog box you'll see a place for "Set Cell". Put in the cell from Step 3. In the "To Value" box type in the number "1". In the "By changing box" click the cell from Step 1 where you entered the variable X.
Viola....Excel solves the problem for you. I'll solve the problem algebraically later, but for now that should get you going. Wow,I didn't know investing was so complicated...
I don't do "load Funds" why should I I pay up front fee to buy a fund, or pay an exit fee to leave a fund, why should I be forced to stay with a non performing fund in order to have my fees reduced over time.
Are you going to buy funds and keep them unchanged for 5 years, What if the market tanks, Don't you want to bail out while you can before you lose too much?
No Load funds put all your money to work for you right away, and with no leaving fee you are free to save your capital if you get scared and want out/..
I am a millionaire, I love no - load mutual funds and the freedom of no loads even with a "SLIGHTLY" higher Mer
I will go for any time. | 