Microsoft Office/贷款还款摊销表

目标

学生将能够 (TSWBAT)

格式化工作表的各个部分以方便阅读
创建全局引用并在公式中使用它们
使用 PMT 函数确定贷款的每月付款
使用 PV 函数确定贷款的现值
分析工作表中的数据
创建摊销表
在工作表中创建超链接
设置打印区域并打印工作表的一部分

词汇

超链接 - 从一个文档链接到另一个文档、网页到另一个网页，或文档的一部分链接到同一文档的另一部分

全局单元格名称 - 给工作簿中的一个单元格起一个名字，可以在任何工作表中使用

PMT 函数 - 根据利率、付款金额和贷款金额确定贷款的每月付款。
-PMT(每月利率/12, 12*年数, 贷款金额)

数据表 - 一组显示公式答案的单元格

单输入数据表 - 公式中的一个值发生变化
双输入数据表 - 公式中的两个值发生变化
输入值 - 放在数据表公式中的值

摊销表 - 一张表格，显示贷款的期初余额、贷款的期末余额、每年的本金支付金额和利息支付金额。

PV 函数 - 确定贷款的现值，或在给定时间段结束时您还需要支付多少

年金 - 一系列固定付款，例如您每月支付的贷款款项

课程

付款计算器

标题

标题
- 在 B1 单元格中输入 “付款计算器”
  - 合并并居中 B1:E1 单元格
  - 根据您的喜好格式化字体 - 大小 16pt
- 在 B2 单元格中输入 “日期”
- 在 B3 单元格中输入 “项目”
- 在 B4 单元格中输入 “价格”
- 在 B5 单元格中输入 “首付”
- 在 B6 单元格中输入 “贷款金额”
- 在 D2 单元格中输入 “利率”
- 在 D3 单元格中输入 “年数”
- 在 D4 单元格中输入 “每月付款”
- 在 D5 单元格中输入 “总利息”
- 在 D6 单元格中输入 “总成本”
格式化
- 使每列的宽度都足以容纳标签
- 将所有标签文本加粗
将工作表名称更改为 “汽车贷款”
将电子表格保存到您的存储空间，并命名为 “贷款计算器”

今日日期函数

在 C2 单元格中输入
=now()

格式化

使用以下方法格式化电子表格的第一部分

外框线
列分隔符
填充颜色
- 转到“开始”选项卡
- 转到“字体”组
- 单击“边框”下拉按钮
- 选择“更多边框”
确保所有颜色搭配在一起，您可以阅读文本

将数据格式化为适当的类型

货币
- 价格
- 首付
- 贷款金额
- 每月付款
- 总利息
- 总成本
百分比
- 利率
常规数字
- 年数
日期
- 日期

输入贷款数据

找到您想要购买的汽车及其价格

转到凯利蓝皮书
- 找到您想要购买的新车
- 找到价格
转到 BankRate.com
- 找到汽车贷款的利率
- 记住该利率的年数
确定您现在是否有任何钱可以作为首付
将您收集到的数据输入您的电子表格
- C3 = 您要购买的汽车名称
- C4 = 汽车价格
- C5 = 您的首付款（如果有），如果没有则为 0
- E2 = 汽车贷款的利率
- E3 = 贷款年数

创建单元格全局名称

单击您要命名的单元格 - C4
转到“公式”选项卡
转到“定义名称”组
单击“定义名称”
确保名称是您想要的，然后单击“确定”
- C4 = 价格
- C5 = 首付
- C6 = 贷款金额

等等...

计算贷款金额

您需要借出的贷款金额是汽车价格减去您在购买汽车时支付的金额或首付款。

转到 C6 单元格
= 价格 - 首付

或

= C4 - C5

确定每月付款

每月还款是指您每月支付的金额，用于偿还汽车的购买价格。该金额的计算会考虑贷款利率、您偿还贷款所需的年数以及贷款总额。

转到单元格 E4
=-pmt(rate/12, 12*years, loan_amount

或

=-pmt(E2/12, 12*E3, C6

确定总利息

总利息是指银行借给您购买汽车的金额所产生的总收益。要进行此计算，您需要知道贷款金额以及您在多年内支付的总金额。您可以通过将年数乘以 12 来计算月份，然后乘以每月还款金额。

转到单元格 E5
=12*years*monthly_payment-loan_amount

或

=12*E3*E4-C6

确定总成本

汽车总成本是指您支付的金额，包括首付和利息。您可以通过将汽车价格加上总利息来计算。

转到单元格 E6
=price+total_interest

或

=C4+E5

分析您的数据

返回凯利蓝皮书
查找相同车型的一年旧的价格
返回 BankRate.com
查找二手车的利率
更改您的数据
分析二手车与新车的总支付金额差异

利率表

标题

B7 = 利率表
- 格式与 B1 相同
- 将 B7:E7 居中
B8 = 利率
C8 = 每月还款
D8 = 总利息
E8 = 总成本

将所有标题设置为粗体

使用填充柄创建一个序列

B10 = 4.00%
B11 = 4.25%
选择单元格 B10:B11
使用填充柄向下拖动，直到到达 8.00%

添加初始值

C9 = E4
D9 = E5
E9 = E6

定义数据表

选择 B9:E26
转到“数据”选项卡
转到“数据工具”组
单击“假设分析”下拉菜单
选择“数据表”
单击“列输入单元格”
单击单元格 E2 中的利率
单击“确定”

格式

选择单元格 B8:E26
使用与上面部分类似的阴影和边框，但使用不同的颜色

条件格式

选择 B10:B26
转到“开始”选项卡
转到“样式”组
单击“条件格式”下拉菜单
单击“突出显示单元格规则 - 等于”
单击“格式化等于”单元格中的框
单击单元格 E2
单击“确定”

更改单元格 E2 中的利率，观察颜色部分的变化。
注意颜色比率会发生变化，但数据表中的值不会。您需要重新选择单元格并重新执行数据表步骤。

摊销表

标题

G1 = 摊销表
- 将 G2:K2 居中
- 字体样式和颜色与其他两个部分相同
G2 = 年份
H2 = 期初余额
I2 = 期末余额
J2 = 本金偿还额
K2 = 利息支付额
将 H2:K2 的文字换行

年份序列

G3 = 1
使用填充柄向下填充到 G26
单击智能按钮 - 自动填充选项
单击“填充序列”

期初余额公式

H3 =C6

期末余额公式

I3 =PV($E$2/12, 12*($E$3-G3), -$E$4)
I4 =IF(H4<=0,0,PV($E$2/12, 12*($E$3-G4), -$E$4))

本金偿还额公式

J3 =H3-I3
J4 =H4-I4

利息支付额公式

K3 =12*$E$4-J3
K4 =IF(H4<=0, 0, 12*$E$4-J4)

使用公式填充表格

选择 I4:K4
向下填充

完成期初余额

H4 = I3
向下填充

摊销总额

I28 = 小计
I29 = 首付
I30 = 总成本
J28 = 自动求和 J 列
K28 = 自动求和 K 列
K29 = C5
K30 = J28 + K28 + K29

格式化

格式与其他部分相同，但使用不同的颜色

超链接

放置图片

转到单元格 H25
在剪贴画或互联网中找到代表您的汽车的图片
转到插入选项卡
根据您使用的是图片还是剪贴画，转到相应选项
找到您的图片

创建一个链接图片

右键单击图片，然后转到超链接
在地址栏中输入网站的 URL - http://www.kbb.com
找到您要购买的汽车的网页

设置打印区域

选择要打印的区域 - 单击并拖动
转到页面布局选项卡
转到页面设置组
单击打印区域的下拉菜单
单击设置打印区域
打印预览，您将看到只有该区域会被打印

您可以从同一个下拉菜单中清除打印区域或更改打印区域。

项目

编辑您创建的电子表格，以购买您在 http://www.remax.com/ 上找到的梦想之家。
从银行（例如 http://www.boxhomeloans.com/）找到贷款利率。
决定您将采取哪种抵押贷款，以选择正确的贷款利率。
更改页面上的图片，并将其链接到您在 remax.com 上找到的梦想之家。

评分标准