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 上找到的夢想之家。

評分標準