Excel制作贷款还款金额速查表

4/24/2004来源:Excel教程人气:32230

  随着住房分配制度的彻底改革,住房分配已全面货币化,购买一套满意的住房是很多人的梦想。
  不过,由于住房价格居高不下,对于我们这群工薪族来说,购买一套住房实属不易,怎么办?贷款呀,花明天的钱,住今天的房。当然,贷款是一定要还的,下面我们就用Excel制作一个“住房贷款还款金额速查表”,帮大家参谋参谋。

  1.启动Excel 2003(Excel 2000/XP版本可仿照操作),新建一工作簿,参照图1的样式,将固定的字符输入其中,然后取名保存。

  2.选中B1单元格,执行“数据→有效性”命令,打开“数据有效性”对话框(图2),单击“允许”右侧的下拉按钮,选中“序列”选项,然后在“来源”下面的方框中输入“商业公积金”字符,按“确定”返回。

  在A1和C1单元格中,分别输入“住房”、“贷款还款速查表”字符。

  3.选中E4、E5单元格,分别输入10000、20000然后选中这两个单元格,将鼠标移至E5单元格右下角成细十字线状时,按住左键向下拖拉至E33单元格,完成金额输入。
  4.按照上述操作,将年限数值输入到相应的单元格中。

  5.再选中C2单元格,打开“数据有效性”对话框,选中“序列”选项,然后在“来源”下面的方框中输入“=$E$4:$E$33”字符,确定返回。

  合并A2和B2单元格,并输入“贷款金额”字符。

  6.选中B4单元格,输入公式:=IF$B$1="公积金",3.6%,4.77%),并用“填充柄”将公式复制到B5至B8单元格;选中B9单元格,输入公式:=IF($B$1="公积金",4.05%,5.04%),同样,用“填充柄”将公式复制到B10至B33单元格。

  提示:由于贷款利率分两段(5年及以下,5年以上),为了公式的简便,我们这里也是用两个公式来实现的。

  7.选中C4单元格,输入公式:=PMTB4/12A412$C$2);选中D4单元格,输入公式:=A4*12*C4。然后同时选中C4和D4单元格,用“填充柄”将公式复制到C5至D33单元格中。

  提示:此处,由于B5至B33单元格中已经填充了内容,在我们同时选中C4和D4单元格后,将鼠标移至D4单元格右下角成细十字线状时,双击左键,即可将公式快速复制到C5至D33单元格中。

  8.选中E列,右击鼠标,在随后出现的快捷菜单中,选“隐藏”选项,将该列隐藏起来。

  至此,“住房贷款还款金额速查表”制作完成。我们来试查一下(假定商业贷款15万元,期限为10年):

  选中C2单元格,此时该单元格右下角出现的下拉按钮(如图3)。

  单击此按钮,在随后出现的下拉列表中,选中150,000选项;在B2单元格,选中“商业”选项,计算的结果就出来了:查10年期限所在的行,知道每月应还款1593.9元,累计还款191270.1元(图1)。

  几点说明:①由于是住户偿还银行的贷款,所以计算出来的结果,系统以红色显示出来。②如果国家调整贷款利率,大家只要修改一下B4至B列最后单元格中的公式即可。