Excel的生活理財術!房屋貸款的每月還多少?你可以這樣計算


(編按:作者熱愛歷史,這次Excel教學以曹操和曹丕為主角,帶領大家學會靠Excel試算房貸,會使用到「分析藍本管理員」和函數PMT。點擊文內圖片都可以放大顯示)

曹操年邁時,要求曹丕在首都置產定居、穩定軍心,才可以擔綱接班人。於是,曹丕開始評估魏國各家銀行的房貸頭期款利率,打算選擇最好的方案,買一個風水寶地。

1. 下載各家銀行的貸款方案

曹丕看中一棟一千萬的房子,這是剛蓋好的預售屋,他打算貸款三百萬。接著,他到不動產資訊平台,點選「查詢」,得知各家銀行的貸款方案。

內政部不動產資訊平台

這些資料可以匯出成Excel檔。

2. 製作房屋貸款評估表格

建立一個房屋貸款評估表,裡面包含四個項目。
貸款額度:你自己決定要跟銀行貸多少錢。
房屋利率:銀行給予的貸款利率。
清償期限:銀行貸款方案規定的還款期限。
每月付款:通過計算,才能知道你每個月要償還多少錢。

這些項目對應的儲存格必須設定好儲存格格式,例如貸款額度對應的B2,儲存格格式要設為「貨幣」,房屋利率對應的B3,儲存格格式要設為「百分比」,清償期限對應的B4,儲存格格式可設為「數值」,每月付款對應的B5,儲存格格式要設為「貨幣」。

經理人

3. 將銀行的房貸方案,設定為分析藍本

從內政部資料挑出有興趣銀行的貸款方案,知道各別房貸利率和清償期限。之後就讓Excel幫你計算每家銀行的方案,究竟要你月付多少錢了。

Excel有個一次計算多種方案的功能,稱為「分析藍本管理員」,你可以點選「資料」→「模擬分析」找到此功能。

經理人

點選此功能後,會跳出新的視窗,讓你輸入各種房貸方案的資料。操作方法如下:「新增」→輸入「分析藍本名稱」(可以輸入銀行名稱)→選取「變數儲存格」(在此指B2:B4,也就是貸款額度、利率和清償期限)

經理人

我們先設定渣打銀行的貸款方案。按下確定後,會再跳出新視窗讓你輸入變數值,依序輸入你要貸款$3,000,000,年利率1.29%,分兩年24期還完,按下確定就新增完成。

經理人

重複上面的操作方式,將第一銀行、中國信託的方案也輸入進分析藍本管理員。設定完後,再點選「顯示」,確定一下是否成功。

經理人

4. 插入函數PMT,計算每期的還款金額

在每月付款金額的B5,插入函數PMT,這個函數專門用來計算基於固定利率、分期付款的方案,究竟該每月付出多少金額。

經理人

PMT 的語法說明如下:
Rate必填,在此是指利率,輸入對應儲存格$B$3。(金錢符號表示指定欄和列,避免公式跑掉)
Nper必填,在此是貸款清償的期限,輸入對應儲存格$B$4。
Pv 必填,這是你還款金額的現值,也是我們俗稱的本金,輸入對應儲存格$B$2。
Fv 這是最後一次付款完成後,所能獲得的未來值或現金餘額。 如果省略不填,則假設其值為 0。

公式設定完畢後,再回到分析藍本管理員,點選「摘要」,目標儲存格為B5(意即請計算B5的值)。

經理人

Excel會自動生成不同方案的還款金額報表!

經理人

《經理人月刊》6期+特刊《商業圖表製作與應用》懂得運用Excel報表,提升職場力!>>

延伸閱讀 /

1. Excel的高效秘訣!超好用的公式VLOOKUP,不同表單也能快速整合

2. Excel聰明理財兩大招!連結網頁資料,直接匯入股票數據、分析趨勢

延伸課程 / 「資深經理人教你,超強Excel數據力」一張數字圖表,成功說服老闆和客戶!

商業數據分析與圖像表達│經理人商學院

地址: 臺北市大安區新生南路3段30號1F
地點: 福華國際文教會館 前瞻廳
時間: 2017-02-22-2017-02-22
「報表分析」、「圖像說服」,職場必備的核心能力! 從資料分析定義客戶需求,並轉化成圖表溝通商業目的,完美強化銷售業績。

圖像式資料分析進階班【限量席次】│經理人商學院

地址: 台北市光復南路102號3樓
地點: Your Space
時間: 2017-03-03-2017-03-03
用Excel Power BI分析數據,直接產製「視覺報表」、「商業簡報」,讓工作效率如虎添翼!

Steve 蘇書平

前台灣微軟資深業務應用經理

曾於台灣微軟管理雲端IoT物聯網業務,具18年以上資訊產業工作經歷,範疇涵蓋研發、行銷、專案管理、客服等部門主管。同為先行智庫執行長、為你而讀共同推動者、台大TEC創新創業中心業師、台大管理碩士學分班創新創業教師。

延伸閱讀