我們知道銀行貸款的按揭還貸，主要分為等額本息、等額本金兩種還款方式。其中等額本息是每月還款固定金額，等額本金是逐月遞減（總利息最少）。之前，小樂分享給大家如何使用Windows計算器簡單速算等額本息每月還款金額，本文結合公積金貸款、商業貸款，等多種複合條件，分享給大家如何使用身邊的Excel，根據不同情況，詳細計算每月貸款還款額度。
工具/原料
Excel
使用Excel設置貸款計算器模板標題
在Excel中，我們先建一張如下圖模板的表格。
顏色說明：
黃色背景單元格，用來自己填寫數據。
綠色背景的單元格，一般也需要自己填好。
橙色背景的單元格，是我們著重關注的貸款每月還款金額。
其他單元格，一般都是讓公式自動計算生成數據。
第1列，在單元格A4使用下列公式填寫年月。
=EDATE("2015-11-2",ROW()-3)
其中2015-11-2，需修改為貸款實際開始還款的那個月，當中的任意一天。
注意：
這裡使用公式，而不是直接填寫年份月份，是為了方便往下拉公式，實現自動填充。
右擊單元格，設置單元格格式。
在自定義中，修改為yyyy-m，這樣的格式。
第1行，在單元格D1使用公式
="年限（"&E1*12&"期)"
在單元格H1使用公式
="打折（實際為"&TEXT(G1*I1,"0.00%")&"）"
在單元格L1使用公式
="總利息："&ROUND(C1*IF(K1=1,G1*I1/12*(E1*12+1)/2,G1*I1/12/(1-1/(1+G1*I1/12)^(E1*12))*E1*12-1),2)&" 相當於本金的"&ROUND(IF(K1=1,G1*I1/12*(E1*12+1)/2,G1*I1/12/(1-1/(1+G1*I1/12)^(E1*12))*E1*12-1),2)&"倍"
然後將上述3個單元格，分別往下拉到第2行（複製公式）
這樣按揭貸款計算器的標題，即製作完畢。
使用公式計算公積金貸款每月還貸金額、本金、利息
計算公積金逐月還貸，每月需還款的金額：
在單元格B4，使用公式
=ROUND(IF($K$1=1,$C$1/$E$1/12+$C$1*(1-((ROW()-4)/$E$1/12))*$G$1/12,$C$1*$G$1*$I$1/12/(1-1/(1+$G$1*$I$1/12)^($E$1*12))),2)
為了計算公積金等額本金或者等額本息，每期還款時，貸款本金和貸款利息分別還了多少，我們在單元格C4，輸入公式：
="本金："&ROUND(IF($K$1=1,$C$1/$E$1/12,-($C$1*$G$1*$I$1/12-B4)*(1+$G$1*$I$1/12)^(ROW()-4)),2)&" 利息："&ROUND(IF($K$1=1,$C$1*(1-((ROW()-4)/$E$1/12))*$G$1*$I$1/12,($C$1*$G$1*$I$1/12-B4)*(1+$G$1*$I$1/12)^(ROW()-4)+B4),2)
公積金月繳額（單位+個人），是需要自己填寫的，因為因人而異，
注意，需要填寫公積金月繳總金額，包含單位交的和個人交的。
然後在單元格F4，輸入公式
=B4-D4
即可立即得到，每月需要實際準備的現金，用於還公積金貸款。
如果結果為負值，說明你的公積金足夠多，根本不需要另外準備現金或打款到貸款銀行賬戶。
使用公式計算商業貸款每月還貸金額、本金、利息
類似地，我們在單元格H4使用公式
=ROUND(IF($K$2=1,$C$2/$E$2/12+$C$2*(1-((ROW()-4)/$E$2/12))*$G$2/12,$C$2*$G$2*$I$2/12/(1-1/(1+$G$2*$I$2/12)^($E$2*12))),2)
來計算逐月按揭商業貸款，需要每月還款多少。
然後在單元格J4，輸入公式
="本金："&ROUND(IF($K$2=1,$C$2/$E$2/12,-($C$2*$G$2*$I$2/12-H4)*(1+$G$2*$I$2/12)^(ROW()-4)),2)&" 利息："&ROUND(IF($K$2=1,$C$2*(1-((ROW()-4)/$E$2/12))*$G$2*$I$2/12,($C$2*$G$2*$I$2/12-H4)*(1+$G$2*$I$2/12)^(ROW()-4)+H4),2)
來計算商貸，每月還貸本金和利息的明細。
至此，我們已經完成了所有的公式製作，
直接選中第4行的相應單元格，往下拉公式
即可實現快速計算每月貸款還款金額。
Excel貸款計算器使用方法與金融數學原理
最後，我們總結一下，剛剛使用Excel製作的貸款計算器的具體使用方法與原理。
使用方法很簡單，直接按照圖示7個步驟，填好數據和公式，最後往下拉即可。
為了兼顧需要深究鑽研的朋友，本文最後補充一下等額本息、等額本金，每月還款本金、利息的計算原理與方法。
等額本金，顧名思義，就是每月還款中，包含的還款本金都一樣。
只不過，由於欠銀行的貸款本金逐月減少，那麼相應的利息也逐月減少。
因此，會出現逐月還款後，所還的金額越來越少的現象（逐月遞減）。
假設貸款總金額a，年利率P，年限n（共n*12個月，即分為n*12期按揭），
月利率p=P/12
具體等額本金的計算公式（第i個月）：
每月還款含本金（都相等）：a/(12n)
每月還款含利息（逐月遞減，等差數列，首項為ap，公差-ap/(12n)）：
ap(1-(i-1)/(12n))
每月還款總額：a/(12n)+ap(1-(i-1)/(12n))
總利息（等差數列求和，），為（ap-（n*12-1）*ap/(n*12)/2）*n*12
=ap(n*12+1)/2
等額本息，顧名思義，就是每月還的本金加利息，總和固定。
假設貸款總金額a，年利率P，年限n（共n*12個月，即分為12n期按揭），
月利率p=P/12
每月還款總額都為x
具體等額本息的計算公式：
第1個月：
還款利息：ap
還款本金：x-ap
第2個月：
還款利息：(a-(x-ap))p = (ap-x)(1+p) +x
還款本金：x-( (ap-x)(1+p) +x ) = -(ap-x)(1+p)
第3個月：
還款利息：(a-(x-ap)+(ap-x)(1+p))p = (ap-x)(1+p)² +x
還款本金：x-( (ap-x)(1+p)² +x ) = -(ap-x)(1+p)²
以此類推，
第i個月：
還款利息：(ap-x)(1+p)^(i-1) +x
還款本金： -(ap-x)(1+p)^(i-1)
一直到最後一個月（第n*12個月）：
還款利息：(ap-x)(1+p)^(12n-1) +x
還款本金： -(ap-x)(1+p)^(12n-1)
將每個月的還款本金（是等比數列，首項為x-ap，公比為1+p），
相加之和應該等於總本金a，即
（x-ap）(1-(1+p)¹²ⁿ) / (1-(1+p)) = a
則
x=ap(1+1/((1+p)¹²ⁿ - 1))
將x再代入上面的各月的式子，得到：
第1個月：
還款利息：ap
還款本金：ap/((1+p)¹²ⁿ - 1)
第2個月：
還款利息：ap((1+p)¹²ⁿ-(1+p))/((1+p)¹²ⁿ - 1)
還款本金：ap(1+p)/((1+p)¹²ⁿ - 1)
第3個月：
還款利息：ap((1+p)¹²ⁿ-(1+p)²)/((1+p)¹²ⁿ - 1)
還款本金：ap(1+p)²/((1+p)¹²ⁿ - 1)
以此類推，
第i個月：
還款利息：ap((1+p)¹²ⁿ-(1+p)^(i-1))/((1+p)¹²ⁿ - 1)
還款本金： ap(1+p)^(i-1)/((1+p)¹²ⁿ - 1)
總利息，ap(1+p)¹²ⁿ(12n)/((1+p)¹²ⁿ - 1) - [(1-(1+p)¹²ⁿ)/(1-(1+p))] *ap/((1+p)¹²ⁿ - 1)
=ap(1+p)¹²ⁿ(12n)/((1+p)¹²ⁿ - 1)-a
注意事項
不同時期貸款基準利率不同，而且折扣不同，需作相應設置修改
公積金賬號因人而異，一般每年會調整一次每月繳存額