您現在的位置是:首頁 > 棋牌
我竟然使用Excel製作了一款房貸計算器
- 由 EXCEL辦公實戰 發表于 棋牌
- 2022-09-13
excel怎麼新增計算器
今天我們來分享一下財務專業函式,具體透過跟大家聯絡緊密的房貸計算器來演示!
今天所涉及到的函式,屬於財務專業函式,需要有一定的財務基礎或者金融學基礎更加好理解!
我們先看一下,我們想要做的效果!
效果
:
等額本息
還款法下每期還款情況!
▼ 動畫演示-引數可調節
其中涉及到的
知識點
:
1、財務函式有PMT、IPMT和PPMT
2、常用函式ROW、IF和SUM
3、控制元件的使用
製作教程
基礎1 | 控制元件的使用
> 【開發工具】- 【插入】 - 【數值調節按鈕】,在合適的位置,畫出合適的大小
> 動畫演示相關設定操作!
▼ 動畫演示-數值調節按鈕設定
操作說明:
1、插入按鈕,右擊 - 【設定控制元件格式】
2、在【控制】中設定,最大值、最小值,步長 和 繫結的單元格
單元格連結
:表示控制元件關聯的單元格,以後數值的調整都會反映在此單元格中!
步長:
表示每次點選按鈕增加或者減少的數值,預設1,這裡調整成5,表示房貸的年限,每次為5,根據實際需要設定即可!~
其他按鈕同理,不再一一演示,對於一些特殊的數值,小數等,可以透過其他單元格過渡,再設定值!
比如這裡的百分比:藉助H3過度,解決控制元件不能設定小數等問題!
同樣,對於一些特別大的數值也需要藉助過度單元格處理!
解決上限為30000的問題!
那麼對應的金額、利率和年限三個可調節引數我們就設定OK了,下面我們來進行明細表的設定!
基礎02 | 明細表設定-財務函式應用
期初或者年份設定:
=IF(ROW(A1)<=$D$3*12,ROW(A1),“”)
操作說明
:利用
名稱框
,點位足夠的區域,按下
Ctrl+D
來填充公式!
我們先跳過其他欄位,先說核心的三個
本金、利息 和 本息和!
函式引數說明:
1、本金對應的函式:
PPMT(利率,第幾期,總期數,現值,[終值],[型別])
2、利息對應的函式:
IPMT(利率,第幾期,總期數,現值,[終值],[型別])
3、本息和對應的函式:
PMT (利率,總期數,現值,[終值],[型別])
4、關係:
PMT = IPMT + PPMT
我們先列出來,是因為他們有太多相同的引數,方便我們高效學習!
以上引數中,常見的現值和終值,財務的朋友應該都沒有問題,如果你不懂財務,那麼你這裡可以簡單理解為:
現值
:貸款本金
終值
:N期獲得
引數中的方括號表示該引數可以選擇使用!
主要說一下 【型別】:期初(1)OR期末(0),預設都是期末支付,也符合一般的習慣!
其中
PMT
我們可以理解為
年金函式
,因為他的性質和年金很像!
下面我們就在實際案例中使用看一下!
本文由“壹伴編輯器”提供技術支援
償還的本息和:
=PMT($C$3/12,$D$3*12,$B$3)
上面的引數都是年化的,下面我們是按月還款,所以我們要折算成月份維度!
由於是償還,所以預設就是負數,如果你不習慣,可以調整一下!這裡月利率已經寫進公式,所以後面我們刪除月利率列!
償還的本金:
=PPMT($C$3/12,$B6,$D$3*12,$B$3)
相對於PMT,本金和利息都多了一個第二引數,那就是目前是第二期!
償還的利息:
=IPMT($C$3/12,$B6,$D$3*12,$B$3)
和本息引數完全一樣,只是一個是I開頭一個是P,非常的簡單!
期初和期末,
期初=上期期末。期末=期初-本期償還的本金
壹伴輯器”提供技術支援
期初:
=IF(B6=1,$B$3,G5)
期末:
=C6+E6
由於是負數,所以是相加,本質是期初-本期償還的本金!
本文由“壹伴編輯器”提供技術支援
公式都準備好後,我們選擇區域,
Ctrl+D
一次性填充即可!
▼ 動畫演示-快速填充公式
由於超過了期限或報錯,所以我們可以判斷一下,期初是否為空來容錯處理!
都加上IF容錯即可!非本次重點,我們就不再一一演示!
最後成品如下:快去動手試試吧!大家也可以網上查詢一下房貸計算器,看一下結果是否一致,小編已校驗!
OK,今天我們的內容就先到這裡!三個財務領域的函式你學會了嗎?
感謝
(收藏、點贊、轉發),您的鼓勵是小編不斷創作的動力!