《會計之友》2007年第6期上刊登了蔣秀蓮、宋言東等同志的《利用Excel雙變量模擬運算表進行購房貸款決策》(以下簡稱“蔣文”)一文,筆者認為,該文實用價值頗大,利用 “蔣文”中的模型,根據(jù)不同房價、不同期限來選擇房貸方式,解決了不同收入層次的人們的房貸選擇問題。但是貸款方案一旦確定,人們更想知道的信息是到一定時間為止共償還了多少貸款,如果提前還貸,還需向銀行支付多少貸款。本文在“蔣文”的基礎上以案例方式對分期償還貸款進行了明細分析。
一、案例的提出
2006年12月1日,甲從中國銀行申請住房商業(yè)貸款15萬元,貸款年限15年,采用等額本息付款方式按月償還貸款,其間中國銀行利率變化了四次:2006年利率為3.5127%,2007年利率為6.156%,2008年利率為6.9977%,2009年利率為4.4254%(注:前三年的利率在基準利率上打九折,2009年的利率在基準利率上打七折)。問:
1.每年的月償還額為多少元?
2.至2009年底,甲共償還多少貸款?其中本金多少?利息多少?如果這時想提前償還貸款,還需向銀行支付多少元?
二、模型的建立
由于知道現(xiàn)值(150 000元),利率(變動利率)和期限(15年),求每年支付的金額(年金),可以用時間價值函數(shù)中的年金函數(shù)(PMT)、年金中的本金函數(shù)(PPMT)和年金中的利息函數(shù)(IPMT)來分別計算月償還額、月償還額中的本金和利息,最后再用Excel中的常用計算函數(shù)求出累計償還額、累計償還本金和利息,以及剩余貸款金額。
(一)建立分期償還貸款分析模型表
創(chuàng)建一個新的工作簿,將其命名為“貸款償還分析表”,在工作簿中選擇一工作表,并將該工作表重命名為“分期償還貸款明細分析表”,在此工作表上建立“分期償還貸款分析模型”,如表1:
各項目的公式設置如下:
總付款期數(shù):C8=C6*C7
每期償還金額:C9==ABS(PMT(C5/C7,C8,C4))
該模型建立之后,每期償還金額與貸款金額、貸款年利率、貸款年限、每年還款期數(shù)等因素之間建立了動態(tài)鏈接,可以通過直接輸入數(shù)據(jù)的方式改變貸款金額、貸款年利率、貸款年限、每年還款期數(shù)中的任意一個或幾個因素的值,來觀察每期償還金額的變化,選擇一種當前能力所及的固定償還金額進行貸款?！笆Y文”是把貸款金額和利率變化的多種結果綜合顯示到一張表上,便于決策,而本文討論的重點不是決策,而是決策后需要了解的相關信息。為了便于貸款額的明細分析,文中只顯示一種結果,表1顯示的結果就是案例中2006年的月償還額1 073.26元。如果要求以后年度的月償還額,得用下面的模型。
(二)建立分期償還貸款雙變量分析表
在“蔣文”中,由于是對貸款進行決策,所以其選用的雙變量為“利率”和“貸款額”。在實際中,一旦決策確定,貸款深受利率和期限的影響,貸款期限的長短可能影響其貸款利率,貸款利率的變動又對貸款分析產(chǎn)生較大的影響。為了觀察兩個因素不同組合下的每期償還額,需要借助模擬運算表建立雙變量分析表來達到目的,本文選用的雙變量為“利率”和“期限”。
1.目標函數(shù)的輸入
在行與列的交叉單元A13輸入目標函數(shù):= ABS(PMT(C5/C7,C8,C4)),設置一個雙變量數(shù)據(jù)表,用13行的各種總付款期數(shù)替換第一個變量(行變量C8,即總付款期數(shù))的值,并且用A列的各種年利率替換第二個變量(列變量C5,即年利率)的值,與此對應的每期償還金額將放在第14行下面,A列右方的單元區(qū)域中,如表2:
2.雙變量分析表值的計算
選擇A13至F21單元區(qū)域,在菜單欄“數(shù)據(jù)”下選擇“模擬運算表”,在“輸入引用行的單元格”中輸入$C$8,在“輸入引用列的單元格”中輸入$C$5,確定,即可看到不同利率、不同期限下的月償還額,如表3所示:
通過該模型,可以觀察兩個因素不同組合下的貸款分析的結果。當貸款各因素的值發(fā)生變化時,只需改變第一變量或第二變量所在行和列的值或其他因素的值,系統(tǒng)就會自動重新計算雙變量分析表中的所有值。在案例中 ,多種不同利率下15期貸款月償還額對應的值為第D列,則甲2006年12月應還的貸款額為1 073.26元,2007年每月應還的貸款額為1 278.46元。2008年每月應還的貸款額為
1 348.05元,2009年每月應還的貸款額為1 141.78元。如果以后利率有變化,只需調(diào)整第A列A17以下單元格的利率則可以觀察到不同的月償還額。此時,案例中的第一個問題已解決。
(三)建立分期償還貸款明細分析表
在分期償還貸款雙變量分析表中,可以觀察到不同期限和利率組合下的月償還額,但如果想知道目前還了多少貸款,還剩多少貸款沒還清,則需要通過明細分析表來實現(xiàn)。在本案例中,由于中國銀行2006年至2009年利率變動了四次,所以在計算每期償還本金和利息時需分別計算,公式設置如下:
1.還款額(第J列)公式的設置
2006年每月還款額J3=D14;
2007年每月還款額J4=$D$15,利用自動填充功能將公式復制到J5至J15單元格中;
2008年每月還款額J16=$D$16,利用自動填充功能將公式復制到J7至J27單元格中;
2009年每月還款額J28=$D$17,利用自動填充功能將公式復制到J29至J39單元格中。
2.償還本金(第K列)公式的設置
2006年每月償還本金K3= -PPMT($A$14/12,I3,180,$C$4);
2007年每月償還本金K4= -PPMT($A$15/12,I4,180,$C$4),利用自動填充功能將公式復制到K5至K15單元格中;
2008年每月償還本金K16= -PPMT($A$16/12,I16,180,$C$4),利用自動填充功能將公式復制到J17至J27單元格中;
2009年每月償還本金K28= -PPMT($A$17/12,I28,180,$C$4),利用自動填充功能將公式復制到J29至J39單元格中。
3.償還利息(第L列)公式的設置
2006年每月償還本金L3= -IPMT($A$14/12,I3,180,$C$4);
2007年每月償還本金L4= -IPMT($A$15/12,I4,180,
$C$4),利用自動填充功能將公式復制到L5至L15單元格中;
2008年每月償還本金L16= -IPMT($A$16/12,I16,180,$C$4),利用自動填充功能將公式復制到L17至L27單元格中;
2009年每月償還本金L28= -IPMT($A$17/12,I28,180,$C$4),用“填充柄”將公式復制到L29至L39單元格中。
4.剩余貸款金額(第M列)公式的設置
2006年12月剩余貸款金額:M3==$C$4-K3;
2007年后每月還款后剩余貸款金額:M4=M3-K4,利用自動填充功能將公式復制到M5至M39單元格中;
合計:J40=SUM(J3:J39),利用自動填充功能將公式復制到K40至L40單元格中。
5.剩余貸款金額(第41行)公式的設置
M41=C4-K40,這個金額應該與M39相等。如表4(本表已作優(yōu)化處理):
至此,可以計算出甲在三年多中,累計償還貸款額為
46 292.75元,其中累計償還本金21 178.76元,累計支付利息25 113.99元,如果在12月底想提前償還貸款,此時還應支付給銀行128 821.24元。
三、模型的應用
以上三個模型是緊密聯(lián)系的,前一個模型的數(shù)據(jù)直接影響后面模型的結果,所以當對貸款進行明細分析時,以上三個模型都會運用到,但是具體運用到各種情況中時操作又有不同。假定在利率變動的情況下,討論幾種情況:一是貸款金額變動,期限不變,比如本文的案例,如果貸款20萬元,其它條件不變,這時只要把圖表中的貸款金額C4由150 000改成200 000萬元即可得到想要的結果;二是貸款金額不變,期限變動,比如本文的案例,如果貸款期限為10年,其它條件不變,則需要改表1和表4,在表1中,把貸款年限C6由15改成10,此時要注意表2不要改,但是取值時要注意的是對應10年的月償還額,即第C列,而不是本文開頭案例中的第D列,由于取值列數(shù)有改變,所以在表4中公式需要稍作變動,月償還額第J列的公式中需要把D改為C,比如J3=C14,J4-J15=$C$14,月償還本金和月償還利息中由于年限由15年變成10年,所以需要把第K列和第L列公式的期數(shù)180改成120,比如K3=-PPMT($A$14/12,I3,120,$C$4),L3=-IPMT($A$14/12,I3,120,$C$4),這樣就可得到想要的結果。一般情況下,選擇了貸款后,除了利率會變動外,總的償還期限不會變化,隨著分期償還期限的增加,只要在第40行下面增加相應的行,利用“填充柄”功能則可擴充表4中的結果,如果利率2009年后再有變化,只需修改表2中A17單元格以下的利率,在表4中修改利率變化月份第J列的公式即可得到想要的結果。
四、結束語
在當今社會中,投資機會無處不在,如何選擇一種合理的貸款結構來籌集資金,進行理性的投資,顯得頗為重要。本文的模型在實際中具有很好的實用價值,不僅可以隨時了解每期還貸金額里包含的利息額、累計償還額等相關信息,而且可以隨時確定實際利率,通過與投資的預期收益率相比較,據(jù)此作出是提前還貸還是另選其它投資項目的決定,從而作出理性的投資決策。
【參考文獻】
[1] 蔣秀蓮,宋言東,等.利用Excel雙變量模擬運算表進行購房貸款決策[J].會計之友,2007(6)上.
[2] 張瑞君.計算機財務管理[M].北京:中國人民大學出版社,2007.
