【20170818】淺談EXCEL財務公式rate計算信貸產品的收益率

圖放在最上面，今天要討論的是：借貸公司放款的實際收益。

已上圖為例：本金120000萬，月息1.68%，等額本息（即等本等息）還款，12期，計算真正的年利率。

一般演算法：1.68%*12=20.16%

但是這種演算法漏掉了一種情況，我每個月收回的12016（=120000/12+120000*1.68%），依然可以放款出去，再次賺取收益。今天要介紹的EXCEL公式，Rate(nper,pmt,pv,fv,type,guess)(nper,pmt,pv是必要的因素)，就是計算這種情況下的收益率公式。

nper--指定一筆年金的付款總期數

pmt--指定每一期的付款金額（本金+利息）

pv--指定未來一系列付款和收款的現值

fv-- 指定在付清貸款後所希望的未來值或現金結存

type--0（默認）或1，0代表付款時間在期末，1表示在期初

guess--對最終利率的猜測。由於Rate函數計算的次數有限，20次計算結果未收斂會返回錯誤值。

相對於這個例子的公式即為：=Rate(12,12016,-120000)*12=35.34%

關於公式中有正有負，讀者可以這麼理解：

期初放款出去12萬，自有資金池減12萬。

每月收款12016元，自有資金池加12016元。

下面開始解析，35.34%是怎麼來的。

（一）第一階段：紅色主框

本金12萬元，出借1年，12期，總收益：120000*1.68%*12=24192

（二）第二階段：藍色底紋框

每月都會收到12016（120000*1.68%+120000/12）利息，分別計算出借11個月，10個月...1個月的收益：

12016*1.68%*11+12016*1.68%*10...+12016*1.68%*1=13323.34

(三)第三階段：藍色橫線無框(這邊比較難以理解，需要看圖好好思考)

每月12016收回後，借出每月還能有1203.2（12016*1.68%+12016/12）利息，分別出借10個月，9個月...1個月的收益：

1203.2*1.68%*10+1203.2*1.68%*9+1203.2*1.68%*8+...1203.2*1.68%*1=1111.76

1203.2*1.68%*9+1203.2*1.68%*8+...1203.2*1.68%*1=909.62

1203.2*1.68%*8+...1203.2*1.68%*1=727.7

......

總計4447.04

（四）第四階段：綠色虛線框

這邊是後續的衍生，因為後面還有幾環節的計算，我在這邊就不一一贅述了。如果第三階段能理解，後面只是相似的計算。

只計入三個階段利潤，一共24192+13323.34+4447.04=41962.38

41962.38/120000=34.96%

已經逼近上述Rate的35.34%的結果了。

值得一提的是：35.34%的結果，是在絕對的資金利用的情況下得出的結論，是理想化的，實際情況中一定要綜合各方面去考慮Rate算出來的結果。

以上便是我對Rate函數的理解，因為用到這個函數的時候很困惑，所以花時間研究了一下，如有錯誤，歡迎大家指正。