按揭贷款分析表和还款明细表
用WPS+2010表格计算按揭贷款月供明细账
用WPS 2010表格计算按揭贷款月供明细账如今，有一套自己的住房是大家追求的目标之一，而对年轻人说，买房几乎都要贷款，月供就自然是大家最关心的事情，月供怎样算？供多少？哪种贷法最实惠？这些问题也许还有些人没有搞清楚吧，下面就借助WPS2010表格来算算按揭贷款月供明细账。
一、按揭贷款月供计算公式1．按揭贷款等额本息还款计算公式每月还本付息金额=[本金×月利率×（1+月利率）还款月数]/[（1+月利率）还款月数-1]其中：每月利息=剩余本金×贷款月利率；每月本金=每月月供额-每月利息。
计算原则：银行从每月月供款中，先收剩余本金利息，后收本金；利息在月供款中的比例中虽剩余本金的减少而降低，本金在月供款中的比例因而升高，但月供总额保持不变。
2．按揭贷款等额本金还款计算公式每月还本付息金额=（本金/还款月数）+（本金－累计已还本金）×月利率其中：每月本金=总本金/还款月数；每月利息=（本金-累计已还本金）×月利率。
计算原则：每月归还的本金额始终不变，利息随剩余本金的减少而减少。
二、用WPS 2010表格计算按揭贷款月供明细账现以商业贷款200000元，贷款年限为20年，银行贷款基准利率为年利率5.94%再打7折为例。
第一步，打开WPS表格，在第1行录入贷款基本信息；接着在a2单元格填入“20”（不含引号，下同），在b2单元格填入“=A2*12”，在c2单元格填入“=5.94%*0.7”，在d2单元格填入“=C2/12”，e2单元格填入“200000”，则如图1。
图1第二步，在第3行输入还款方式，在第4行输入明细账名称，在第5行输入第一次还款前的数据，如图2。
图2第三步，根据等额本息还款计算公式在b6单元格中输入“=(E5*D$2*(1+D$2)^(B$2-A5))/((1+D$2)^(B$2-A5)-1)”；在c6单元格中输入“=E5*D$2”；在d6单元格中输入“=B6-C6”；在e6单元格中输入“=E5-D6”。
房屋贷款还款明细计算表
Finance 贷款额 贷款费用
Loan amount
1,000,000.00
Interest Rate
贷款年利息 贷款月利息
Loan term
5.400% 0.450% 30 等额本金法 等额本金还款法，指每月等额偿还贷款本金，贷款利息随本息逐月递减 每月偿还金额（本+息）逐渐减少。总共偿还的利息比等额本息法要少 合收入高且有一定积蓄的中年人。 月还本金 2,777.78 利息总额 利息 月还 本息和 ####### 7,277.78 4,500.00 7,265.28 4,487.50 7,252.78 4,475.00 7,240.28 4,462.50 7,227.78 4,450.00 7,215.28 4,437.50 7,202.78 4,425.00 7,190.28 4,412.50 7,177.78 4,400.00 7,165.28 4,387.50 7,152.78 4,375.00 7,140.28 4,362.50 7,127.78 4,350.00 7,115.28 4,337.50 7,102.78 4,325.00 7,090.28 4,312.50 7,077.78 4,300.00 7,065.28 4,287.50 7,052.78 4,275.00 7,040.28 4,262.50 7,027.78 4,250.00 7,015.28 4,237.50 7,002.78 4,225.00 6,990.28 4,212.50 6,977.78 4,200.00 6,965.28 4,187.50 6,952.78 4,175.00 6,940.28 4,162.50 6,927.78 4,150.00 6,915.28 4,137.50 6,902.78 4,125.00 6,890.28 4,112.50 6,877.78 4,100.00 6,865.28 4,087.50 6,852.78 4,075.00 6,840.28 4,062.50 6,827.78 4,050.00 6,815.28 4,037.50 6,802.78 4,025.00 6,790.28 4,012.50 6,777.78 4,000.00 6,765.28 3,987.50 6,752.78 3,975.00 6,740.28 3,962.50 6,727.78 3,950.00 6,715.28 3,937.50 6,702.78 3,925.00 6,690.28 3,912.50 6,677.78 3,900.00 6,665.28 3,887.50 6,652.78 3,875.00 6,640.28 3,862.50 6,627.78 3,850.00 6,615.28 3,837.50 6,602.78 3,825.00 6,590.28 3,812.50
贷款还款明细表
年还11473.56元
月还1092.72元
年还13112.64元
9
月还125.32元
年还1503.84元
月还250.64元
年还3007.68元
月还375.96元
年还4511.52元月还501Βιβλιοθήκη 28元年还6015.36元
月还626.60元
年还7519.20元
月还751.92元
年还9023.04元
月还1382.78元
年还16593.36元
月还1580.32元
年还18963.84元
6
月还170.73元
年还2048.76元
月还341.46元
年还4097.52元
月还512.19元
年还6146.28元
月还682.92元
年还8195.04元
月还853.65元
年还10243.80元
月还1024.38元
年还12292.56元
月还814.59元
年还9775.08元
月还930.96元
年还11171.52元
月还1058.19元
年还12698.28元
月还1209.36元
年还14512.32元
8
月还136.59元
年还1639.08元
月还273.18元
年还3278.16元
月还409.77元
年还4917.24元
月还546.36元
年还6556.32元
月还682.95元
年还8195.40元
月还819.54元
年还9834.48元
月还877.24元
年还10526.88元
月还1002.56元
年还12030.72元
按揭贷款分析表和还款明细表
制作按揭贷款分析表模拟运算表应用的范围比较广泛，除了前面所介绍的投资收益预测分析以外，与此类似的，利用模拟运算表制作按揭贷款分析表也是比较常见的一种应用。
假设购买一套住房，需要向银行贷款约15~30万元，分10到30年时间还贷，在不同的贷款利率情况下，要计算等额还款方式下每月所需按揭的费用，可依照如下步骤操作：步骤1 选中A1单元格，单击菜单“数据”→“有效性”，打开“数据有效性”对话框，选中“设置”选项卡。
在“允许”下拉列表中选择“序列”，并在“来源”编辑框中输入各种贷款额度，如“15万元,20万元,25万元,30万元”，各项之间用半角逗号间隔，如图1-1所示，单击“确定”按钮关闭对话框。
在A1单元格数据有效性的下拉列表中选择一个贷款额度，例如“15万元”。
图1-1 设置“数据有效性”步骤2 在C3:C7单元格内输入15年~30年间贷款年限，在D2:L2单元格内输入各种贷款利率，例如本例中使用了从2002年至今历年来的公积金贷款年率，添加行列标题美化格式后显示如图1-2所示：图1-2 设置各项计算参数步骤3 选中C2单元格，输入公式：=PMT(A3/12,A4*12,-LEFT(A1,2)*10000)公式结果显示为错误值“#DIV/0!”，此结果没有实际意义，并不影响模拟运算表的继续操作。
步骤4 选中单元格区域C2:L7，单击菜单“数据”→“模拟运算表”，打开“模拟运算表”对话框，在“输入引用行的单元格”编辑框中输入“$A$3”，在“输入引用列的单元格”编辑框中输入“$A$4”，如图1-3所示：图1-3 设置行列引用单元格步骤5 单击“确定”按钮完成操作，调整数据显示格式后结果如图1-4所示：图1-4 完成后的按揭贷款分析表图1-4中详细显示了贷款15万元的情形下，各种贷款年率和贷款时间所对应的按月等额还款方式的具体按揭金额。
在A1单元格的下拉列表中选择不同的贷款金额选项，模拟运算表中会自动变化显示相应的月度按揭金额。
河北秦皇岛按揭贷款还款计算表
河北秦皇岛按揭贷款还款计算表【原创版】目录1.秦皇岛按揭贷款还款计算表概述2.计算表包含的内容3.计算表的使用方法4.案例分析5.注意事项正文【秦皇岛按揭贷款还款计算表概述】秦皇岛按揭贷款还款计算表是一款帮助购房者计算按揭贷款还款金额的工具，适用于秦皇岛地区的购房者。
通过该计算表，购房者可以快速、准确地了解贷款还款的具体金额，从而更好地规划自己的财务状况。
【计算表包含的内容】秦皇岛按揭贷款还款计算表主要包括以下几个方面的内容：1.贷款金额：购房者所申请的按揭贷款金额。
2.贷款期限：贷款的还款期限，通常包括短期贷款和长期贷款两种类型。
3.贷款利率：贷款的利率，根据银行政策和市场行情不断调整。
4.还款方式：还款方式包括等额本息还款和等额本金还款两种方式。
5.提前还款：购房者选择提前还款时，可以查询提前还款的具体金额和剩余贷款期限。
【计算表的使用方法】1.购房者首先需要确定贷款金额、贷款期限和贷款利率，这些信息可以从银行或其他金融机构获取。
2.打开秦皇岛按揭贷款还款计算表，输入贷款金额、贷款期限和贷款利率。
3.选择还款方式，等额本息还款或等额本金还款。
4.如果购房者选择提前还款，还需输入提前还款金额和提前还款期限。
5.点击计算按钮，即可得到详细的还款计划，包括每期还款金额、剩余贷款金额和总还款金额等。
【案例分析】以贷款金额 30 万元，贷款期限 30 年，贷款利率 4.9% 为例，采用等额本息还款方式，计算结果如下：总还款金额：645616 元首期还款金额：1520 元月还款金额：1520 元【注意事项】1.计算表仅供参考，具体还款金额以银行审批结果为准。
2.贷款利率会根据市场行情和政策调整，购房者需关注银行最新政策。
3.提前还款可能产生额外费用，购房者需提前与银行沟通了解相关政策。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
制作按揭贷款分析表
模拟运算表应用的范围比较广泛，除了前面所介绍的投资收益预测分析以外，与此类似的，利用模拟运算表制作按揭贷款分析表也是比较常见的一种应用。
假设购买一套住房，需要向银行贷款约15~30万元，分10到30年时间还贷，在不同的贷款利率情况下，要计算等额还款方式下每月所需按揭的费用，可依照如下步骤操作：步骤1 选中A1单元格，单击菜单“数据”→“有效性”，打开“数据有效性”对话框，选中“设置”选项卡。
在“允许”下拉列表中选择“序列”，并在“来源”编辑框中输入各种贷款额度，如“15万元,20万元,25万元,30万元”，各项之间用半角逗号间隔，如图1-1所示，单击“确定”按钮关闭对话框。
在A1单元格数据有效性的下拉列表中选择一个贷款额度，例如“15万元”。
图1-1 设置“数据有效性”
步骤2 在C3:C7单元格内输入15年~30年间贷款年限，在D2:L2单元格内输入各种贷款利率，例如本例中使用了从2002年至今历年来的公积金贷款年率，添加行列标题美化格式后显示如图1-2所示：
图1-2 设置各项计算参数
步骤3 选中C2单元格，输入公式：
=PMT(A3/12,A4*12,-LEFT(A1,2)*10000)
公式结果显示为错误值“#DIV/0!”，此结果没有实际意义，并不影响模拟运算表的继续操作。
步骤4 选中单元格区域C2:L7，单击菜单“数据”→“模拟运算表”，打开“模拟运算表”对话框，在“输入引用行的单元格”编辑框中输入“$A$3”，在“输入引用列的单元格”编辑框中输入“$A$4”，如图1-3所示：
图1-3 设置行列引用单元格
步骤5 单击“确定”按钮完成操作，调整数据显示格式后结果如图1-4所示：
图1-4 完成后的按揭贷款分析表
图1-4中详细显示了贷款15万元的情形下，各种贷款年率和贷款时间所对应的按月等额还款方式的具体按揭金额。
在A1单元格的下拉列表中选择不同的贷款金额选项，模拟运算表中会自动变化显示相应的月度按揭金额。
公式解析：
=PMT(A3/12,A4*12,-LEFT(A1,2)*10000)
PMT函数是一个财务函数，用于计算固定利率等额分期付款方式下的每期付款额。
该函数的语法为PMT(rate,nper,pv,fv,type)。
其中rate指的是固定利率，利率的时间单位必须与贷款期限的单位以及最后计算的还款时
间频率单位相一致，在本例中D2:L2区域的利率单位为“年”，最后计算目标为按月还款额，所以需要将年率除以12得到月率。
rate参数指向单元格A3，这个单元格没有实际意义，只作为引用位置。
nper指的是付款的总期数，也就是贷款的期限，在本例中需要将C3:C7中相应的年份乘以12得到月份数，以期与rate参数的单位保持一致。
nper参数指向单元格A4，与上面的A3单元格一样，此单元格也没有实际意义，只用作模拟运算表参数的引用位置。
pv指现值，在贷款模型中即指贷款的总额。
本例中需要从A1单元格的字符串中取出相应的数值同时乘上单位“万”，公式中“LEFT(A1,2)*10000”部分即是起此作用。
fv指终值，在贷款模型中终值为零，可省略。
type指付款方式，为1时代表期初付款，为0或省略时代表期末付款，在贷款模型中一般均为期末付款方式。
关于PMT函数的其他详细内容可参见Excel帮助。
如果想要了解全部按揭期结束总的还款金额，可以直接在C2单元格内将公式改为：
=PMT(A3/12,A4*12,-LEFT(A1,2)*10000)*A4*12
显示结果如图1-5所示：
图1-5 显示按揭到期付款总额
如果想要进一步了解全部还款金额中所包含的利息部分，可将公式修改为：
=PMT(A3/12,A4*12,-LEFT(A1,2)*10000)*A4*12-LEFT(A1,2)*10000
显示结果如图1-6所示：
图1-6 显示按揭到期利息总额
制作贷款还贷明细表
除了了解不同贷款年限、不同贷款利率下的还贷金额情况外，还有不少贷款人对每月按揭款中的本金和利息的分摊情况感兴趣，这同样可以通过模拟运算表来简单的实现。
以2007年9月15日起施行的五年期以下公积金贷款年率4.77%为例，要制作每月按揭还款的明细详表，操作步骤如下：
步骤1 在B1单元格内输入贷款年率4.77%。
步骤2 选中B2单元格，按<Ctrl+1>组合键打开“单元格格式”对话框，选择“数字”选项卡，在“分类”列表框内选择“自定义”，并在“类型”文本框内输入“#万元”，如图2-1所示，单击“确定”按钮关闭对话框。
图2-1 设置单元格格式
步骤3 选中B2单元格，单击菜单“数据”→“有效性”，打开“数据有效性”对话框，选中“设置”选项卡。
在“允许”下拉列表中选择“序列”，并在“来源”编辑框中输入各种贷款额度，如“15,20,25,30”，各项之间用半角逗号间隔，如图2-2所示。
单击“确定”按钮关闭对话框，然后在数据有效性下拉列表中选择一个金额，例如30。
图2-2 设置数据有效性
步骤4 选中B3单元格，参照步骤2设置单元格格式为“#年”，然后参照步骤3设置数据有效性序列为“1,2,3,4,5”。
完成后可在A1:A3单元格添加标题增强表格可读性，显示如图2-3所示：
图2-3 设置相关运算参数
注意：步骤2~步骤4之中设置数据有效性的相关操作是为了提供更多可选参数，如果用户能够确定准确的相应贷款信息，则可以省略这些步骤。
与此情况类似，这其中使用单元格格式设置的相关操作是为了增强表格数据的可读性，用户也可以有选择的省略操作。
步骤5 选中D3单元格，在单元格内输入公式：
=IF(ROW(A1)>$B$3*12,"",ROW(A1))
然后复制公式向下填充至D62单元格。
此列数据为模拟运算表的变量参数引用列，可将D3:D62区域的单元格格式设置为“第#个月”增强数据的可读性。
步骤6 在E2单元格内输入公式“=PPMT(B1/12,B4,B3*12,-B2*10000)”，在F2单元格内输入公式“=IPMT(B1/12,B4,B3*12,-B2*10000)”
步骤7 选中单元格区域D2:F62，单击菜单“数据”→“模拟运算表”，打开“模拟运算表”对话框，在“输入引用列的单元格”编辑框中输入“$B$4”，如图2-4所示：
图2-4 创建单变量模拟运算表
步骤8 单击“确定”按钮关闭对话框，完成模拟运算表的创建。
在结果区域的末尾部分会显示“#VALUE!”的错误值，影响表格的外观，可通过设置单元格格式的方式进行隐藏：选中单元格区域E3:F62，参考步骤2的操作自定义单元格格式为“[黑色]#.00”，然后设置字体颜色与单元格底纹颜色相同即可。
同理，E2和F2单元格内的错误值“#NUM!”也可以通过同样方法进行隐藏美化。
在进行格式美化并添加表头标题后，表格显示如图2-5所示：
图2-5 完善后的还贷明细表
当用户在B2单元格或B3单元格的数据有效性下拉列表中选择其他的贷款金额和贷款年限时，无需修改模拟运算表，自动显示对应的结果。
例如在B2单元格选择15万元，在B3单元格选择1年，显示如图2-6所示：
图2-6 修改初始参数后自动显示相应结果
公式解析：
D3单元格公式：=IF(ROW(A1)>$B$3*12,"",ROW(A1))
ROW函数语法为ROW(reference)，返回引用的行号，“ROW(A1)>$B$3*12”部分即是将单元格所在行的行数与B3单元格年限所对应的月份数进行对比。
此公式可以根据B3单元格所选择的贷款年限，在D列中的模拟运算表参数区域中自动生成有效的月份数，超出年限的无效月份数则自动显示空白单元格。
模拟运算表的运算公式：
=PPMT(B1/12,B4,B3*12,-B2*10000)
=IPMT(B1/12,B4,B3*12,-B2*10000)
PPMT函数和IPMT函数均为财务函数，分别求取基于固定利率及等额分期付款方式下、给定期数的本金偿还额和利息偿还额。
两个函数的语法分别为：
PPMT(rate,per,nper,pv,fv,type)
IPMT(rate,per,nper,pv,fv,type)
其中参数per表示用于计算本息的具体贷款期数，其他参数含义可参考前文，此处不再赘述。
本篇文章节选自《Excel数据处理与分析实战技巧精粹》ISBN：9787115182326人民邮电出版社。