excel單變數求解貸款年限

『壹』 如何用excel計算房貸

1.正常演算法：列出一個大表來求解寬碧。

因為很多人不了解房貸計算原理，為了大家方便，我先說正常人演算法(如果這部分比較熟悉，直接跳到後面)。計算前必須知道的幾個參數：貸款總額、利率、總還款期。此時有人會問，月供不是必須的參數嗎？其實不是，因為知道了貸款總額、利率、還款總年限，就可以自己算月供了。

那麼我們先降低難度，假設我們都知道貸款總額、利率、還款總期數、月供這四個數字，那麼我們就可以通過做表的方式算出來。為了方便大家理解，我採用一個大概的情況，假設貸款總額為100萬，利率為基準利率的4.9%，貸款20年，即還款總額為20年*12期/年=240期，月供6544.44元。我先給你看成品表：

第一欄是期初貸款余額(也等於上一欄第二欄的期末貸款余額)。第二欄是期末貸款余額，即本期支付月供後剩餘的貸款余額。第三欄是月供。第四欄是當期應支付的利息。第五欄是當期支付的本金。其中，第二列等於第一列減去第五列，可以直觀的理解。第五列等於第三列減去第四列，唯一計算公式稍微復雜的是第四列。

第四列公式上方的數字用紅框標出，其中round函數是取兩位小數的函數，不用也沒關系。

其實按揭的原理很簡單。我以第一個月為例。第一個月，我欠銀行貸款100萬，銀行每月收取100萬利息乘以4.9%的1/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，-1，000，000)。當然，因為這個計算出來的小數點很長，你可以再使用一個舍入函數，你可以看到計算出來的結果是6544.44。

2.精英演算法：得到它的公式。

我先告訴你結果，然後再說原理。直接EXCEL演算法是FV([年利率]/12，[還款期數]，[月供]，[貸款總額])。你是對的。這個公式就夠了。

你可能不太理解這個公式。我先用數學公式模擬一下。同樣的演算法是：[貸款余額]*(1 ([年利率]/12)[已還期數]-[月供]*(1-(1[年利率]/12)[已還期數)]/(1-(1)

這里使用的原理在金融中很常見，它被稱為復制原理。怎麼抄？假設我的房貸沒有按照原來的方式計算，銀行給我一個「替代」的方法。銀行要求我每個月去銀行投資一個定投產品。這個產品的利率和復利方式和房貸一模一樣，每個月的定投錢和月供一模一樣。那麼對於我來說，我每個月還是給銀行那麼多月供，其實和還月供是一樣的。

現在，唯一不同的是，我的房貸沒有還，每個月都會扭虧為盈。但與此同時，銀行讓我做的理財也在扭虧為盈。到期末，我定投的利潤就和房貸的總結果一樣了。我會直接拿定投的錢，一次性把房貸連本帶利還上，這樣就和銀行扯平了。那麼房貸利息總額，減去我的虛擬定投產品總額，就是我還欠銀行的錢。

這就是這些數學演算法的內涵。數學演算法前半部分[貸款余額]*(1 ([年利率]/12)[還款期數]，即房貸還清後我欠銀行的錢。第二部分【月供】*(1-(1[年利率]/12)【已還期數】/(1-(1[年利率]//12))為本人月供產生的本息之和。後面部分其實襲瞎用的是幾何級數的求和法，或者EXCEL的FV函數變成FV([年利率]/12，[還款期數]，[月供])。

在這里看到朋友是不是感覺很熟悉？和前面的公式基本一樣。這個說法非常正確！實際上，前半部分

部分也可以用FV函數來算，演算法是FV([年利率]/12,[已還款期數],0,-[貸款總額])，那麼兩個FV函數一合並，就是FV([年利率]/12,[已還款期數],[月供],-[貸款總額])。

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

相關問答：如何計算房貸余額

一、要准確計算出按揭款的余額，你必須清楚知道自己的貸款金額、貸款開始日期、執行利率，還款方式，中途央行調息記錄等關健信息：1、等額本息還款法計算公式：[貸款本金×月利率×（1+月利率）^還款月數]÷[（1+月利率）^還款月數－1]下面舉例說明等額本息還款法，假定借款人從銀行獲得一筆20萬元的個人住房貸款，貸款期限20年，貸款月利率4.2‰，每月還本付息。按照上述公式計算，每月應償還本息和為1324.33元。現行的基準利率為5.4%，等額本息還款法的計算方式很復雜，「^」表示「冪」，建議你在網下一個專業的貸款計算器計算，地址如下：http://wenku..com/view/6ad868c2cf84b9d529ea7a4f2、等額本金還款法計算公式：因等額本金還款法每期還款月供不一樣，第N期還款月供=貸款本金/貸款期數+[貸款本金-貸款本金/貸款期數*(N-1)]*年利率/123、中途有利率調整時，應分段從調整日按剩餘的貸款本金和剩餘時間，按現行利率從新計算。二、有三種情況下會產生計算余額不一致：1、人民銀行的基準利率有所調整，貸款時執行的利率是按原利率執行，中途利率調整後是，銀行是根據剩餘貸款本金和剩餘貸款期限按新利率從新計算的，所以不管你按之前的利率算還是按現在的利率算，月供都會有差異。2、當時執行的利率有上浮或下浮，在房貸較緊的時候，銀行會放緩審批和上調利率，政策寬松的時候銀行之間則會搶業務，會在基準利率上打折，如果你不知道自己的利率是在基準上面上浮或下浮，按基準利率算，月供肯定會不一樣。3、選擇的還款方式不同，銀行貸款有等額本息還款法和等額本金還款法，你在用房貸計算器時選擇的方式和你銀行貸款時選擇的方式不一樣，也會造成月供不一樣。

『貳』 EXCEL用模擬運算表求 出當年限和貸款金額改變時 每月的償還金額

1、找到「數據」；

『叄』 用excel怎麼使用公式求：某人需貸款20萬，他准備每月還款1500元，18年還清，使用單變數求解來計算貸款利率

月利率:=RATE(貸款期限(月),每月還款數(還款是負值,借為正),貸款額)*12
所以本次貸款年利率:
=rate(18*12,-1500,200000)*12=5.8545%

『肆』 已知貸款金額100萬元 每月還貸20000元 貸款利率8%,用EXCEL單變數求解求多少年還清

在表格中:第一行輸入:貸款金額:100萬
第二行:月還:2萬
第三行:利率8%
第四行: 選擇插入函數:-------財務-------NPER-------在函數參數中:RATE輸入：8%/12；PMT：-2；PV：100；FV：0； TYPE：不輸，直接點確定，將計算結果除以12就得出了貸款年限。

『伍』 excel 利用單變數求解，計算20年期貸款，在利率為5.94%，月還款能力為4300的情況下，能貸款多少

原來用單變數求解解決比較復雜，這問題是有財務專用函數PV()處理的，按你貼出圖片的數據位置，貸款金額在F4、年利率在F5、貸款年限在F6、月還款額在F7，在F4輸入公式：
=PV(F5/12,F6*12,-F7)

或直接輸入數值計算：
=PV(5.94%/12,20*12,-4300)

PV()函數用法：
PV(rate,nper,pmt,fv,type)
Rate 為各期利率。例如，如果按 10% 的年利率借入一筆貸款來購買汽車，並按月償還貸款，則月利率為 10%/12（即 0.83%）。可以在公式中輸入 10%/12、0.83% 或 0.0083 作為 rate 的值。
Nper 為總投資（或貸款）期，即該項投資（或貸款）的付款期總數。例如，對於一筆 4 年期按月償還的汽車貸款，共有 4*12（即 48）個償款期數。可以在公式中輸入 48 作為 nper 的值。
Pmt 為各期所應支付的金額，其數值在整個年金期間保持不變。通常 pmt 包括本金和利息，但不包括其他費用及稅款。例如，$10,000 的年利率為 12% 的四年期汽車貸款的月償還額為 $263.33。可以在公式中輸入 -263.33 作為 pmt 的值。如果忽略 pmt，則必須包含 fv 參數。
Fv 為未來值，或在最後一次支付後希望得到的現金余額，如果省略 fv，則假設其值為零（一筆貸款的未來值即為零）。例如，如果需要在 18 年後支付 $50,000，則 $50,000 就是未來值。可以根據保守估計的利率來決定每月的存款額。如果忽略 fv，則必須包含 pmt 參數。
Type 數字 0 或 1，用以指定各期的付款時間是在期初還是期末。

『陸』 EXCEL請用單變數求解工具計算月還款額為6000,年限為30年時的貸款年利率

按以下步驟操作：
1、數據----假設分析----單變數求解
2、目標單元格，選中你的月還款額數據所在單元格（5368.22）,目標值輸入6000，可變單元格選中利率所在的單元格（5.00%），確定
以後再截圖，把行列標附帶上，不然不知道目標單元格的名稱。
注意，可變單元格必須為數字，目標單元格需要是公式。