在计算贷款利率时,需要使用PMT函数,而如果反过来计算符合目标月还款额的贷款利率,则可以使用单变量求解和PMT函数来实现,下面通过实例说明具体操作步骤。

步骤1:首先准备运算工作表。新建一个空白工作表,然后按下列要求输入数值或公式,具体如图22-94所示。

Excel 实例:使用单变量求解计算银行贷款利率-Excel22

图22-94 准备运算工作表

  • 在单元格A1中,输入“贷款总额”。
  • 在单元格A2中,输入“期限(月)”。
  • 在单元格A3中,输入“利率”。
  • 在单元格A4中,输入“月还款额”。
  • 在单元格B1中,输入“100000”,即需要贷入的总金额。
  • 在单元格B2中,输入“180”,即需要还款的月数。
  • 在单元格B4中,输入“=PMT(B3/12,B2,B1)”,该公式用于计算月还款金额。

提示:在本例中,已知每月需要还款额为“¥900”,但并不需要在此处输入该金额,因为下一步需要使用单变量求解确定利率,而单变量求解需要以公式开头。由于单元格B3中不含有数值,Excel会假设利率为0%,并使用本例中的值返回月还款金额“¥555.56”,此时可以忽略该值。有关PMT函数的用法,参见前面章节该函数的介绍。

步骤2:接下来设置B3单元格的格式。右键单击B3单元格,然后单击弹出菜单中的“设置单元格格式”对话框,将数字格式设置为“百分比”,并将小数位数设置为2,如图22-95所示。

步骤3:下面使用“单变量求解”计算贷款利率。单击“数据”选项卡,然后单击“预测”组中的“模拟分析”|“单变量求解”命令,打开“单变量求解”对话框。

步骤4:在“目标单元格”框中,输入要求解的公式所在单元格的引用,本例中输入“$B$4”。

步骤5:在“目标值”框中输入所需要的公式结果,本例中结果为-900(负数表示为还款金额)。

步骤6:在“可变单元格”框中输入要调整的值所在单元格的引用,本例中输入“$B$3”,具体设置如图22-96所示。

Excel 实例:使用单变量求解计算银行贷款利率-Excel22

图22-95 设置单元格格式

步骤7:单击“确定”按钮,查看“单变量求解状态”,如图22-97所示。

Excel 实例:使用单变量求解计算银行贷款利率-Excel22

图22-96 “单变量求解”对话框

Excel 实例:使用单变量求解计算银行贷款利率-Excel22

图22-97 单变量求解状态

步骤8:单击“确定”按钮,单变量求解功能运行并产生结果,当月还款额为900时,利率为7.02%,如图22-98所示。

Excel 实例:使用单变量求解计算银行贷款利率-Excel22

图22-98 使用单变量求解功能计算出的利率