編按：

哈嘍，大家好！哪種房貸還款方式更劃算？用Excel算算就知道了。房貸有等額本金和等額本息兩種還款方式。等額本金還款，每月還的金額不一致，第一月最多，以后逐月減少；等額本息還款，每月還的金額一樣。哪中劃算？今天我們將用PMT()函數(shù)、PPMT()函數(shù)、IPMT()函數(shù)、CUMIPMT()函數(shù)、CUMPRINC()函數(shù)來帶大家了解貸款的那些事！趕緊來看看吧~

在貨幣借貸的過程中，為了突出貨幣的時(shí)間價(jià)值，通常借方都需要向貸方支付一定比例的利息以獲取資金的使用權(quán)，而借貸行為是日常工作中很容易產(chǎn)生的一種經(jīng)濟(jì)行為，在借貸過程中必須要摸清本金和利息的關(guān)系，才能準(zhǔn)確明白資金的來龍去脈。分期付款或還款已不再是企業(yè)貸款的專利，現(xiàn)在很多人也開始分期支付一些較大額的款項(xiàng)，如房貸、車貸。在銀行的分期付款過程中，可以采用等額本息法和等額本金法兩種還款方式。不同的還款方式，本金和利息的變化也不同。哪種劃算呢？

一、等額本金法每期還款額的計(jì)算

等額本金還款的特點(diǎn)是每個(gè)月歸還相同的本金，也就是將貸款本金平均分在每一期還款中。每月歸還的本金為貸款總額÷還款期數(shù)。

等額本金每個(gè)月要?dú)w還的利息是貸款余額在上一個(gè)月所產(chǎn)生的利息。也就是說，隨著還款的進(jìn)行，所欠的貸款越來越少，那么每個(gè)月需要還給銀行的利息就越來越少。

舉個(gè)例子，某人以5.25%的利率貸款50萬元，計(jì)劃以等額本金的方式分5年還清，要計(jì)算他每月需要還款的總金額，以及其中包括的本金和利息。具體步驟如下：

①  合并A6:D6單元格，輸入“還款明細(xì)數(shù)據(jù)”，在A7:D7單元格分別輸入“期次”“還款額”“本金”“利息”，在A8單元格輸入數(shù)字“1”，單擊鼠標(biāo)右鍵，打開“設(shè)置單元格格式”對(duì)話框，在“自定義”選項(xiàng)卡中設(shè)置單元格格式代碼為"第"00"期"。這里的“第01期”只是一個(gè)障眼法，當(dāng)選擇A8單元格后，在編輯欄中即可看到它的真實(shí)值，實(shí)際上是數(shù)字1，而非文本“第01期”。選中A8單元格，按住Ctrl鍵，向下填充公式至A67單元格。

②  在C8單元格中輸入公式“=$A$3/$C$3/12”，雙擊C8單元格右下角的自動(dòng)填充柄，向下填充公式至C67單元格，可以得到各期還款額的本金。該公式用于計(jì)算單元格對(duì)應(yīng)的還款期次的還款本金，每個(gè)月應(yīng)該歸還的本金就是50÷5÷12≈0.83萬元。

③  在D8單元格中輸入公式“=($A$3-(A8-1)*C8)*$B$3/12”，雙擊D8單元格右下角的自動(dòng)填充柄，向下填充公式至D67單元格，可以得到各期還款額的利息?！?span>$A$3-(A8-1)*C8”為貸款余額，“$B$3/12”可以將給定的年利率轉(zhuǎn)化為月利息。

④  在B8單元格中輸入公式“=C8+D8”，雙擊B8單元格右下角的自動(dòng)填充柄，向下填充公式至B67單元格，可以得到各期總還款額?？傔€款額為本金與利息之和。

從表中可以看出等額本金的還款方式，每個(gè)月的還款本金是相同的，但是利息會(huì)遞減。最初每月還款比較多，越到后來貸款余額越少，利息越少，每月還款額逐漸減少。

二、等額本息法中每期還款額的計(jì)算

等額本息法分期付款是指在欠款償還期間，每期支付相等的金額，直到所有欠款還清，如果要計(jì)算等額本息法中每一期應(yīng)還的金額，可以使用PMT()函數(shù)。該函數(shù)的語法格式為：

PMT(rate,nper,pv,[fv],[type])

PMT()包含3個(gè)必選參數(shù)rate、nper和pv，以及兩個(gè)可選參數(shù)fv和type，各參數(shù)意義如下：

?  rate：各期利率的數(shù)字，通常以百分比形式出現(xiàn)，在整個(gè)貸款期間保持不變。

?  nper：貸款所需償還的總次數(shù)，通常以月為單位。

?  pv：在此函數(shù)中表示貸款的總額。

?  fv：在此函數(shù)中表示最后一次還款后的剩余金額（通常為0，表示全部還清）。

?  type：指定各期的付款時(shí)間是在期初（用數(shù)字1表示）還是期末（用數(shù)字0表示）。

如果要計(jì)算每期還款額中包含的本金，可以使用PPMT()函數(shù)來完成，其語法格式為：

PPMT(rate,per,nper,pv,[fv],[type])

如果要計(jì)算在相同條件下，每期還款額中包含的利息金額，則可以使用IPMT()函數(shù)來完成，其語法格式為：

IPMT(rate,per,nper,pv,[fv],[type])

從這兩個(gè)函數(shù)的語法格式上可以看出，它們的語法結(jié)構(gòu)完全相同。都包含4個(gè)必選參數(shù)rate、per、nper和pv，以及兩個(gè)可選參數(shù)fv和type，各參數(shù)意義如下：

rate：貸款或投資期間的固定利率。

per：要計(jì)算本金（或利息）的期次，必須在1-nper之間。

nper：付款總期數(shù)。

pv：在這兩個(gè)函數(shù)中可表示貸款的總額（或投資本金）。

fv：最后一次付款后剩余金額（通常為0）。

type：指定各期的付款時(shí)間是在期初（用數(shù)字1表示）還是期末（用數(shù)字0表示）。

還是用上個(gè)例子，某人以5.25%的利率貸款50萬元，計(jì)劃以等額本息的方式分5年還清，要計(jì)算他每月需要還款的總金額，以及其中包括的本金和利息。

計(jì)算等額本息還款法中各期的還款額，可以使用PMT()函數(shù)，計(jì)算還款金額中包含的本金，可使用PPMT()函數(shù)，而在得到本金后，可以通過還款額-本金的方法得到其中包含的利息，也可以直接使用IPMT()函數(shù)來取得。具體步驟如下：

①  與等額本金法的步驟①一致，搭建數(shù)據(jù)基礎(chǔ)框架。

②  在C7單元格輸入公式“=PMT($C$3/12,$D$3*12,-$B$3)”，雙擊C7單元格右下角的自動(dòng)填充柄，向下填充公式至C66單元格，可以得到各期還款總額?！?span>$C$3/12”為各期利率數(shù)據(jù)，“$D$3*12”為總期數(shù)，“-$B$3”為貸款總額。這里為了得到正數(shù)的本金數(shù)字，所以給貸款總額添加了負(fù)號(hào)。

③  在D7單元格中輸入公式“=PPMT($C$3/12,B7,$D$3*12,-$B$3)”，雙擊D7單元格右下角的自動(dòng)填充柄，向下填充公式至D66單元格，可以得到各期還款額的本金。該公式用于求得單元格對(duì)應(yīng)的還款期次中的還款本金，“$C$3/12”和“$D$3*12”分別代表將給定的年利率轉(zhuǎn)化為月利息，以及將還款年限轉(zhuǎn)化為還款月數(shù)。這里為了得到正數(shù)的本金數(shù)字，所以給B3單元格的值添加了負(fù)號(hào)。

④  在E7單元格中輸入公式“=IPMT($C$3/12,B7,$D$3*12,-$B$3)”，雙擊E7單元格右下角的自動(dòng)填充柄，向下填充公式至E66單元格，可以得到各期還款額的利息。該公式與上一個(gè)公式的參數(shù)完全相同，但求取的結(jié)果是該還款期次中的利息。

在固定利率的情況下，如果采用等額本息法還款，在每一期的還款金額中，利息在不斷減少，而本金會(huì)不斷增加。

在表中匯總一下向銀行支付的還款總額，可以看出等額本金還款支出的總額更少。如果不考慮通貨膨脹的影響，等額本金還款方式更劃算。
  無論是等額本息還是等額本金的還款方式，每個(gè)月的還款都由本金和利息兩部分構(gòu)成，其中的利息是未歸還的貸款金額在上一個(gè)月的時(shí)間內(nèi)產(chǎn)生的利息。在任何時(shí)候提前還款，都會(huì)減少后續(xù)利息的產(chǎn)生，所以僅從利息角度看，提前還款并不會(huì)吃虧。提前還款的的確確可以減少利息，但是如果算上有些銀行對(duì)提前還款收取違約金、通貨膨脹等因素，提前還款不見得合算。

三、信用卡消費(fèi)的貓膩

隨著信用卡越來越流行，很多年輕人都養(yǎng)成了提前消費(fèi)的習(xí)慣，當(dāng)消費(fèi)達(dá)到一定金額后，銀行就可能會(huì)提供免息分期付款業(yè)務(wù)，但這里面卻有一些貓膩。首先這里的免息確實(shí)是不需要利息，但卻多出了一項(xiàng)手續(xù)費(fèi)率，其實(shí)與利息的作用是相同的，并且普遍高于銀行同期貸款利息。

在信用卡等額分期還款中，銀行計(jì)算的每期還款額公式為：分期總額/還款期數(shù)+分期總額*各期手續(xù)費(fèi)率。從計(jì)算公式中可以看出每月所還的利息并不會(huì)隨著還入本金的增加而減少。

例如同樣的50萬元分5年還，手續(xù)費(fèi)率為5.25%，使用PMT()函數(shù)計(jì)算的各期還款額和使用信用卡分期還款公式計(jì)算的各期還款額并不相等。

從圖中可以看出，在相同的貸款金額、利率和還款期限下，信用卡分期還款的各期還款額明顯高于銀行貸款的還款額，這是因?yàn)樵谛庞每ǚ制谥?，并未考慮貨幣的時(shí)間價(jià)值，及已經(jīng)還入的本金依然在被記利息，即從開始還款到還款結(jié)束，要支付的利息始終都是貸款原始金額的利息。而正常的財(cái)務(wù)處理中，已經(jīng)還款的本金是不能被計(jì)入利息的（如PMT函數(shù)計(jì)算的結(jié)果），而信用卡分期主要就是賺取客戶的這一部分差額，這也是為什么信用卡可以直接貸款，而向銀行貸款手續(xù)非常麻煩的原因。

四、付款期間的本金與利息的關(guān)系

在等額本息法分期付款的借貸行為中，如果要得到兩次付款期間產(chǎn)生的總利息，可以使用CUMIPMT()函數(shù)來完成，其語法格式為：

CUMIPMT（rate,nper,pv,start_period,end_period,type）

同樣的條件下，如果要計(jì)算兩次還款期間內(nèi)總共償還的本金，則可以使用CUMPRINC()函數(shù)來完成，其語法結(jié)構(gòu)為：

CUMPRINC（rate,nper,pv,start_period,end_period,type）

從兩個(gè)函數(shù)的語法格式上可以看出，兩個(gè)函數(shù)的參數(shù)完全相同，都包含相同的6個(gè)必選參數(shù)，各參數(shù)意義如下：

?  rate：支付期間的固定利率。

?  nper：付款總期數(shù)。

?  pv：在這兩個(gè)函數(shù)中可表示貸款的總額（或投資本金）。

?  Start_period：計(jì)算利息或本金的首期，總付款基數(shù)從1開始計(jì)數(shù)。

?  end_period：計(jì)算利息或本金的末期。

?  type：指定付款時(shí)間是在期初（用數(shù)字1表示）還是期末（用數(shù)字0表示）。

在使用這兩個(gè)函數(shù)的過程中，各參數(shù)需要遵循如下幾個(gè)規(guī)則：

①  函數(shù)的rate和nper必須具有相同的單位，如年利率對(duì)應(yīng)的期限為年，月利率對(duì)應(yīng)的期限為月。

②  rate，nper，pv都必須大于等于1，否則函數(shù)返回錯(cuò)誤值#NUM!。

③  start_period，end_period都必須大于等于1，且start_period不能大于end_period。

④  type參數(shù)只能是0或1，若是其他值，則函數(shù)返回錯(cuò)誤值#NUM!。

舉個(gè)例子，某信貸公司想快速計(jì)算用戶在任意期間所需償還的利息和本金，可以通過EXCEL函數(shù)來完成。

要計(jì)算兩個(gè)指定期間的累積利息和本金，可分別是使用CUMIPMT()函數(shù)和CUMPRINC()函數(shù)求得。具體步驟如下：

①  輸入基礎(chǔ)數(shù)據(jù)，選中C5、E5單元格，打開“設(shè)置單元格格式”對(duì)話框，在“自定義”選項(xiàng)卡中設(shè)置單元格格式代碼為"第"00"期"。

②  在“開發(fā)工具”選項(xiàng)卡下點(diǎn)擊“插入”，將“表單控件”下的數(shù)值調(diào)節(jié)鈕（窗體控件），繪制到C5單元格右側(cè)，右擊“設(shè)置控件格式”，將最小值設(shè)置為“1”，因?yàn)檫€款次數(shù)共60次，所以最大值設(shè)置為“60”，步長(zhǎng)為“1”，單元格鏈接為“$C$5”，點(diǎn)擊“確定”按鈕。按照同樣的方法在E7單元格右側(cè)插入數(shù)值調(diào)節(jié)鈕，唯一不同的是單元格鏈接為“$E$5”。

③  選中C6單元格，輸入公式“=IFERROR(ABS(CUMIPMT(D3/12,E3,B3,C5,E5,0)),"參數(shù)錯(cuò)誤")”，按Enter鍵確認(rèn)。首先通過函數(shù)求得該項(xiàng)貸款從C5單元格指定的期數(shù)至E5單元格指定的期數(shù)之間應(yīng)支付的利息，再用ABS()函數(shù)對(duì)返回的結(jié)果取絕對(duì)值，最后用IFERROR()函數(shù)來檢測(cè)錯(cuò)誤，并根據(jù)情況返回具體數(shù)值或返回“參數(shù)錯(cuò)誤”文本。

④  選中E6單元格，輸入公式“=IFERROR(ABS(CUMPRINC(D3/12,E3,B3,C5,E5,0)),"參數(shù)錯(cuò)誤")”，按Enter鍵確認(rèn)。第二個(gè)公式參數(shù)的組成和作用與第一個(gè)公式基本相同，用于求得相同時(shí)間所需償還的本金。兩個(gè)函數(shù)求得的結(jié)果都為負(fù)值，可使用ABS()函數(shù)返回其絕對(duì)值，以顯示正數(shù)。由于函數(shù)的任意一個(gè)參數(shù)錯(cuò)誤都會(huì)導(dǎo)致函數(shù)返回錯(cuò)誤值，可使用IFERROR()函數(shù)來檢測(cè)錯(cuò)誤并返回一些說明文本。

OK，貸款利息的那些事今天就說到這，我們了解了等額本金和等額本息的計(jì)算方法，同時(shí)在等額本息法中也學(xué)習(xí)了PMT()函數(shù)、PPMT() 函數(shù)、IPMT()函數(shù)、CUMIPMT()函數(shù)、CUMPRINC()函數(shù)的用法，小伙伴們，還有什么別的想法，歡迎留言。

本文配套的練習(xí)課件請(qǐng)加入QQ群：1043683754下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

金額分列的方法《財(cái)務(wù)必看！最簡(jiǎn)單的金額分列方法！》

收支表《財(cái)務(wù)小伙伴別被誤導(dǎo)了，這才是收支表快速轉(zhuǎn)換的正確方式！》

財(cái)務(wù)對(duì)賬的技巧《財(cái)務(wù)對(duì)賬必會(huì)的幾個(gè)函數(shù)！》

制作工資條《兩個(gè)最快捷的工資條制作方法，10秒鐘2000人》