如何在EXCEL中解一元多次方程

记得小Y在金融资产讲义里提到了在EXCEL中解出那个实际利率，而不是使用内插法，今日小Y无事，列举几种在EXCEL中解一元多次方程的方法，供大家参考，不足之处，还请大家指正。

当时的例子为：

59×（1＋r）^-1＋59×（1＋r）^-2＋59×（1＋r）^-3＋59×（1＋r）^-4＋（59＋1250）×（1＋r）^-5＝1000

这实际是一个一元五次方程，其实可以将这个方程弄得更复杂些，比如说加个三角函数COS之类的，再对r进行开多次方等等，其解决方法是相同的。

方法一：试算法

根据财务知识，我们知道r的值为0-1之间，为此，我们在B2中输入公式：=59*(1+a2)^-1+59*(1+a2)^-2+59*(1+a2)^-3+59*(1+a2)^-4+(59+1250)* (1+a2) ^-5-1000，然后选中B2的填充柄，按住CTRL键，往下复制，我们在A2中根据精度需要，如果是保留小数点后三点，则输入0.001，然后在A3里输入0.002，选中A2和A3，选中填充柄，按住CTRL键往下复制，在B列结果为正负交界处选择一个B列绝对值最小的数，其对应行的A列值即为r的值，假如是K。

如果对精度要求特别的高，则可以以K为基数，不停的在此基础上增加小数位数，至到对应的B例的值最接近零为止。

方法二：单变量求解

在A1单元格里输入公式：=59*(1+a2)^-1+59*(1+a2)^-2+59*(1+a2)^-3+59*(1+a2)^-4+(59+1250)*(1+a2)^-5-1000

点击“工具”——“单变量求解”，将目标单元格选定为A1，将目标值输入0，将可变单元格选择为A2，点击确定，A2单元格的内容即为上述等式里R的值。如果不将等式里的1000移入到公式里，则可直接在目标值里输入1000，然后确定，A2的结果与公式里包含“-1000”的结果是一样的。

方法三：VBA逼近

Sub test()

Dim i As Double

Dim j As Double

Dim k As Double

Dim k1 As Double

Dim m As Double

For i = 0 To 1 Step 0.01 ‘精度为0.01

j = 59 * (1 + i) ^ -1 + 59 * (1 + i) ^ -2 + 59 * (1 + i) ^ -3 + 59 * (1 + i) ^ -4 + (59 + 1250) * (1 + i) ^ (-5) - 1000

If j < 0 Then

k = j

m = i

Exit For

End If

Next

m = i - 0.01

k1 = 59 * (1 + m) ^ -1 + 59 * (1 + m) ^ -2 + 59 * (1 + m) ^ -3 + 59 * (1 + m) ^ -4 + (59 + 1250) * (1 + m) ^ (-5) - 1000

If Abs(k1) > Abs(j) Then

MsgBox i

Else

MsgBox m

End If

End Sub

这段代码看起来比较复杂，变量也多，其实是为了更好的控制精度。运行速度倒是比较快的，就是步长为0.0000001的时候，运行时间也就一秒左右。

方法四、牛顿迭代法

该方法比较专业，但在编程中却是十分常用。

牛顿迭代法又称牛顿切线法，其公式如下：

F’(x0)=f(x0)/(x-x0)

x1=x0-f(x0)/f’(x0)

其中x0为根的接近值，f(x)为原方程，f'(x)为原方程的导数方程，x1为所求的根

注：导数方程，就是变量的系数与变量的方数之间的积，同时，各变量的方次减一。这个表述不太准确，差不多就是这样的吧。

使用该方法时，我们对原等式进行变形，并将(1+r)=X

通过变形得到如下等式：

1000*x^5-59*x^4-59*x^3-59*x^2-59*x-1309=0

同时：

F=(1000*x^4-59*x^3-59*x^2-59*x-59)*x-1309

F₁=(((5000*x-236)*x-177)*x-118)*x-59

如果我们只求X在0附近的一个根，根据牛顿迭代法，我们可以写出如下代码：

Sub test()

Dim x0 As Double

Dim x1 As Double

Dim f As Double

Dim f1 As Double

x1 = 0.008

Do Until Abs((x1 - x0)) < 0.00001 '控制精度

x0 = x1

f = (1000 * x0 ^ 4 - 59 * x0 ^ 3 - 59 * x0 ^ 2 - 59 * x0 - 59) * x0 – 1309

f1=5000*x0^4-236*x0^3-177*x0^2-118*x0-59

x1 = x0 - f / f1

Loop

MsgBox x1

End Sub

由此得出X1=1.09995

R=x1-1=0.0995

上述三种方法都有不足之处，第一，需要知道值的区间范围，第二种方法也只能给出多个正确答案中的一个，不过在经济实践中，基本值就只有一个正确答案。

至于利用EXCEL解方程组和给出符合条件的全部值，等小Y学习下算法再补上，以前小Y从来没有写过与数学沾边的程序。