前沿拓展：

**貸計算

計算房貸剩余貸款，計算方式有以下兩種：
一、等額還款法：即借款人每月按相等的金額償還貸款本息，其中每月貸款利息按月初剩余貸款本金計算并逐月結清。
具體的公式為：月還款額=本金*月利率*。式中n表示貸款月數，^n表示n次方，如^240，表示240次方(貸款20年、240個月)月利率=年利率/12，總利息=月還款額*貸款月數-本金。
二、等額本金還款法：即借款人每月按相等的金額(貸款金額/貸款月數)償還貸款本金，每月貸款利息按月初剩余貸款本金計算并逐月結清，兩者合計即為每月的還款額。
具體的公式為：月還款額=本金/n+剩余本金*月利率，總利息=本金*月利率*(貸款月數/2+0.5)。
【拓展資料】
房貸，也被稱為房屋抵押貸款。房貸，是由購房者向銀行填報房屋抵押貸款的申請，并提供合法文件如身份證、收入證明、房屋買賣合同、擔保書等所規定必須提交的證明文件，銀行經過審查合格，向購房者承諾發放貸款，并根據購房者提供的房屋買賣合同和銀行與購房者所訂立的抵押貸款合同，辦理房地產抵押登記和公證，銀行在合同規定的期限內把所貸出的資金直接劃入售房單位在該行的帳戶上。
2013年11月24日，最新的調查數據顯示，根據10月11日-11月11日的樣本數據，32個城市中已有17個城市出現暫停房貸現象。
房貸的參與方，包括提供信貸資金的商業銀行、最終購置房產的購房人，以及房產的業主（包括開發商/二手房的業主），在申請貸款時，還需要有評估公司、房屋抵押擔保公司的參與。
根據房地產擔保公司北京萬才聯合投資管理有限公司2010年末發布的統計，在國內主要一線城市，房貸的使用率已經達到較高水準，以購房按揭貸款來說，貸款比重已經達到7成以上，而且近幾年，通過用自己名下或親人的房產申請房產抵押消費貸款以實現盤活不動產的居民人數也越來越多。“房貸”已經成為與居民生活息息相關的一種生活方式。

這幾天因為存量房貸轉軌對接LPR的事情，大家對于房貸突然又關注了起來。至于怎么選，前兩天也說了我的建議。不過我建議歸建議，怎么選最關鍵的是對未來利率走勢的預判，我說的再多，也不如大家自己決定的好。

正是因為大家對房貸突然關注了，所以這幾天突然有很多朋友來問我，還了這么長時間的房貸，現在還欠銀行多少錢？也就是房貸余額還有多少。其實稍微懂一點金融的朋友就知道用EXCEL，列個表來算是比較容易的。但是很多人不知道，其實不用列個表，如果房貸利率從來沒變過的話，在EXCEL只用一個公式就能算出來了。下面告訴大家怎么**作以及原理。

1.正常人算法：列一個大表求解

因為很多人不懂房貸計算的原理，為了方便大家，我先講正常人算法（如果這部分很熟的話直接跳過到后面就行）。在計算之前必須要知道的幾個參數：貸款總額、利率和還款總期數。講到這里有人就要問了，月供多少錢不是必須參數嗎？其實并不是的，因為知道貸款總額、利率和還款總期數就能自己算月供了。

那么我們先降低難度，假設貸款總額、利率、還款總期數和月供這四個數字我們都知道，接下來我們列一個表格就能算出來。為了方便大家理解，我采用一種通用的情況，假設貸款總額100萬，利率是基準利率4.9%，貸款20年也就是總還款20年*12期/年=240期，每個月月供是6544.44元。我先把做好的表拿給大家：

第一列是期初的貸款余額（也等于上一列第二列的期末貸款余額）。第二列是期末貸款余額，也就是本期還了月供以后剩余的貸款余額。第三列是月供。第四列是本期應該還的利息。第五列是本期還的本金。其中，第二列等于第一列減去第五列這個用直覺就能理解，第五列的等于第三列減去第四列，唯一有稍微復雜計算公式的是第四列。

第四列的公式上面的圖用紅框標出來了，其中round函數是取小數點后2位的函數，不用的話也沒關系。

其實房貸的原理很簡單，我就用第一個月來舉例，第一個月欠銀行貸款100萬，一個月銀行要收利息100萬乘以4.9%的12分之一（因為4.9%是年利率，這樣算出月利率），也就是產生了4083.33的利息。那么月供6544.44比這個利息多，多出來的部分就用來還本金，并且導致貸款余額減少。那么每一列都用同樣的方法計算，一直算到最后一期，這個表就做完了。這里使用EXCEL下拉框拉一下就搞定。

那么隨著時間推移，月供里面超過利息的部分越來越多，也就是說，貸款余額越來越少，月供里還的利息也越來越少，還的本金越來越多，最后把整個貸款都還完。

做完表格以后，查房貸余額就容易了，只要知道自己還了多少期，找到對應期數那一行的第三列期末貸款余額就知道了。或者我們也可以用高級一點的辦法，用vlookup函數，公式是：vlookup([第一列]:[第三列],[還款期數],3,0)。在我這個例子里面第一列是A列，第三列是C列，還款期數寫在D2單元格里面。

講到這里最簡單的模式已經講完了。其實這個表里面如果不知道月供，也可以算出來的。這里有兩種方法，第一種方法就是用EXCEL的方法**算。還是用同樣的這個表格，先把第三列月供這一列清空，然后讓他們都等于一個未知數單元格，之后使用EXCEL單變量求解的功能，目標單元格是最后一期期末貸款余額等于0，然后EXCEL會自動算出來。因為這個方法比較復雜，而且很傻，現在已經基本上沒人用了（甚至知道的人都不多了）。

第二種方法是用年金函數，也就是PMT函數。公式是PMT([年利率]/12,[年數]*12,-[貸款總額])，注意貸款總額前面有一個負號，在我這個例子里就是PMT(B3/12,20*12,-1000000)。當然，因為這個算出來的小數點后面很長，可以再套用一個round函數，可以看到算出來的結果就是6544.44。

2.精英算法：一個公式搞定

我先告訴大家結果，然后再說原理吧。直接用EXCEL算法是FV([年利率]/12,[已還款期數],[月供],-[貸款總額])。對你沒看錯，就這一個公式就夠了，看到這里學過CFA的高端人士肯定已經知道是怎么回事了。這個計算結果跟用表格計算是基本一樣的，出現0.02的尾差是因為6544.44這個月供本身就會造成0.07的尾差。

這個公式大家可能不是很明白，我先用數學計算式模擬一下，同樣的算法也就是：[貸款余額]*(1+([年利率]/12)^[已還款期數]-[月供]*(1-(1+[年利率]/12)^[已還款期數])/(1-(1+[年利率]//12))。

這里用的原理在金融里面還是挺常見的，叫做**原理。怎么個**法呢？就是假設我的房貸不按照原先的方式計算了，銀行給我一種“替代”方法。銀行要求我每個月去銀行投資一個定投產品，這個產品的利率和復利方式跟房貸的利率一模一樣，每個月定投的錢也跟月供一模一樣。那么對我來說，我每個月還是給銀行那么多月供的錢，跟每個月還月供其實是一樣的。

現在唯一的區別就是，我的房貸沒有還，每個月都會利滾利，但同時銀行讓我做的理財也在利滾利，到最后一期，我定投理財的利滾利會和房貸利滾利的總結果一樣，我就直接拿定投的錢把房貸連本帶利一次性還掉就跟銀行兩清了。那么房貸利滾利的總金額，減掉我這個虛擬的定投產品總金額，就是我還欠銀行的錢。

這些就是數學算法的內涵，數學算法的前半部分[貸款余額]*(1+([年利率]/12)^[已還款期數]，也就是到已還款期數那一天，房貸利滾利之后我欠銀行的錢。后半部分[月供]*(1-(1+[年利率]/12)^[已還款期數])/(1-(1+[年利率]//12))，就是我買月供每個月定投產生的本息和。后半部分其實是用的等比數列的求和方法，或者用EXCEL的FV函數就變成了FV([年利率]/12,[已還款期數],[月供])。

看到這里有朋友是不是覺得很熟悉，這不就跟前面一個公式計算基本差不多的嘛。這個說法非常正確！其實前半部分也可以用FV函數來算，算法是FV([年利率]/12,[已還款期數],0,-[貸款總額])，那么兩個FV函數一合并，就是FV([年利率]/12,[已還款期數],[月供],-[貸款總額])。

如果學過CFA的話，這個就更簡單了，就是已知現金流和利率情況下，求某一個時點的終值。所以用FV函數一個公式就能解決。這里應用的條件是，整個期限利率是沒有變過的，如果利率有變過，比如2014年以前的朋友，那用這一個公式是算不出來的。

當然，有朋友可能覺得，這么算還是太麻煩了。那我的建議是，下載房貸銀行的APP或者打電話銀行，直接查詢房貸余額即可。

拓展知識：