怪老子ETF線上課程

房屋貸款之進階討論

日期:2008-02-25

房屋貸款的基本型態及特性,於本網站『房屋貸款規劃』裡已經多有著墨。可是在實務中卻還有很多的變形,例如銀行為吸引客戶所使用的多階段利率,以及指數型利率等。還有當貸款人想提早償還本金來減輕利息負擔時,整個貸款情形又會變得如何?這裡會有詳細敘述,以及提供一個通用的試算表,讓投資者得以盡情的進行貸款規劃,深入了解貸款之本質。因為本息平均分攤還款方式比較為大眾所接受,以下都是以本息平均分攤為前提。

部份本金提早攤還

當貸款者的積蓄累積到一定程度、或剛領到一筆獎金,都希望能提早償還部分貸款,減少利息的負擔。大部分銀行為了顧及客戶的需求,也都會接受提早償還的請求。在貸款期中提早償還部分貸款本金時,有些銀行會提供兩種選擇,貸款者可以選定一種適合自己需求的方式。

  1. 每月約定繳款不變,提早償還期間
  2. 償還期間不變,減低每月繳款金額

每月繳款金額維持不變,縮短貸款期間

如果希望未來每月繳款金額不變,貸款者最想知道的是,整個貸款期間將會縮短多久呢?這問題可以利用EXCEL的NPER函數輕易的算出,還要多久(幾期)才可還清。

NPER(rate, pmt, pv, fv, type)

將下列的參數填入便知道還得要幾期才能還清。

rate = 約定之年利率 / 12
pmt = 目前約定之每月繳款金額
pv = 尚欠貸款本金 - 提早償還之本金

例如100萬20年期的貸款,約定年利率3.5%,所以月繳5,800元。經過兩年後,有一筆20萬元的獎金進帳,希望全部用來提早償還部份貸款本金。經查貸款攤還表,得知兩年後尚欠貸款本金(貸款餘額)為928,439元,所以扣除提早償還的20萬元,尚餘72,8439元。用NPER函數可得知,如果每月貸繳款金額不變,還要:

=NPER(3.5%/12, -5800, 728439) = 156.6,就是還要157個月才能還清,由於原來還有216個月未繳款,現在縮短成157個月,也就是可以提早59個月清償貸款。

償還期間不變,減少每月付款金額

如果提早償還部分本金後,繳款期間仍然希望保持不變,那麼貸款者想知道的是,未來每月之繳款金額可以減少多少呢?這問題可以利用EXCEL的PMT函數算出未來新的月繳款金額。

PMT(rate, nper, pv, fv,type)

將下列的參數填入便知道新的月繳款金額為多少了。

rate = 約定之年利率 / 12
nper = 未繳款期數
pv = 尚欠貸款本金 - 提早償還之本金

同上面的例子,100萬20年期的貸款,約定年利率3.5%,所以每月繳交5,800元。兩年後提早償還20萬元,所以到第24期時,扣除提早償還金額尚欠72,8439元,還剩216期未繳款,經帶入pmt公式計算:

=-pmt(3.5%/12, 216, 728439) = 4,550,也就是說貸款期間不變,未來只須每月繳交貸款4,550即可。

▼貸款100萬、20年期利率3.5%

攤還表一

▼貸款100萬、20年期利率3.5%,第二年底提前償還貸款20萬元

提前還款

多段式利率

由於銀行間房貸競爭激烈,為了爭取客戶銀行經常於貸款之前期,提供優惠利率,等優惠期間過後再恢復正常利率。這就是常聽到的,二段式、三段式利率,甚至於四段式利率。同時也有銀行為了減低貸款者初期的負擔,會提供某些期間的寬限期,寬限期內只繳息不繳本金。只繳利息不還本金的結果,不過是把本金的償還部分往後延緩而已,結果是過了寬限期後,月繳金額不只會比無寬限期多,總繳利息也會比較多。

月繳金額如何計算

多段式利率的月繳款金額,每一段不同利率都有各自的月繳金額。例如貸款100萬分20年本息均分償還,前兩年的利率以2.5%優惠,兩年以後每年都3.2%。因為利率是兩段,所以月繳款也會有兩段,前兩年月繳5,299元,兩年之後每月繳款5,614。那這兩段的月繳款又分別是如何算出來的呢?當計算某一利率的月繳款時,不論往後利率是否更動,都假設到期滿還清,都以該利率本息攤還。

以同例子來說,前一段的優惠利率2.5%雖然只適用兩年,但是在計算2.5%的月繳款時,是假設貸款金額是100萬元,直到期滿20年都適用2.5%,然後以這條件所計算出來的本息平均攤還金額。計算第二段利率3.2%的月繳款時,亦假設第三年到期滿總共18年都適用3.2%的利率,但是貸款金額必需使用上一段利率結束的貸款本金餘額,亦就是繳完第二年後,還欠銀行多少本金。這金額可以由前一段利率的攤還表得知為920,946。

攤還表二

不同利率的月繳款都可用Excel的pmt函數來計算,公式如下:

第一階段(利率2.5%):

rate = 2.5% / 12;pv = 1,000,000;nper = 20*12
=-pmt(2.5% / 12, 20*12, 1000000) = 5,299

第二階段(利率3.2%):

rate = 3.2% / 12;pv = 920,946;nper = 18*12
=-pmt(3.2% / 12, 18*12, 920946) = 5,614

寬限期之月繳款

寬限期只需繳交利息,無需償還本金,所以寬限期內的月繳款等於當月利息。因為本金並未減少,所以寬限期內的利息每月都一樣,都等於: 貸款金額*(年利率/12)。

繳款期中利率變更

在繳款期當中,因為市場利率升高,銀行也有可能會提高貸款利率,往後的月繳款也會重新計算過,計算的方式和多階段利率的方法完全一模一樣。當利率要改變時,會把到上個月為止的貸款結餘(尚欠本金)當成新的貸款金額(pv),期數(nper)就是還有幾期尚未繳款,然後用新的利率(rate),再將這些參數代入pmt函數,就會計算出新的月繳款金額。

例如和銀行約定貸款20年期100萬,年利率2.5%,所以月繳5,299元。等到第二年結束時,銀行將提升利率至3.2%。這時月繳款會重新計算,銀行將兩年後的貸款餘額920,946當成新的貸款,再依18年期、新的利率3.2%核算月繳款,得到新的月付款金額5,614元。
=-pmt(3.2% / 12, 18*12, 920946) = 5,614

由這例子可以看出多階段利率的月繳款計算,和一階段利率中的利率變更有著異曲同工之處。多階段利率也可以解釋成:一開始試用一階段利率,但是銀行『預先通知』,在什麼時候會自動調整利率而已。簡單說就是『預謀』調整利率,而不是視市場狀況動態調整。

指數型利率是說貸款利率會『隨時』跟著市場利率變動而調整,可是每次調整利率,未來的月繳款就得跟著調整,那可是會累死人的!那應該多久調整一次較為合宜呢?一般實務上多以一季調整一次的居多,這樣才會兼顧到客戶觀感及銀行內部作業的方便。

Excel製作多階段利率攤還表

有許多的網站都有提供貸款試算,但是卻都無法應用於利率變化時的情形,這裡提供一個以excel製作的通用型攤還表,這張表可以看出每月支出的利息,還可列出詳細的每月本金歸還狀況。不僅如此,利率可以每月分別設定。如果還款期間是20年(240個月),那麼最多可以分成240段的利率,也就是說即使每個月都調整一次利率也足以應付。如果有提前償還部分本金情況,只要於提前償還的月份裡輸入金額,在還款期不變的情況下,也會自動調整下個月份之後的每月繳款金額。

透過學習如何製作這份貸款攤還表,可以很清楚的了解銀行是如何計算貸款利息以及每月的繳款金額。其實這張攤還表還是個不錯的Excel應用的例子,對有心想學好Excel的人是個不錯的範例。

下載試算表

步驟
動作
說明
1

輸入標題文字

A1:輸入文字“貸款金額”
A2:輸入文字“寬限期(月)”
A3:輸入文字“貸款年數”
A4:輸入文字“繳交利息總額”
A5:輸入文字“本利和”

A7:輸入文字“月份”
B7:輸入文字“貸款結餘”
C7:輸入文字“償還本金”
D7:輸入文字“提早還款”
E7:輸入文字“利息”
F7:輸入文字“貸款利率”
G7:輸入文字“每月繳款”

製作表頭
2

輸入參數

B1:輸入貸款金額的數字
B2:輸入寬限期月的月數
B3:輸入貸款年限

 
3

輸入起始月份及貸款金額

A8 輸入數值 0
B8輸入公式 =B1

 
4

A9輸入公式
=A8+1

本月份等於上月加1
5 F9輸入數值 3.5% 輸入本月適用年利率
6 計算當月利息(E9)輸入公式
=B8*F9/12
當月利息
= 前月貸款餘額*本月利率/12
7 計算當月繳款(G9)輸入公式
=IF(A9<=$B$2, B8*(F9/12),-PMT(F9/12,$B$3*12-A8,B8))

如果本月份(A9)在寬限期內,月繳款只等於利息(上月的貸款餘額*年利率/12),否則用pmt計算本利平均攤還之月繳款。

rate = 當月利率(F9) / 12
nper = 尚未繳款月數
pv = 前月之貸款餘額

8 計算當月償還本金(C9)輸入公式
=G9-E9
當月償還本金(C9)=
當月繳款金額(G9) - 當月所繳利息(E9)
9 計算當月貸款結餘(B9)輸入公式 =B8-C9-D9 本月貸款結餘(B9)=
上月貸款結餘(B8) -本月償還的本金(C9) - 提早還款金額(D9)
10 a) 點選及複製A9:G9這一列儲存格
b) 往下貼N列,N = 貸款年限*12 - 1
將第九列的值及公式複製到其他月份。例如20年期的貸款,應複製從第10列起到第248列,總共240列(含第9列)。
11 B4輸入公式
=SUM(E:E)
計算所有E欄的加總,就是全部貸款所繳之利息
12

B5輸入公式
=SUM(C:C)+B4

計算所有C欄的本金的加總,再加上B4的利息總額

利率那一欄(F欄)必須輸入每月的利率,因為每月的利率大部分都是一樣,所以可以只輸入一個,其他用複製的就可以。另一種方式是輸入一個利率於F9,然後下一列F10輸入公式=F9,再將F10複製到F欄的其他列。這樣每個月的利率就會等於上個月的數值了。當在任一期F欄的利率作更動時,之後各月利率的值都會跟著變動,使用起來就非常方便。

本攤還表的總列數應該和繳款的月份一樣,例如20年期就應該有240列,30年期有360列。做好後可以檢查最後一個月的貸款餘額應該等於零,就知道是否做對了。下面連結是第一銀行的理財試算網站,讀者可以進入網站試算,看結果是不是一樣。

第一銀行理財試算

讀者或許會問,我直接去銀行網站試算就好了,何必那麼辛苦自己做攤還表?學會自己做攤還表,可以非常清楚的知道銀行貸款是如何運作,進而能規劃如何節省利息。而且這份貸款攤還表也是一個非常好的Excel應用,就當作學習也是值得的。

本網站相關文章

房貸利率比較-實例一
房屋貸款規劃
多段式房貸利率如何比較
EXCEL之投資應用




■ 本網站內容儘可能精確完整,但不保證無誤。若做為投資依據,風險請自行斟酌 ,本網站不負賠償之責任。
■ 網站所有資料均為版權所有,非經書面允許請勿轉載或使用。