編按：哈嘍，大家好！哪種房貸還款方式更划算？用Excel算算就知道了。房貸有等額本金和等額本息兩種還款方式。等額本金還款，每月還的金額不一致，第一月最多，以後逐月減少；等額本息還款，每月還的金額一樣。哪中划算？今天我們將用PMT()函數、PPMT()函數、IPMT()函數、CUMIPMT()函數、CUMPRINC()函數來帶大家了解貸款的那些事！學習更多技巧，請收藏關注部落窩教育excel圖文教程。
在貨幣借貸的過程中，為了突出貨幣的時間價值，通常借方都需要向貸方支付一定比例的利息以獲取資金的使用權，而借貸行為是日常工作中很容易產生的一種經濟行為，在借貸過程中必須要摸清本金和利息的關係，才能準確明白資金的來龍去脈。分期付款或還款已不再是企業貸款的專利，現在很多人也開始分期支付一些較大額的款項，如房貸、車貸。在銀行的分期付款過程中，可以採用等額本息法和等額本金法兩種還款方式。不同的還款方式，本金和利息的變化也不同。哪種划算呢？
一、等額本金法每期還款額的計算
等額本金還款的特點是每個月歸還相同的本金，也就是將貸款本金平均分在每一期還款中。每月歸還的本金為貸款總額÷還款期數。
等額本金每個月要歸還的利息是貸款餘額在上一個月所產生的利息。也就是說，隨著還款的進行，所欠的貸款越來越少，那麼每個月需要還給銀行的利息就越來越少。
舉個例子，某人以5.25%的利率貸款50萬元，計劃以等額本金的方式分5年還清，要計算他每月需要還款的總金額，以及其中包括的本金和利息。具體步驟如下：
① 合併A6:D6單元格，輸入「還款明細數據」，在A7:D7單元格分別輸入「期次」「還款額」「本金」「利息」，在A8單元格輸入數字「1」，單擊滑鼠右鍵，打開「設置單元格格式」對話框，在「自定義」選項卡中設置單元格格式代碼為"第"00"期"。這裡的「第01期」只是一個障眼法，當選擇A8單元格後，在編輯欄中即可看到它的真實值，實際上是數字1，而非文本「第01期」。選中A8單元格，按住Ctrl鍵，向下填充公式至A67單元格。
② 在C8單元格中輸入公式「=$A$3/$C$3/12」，雙擊C8單元格右下角的自動填充柄，向下填充公式至C67單元格，可以得到各期還款額的本金。該公式用於計算單元格對應的還款期次的還款本金，每個月應該歸還的本金就是50÷5÷12≈0.83萬元。
③ 在D8單元格中輸入公式「=($A$3-(A8-1)*C8)*$B$3/12」，雙擊D8單元格右下角的自動填充柄，向下填充公式至D67單元格，可以得到各期還款額的利息。「$A$3-(A8-1)*C8」為貸款餘額，「$B$3/12」可以將給定的年利率轉化為月利息。
④ 在B8單元格中輸入公式「=C8+D8」，雙擊B8單元格右下角的自動填充柄，向下填充公式至B67單元格，可以得到各期總還款額。總還款額為本金與利息之和。
從表中可以看出等額本金的還款方式，每個月的還款本金是相同的，但是利息會遞減。最初每月還款比較多，越到後來貸款餘額越少，利息越少，每月還款額逐漸減少。
二、等額本息法中每期還款額的計算
等額本息法分期付款是指在欠款償還期間，每期支付相等的金額，直到所有欠款還清，如果要計算等額本息法中每一期應還的金額，可以使用PMT()函數。該函數的語法格式為：
PMT(rate,nper,pv,[fv],[type])
PMT()包含3個必選參數rate、nper和pv，以及兩個可選參數fv和type，各參數意義如下：
? rate：各期利率的數字，通常以百分比形式出現，在整個貸款期間保持不變。
? nper：貸款所需償還的總次數，通常以月為單位。
? pv：在此函數中表示貸款的總額。
? fv：在此函數中表示最後一次還款後的剩餘金額（通常為0，表示全部還清）。
? type：指定各期的付款時間是在期初（用數字1表示）還是期末（用數字0表示）。
如果要計算每期還款額中包含的本金，可以使用PPMT()函數來完成，其語法格式為：
PPMT(rate,per,nper,pv,[fv],[type])
如果要計算在相同條件下，每期還款額中包含的利息金額，則可以使用IPMT()函數來完成，其語法格式為：
IPMT(rate,per,nper,pv,[fv],[type])
從這兩個函數的語法格式上可以看出，它們的語法結構完全相同。都包含4個必選參數rate、per、nper和pv，以及兩個可選參數fv和type，各參數意義如下：
rate：貸款或投資期間的固定利率。
per：要計算本金（或利息）的期次，必須在1-nper之間。
nper：付款總期數。
pv：在這兩個函數中可表示貸款的總額（或投資本金）。
fv：最後一次付款後剩餘金額（通常為0）。
type：指定各期的付款時間是在期初（用數字1表示）還是期末（用數字0表示）。
還是用上個例子，某人以5.25%的利率貸款50萬元，計劃以等額本息的方式分5年還清，要計算他每月需要還款的總金額，以及其中包括的本金和利息。
計算等額本息還款法中各期的還款額，可以使用PMT()函數，計算還款金額中包含的本金，可使用PPMT()函數，而在得到本金後，可以通過還款額-本金的方法得到其中包含的利息，也可以直接使用IPMT()函數來取得。具體步驟如下：
① 與等額本金法的步驟①一致，搭建數據基礎框架。
② 在C7單元格輸入公式「=PMT($C$3/12,$D$3*12,-$B$3)」，雙擊C7單元格右下角的自動填充柄，向下填充公式至C66單元格，可以得到各期還款總額。「$C$3/12」為各期利率數據，「$D$3*12」為總期數，「-$B$3」為貸款總額。這裡為了得到正數的本金數字，所以給貸款總額添加了負號。
③ 在D7單元格中輸入公式「=PPMT($C$3/12,B7,$D$3*12,-$B$3)」，雙擊D7單元格右下角的自動填充柄，向下填充公式至D66單元格，可以得到各期還款額的本金。該公式用於求得單元格對應的還款期次中的還款本金，「$C$3/12」和「$D$3*12」分別代表將給定的年利率轉化為月利息，以及將還款年限轉化為還款月數。這裡為了得到正數的本金數字，所以給B3單元格的值添加了負號。
④ 在E7單元格中輸入公式「=IPMT($C$3/12,B7,$D$3*12,-$B$3)」，雙擊E7單元格右下角的自動填充柄，向下填充公式至E66單元格，可以得到各期還款額的利息。該公式與上一個公式的參數完全相同，但求取的結果是該還款期次中的利息。
在固定利率的情況下，如果採用等額本息法還款，在每一期的還款金額中，利息在不斷減少，而本金會不斷增加。學習更多技巧，請收藏關注部落窩教育excel圖文教程。
在表中匯總一下向銀行支付的還款總額，可以看出等額本金還款支出的總額更少。如果不考慮通貨膨脹的影響，等額本金還款方式更划算。無論是等額本息還是等額本金的還款方式，每個月的還款都由本金和利息兩部分構成，其中的利息是未歸還的貸款金額在上一個月的時間內產生的利息。在任何時候提前還款，都會減少後續利息的產生，所以僅從利息角度看，提前還款並不會吃虧。提前還款的的確確可以減少利息，但是如果算上有些銀行對提前還款收取違約金、通貨膨脹等因素，提前還款不見得合算。
三、信用卡消費的貓膩
隨著信用卡越來越流行，很多年輕人都養成了提前消費的習慣，當消費達到一定金額後，銀行就可能會提供免息分期付款業務，但這裡面卻有一些貓膩。首先這裡的免息確實是不需要利息，但卻多出了一項手續費率，其實與利息的作用是相同的，並且普遍高於銀行同期貸款利息。
在信用卡等額分期還款中，銀行計算的每期還款額公式為：分期總額/還款期數+分期總額*各期手續費率。從計算公式中可以看出每月所還的利息並不會隨著還入本金的增加而減少。
例如同樣的50萬元分5年還，手續費率為5.25%，使用PMT()函數計算的各期還款額和使用信用卡分期還款公式計算的各期還款額並不相等。
從圖中可以看出，在相同的貸款金額、利率和還款期限下，信用卡分期還款的各期還款額明顯高於銀行貸款的還款額，這是因為在信用卡分期中，並未考慮貨幣的時間價值，及已經還入的本金依然在被記利息，即從開始還款到還款結束，要支付的利息始終都是貸款原始金額的利息。而正常的財務處理中，已經還款的本金是不能被計入利息的（如PMT函數計算的結果），而信用卡分期主要就是賺取客戶的這一部分差額，這也是為什麼信用卡可以直接貸款，而向銀行貸款手續非常麻煩的原因。
四、付款期間的本金與利息的關係
在等額本息法分期付款的借貸行為中，如果要得到兩次付款期間產生的總利息，可以使用CUMIPMT()函數來完成，其語法格式為：
CUMIPMT（rate,nper,pv,start_period,end_period,type）
同樣的條件下，如果要計算兩次還款期間內總共償還的本金，則可以使用CUMPRINC()函數來完成，其語法結構為：
CUMPRINC（rate,nper,pv,start_period,end_period,type）
從兩個函數的語法格式上可以看出，兩個函數的參數完全相同，都包含相同的6個必選參數，各參數意義如下：
? rate：支付期間的固定利率。
? nper：付款總期數。
? pv：在這兩個函數中可表示貸款的總額（或投資本金）。
? Start_period：計算利息或本金的首期，總付款基數從1開始計數。
? end_period：計算利息或本金的末期。
? type：指定付款時間是在期初（用數字1表示）還是期末（用數字0表示）。
在使用這兩個函數的過程中，各參數需要遵循如下幾個規則：
① 函數的rate和nper必須具有相同的單位，如年利率對應的期限為年，月利率對應的期限為月。
② rate，nper，pv都必須大於等於1，否則函數返回錯誤值#NUM!。
③ start_period，end_period都必須大於等於1，且start_period不能大於end_period。
④ type參數只能是0或1，若是其他值，則函數返回錯誤值#NUM!。
舉個例子，某信貸公司想快速計算用戶在任意期間所需償還的利息和本金，可以通過EXCEL函數來完成。
要計算兩個指定期間的累積利息和本金，可分別是使用CUMIPMT()函數和CUMPRINC()函數求得。具體步驟如下：
① 輸入基礎數據，選中C5、E5單元格，打開「設置單元格格式」對話框，在「自定義」選項卡中設置單元格格式代碼為"第"00"期"。
② 在「開發工具」選項卡下點擊「插入」，將「表單控制項」下的數值調節鈕（窗體控制項），繪製到C5單元格右側，右擊「設置控制項格式」，將最小值設置為「1」，因為還款次數共60次，所以最大值設置為「60」，步長為「1」，單元格連結為「$C$5」，點擊「確定」按鈕。按照同樣的方法在E7單元格右側插入數值調節鈕，唯一不同的是單元格連結為「$E$5」。
③ 選中C6單元格，輸入公式「=IFERROR(ABS(CUMIPMT(D3/12,E3,B3,C5,E5,0)),"參數錯誤")」，按Enter鍵確認。首先通過函數求得該項貸款從C5單元格指定的期數至E5單元格指定的期數之間應支付的利息，再用ABS()函數對返回的結果取絕對值，最後用IFERROR()函數來檢測錯誤，並根據情況返回具體數值或返回「參數錯誤」文本。
④ 選中E6單元格，輸入公式「=IFERROR(ABS(CUMPRINC(D3/12,E3,B3,C5,E5,0)),"參數錯誤")」，按Enter鍵確認。第二個公式參數的組成和作用與第一個公式基本相同，用於求得相同時間所需償還的本金。兩個函數求得的結果都為負值，可使用ABS()函數返回其絕對值，以顯示正數。由於函數的任意一個參數錯誤都會導致函數返回錯誤值，可使用IFERROR()函數來檢測錯誤並返回一些說明文本。
OK，貸款利息的那些事今天就說到這，我們了解了等額本金和等額本息的計算方法，同時在等額本息法中也學習了PMT()函數、PPMT() 函數、IPMT()函數、CUMIPMT()函數、CUMPRINC()函數的用法，小夥伴們，還有什麼別的想法，歡迎留言。學習更多技巧，請收藏關注部落窩教育excel圖文教程。
＊＊＊＊部落窩教育-excel解析還貸方式＊＊＊＊
原創：賦春風/部落窩教育（未經同意，請勿轉載）
更多教程：部落窩教育