錦子老師

雖然錦子我很窮，但是我一直夢想有一個Big House，大大的房間，落地窗，游泳池……。

打住，別做白日夢了，還是好好學習Excel吧。這些年身邊越來越多的人選擇做房奴，錦子這期就帶大家用Excel來算算月供奉金多少吧。

1、使用“模擬運算表”進行貸款方案設計

許多人在買房貸款時需要考慮總共要貸多少錢、貸款多長時間、每個月可以還多少，這種情況下我們通常會使用Excel中的模擬分析工具。

現假設總貸款500萬，貸款年限30年，按照等額還款方式，可以利用PMT函數計算月供金額。

PMT(Rate, Nper, Pv, Fv, Type)。

PMT（貸款利率，付款總期數，現值（本金），餘值，付款時間是期初還是期末（0代表後付，可省略，1代表先付））

步驟1：點取A5儲存格輸入公式「=PMT(C2/12,B2*12,-A2)」。

當貸款總額為500萬，期限30年，利率為4.90%時，月還款金額為23870.76元。（大家能接受不？）但是，實際情況往往更複雜。

在真正買房的時候，為了綜合衡量自身的還款能力，選擇合適的貸款方案，我們需要考慮不同貸款年限和不同貸款金額的月供變動情況。

假定首付後能夠接受的貸款總額在200萬-800萬之間，貸款期限在10年-30年的之間，那麼在不同貸款總額、不同貸款期限條件下，每月還款金額多少呢？

步驟2：首先，以月還款金額A5為基準儲存格，輸入一組從200萬-800萬以50萬為間隔的縱向等差序列，再生成一組10-30以5為間隔的橫向等差序列。

步驟3：框選A5:F18儲存格範圍，點取「資料 >樞紐分析 > 運算列表」選項。

步驟4：在【運算列表】對話方塊「列變數儲存格」欄位輸入$B$2，「欄變數儲存格」欄位輸入$A$2，如下圖所示。

注意：運算列表的列是「貸款年數」這組變數，因此「列變數儲存格」欄位參照了「貸款年數」這個變數所在的儲存格B2；

運算列表的欄是「貸款金額」這組變數，因此「欄變數儲存格」欄位參照了「貸款金額」這個變數所在的儲存格A2。

在生成的運算列表範圍，可以點取「常用 > 設定格式化條件 > 色階」進行儲存格處理，綠色、黃色、紅色分別代表較高、中等、較低的月供金額，這樣我們在買房的時候就能根據自身的承受能力進行決策了。

以目前的工資水準來看，錦子老師能夠接受的就是200萬30年，可是這樣首付的壓力很大啊，瞬間悲傷逆流成河~

2、還款方式是等額本息還是等額本金？

在1中我們計算的前提是使用了等額本息的還款方式，其實在真正買房貸款時，有兩種還款方式，一種是等額本息，就是每期本息之和為一個固定金額；第二種是等額本金，即每期償還的本金相等，利息隨著要償還的本金越來越少而遞減。

假設錦子老師要買房啦，價值100萬，首付30萬，貸款70萬，貸款30年，年利率4.90%，現有等額本息和等額本金兩種還款方式，我應該選擇哪一種呢？

等額本息還款方式：

B7儲存格(償還本金)輸入公式「=PPMT($C$2/12,A7,$B$2*12,-$A$2)」。

C7儲存格(償還利息)輸入公式「=IPMT($C$2/12,A7,$B$2*12,-$A$2)」。

D7儲存格(本息合計)輸入公式「=PMT($C$2/12,$B$2*12,-$A$2)」或者「=償還本金+償還利息」。

E7儲存格(貨款餘額)輸入公式「=$A$2-SUM(B$7:B7)」。

等額本金償還方式：

F7儲存格(償還本金)輸入公式「=$A$2/360」。

G7儲存格(償還利息=上期餘額*利率)輸入公式「=($A$2-SUM(F$7:F7))*$C$2/12」。

H7儲存格(本息合計=償還本金+償還利息)輸入公式「=F7+G7」。

I7儲存格(貨款餘額)輸入公式「=($A$2-SUM(F$7:F7))」。

採用等額本息還款方式30年下來總共償還金額為1,337,431.34元，採用等額本金還款方式30年下來總共還款金額為1,213,070.833元。

由於本金都是70萬，所以可以得出等額本息比等額本金多付利息：124360.507元。

好嚇人啊，30年整整多了12萬！

我們再借用圖表對月供金額進行分析。

等額本金方式因為利息不斷減少，所以月還款額不斷地降低，但是前期月還款壓力比較大，等額本息前期的壓力相對較小。

從台幣貶值的角度來看，等額本息每個月還款固定，從現值的角度來講是逐步下降的，而等額本金初期還款多，現值高，可能不利於投資。

所以如果是買房自住，儘量選擇等額本金，畢竟前期壓力大點兒沒啥，後期越來越少，總利息也少很多；如果是投資的話，儘量選擇等額本息，既能緩解創業前期的資金壓力，又能體現錢的時間價值。

各位要買房的親朋好友們，你們準備好（錢）了嗎？

總結：

（1）認識「運算列表」這個不常用，但是很強大的假設工具。

（2）學會一些最基礎的財務函數。

當然，貼心的錦子老師還是為大家準備了原始檔案哦，計算結果都在裡面，可以直接套用。