本文将教你如何在EXCEL表格中创建一个利息支付计算器。Windows版本和MAC版本的EXCEL都可以。
步骤
-
{"smallUrl":"https:\/\/www.zenmeban.com\/images_en\/thumb\/4\/46\/Calculate-an-Interest-Payment-Using-Microsoft-Excel-Step-1-Version-5.jpg\/v4-460px-Calculate-an-Interest-Payment-Using-Microsoft-Excel-Step-1-Version-5.jpg","bigUrl":"https:\/\/www.zenmeban.com\/images\/thumb\/4\/46\/Calculate-an-Interest-Payment-Using-Microsoft-Excel-Step-1-Version-5.jpg\/v4-728px-Calculate-an-Interest-Payment-Using-Microsoft-Excel-Step-1-Version-5.jpg","smallWidth":460,"smallHeight":348,"bigWidth":728,"bigHeight":551,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
1
打开EXCEL软件。双击EXCEL应用程序图标,该图标在深绿色背景上有个白色类似“X”的字母。
-
{"smallUrl":"https:\/\/www.zenmeban.com\/images_en\/thumb\/8\/81\/Calculate-an-Interest-Payment-Using-Microsoft-Excel-Step-2-Version-5.jpg\/v4-460px-Calculate-an-Interest-Payment-Using-Microsoft-Excel-Step-2-Version-5.jpg","bigUrl":"https:\/\/www.zenmeban.com\/images\/thumb\/8\/81\/Calculate-an-Interest-Payment-Using-Microsoft-Excel-Step-2-Version-5.jpg\/v4-728px-Calculate-an-Interest-Payment-Using-Microsoft-Excel-Step-2-Version-5.jpg","smallWidth":460,"smallHeight":346,"bigWidth":728,"bigHeight":547,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
2
单击空白工作簿。这个选项位于EXCEL表格主页的左上角,能为利息支付计算器创建一个新的工作表。
-
{"smallUrl":"https:\/\/www.zenmeban.com\/images_en\/thumb\/9\/96\/Calculate-an-Interest-Payment-Using-Microsoft-Excel-Step-3-Version-5.jpg\/v4-460px-Calculate-an-Interest-Payment-Using-Microsoft-Excel-Step-3-Version-5.jpg","bigUrl":"https:\/\/www.zenmeban.com\/images\/thumb\/9\/96\/Calculate-an-Interest-Payment-Using-Microsoft-Excel-Step-3-Version-5.jpg\/v4-728px-Calculate-an-Interest-Payment-Using-Microsoft-Excel-Step-3-Version-5.jpg","smallWidth":460,"smallHeight":346,"bigWidth":728,"bigHeight":547,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
3
设置行。在以下各单元格中输入支付内容的标题:
-
A1单元格 - 输入本金
-
A2单元格 - 输入利率
-
A3单元格 - 输入期数
-
A4单元格 - 输入应付
-
{"smallUrl":"https:\/\/www.zenmeban.com\/images_en\/thumb\/a\/ae\/Calculate-an-Interest-Payment-Using-Microsoft-Excel-Step-4-Version-5.jpg\/v4-460px-Calculate-an-Interest-Payment-Using-Microsoft-Excel-Step-4-Version-5.jpg","bigUrl":"https:\/\/www.zenmeban.com\/images\/thumb\/a\/ae\/Calculate-an-Interest-Payment-Using-Microsoft-Excel-Step-4-Version-5.jpg\/v4-728px-Calculate-an-Interest-Payment-Using-Microsoft-Excel-Step-4-Version-5.jpg","smallWidth":460,"smallHeight":347,"bigWidth":728,"bigHeight":549,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
4
输入各项的值。B1单元格中输入欠款总额。
- 假设你用10000元首付购买了一艘价值20000元的船,在B1输入10,000。
-
{"smallUrl":"https:\/\/www.zenmeban.com\/images_en\/thumb\/a\/a8\/Calculate-an-Interest-Payment-Using-Microsoft-Excel-Step-5-Version-5.jpg\/v4-460px-Calculate-an-Interest-Payment-Using-Microsoft-Excel-Step-5-Version-5.jpg","bigUrl":"https:\/\/www.zenmeban.com\/images\/thumb\/a\/a8\/Calculate-an-Interest-Payment-Using-Microsoft-Excel-Step-5-Version-5.jpg\/v4-728px-Calculate-an-Interest-Payment-Using-Microsoft-Excel-Step-5-Version-5.jpg","smallWidth":460,"smallHeight":346,"bigWidth":728,"bigHeight":548,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
5
输入现行利率。B2单元格中输入需支付的利率。
-
{"smallUrl":"https:\/\/www.zenmeban.com\/images_en\/thumb\/3\/37\/Calculate-an-Interest-Payment-Using-Microsoft-Excel-Step-6-Version-5.jpg\/v4-460px-Calculate-an-Interest-Payment-Using-Microsoft-Excel-Step-6-Version-5.jpg","bigUrl":"https:\/\/www.zenmeban.com\/images\/thumb\/3\/37\/Calculate-an-Interest-Payment-Using-Microsoft-Excel-Step-6-Version-5.jpg\/v4-728px-Calculate-an-Interest-Payment-Using-Microsoft-Excel-Step-6-Version-5.jpg","smallWidth":460,"smallHeight":347,"bigWidth":728,"bigHeight":549,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
6
B3单元格输入剩余付款期数。假设你计划12个月还清,则在B3单元格输入12。
-
{"smallUrl":"https:\/\/www.zenmeban.com\/images_en\/thumb\/9\/9c\/Calculate-an-Interest-Payment-Using-Microsoft-Excel-Step-7-Version-4.jpg\/v4-460px-Calculate-an-Interest-Payment-Using-Microsoft-Excel-Step-7-Version-4.jpg","bigUrl":"https:\/\/www.zenmeban.com\/images\/thumb\/9\/9c\/Calculate-an-Interest-Payment-Using-Microsoft-Excel-Step-7-Version-4.jpg\/v4-728px-Calculate-an-Interest-Payment-Using-Microsoft-Excel-Step-7-Version-4.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
7
选中B4单元格。单击可选中,输入应付利息的计算公式。
-
{"smallUrl":"https:\/\/www.zenmeban.com\/images_en\/thumb\/e\/ef\/Calculate-an-Interest-Payment-Using-Microsoft-Excel-Step-8.jpg\/v4-460px-Calculate-an-Interest-Payment-Using-Microsoft-Excel-Step-8.jpg","bigUrl":"https:\/\/www.zenmeban.com\/images\/thumb\/e\/ef\/Calculate-an-Interest-Payment-Using-Microsoft-Excel-Step-8.jpg\/v4-728px-Calculate-an-Interest-Payment-Using-Microsoft-Excel-Step-8.jpg","smallWidth":460,"smallHeight":346,"bigWidth":728,"bigHeight":548,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
8
输入应付利息计算公式。B4单元格输入
=IPMT(B2, 1, B3, B1)
并按回车键。这样就能计算每期应付的利息。
- 这种方法并没有算复利,而复利一般随着支付总额减少而降低。用本金减去某期的支付额,然后重新计算B4单元格可得出。
小提示
- 将A1至B4单元格复制粘贴到表格的其他地方,可以比较不同利率和期限引起的变化,同时也不会丢失原始的公式和结果。
警告
- 利率可能会变动。计算利息之前,一定要仔细阅读利息协议上的细节。