一文讀懂貸款利率、利息、分期還款額的計算（附案例演示）

本文將會對貸款利率計算相關的問題作出探討，並把行文重心放在通過案例演示如何藉助Excel函數解決利率計算問題。

一、利率究竟包含什麼

在此前的文章中，第一消費金融（ID：TodayCFC）已經明確，貸款利率用來衡量借款方為取得貨幣資金的使用權而支付給貨幣資金所有者的價格，而利息是貨幣資金所有者因暫時讓渡貨幣資金的使用權，從借款人那裡取得的報酬。利息的高低通過利率大小表示。

從以上定義中，第一消費金融（ID：TodayCFC）希望明確一個常識，即借款方為了獲得一筆貨幣資金使用權，而向貨幣資金所有者支付的一切費用，無論其名目稱謂是什麼，都應該算作利率。

在現實中，真實的借貸行為伴隨著花樣百出的費用——利息（包括砍頭息）、手續費、居間服務費、人工成本、徵信查詢費用、運營商通信查詢費用、保險費用、罰息，等等。

很多平台將利率降到低於24%，然後將其他收費項目提上去，比如收取高昂的手續費，或者搭售保險，最後實際貸款貸款利率大於24%。這種掩耳盜鈴式的套路，在法院的審判中不僅不受保護（諸位看官可以移步裁判文書網尋找案例），還可能會被銀監會開罰單。貸款平台貸出資金收取的一切費用，無論以什麼名義，其相應的利率不得超過24%。

接下來進入Excel函數的講解。

二、什麼是Excel函數

在回答什麼是「Excel函數」之前，先搞清楚什麼是「函數」。

據考證，「函數」一詞，最早見於清朝數學家李善蘭的著作《代數學》。李善蘭將「function」翻譯為「函數」。

在古代漢語中，「函」的讀音與「含」相同，意思在表示包含、包容時完全相同。如果當初李善蘭將「function」翻譯為「含數」，那麼今人理解這個概念就會容易很多。李善蘭解釋「函數」時稱，「凡此變數中函（含）彼變數者，則此為彼之函數（含數）」（所有此一變數中包含彼一變數的情況，那麼此一變數為彼一變數的函數）。

簡而言之，一個變數隨另一個變數而變化，那麼前者就是後者的函數。比如Y隨著x的變化而變化，那麼Y就是x的函數，二者關係表達為Y=f（x）（f即function的首字母）。

Excel函數就是在Excel中處理這種一個變數隨另一個或另一些變數而變化的計算公示，如求和、求平均值、求最小值。Excel函數通常由表示公示開始的等號（=）、函數名（如SUM）、左括弧、區域運算符（如聯名運算符逗號、引用運算符冒號），以區域運算符引用的參數（如A1）、空格（不需要時就沒有）和右括弧構成。

在Excel中，如果單元格的格式沒有被設置為文本，當用戶在單元格中輸入的第一個字元是等號（=）、@、加號（+）或減號（-）等這四種時，Excel自動變為輸入公示狀態。一般來說，以手動輸入等號或者用滑鼠點擊fx，為輸入公示的開始。

如：以等號開始在單元格輸入求和函數（Summation），如=SUM（A1：B2）後按回車鍵，Excel將返回從A1到B2這4個單元格中的數值的和。

在運用Excel函數進行計算時，函數公式一般大寫，但小寫也可以，比如SUM輸成sum也可以；單元格一般輸入列時需要大寫，但是A1輸入成a1也可以，因為Excel會自動調整大小寫和全形半形。

三、Excel中的運算符

在Excel中，貸款函數涉及的常見運算符有：

①負號，即「-」，如：-1。

②百分號，即「%」，如1%。

③加和減，即「+」、「－」，如1+1-2。

④乘和除，即「*」、「/」（注意在Excel中輸入「×」和「÷」會被提示錯誤，乘法符號的輸入辦法是shift+8,，除號的輸入辦法是英文輸入狀態下按shift左邊的鍵/）。

⑤乘冪，即「^」。輸入辦法是英文輸入狀態下鍵入shift+6。

⑥冒號，即「：」，在Excel中叫區域運算符，生成兩個單元格之間的所有單元格的引用，引用冒號兩邊所引用的單元格為左上角和右下角的矩形單元格區域。如=SUM(A1：A3)的求和範圍為A1左上角到A3右下角，對應的單元格包含A1、A2和A3；=SUM（A1：B3）則是A1左上角到B3右下角，對應的單元格有A1、A2、A3、B1、B2和B3。

⑦逗號，即「，」，名為聯合運算符，屬於引用運算符的一種，將多個引用合併為一個引用。如=SUM（A1：（B1:B3，D1:D3））中的兩個引用分別為A1和（B1:B3，D1:D3），後者由逗號將兩個引用B1：B3和D1：D3合併為一個引用。

以上為Excel貸款函數使用過程中一般會涉及到的運算符。

四、Excel函數中的必選參數與可選參數

在Excel函數中，一些函數可以只使用其部分參數就能得到想要的值。也即，Excel函數參數分為必選參數和可選參數。那些可選參數外面一般有方括弧（[]），如[type]。

以FV函數為例進行解釋。

FV函數是基於固定利率及等額分期付款方式，計算償還清最後一期貸款時的本金和利息和，其完整表達式為=FV(rate，nper，pmt，pv，type)。

此函數相關的參數解釋為：rate，各期利率；nper，為number of periods的縮寫，表示貸款期數；pmt，為payment的縮寫，表示每期償還額；pv，為present value的縮寫，也稱期初金額，即本金，在借款中指貸款數額；type，對應邏輯值0或1，用以指定還款時間在期初還是在期末，如果為1，還款在期初；如果為0或忽略，還款在期末。

FV：終值（Future Value）函數，求未來值——在最後一次付款期後獲得的現金餘額——即求期末本利和的價值。很顯然，利率（rate）、借款期數（nper）和分期還款額（pmt）是必不可少的。如果在FV函數中，pmt和pv必須寫一個，如果已經寫了pmt的值，則pv可以省略；反之亦然，如果寫了pv，則pmt可以省略。一般還款時間是期初或者期末對計算最後還多少錢沒什麼影響，所以type也是可選參數，不用填寫。

這裡需要強調的是，rate和nper的單位必須一致，都為月、季度、年或者其他。比如，一年期年利率20%的貸款，如果按月還款，則rate為20%/12，nper為1*12；如果按年支付，則rate為20%，nper為1；如果按季度支付，rate為20%/4，nper為1*4，等等。

綜上，FV函數完整表達式=FV(rate，nper，pmt，[pv]，[type])實際上可以省略為=FV(rate，nper，pmt)或者=FV（rate，nper，pv）。

五、現金收入與現金支出

現金流分析是財務報表分析的一個領域，分析對象為某一會計期內現金流入和現金流出的信息。

在交易中，一方產生現金流入，意味著另一方的現金流出。貸款活動中，貸款方將一筆錢按照一定利率貸出去，意味著貸款方出現了現金流出，而借款方出現了現金流入。比如，一個人向銀行借錢30萬元，還款方式為每月等額本息。在剛開始，銀行將30萬元打到借款人賬戶上，對銀行而言是現金流出，對於借款人而言則是現金流入；此後，借款人按月還本付息，對於銀行而言是現金流入，對於借款人則是現金流出。在這個案例中，銀行放貸，獲得投資回報率；借款人付出借款利率。也即是說，在沒有中介參與的情況下，投資回報率和借款利率是一回事，不過立場一個是貸方，一個是投資方而已。

在運用Excel計算貸款相關問題時，首先要確定是按照貸款方還是借款方的立場計算，用正數表示現金流入，負數表示現金流出。在Excel中錄入數據時，負數前面要加上負號。

六、案例演示貸款相關函數的使用

接下來演示的Excel函數，大多數與貸款計算相關。

① RTAE函數：=RATE(nper，pmt，pv，[fv]，[type]，[guess])

用途：已知還款期數、每期還款額、貸款金額，求貸款利率。

參數：Nper為貸款期數，Pmt為每期還款額，Pv為借款本金，Fv為未來值，Type指定各期的付款時間是在期初還是期末(1為期初。0為期末)。

案例：在借唄借款10000元，選擇分6期償還，每期還款額1756.68元。在Excel輸入：=RATE（nper，pmt，pv）=（6，1756.68，10000）=2%。月利率2%，則年利率=月利率*12=24%。

借唄在常見問題解答中稱，借唄的日利率區間為萬分之1.5至萬分之6。按照「年利率=月利率*12=日利率*360」計算，萬分之六的日利率對應的年利率為21.6%。前面的計算得出的年利率為24%，是哪裡出了問題？

問題出在返回rate結果的單元格的小數位數為0位。這裡將該單元格調整為小數點後保留2位，單元格的值就不會四捨五入，而是從2%還原為1.52%。

② PMT函數：=PMT(rate，nper，pv，[fv]，[type])

用途：基於固定利率及等額分期付款方式，返回貸款的每期付款額，即已知貸款利率、期限、貸款金額，求每期還款額。PMT返回的付款包括本金和利息，但不包括稅金、準備金，也不包括某些與貸款有關的費用。

參數：Rate貸款利率，Nper該項貸款的付款期總數，Pv為本金，Fv為未來值，Type指定各期的付款時間是在期初還是期末(1為期初。0為期末)。

案例：在借唄借款10000元，選擇分6期，即6個月償還，日利率為萬分之五，請問每月需要還多少錢？

在Excel輸入：=PMT（rate，nper，pv）=（0.0005*30,6,10000）=1755.25。

但是，在支付寶借唄界面，顯示的每期還款額為1756.68元。為什麼兩個數字存在細微差別？

這裡的原因應該是借唄在計算時，每個月的時間算了30.476天。實際上，從2017年10月4日開始借錢，到最後一次還錢時間2018年4月4日，應該是182天，每個月均值應該只有30.33天。不清楚借唄是如何計算出這個結果的。

本文尚未寫完，下一篇將講解PPMT、IPMT、CUMPRINC、CUMIPMT、NPV、IRR、XNPV和XIRR函數，處理諸如砍頭息計算之類的問題。

http://weixin.qq.com/r/93Vrc3HEOyxErTUQ9yDl (二維碼自動識別)