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

June 9, 2005 4:54 PM Subscribe

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.

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.

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

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

posted by yarmond at 12:07 AM on June 11, 2005

*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:- Rearrange your formula so that one side is only zero, and call this function
*f(q)=pv*(1+r)*so that^{n}+pmt*((1+r)^{n}-(1+q)^{n})/(r-q) - fv*f(q)*is zero when you put in the right*q*. - Make an initial guess
*q*(It doesn't actually have to be a good guess)_{0} - Evaluate
*f(q*using the formula._{0}) - Evaluate
*f(q*where_{0}+dq)*dq*is some small number, say 10^{-6}. - Evaluate the derivative,
*f***'**(q_{0})=[f(q_{0}+dq)-f(q)]/dq - Make a new guess,
*q*_{1}=q_{0}- f(q_{0})/f**'**(q_{0}) - Repeat this procedure using the updated values of
*q*to obtain an accurate solution.

posted by yarmond at 12:07 AM on June 11, 2005

« Older Trouble Installing Jimmy Neutron Game on XP | Help us prepare for the fanciest dinner of our... Newer »

This thread is closed to new comments.

posted by whatzit at 6:32 PM on June 9, 2005