學會這4個函數，製作還款明細表是一件一勞永逸的事

本文是接著文章一文讀懂貸款利率、利息、分期還款額的計算（附案例演示），繼續探討如何運用Excel計算貸款利率、利息、分期還款額。

前一篇文章講解了RATE函數、PMT函數，本文將介紹貸款函數。

一、PPMT函數與IPMT函數

PMT函數用來計算基於固定利率和等額分期還款方式下的每期還款額。PPMT函數和IPMT函數則基於固定利率和等額分期還款方式，分別計算每期還款額中的本金和利息。三者關係為PMT=PPMT+IPMT。

① PPMT函數：=PPMT（rate，per，nper，pv，[fv]，[type]）

用途：PPMT函數是Principle of Payment的縮寫，其中Principle意思是本金，Payment意思是每期還款額。因此，Principle of Payment的意思是每期還款額中的本金數額。PPMT函數是基於固定利率及等額分期付款方式，返回貸款的每期付款額中的本金數額。

參數：Rate為每期利率，Per（Period的縮寫，意思是某一特定時期）用於計算本金數額的期數（1≤Per≤Nper），Nper為付款期數，Pv為本金，Fv為期末本金和利息和，Type指定各期的還款時間是在期初還是在期末（期初值為1，期末值為0）。

② IPMT函數：=IPMT（rate，per，nper，pv，[fv]，[type]）

用途：IPMT函數是Interest of Payment的縮寫，其中Interest是利息的意思。因此，Interest of Payment的意思是每期還款額中的利息。IPMT函數是基於固定利率及等額分期還款方式，計算貸款的每期還款額中的利息。

參數：IPMT函數的參數與PPMT參數完全一致，所以不再介紹。

案例：在借唄借款10000元，選擇分6期，即6個月償還，日利率萬分之五，請問第3期償還的本金和利息分別是多少？

在Excel輸入：=PPMT（rate，per，nper，pv，[fv]，[type]）=（0.05%，3，6，10000）和=IPMT（rate，per，nper，pv，[fv]，[type]）=（0.05%，3，6，10000），分別得到結果為-1653.77元、-101.48元，即第3期時需要償還本金1653.77元，需要支付利息101.48元。每期還款額PMT為1755.25元。

二、CUMPRINC函數、CUMIPMT函數

CUMPRINC函數計算一定時期內（從start-period到end-period），累計還款額中的本金之和；CUMIPMT函數計算一定時期內（從start-period到end-period），累計還款額中的利息之和。這兩個函數與PMT函數的關係為：PMT*求和期數=CUMPRINC+CUMIPMT。

① CUMPRINC函數：=CUMPRINC（rate，nper，pv，start_period，end_period，type）

用途：CUMPRINC函數由表示累計的Cumlulative的前三個字母和表示本金的Principal的前五個字母組合而成，給定了計算的範圍為表示時間開始的start_period和表示時間結束的end_period，因此CUMPRINC函數意為特定時期的本金之和。

參數：rate為每期利率，nper為還款期數，pv為貸款總額，start_period開始時間，end_period結束時間，type指定各期的還款時間是在期初還是在期末（期初值為1，期末值為0）。由於每一期還款是在期末，比如下面會列舉的借唄案例中2017年10月5日借款的首次還款時間為第一個月滿後的2017年11月5日，所以type不能省略，需要填寫表示期末的值0。

② CUMIPMT函數：=CUMIPMT（rate，nper，pv，start_period，end_period，type）

用途：CUMIPMT函數由Cumlulative的前三個字母和Interest of Payment的縮寫IPMT構成，給定了計算的範圍為表示時間開始的start_period和表示時間結束的end_period，因此CUMIPMT函數意為特定時期的利息之和。

參數：CUMIPMT函數的參數與CUMPRINC函數的參數完全一致，所以不再介紹。

案例：在借唄借款10000元，選擇分6期，即6個月償還，日利率萬分之五，請問第3期到第5期一共還了多少本金，又一共還了多少利息？

在Excel中輸入=CUMPRINC（rate，nper，pv，start_period，end_period，type）=（0.05%,6,10000,3,5）和=CUMIPMT（rate，nper，pv，start_period，end_period，type）=（0.05%,6,10000,3,5），分別得到結果為-5036和-229.7，即第3期到第5期償還借唄的本金之和為5036元，償還利息之後為229.7元。

三、製作還款明細表

用Excel製作還款明細表，可以讓人清楚地看到整個還款期中每一期償還的本金和利息，以及每一期償還後剩餘應還本金和應還利息。

講這部分前，需要先講一個知識點：相對引用、絕對引用和混合引用。

如果在單元格A1中輸入=B1，那麼，A1就是B1的引用單元格，而B1則成為A1的從屬單元格。從屬單元格和引用單元格之間有3種不同的引用方式，分別是相對引用、絕對引用和混合引用。Excel中通過美元符號$來區分這三種引用，輸入美元符號的辦法是英文狀態輸入狀態下shift+4。

①相對引用

相對引用是指，當複製Excel函數到其他單元格時，Excel保持從屬單元格與引用單元格的相對位置不變。

如上圖所示，在B1單元格輸入=A1後，如果向下複製公式，B列每一個單元格執行的都是等於跟其在同一行的A列的單元格，B2單元格中的公式為=A2，B3中變為=A3，B4中的單元格公式為C4，等等；而如果向右複製公式，右邊的單元格執行的是等於其左邊相鄰的單元格的引用規則，如C1單元格的公式變為=B1，D1單元格的公式變為=C1，等等。

②絕對引用

在絕對引用的情況下，複製公示到其他任何單元格，Excel保持所引用的單元格位置不變。

如上圖所示，在單元格B1輸入=$A$1，即意味著B1絕對引用A1，之後B1的公示無論是複製到C1，還是B5，或者D3，所引用的單元格位置都指向A1。

③ 混合引用

使用混合引用時，複製單元格到其他單元格，Excel出現兩種情況：列絕對引用，行相對引用；列相對引用，行絕對引用。

如上圖所示，在B1單元格先後輸入=A1和=$A1，會發現相對引用狀況下，將B1單元格的公式=A1複製到C3時，C3返回的是0，因為遵從的是=B3，而B3是空格，所以返回值0；而在絕對引用的情況下，將B1單元格的公式複製到C3時，C3的值從之前的0變成了2，是因為C3單元格遵從是=$A3。

講完了相對引用、絕對引用和混合引用，接下來可以講製作貸款明細表了。

案例：在借唄借款10000元，選擇分6期，即6個月償還，日利率萬分之五，請問第1期至第6期每一期償還的本金是多少，每一期支付的利息是多少，每一期償還後剩餘應還本金是多少，每一期償還後應還的利息還有多少？

先在Excel中C1單元格錄入日利率rate值0.05%，C2單元格錄入還款期數nper值6，C3單元格錄入貸款總額pv值10000。

在C5單元格，通過PMT函數=PMT(C1*30,C2,C3)計算得到每期還款額pmt值為-1755.25；在C6單元格，通過=C5*C2，即每期還款額乘以還款期數，計算得到還款總額為-10531.51；在單元格C7，可以使用=C6+C3（也可以用更複雜的=CUMIPMT(C1*30,C2,C3,1,6,0)計算出來），即還款總額加貸款總額，計算得到還款利息總額為-531.51。

在E2單元格輸入1，向下拉到E7，得到還款期數1到6共6個數值。這個後面計算剩餘應還本金、剩餘應還利息時均需要相對引用E2單元格。

在F2、G2、H2、I2，依次輸入以下四個公式，回車後分別下拉到F7、G7、H7、I7，即可得到此次借唄借款的還款明細表。

每期償還本金為：=PPMT($C$1*30,$E2,$C$2,$C$3)

每期償還利息為：=IPMT($C$1*30,$E2,$C$2,$C$3)

剩餘應還本金為：=$C$3+CUMPRINC($C$1*30,$C$2,$C$3,1,E2,0)

剩餘應還利息為：=CUMIPMT($C$1*30,$C$2,$C$3,1,E2,0)-$C$7

在明細表上，可以清晰地看到等額本息還款模式下，每期償還的本金越來越多，而每期償還的利息越來越少。