Embarrassing but true: basic algebra eludes me, how do I solve this equation?

June 9, 2005 4:54 PM

Follow up Filter. Last week on AskMe I asked for help getting a formula to calculate "future value" in excel. blue mustard came through with the perfect answer: "fv = pv (1+r)n + pmt ((1+r)n - (1+q)n) / (r-q)" Now, I need to have a formula I can plug into excel that solves for q in blue mustard's formula.

There are two problems I am having with this. The first is that I have no recall of all that basic algebra I took and so I'd like help with the equation. The second problem is I do not know how, in excel, to correctly write the formula involving roots of numbers. I know that "sqrt" returns the square root. But exactly how will I type this new (solving for q) equation into excel so it works? **Note that in blue mustartd's answer in the other thread the "n" is "to the power of" I just don't know how to superscript like bm did. Also, if this question is confusing, I ask it a little more clearly in the last entry of the question last week, but (alas) by the time I posted that follow up everyone had moved on.

posted by wtfwjd? to computers & internet (3 comments total)

Have you considered "goal seek" under the Tools menu? Instead of solving explicitly for q, you can solve for [future value] by changing [another variable in a different cell]. It eliminates the algebra, the absolute value issues of sqrt(), etc.
posted by whatzit at 6:32 PM on June 9, 2005

Thanks whatzit, but that won't solve my problem. I have thousands of these calculations to do, so need to be able to put it in a cell and just copy down. Thanks for the input though,
posted by wtfwjd? at 8:09 PM on June 9, 2005

There is (probably) no formula for the problem you are trying to solve. The equation you have to start with is a polynomial equation of degree n in q, which, in general has no algebraic solution for n larger than 4. What you need to do here is use an iterative solution technique, where you guess the answer, see how close it is, and modify your guess to make it closer. The easiest way of doing this is to use Newton's Method. In Excel, it goes something like this:

1. Rearrange your formula so that one side is only zero, and call this function f(q)=pv*(1+r)ⁿ+pmt*((1+r)ⁿ-(1+q)ⁿ)/(r-q) - fv so that f(q) is zero when you put in the right q.
2. Make an initial guess q₀ (It doesn't actually have to be a good guess)
3. Evaluate f(q₀) using the formula.
4. Evaluate f(q₀+dq) where dq is some small number, say 10^-6.
5. Evaluate the derivative, f'(q₀)=[f(q₀+dq)-f(q)]/dq
   
6. Make a new guess, q₁=q₀ - f(q₀)/f'(q₀)
7. Repeat this procedure using the updated values of q to obtain an accurate solution.

You should be able to put all these intermediate values in a row in Excel, and then, if you have the cell references right, you can just use that row to fill in a bunch of rows below that to perform the iteration. This procedure is most likely what Excel is doing in Goal Seek, but this should let you do it for an entire spreadsheet at once.
posted by yarmond at 12:07 AM on June 11, 2005