貸款公司excel

① EXCEL貸款每月還款公式

EXCEL貸款每月還款公式需要用到PMT函數，具體操作如下：

1. 用到的語法：=PMT(rate,nper,pv,fv,type)

2. 其各參數的含義如下：

   Rate：貸款利率，如果貸款年利息為0.06，則每月還款利息為0.06/12。

   Nper：該項貸款的付款總數，通常指該項貸款的按月付款總次數，如果對一筆貸款按年償還15年，則該項為15*12。

   Pv 現值，即為還款總額，例如當貸款25萬時，該項應填寫250000元。

   Fv 為未來值，指在付清貸款後所希望的未來值或現金結存。通常是可選的，默認為0。

   Type 數字 0 或 1，用以指定各期的付款時間是在期初還是期末。Type=0或省略，代表「期末」，Type=1代表「期初」。

3. 為了正確的應用該公式，特製作如下表格，並在A6單元格中輸入公式「=PMT(A2/12, A3, A4)」即可得每月應還款數額。

② 貸款計算公式 excel

一、等額本息 利用函數PPMT(rate,per,nper,pv,fv,type)計算本金，IPMT函數計算利息 本金＝PPMT(各期利率,第幾期,總期數,本金） 利息＝IPMT(各期利率,第幾期,總期數,本金） Excel中的PMT函數，通過單、雙變數的模擬運算來實現貸款的利息計算。 PMT函數可基於利率及等額分期付款方式，根據貸款利率、定期付款和貸款金額，來求出每期(一般為每月)應償還的貸款金額。PMT函數的格式和應用方式： PMT(Rate，Nper，Pv，Fv，Type) 其中各參數的含義如下： Rate：各期利率，例如，如果按吧.四%的年利率借入一筆貸款來購買住房，並按月償還貸款，則月利率為吧.四%/一二(即0.漆%)。用戶可以在公式中輸入吧.四%/一二、0.漆%或0.00漆作為Rate的值。 Nper：貸款期數，即該項貸款的付款期總數。例如，對於一筆一0年期按月償還的住房貸款，共有一0×一二(即一二0)個償款期數。可以在公式中輸入一二0作為Nper的值。 Pv：現值，或一系列未來付款的當前值的累積和，也就是貸款金額。 Fv：指未來終值，或在最後一次付款後希望得到的現金余額。如果省略Fv，則假設其值為零，也就是一筆貸款的未來值為零，一般銀行貸款此值為0。 二、等額本金 利息計算＝（本金 — 已歸還本金累計額）×每月利率 每月還款＝（貸款本金 / 還款月數）+（本金 — 已歸還本金累計額）×每月利

③ Excel讀書筆記29——貸款管理表——貸款利息計提與查詢的全自動管理示例

在具有一定規模的企業中，除了會計核算和財務管理以外，融資管理也是一項嚴謹而重要的工作。特別是融資批量較大的時候，不同授信期限、不同融資額度、不同還款到期日以及到期應付金額等信息的整理與統計，往往弄得資金管理人員疲於奔命。

本節中，我們以逸凡公司的授信及貸款情況為例，討論如何用Excel來設計一套信息量強大的「貸款管理統計表」（參見示例文件「表5-4 貸款管理統計表」）。

一、基本框架與功能展示

「貸款管理統計表」由授信台賬（見圖5-71）和貸款台賬（見圖5-72）構成。

圖5-71 授信台賬

圖5-72 貸款台賬

貸款台賬將擔負起每筆貸款信息記錄和未來應付總額查詢預警的重任。

我們的目標是：只需要在授信台賬中手工錄入授信合同號、金融機構、授信總額度以及授信期限，在貸款台賬手工錄入授信合同號、貸款合同號、貸款本金、貸款起始日、期限、年利率以及結息周期等基礎信息，就可以實現以下功能。

1.主要信息功能

（1）自動顯示每筆授信當前已用額度、可用額度及所有授信總體可用額度。

（2）自動顯示當前貸款總額、每筆（及全部）貸款本月應付利息和應付本息總額。

2.輔助信息功能

用戶錄入自定義期間後（不大於30天），自動提示未來該天數內將要到期應付的本金、利息及本息總額，並對具體明細記錄予以標識。

二、基本前提及假設

1.授信、融資期限以月為單位

一般情況下，金融機構為企業提供的授信期限及融資期限均以整月（或可折算為整月）為單位進行計算。例如半年（6個月）、一年（12個月）等。但是也有個別以天（無法折算為整月）計算的情況。例如100天等。本節內容僅考慮相關期限可以以月為單位進行計算的情況。

2.貸款本金在到期日一次性償還

在貸款本金的歸還問題上，對於大多數企業和金融機構來說，都還是習慣採用到期日一次性償還的方式。故本節內容就不考慮提前償還、分期償還、展期償還、無力償還甚至耍賴不還等非主流情況了。

3.利息結算周期僅僅考慮月度和季度兩類，且貸款期限大於結息周期

月度結息和季度結息是目前最廣泛採用的結息方式。為避免討論的事項過於繁雜，本節內容只考慮上述兩類結息方式。且當貸款期限小於3個月時，只能採用月度結息方式。

4.貸款到期日與結息規則

需要聲明的是，以下結息規則為我國金融機構現行通用規則，不屬於假設。如果政策有變動，再將它們降級為假設吧。

（1）貸款（授信）到期日。

貸款（授信）到期日和定期存款到期日不同，存款到期日一般為對年（月），而貸款到期日一般為對年（月）的前一天。例如，同樣以2013年1月1日為起始日，且期限為一年，定期存款的到期日為2014年1月1日，而貸款的到期日一般為2013年12月31日。

（2）結息付息日。

1）月度結息付息：每月20日為結息日，次日付息。

即上月21日～本月20日的貸款利息，在本月21日支付，以此類推。當然，前提是貸款的起始日早於上月21日且貸款的到期日晚於本月的20日。

2）季度結息付息，每季度末月20日為結息日，次日付息。

即上季度末月21日～本季度末月20日的貸款利息，在本季度末月21日支付。當然，前提是貸款的起始日早於上季度末月21日且貸款的到期日晚於本季度末月的20日。

3）貸款到期當日，結算並支付尚未支付的利息。

（3）計算日利息時，全年按360天計算。

有資金管理經驗的讀者朋友應該發現過一個現象：一筆貸款實際支付的利息總額，總會略大於按貸款利率計算的利息總額。這是因為在貸款期限內每次結息採用的日利息，是以一年360天計算的，但是全年有365（或366）天。這樣，實際支付的年利息就等於貸款本金×年利率×（365÷360）了。

（4）計息起止日。

從貸款到期日的規則可以看出，貸款計息按是資金實際佔用天數計息的（而不是定期存款按算頭不算尾模式計息），故計息起止日計算的原則有兩條：計息起始日為貸款起始日與上期結息日次日的較晚者；計息截止日為本期結息日與貸款到期日的較早者。

具體來說，就是：貸款起始日早於上期結息日次日時，如果貸款到期日晚於本期結息日，則本期計息起止日為上期結息日次日至本期結息日；如果貸款到期日早於本期結息日，則本期計息起止日為上期結息日次日至貸款到期日。

例如：當前為2014年7月11日，某筆貸款於2014年1月15日發放，一年期，月度結息。則其貸款起始日（2014年1月15日）早於上期結息日次日（2014年6月21日）且貸款到期日（2015年1月14日）晚於本期結息日（2014年7月20日），故本期計息起止日為2014年6月21日～2014年7月20日。

如果上述借款為半年期，則其貸款到期日（2014年7月14日）早於本期計息日（2014年7月20日），故本期計息起止日為2014年6月21日～2014年7月14日。

貸款起始日晚於上期結息日次日時，如果貸款到期日晚於本期結息日，則本期計息起止日為貸款起始日至本期結息日；如果貸款到期日早於本期結息日，則本期計息起止日為貸款起始日至貸款到期日。後面這種情況只可能在貸款期限小於一個月時才會出現，所以屬於小概率事件。

例如：當前為2014年7月11日，某筆貸款於2014年6月28日入賬，一年期，月度結息。則其貸款起始日（2014年6月28日）晚於上期結息日的次日（2014年6月21日）且貸款到期日（2015年6月27日）晚於本期結息日（2014年7月20日），故本期計息起止日為2014年6月28日～2014年7月20日。

三、注意事項

為了便於後續拓展及查詢工作，在手工錄入相關參數時應注意保持名稱的統一（例如「金融機構」中涉及某銀行名稱時，應統一名稱規格，避免同一個金融機構出現多種名稱）。

在本章第一節中，我們曾經提到過用數據有效性中的序列設置來杜絕「同物不同名」情況的發生。比如貸款台賬中的結息周期就可以用這一招限定只能錄入「月度」和「季度」。但是對於金融機構而言，這一招就不太好使了，因為金融機構這個參數填寫的范圍不具有較強的限定性，你不可能預知是否會有新的機構出現，這也就無法設計備選選項了。所以這里我們只能強調填寫信息時的規范了。

四、知識點裝備

在閱讀本節下面的內容前，請各位讀者朋友首先確認大腦中是否已經基本裝備了圖5-73中的相關知識點。

在開始講解本節案例前，我們插播一個即將用到的知識點：定義和使用名稱。

通俗地說，定義名稱類似於數學中常用的「設X=某信息」，這里的「某信息」可以是常量、單元格區域或者公式等。設置完成後，我們即可用「X」來實現其被賦予的「某信息」的功能。使用名稱可簡化復雜的公式，使其更加容易理解和維護。下面，我們舉例簡單說明。

圖5-73 相關知識點

在圖5-74的訂貨單中，我們將總金額的公式使用定義名稱將其設為「總金額」。操作步驟為：【公式】選項卡→「定義的名稱」組→定義名稱（見圖5-74）。

圖5-74 定義名稱的使用

此時，我們就可以用定義的名稱來設置總金額公式（見圖5-75）。

D3:D5單元格區域的公式均為：=總金額

圖5-75 用定義的名稱代替傳統公式

使用定義名稱和設置輔助參數類似，可以將冗長的復合公式分步簡化，從而增強復合公式的可理解性。後文將有具體應用。

五、主要信息的公式設計方法

在對「貸款管理統計表」的框架、功能和相關注意事項有了大致的認識後，我們就通過逸凡公司貸款的案例來討論「貸款管理統計表」的設計了。

【案例5-4】逸凡公司截至2014年7月11日獲得金融機構的授信情況如圖5-76所示，獲得貸款情況如圖5-77所示，單位均為元。

圖5-76 逸凡公司授信信息表

圖5-77 逸凡公司貸款信息表

結合「貸款管理統計表」結構，相關設計方法如下。

1.授信台賬公式設計方法（上）

（1）即時日期顯示（D2單元格）。

要讓表格始終顯示「今天」的日期，在保證計算機系統日期無誤的情況下，我們只需要用到一個簡單的日期公式即可（見圖5-78）。

D2單元格的公式為：=TODAY( )

圖5-78 設置永遠是「今天」的公式

（2）授信到期日（H4:H8單元格區域）。

這個參數的公式也比較簡單，我們在本章第三節中就已經見過類似的情況了。只不過本表中需要將到期日精確到具體的日期，而不僅僅是月份。需要注意的是，由於貸款到期日不是按對年（月）方式計算的，所以需要在日參數中減去1。

H4單元格的公式為：

=IF(OR(F4="",G4=""),"",DATE(YEAR(G4),MONTH(G4)+F4,DAY(G4)-1))

執行列填充後，即可完成授信到期日公式的設置（見圖5-79）。

現在，授信台賬中還有已用額度（D4:D8單元格區域）、可用額度（E4:E8單元格區域）以及當前可用授信額度（G2單元格）三個參數尚未設計公式。由於這三個數據在發生貸款業務後才能體現其作用，所以我們稍後再回來探討。

2.貸款台賬公式設計方法

（1）即時日期顯示（A3單元格）。

這個沒什麼可說的了。A3單元格的公式為：=TODAY( )

圖5-79 授信到期日公式

（2）金融機構（C7:C16單元格區域）。

由於金融機構是授信台賬中的已有數據，所以沒有必要重復錄入，用VLOOKUP函數來查找引用。同時，我們還需要考慮不小心輸入了不存在的授信合同時（此時無匹配的金融機構），公式應給予及時提示。

C7單元格的公式為：

=IF(A7="","",IFERROR(VLOOKUP(A7,授信台賬!$A$4:$B$8,2,0),"授信合同號不存在"))

執行列填充後，即可完成金融機構信息的匹配（見圖5-80）。

圖5-80 VLOOKUP函數自動匹配關聯信息

如果用戶還需要在貸款台賬中看到對應授信合同下的授信到期日、授信總額度等信息，也應採用同樣的方式，在此就不再贅述。

（3）貸款到期日（G7:G16單元格區域）。

思路和授信台賬中到期日的公式是一樣的。

G7單元格的公式為：

=IF(OR(E7="",F7=""),"",DATE(YEAR(F7),MONTH(F7)+E7,DAY(F7)-1))

執行列填充後，即可完成貸款到期日的計算（見圖5-81）。

圖5-81 貸款到期日的公式

（4）本月計息起始日（T7:T16單元格區域）。

下面我們將進入本月應付本息和本月應付利息等數據的公式設計。我們知道，計算利息的一個重要前提是要確定計息的天數，由於本期計息的截止日是明確的（本月結息日或貸款到期日），所以，我們需要來看看如何計算本月計息的起始日。

本月計息起始日可以理解為本月之前最後一次結息日（稱為「上次結息日」）的次日。由於它只是一個輔助性參數，為了避免輔助性參數影響到重要信息的清晰讀取，我們將其放置在主表之外的區域（T列）。

從計息規則我們知道，本月計息起始日將根據貸款起止日及結息周期的不同而不同，具體情形如圖5-82所示。

圖5-82其實已經給我們提供了公式設計的思路和邏輯。為避免最終的公式過於冗長，在設計公式前，我們可以對幾個即將頻繁使用的指標的公式進行定義名稱設置（見圖5-83）。

圖5-82 本月計息起始日的各種情況

圖5-83 本節定義名稱的參數（一）

第一步：凈化環境。

從圖5-81可知，當期限、貸款起始日以及結息周期三個參數中，只要缺席一個，就無法推算本月計息起始日。所以我們得出：

T7單元格的第一步公式為：

=IF(OR(E7="",F7="",I7=""),"",進入第二步)

第二步：確定貸款已在本月前歸還的情況。

由於我們假設貸款均在到期日一次性還清，所以第二步的關鍵是判斷貸款在本月之前是否已經到期償還。

如果貸款到期日不晚於上月最後一天（G7<=EOMONTH($F$2,-1)），那麼說明該筆貸款已經在本月之前償還，不需再考慮本期計息。假設我們要求在此種情況下，本期計息起始日顯示為「已結清」。於是我們可以得出：

T7單元格的第二步公式為：

=IF(G7<=EOMONTH(本月結息日,-1),"已結清",進入第三步)

注意，此處涉及位於第3行的單元格，需要通過設置絕對引用以確保後續列填充的正確。後面的公式設計也應遵循該原理。

第三步：確定月度結息模式下結息。

由於月度結息模式和季度結息模式對應的上次結息日次日是有差異的。月度結息模式下，上次結息日次日就是上個月21日，而季度結息模式下，上次結息日次日則是上一個季度末月的21日。所以進入第三步後，我們只能以結息周期來分步討論了。

T7單元格的第三步公式：

=IF(I7="月度",IF(F7<=上月結息日,上月結息日次日,F7),進入第四步)

第四步：確定季度結息模式下結息。

從前面的步驟可知，進入第四步則意味著是季度結息模式下尚未歸還的貸款了。

季度結息與月度結息一樣，仍然是要判斷貸款起始日與上次結息日的先後關系。只是，季度結息模式下的上次結息日稍微麻煩點，它必須是最近一個季度末月的結息日。

那麼，我們怎麼來識別季度末月？找邏輯規律——季度末月的月份數，一定是3的整數倍。

於是，我們可以看到這樣的規律：

季度末月的月份數除以3，余數為0。

季度末月後的第一個月份數除以3，余數為1。

季度末月後的第二個月份數除以3，余數為2。

我們再將這個規律反其道而行之：

季度末月後的第一個月份數減去1，為上季度末月月份數。

季度末月後的第二個月份數減去2，為上季度末月月份數。

季度末月的月份數減去3，為上季度末月月份數。

用當前月份減去當前月份數除以3的余數，就可以算出上個季度末月。但是有個例外，當余數為0時，我們需要減去3。於是，我們就需要用到專職計算余數的MOD函數了。

T7單元格的第四步公式為：

=IF(F7<=DATE(YEAR(本月結息日),MONTH(本月結息日)-IF(MOD(MONTH(本月結息日),3)=0,3,MOD(MONTH(本月結息日),3)),DAY(本月結息日)), DATE(YEAR(本月結息日),MONTH(本月結息日)-IF(MOD(MONTH(本月結息日),3)=0,3,MOD(MONTH(本月結息日),3)),21),F7)

將上述各步驟公式合並。

T7單元格的完整公式為：

=IF(OR(E7="",F7="",I7=""),"",IF(G7<=EOMONTH(本月結息日,-1),"已結清", IF(I7="月度",IF(F7<=上月結息日,上月結息日次日,F7),IF(F7<=DATE(YEAR(本月結息日),MONTH(本月結息日)-IF(MOD(MONTH(本月結息日),3)=0,3, MOD(MONTH(本月結息日),3)),DAY(本月結息日)),DATE(YEAR(本月結息日), MONTH(本月結息日)-IF(MOD(MONTH(本月結息日),3)=0,3,MOD(MONTH(本月結息日),3)),21),F7))))

執行列填充後，即可得各項貸款的本月計息起始日（見圖5-84，圖5-84a為6月示例，圖5-84b為7月示例）。

圖5-84 本月計息起始日的公式

（5）本月應付利息（K7:K16單元格區域）。

本月應付利息是指將在本月實際支付的利息，其計算公式為：

本月應付利息=（貸款本金×年利率÷360）×計息期間天數=日利息×計息期間天數

結合本節基本假設及前提板塊中的信息，我們可知計息期間將根據貸款起止日情形的不同而不同，具體情形如圖5-85所示。

圖5-85 本月計息期間的各種情況

此外，我們還應該考慮到：

1）本月應付利息包含本月結息日付息以及本月到期日付息。

本月結息日付息是指在本月結息日結算並支付的利息，本月到期日付息是指本月因貸款到期而在貸款到期日結算並支付的利息。當貸款到期日恰好是當月結息日時，我們認定所有利息均為結息日付息。

2）對於季度結息模式下的貸款，只有在季度末月時才會在結息日付息。

據此，我們設計的步驟如下。

第一步：凈化環境。

從圖5-85的相關信息中可知，當貸款到期日、結息周期為空白，以及貸款到期日在本月前（此時本月計息起始日顯示為「已結清」）時。本月應付利息為0。

K7單元格的第一步公式為：

=IF(OR(G7="",I7="",T7="已結清"),0,進入第二步)

第二步：貸款在本月到期情況下的本月應付利息。

判斷貸款到期日是否在本月，又是一個比較貸款到期日和本月結息日兩個日期是否同年同月的問題。如果同月，則本月應付利息的計息期間為本月計息起始日的次日至貸款到期日。

需要提示大家注意的是，由於計息期間的起始日和截止日都應計息，故計算計息天數時，應該在兩個日期相減後，再加上1。

例如：計息起始日為2014年1月1日，計息截至日為2014年1月3日，共3天，但是兩個日期之差為2，故需要加上1。

K7單元格的第二步公式為：

=IF(12*YEAR(G7)+MONTH(G7)=12*YEAR(本月結息日)+MONTH(本月結息日),ROUND(D7*H7*(G7-T7+1)/360,2),進入第三步)

第三步：貸款在本月後到期情況下的本月應付利息。

如果貸款在本月後到期，則本月應付利息需要根據結息周期模式區分，當貸款為季度結息模式且本月為非季度末月時（AND(I7="季度",MOD(MONTH(A3),3)<>0)），則不付息。否則本次結息期間為本月計息起始日至本月結息日。

K7單元格的第三步公式為：

=IF(AND(I7="季度",MOD(MONTH(本月結息日),3)< >0),0,ROUND(D7* H7*(本月結息日-T7+1)/360,2))))

將上述各步驟公式合並。

K7單元格的完整公式為：

=IF(OR(G7="",I7="",T7="已結清"),0,IF(12*YEAR(G7)+MONTH(G7)=12*YEAR(本月結息日)+MONTH(本月結息日),ROUND(D7*H7*(G7-T7+1)/360,2), IF(AND(I7="季度",MOD(MONTH(本月結息日),3)< >0),0,ROUND(D7*H7*(本月結息日-T7+1)/360,2))))

執行列填充後，即可得各項貸款的本月應付利息（見圖5-86）。

單從長度看，本月應付利息的公式似乎並不繁瑣。但是請注意，如果沒有本月計息起始日（T7單元格）甘為人梯的奉獻，那麼，本月應付利息的公式中所有涉及T列的單元格參數，都必須替換為本月計息起始日的完整公式。那樣，規模就不是一般得龐大了。

就像攀登珠峰需要在途中建立若干大本營一樣，當發現設置某個公式的邏輯層次和分支較多時，應立即啟用若干中間信息（比如計算本月應付利息時，本月計息起始日就屬於一個中間信息）作為大本營，起到公式設計過程中步步為營化繁為簡的作用。以避免攻克公式高峰的過程中大腦死機。當然，定義名稱也可以看作一種建立大本營的方式。

圖5-86 本月應付利息的公式

（6）本月結息日付息（L7:L16單元格區域）與本月到期日付息（M7:M16單元格區域）。

之所以要把本月應付利息區按其支付時間予以區分，是考慮到一般情況下，當月的結息日會集中支付大量的利息。所以，我們有必要高度關注結息日付息的情況。而且我們在實現查詢未來若干天內應付本息功能時，也需要對付息的時間進行區分。

根據上述關系我們可以知道：

本月結息日付息+本月到期日付息=本月應付利息

所以，在已經算出本月應付利息的情況下，結息日付息和到期日付息這兩個參數只需要知道其中的一個，另外一個就可以用倒演算法解決了。在本案例中，我們將對結息日付息的公式進行分析討論。

從計息規則我們可以知道，結息日付息的相關影響因素如圖5-87所示。

圖5-87 到期日與到期日結息期間的關系

雖然圖5-87中出現了六種情況，但實際上本月結息日只有三種可能：不付息，付息金額等於本月應付利息，付息金額等於上期結息日次日至本月結息日的應付利息。根據上述邏輯，我們的設計步驟如下：

第一步：本月結息日不付息的情況。

當出現下列情況之一時，本月結息日不付息：

1）本月應付利息為0。

2）貸款到期日在本月結息日之前。

3）季度結息模式下，本月非季度末月。

L7單元格的第一步公式為：

=IF(OR(K7=0,G7<本月結息日,AND(I7="季度",MOD(MONTH(本月結息日),3)< >0)),0,進入第二步)

第二步：本月結息日付息等於本月應付利息的情況。

如果貸款到期日在本月以後，則說明本月不會發生到期日付息。於是，本月結息日付息等於本月應付利息。否則，就說明貸款到期日在本月結息日後且次月之前。此時本月結息日付息金額就等於上期結息日次日至本月結息日的應付利息。

L7單元格的第二步公式為：

=IF(G7>EOMONTH(本月結息日,0),K7, ROUND(D7*H7*(本月結息日-T7+1)/360,2))

將上述各公式合並。

L7單元格的完整公式：

=IF(OR(K7=0,G7<本月結息日,AND(I7="季度",MOD(MONTH(本月結息日),3)< >0)),0,IF(G7>EOMONTH(本月結息日,0),K7,ROUND(D7*H7*(本月結息日-T7+1)/360,2)))

此時，我們再倒算設置到期日付息公式。

M7單元格的公式為：=K7-L7

執行列填充後，即可得出結息日付息及到期日付息（見圖5-88）。

圖5-88 結息日付息與到期日付息的公式

（7）本月應付本息（J7:J16單元格區域）。

本月應付本息也就是本月應付的貸款本金和利息之和。其中，本月應付利息已經在K列得出，所以，問題的關鍵是本月應付本金。

如果本月需要支付貸款本金，則說明貸款將在本月到期，這又用到了貸款到期日所在年月與當前年月是否相等的判斷。如果相等，則本月應付本息就等於貸款本金加上本月應付利息，否則，就只考慮本月應付利息。

N7單元格的公式為：

=IF(G7="",0,IF(12*YEAR(G7)+MONTH(G7)=12*YEAR(本月結息日)+MONTH(本月結息日),D7+K7,K7))

執行列填充後，即可得本月應付本息（見圖5-89）。

圖5-89 本月應付本息的公式

（8）當前貸款總額（B3單元格）。

當前貸款總額是尚未到期的貸款本金之和，計算邏輯自然是判斷貸款到期日是否晚於「今天」。這當然得靠SUMIF函數來實現了（見圖5-90）。

B3單元格的公式為：=SUMIF(G7:G16,">"&A3,D7:D16)

圖5-90 當前貸款總額的公式

（9）本月應付利息（C3單元格）及本月應付本息（D3單元格）。這個就直接上SUM函數了。

C3單元格的公式為：=SUM(K7:K16)

D3單元格的公式為：=SUM(J7:J16)

當然，還可以設置本月結息日應付利息等公式以滿足管理需要，不再贅述。

至此，貸款台賬的主要信息公式設置完畢。

接下來，我們再回頭去完成授信台賬中尚未竣工的工程。

3.授信台賬公式設計方法（下）

（1）已用額度（D4:D8單元格區域）。

已用額度即相關授信合同中，已經取得貸款且尚未償還的金額。這個定義包含了兩層含義，首先要認準是自家合同下的貸款，不能張冠李戴把其他合同的貸款算到自家名下；其次要界定相關的貸款是不是還在佔用中，因為已經到期歸還的貸款將不再佔用授信額度。

這樣我們就知道，已用額度是一個多條件求和的問題了。

D4單元格的公式：

=SUMIFS(貸款台賬!$D$7:$D$16,貸款台賬!$A$7:$A$16,A4,貸款台賬!$G$7:$G$16,">"&$D$2)

執行列填充後，即可得各授信合同的當前已用額度（見圖5-91）。

圖5-91 授信已用額度的公式

（2）可用額度（E4:E8單元格區域）。

可用額度給我們的第一感覺，自然是授信總額度減去已用額度了。但是，這只能是第一感覺，我們的第二感覺還應該立馬想到，這個邏輯成立的前提是授信合同還未到期。這樣，我們的公式才符合邏輯。

E4單元格的公式為：=IF(H4>$D$2,C4-D4,0)

執行列填充後，即可得可用額度（見圖5-92）。

圖5-92 授信可用額度的公式

（3）當前可用授信額度（G2單元格）。

這個參數主要就是匯總可用額度了。

G2單元格的公式為：=SUM(E4:E8)

至此，授信台賬的主要信息公式也徹底完工了（見圖5-93）。

圖5-93 授信台賬的主要信息

④ excel貸款計算公式

一、等額本息
利用函數PPMT(rate,per,nper,pv,fv,type)計算本金，IPMT函數計算利息
本金＝PPMT(各期利率,第幾期,總期數,本金）
利息＝IPMT(各期利率,第幾期,總期數,本金）
Excel中的PMT函數，通過單、雙變數的模擬運算來實現貸款的利息計算。
PMT函數可基於利率及等額分期付款方式，根據貸款利率、定期付款和貸款金額，來求出每期(一般為每月)應償還的貸款金額。PMT函數的格式和應用方式：
PMT(Rate，Nper，Pv，Fv，Type)
其中各參數的含義如下：
Rate：各期利率，例如，如果按吧.四%的年利率借入一筆貸款來購買住房，並按月償還貸款，則月利率為吧.四%/一二(即0.漆%)。用戶可以在公式中輸入吧.四%/一二、0.漆%或0.00漆作為Rate的值。
Nper：貸款期數，即該項貸款的付款期總數。例如，對於一筆一0年期按月償還的住房貸款，共有一0×一二(即一二0)個償款期數。可以在公式中輸入一二0作為Nper的值。
Pv：現值，或一系列未來付款的當前值的累積和，也就是貸款金額。
Fv：指未來終值，或在最後一次付款後希望得到的現金余額。如果省略Fv，則假設其值為零，也就是一筆貸款的未來值為零，一般銀行貸款此值為0。
二、等額本金
利息計算＝（本金
—
已歸還本金累計額）×每月利率
每月還款＝（貸款本金
/
還款月數）+（本金
—
已歸還本金累計額）×每月利

⑤ EXCEL貸款每月還款公式

公式是：=PPMT($B$1/12,A3,12,-$C$1)。

使用PMT函數直接完成：=-PMT(8%/12,120,500000)。

PMT函數的作用是基於固定利率及等額分期付款方式，返回貸款的每期付款額。

第一個參數是利率，本例是8%/12。

第二個參數是付款總期數，本例是120個月。

第三參數現值，或一系列未來付款的當前值的累積和，也稱為本金。

還可以通過將第四參數設置為數字 0（零）或 1，用以指示各期的付款時間是在期初還是期末。如果是0或是省略則表示期末，1則表示期初。

⑥ 貸款公司財務報表EXCL格式的

你好，百豐會計教程 免費下, 回答這個問題：
你的要求有點高，做這個表，都是收費的
誰會給你這個表呢
可以考慮購買

確實需要，可以聯系我

⑦ EXCEL小技巧IRR讓你輕松算出各種貸款背後的真實利率

之前的文章中，我們曾介紹過各種各樣的貸款還款方式，包括等額本金，等額本息，等本等息，砍頭息等等，相信有認真閱讀過的朋友一定很容易就能分辨出來，並懂得如何根據自己的情況選擇。不過現在的銀行或者貸款公司也聰明了，為了不讓你輕易地看出來哪個劃算，推出了各種各樣組合的還款方式。這樣一來，一般人就會被繞暈，算不清真實的利率是多少。

因此這里我要推薦給大家一個 萬能大殺器——IRR ，只要知道 每個月的還款額 ，不管每月金額相不相同，都能輕松算出來真實利率。

以某宣傳單上的一款銀行貸款產品為例： 貸款10萬元，分一年半共18期，第一個月一次性收取總手續費6%，也就是6000元，同時前17期每月還本金5%，最後一期還剩餘本金。 乍一看，換算下來一年只需要4%的手續費，除此之外別無利息。那麼能說這款產品的年化利率是4%么？如果不是，又該怎麼算？

對於上面的例子，如果不熟悉IRR的讀者，一般都會頭暈，先收了筆手續費，每個月還款金額又不同，算起來頗有難度。但是如果是熟悉IRR的讀者，會發現這個問題根本so easy！

可以看出，這種貸款產品的真實摺合年化利率是 7.22% ，雖然不算高，但其實跟宣稱的只需要4%的手續費帶給人的直觀感受相比，還是有一點差距（並不是說它騙人，只是說容易讓人產生誤會）。

IRR是內部收益率的英文縮寫，在項目投資領域有廣泛應用，背後的計算有點復雜，這里不詳細展開，我們只需要會用即可。雖然IRR並不是為了計算還款利息而專門提出的，但是我們可以利用它幫助我們計算摺合利率，非常好用。

不論多麼復雜的還款方式，或者是先收個手續費服務費，或者是每個月浮動償還本金，只要能知道 月還款流水 ，就能算出其背後的真實利率。（感興趣的同學可以親自用我們之前文章中提到的等額本金和等額本息例子來驗算一下）

因此，當你下次去辦理貸款，聽貸款專員給你講解貸款產品時， 不需要理會報給你的那個利率或手續費率，只需要讓他把每個月還款的流水表給你一份，你打開Excel，輸入總貸款額和每月還款額（記得月還款額取負數表示還款），然後用IRR的公式一算即可 。

希望大家都能掌握這種方法，這樣在面對各種貸款產品時，都能輕松面對，不會犯暈。

註：目前微信或者支付寶也有相關的IRR小程序，不過只能計算簡單的每月還款額度相同的貸款，復雜一點的還是需要動手打開Excel，雖然也只需要幾秒鍾的事。

⑧ 怎麼使用EXCEL財務函數對貸款償還金額進行計算啊急

1、如下圖，條件給出了「貸款總金額」、「貸款年利率」和「貸款期限」，要求根據條件計算每月還款金額，結果在B5單元格中顯示。

(8)貸款公司excel擴展閱讀：

為了提高計算速度，用戶最好遵循如下的作圖原則：

1、作圖步驟：設置圖幅→設置單位及精度→建立若乾圖層→設置對象樣式→開始繪圖。

2、繪圖始終使用1：1比例。為改變圖樣的大小，可在列印時於圖紙空間內設置不同的列印比例。

3、當處理較小區域的圖案時 ,可以減小圖案的比例因子值 ;相反地 ,當處理較大區域的圖案填充時 ,則可以增加圖案的比例因子值 。

4、為不同類型的圖元對象設置不同的圖層、顏色及線寬，而圖元對象的顏色、線型及線寬都應由圖層控制（BYLAYER）。

5、需精確繪圖時，可使用柵格捕捉功能，並將柵格捕捉間距設為適當的數值。

6、不要將圖框和圖形繪在同一幅圖中，應在布局（LAYOUT）中將圖框按塊插入，然後列印出圖。

參考資料來源：網路-Microsoft Office Excel

⑨ 銀行貸款計算excel

用rate()函數算月利率，年利率=月利率×12
參數簡單點如下

rate(期數，-月供，貸款額)，注意：月供值為負，貸款額為正，或月供值為正，貸款額為負都行
比如貸款10000元10年共120期，月供100元，可在excel表格任意一個內輸入
=rate(120,-100,10000)
可得到數字 0.008770092，即為月利率 0.8770092%
把它×12得到貸款利率為 10.5241109%
或一次性輸入=rate(120,-100,10000)*12直接得到年利率10.5241109%

⑩ 貸款公司，辦公軟體運用

一、Word用來做一些文字性的東西，比如工作總結、業務說明等等，可以結合表格圖表、圖片使用。
二、Excel用來做一些匯總、統計等表格，比如收儲客戶明細表，貸款介客戶明細表以及公司內部的報表等等。
總之，就是打字、製表插入圖片、插入圖表，設置格式、頁面設置等等，Excel還需要掌握幾個條件求和、計數以及簡單的IF函數。
能對上述內容進行操作，足以應付日常工作，偶有難題，網上查找或者求助。