如何製作一份貸款EXCEL試算表

我要自行製作一份貸款試算表

1.貸款金額:724202元

2.期數:80期

3.利率:5%

4.日期:97/12/10

要如何來製作[本利均攤法還,利息會隨本金減少而減少]

這份表格是否也可以計算出,我還欠銀行多少錢?

*會很難嗎?

*要用什麼公式來計算?

*日期要用什麼公式?

日期如果使用民國,是否表格會自行計算出下個月的10日

請EXCEL的高手教教我

已更新項目:

Jiunn大大:我有試過您教

2 個已更新項目:

Jiunn大大:我有試過您教的方法,但還是製做不出,所以我又把問題撲上去

3 個已更新項目:

Jiunn大大:請問儲存格 C3 公式 =$D$1公式是否有誤?公式是否為=$A$1呢?再請教Jiunn大大我的日期為何複製都是只有2008年與2009年呢?為何它無法算出2010年它是不是應該要算到2014年8月,再麻煩您能教一下?謝謝

3 個解答

評分
  • 1 0 年前
    最佳解答

    我已經幫你寫好了並依照你給的資料測試過... 公式也 po 給你了.. 可是你又再次發問~~~~~

    如果需要檔案的話,告訴我吧~~~

    2009-04-22 08:41:43 補充:

    儲存格 A1:724202 (即你的貸款總數)

    儲存格 B1:80 (即你的貸款期數)

    儲存格 C1:5% (即你的貸款利率)

    儲存格 D1:2008/12/10 (即你的第一次付款日期,會自動轉換成 97/12/10)

    儲存格 A3 公式 =$D$1

    儲存格 A4 公式 =IF($B4>0,DATE(YEAR($A3)+IF(MONTH($A3)=12,1,0),MOD(MONTH($A3)+1,12),DAY($D$1)),"")

    儲存格 B3 輸入 1

    儲存格 B4 公式 =IF(OR($B3=0,ISBLANK($B3)),0,$B3+1)

    儲存格 C3 公式 =$D$1

    儲存格 C4 公式 =$C3+$E3

    儲存格 D3 公式 =$C$1

    儲存格 E3 公式 =ROUND(PPMT($D3/12,1,$B$1-$B3+1,$C3),0)

    儲存格 F3 公式 =ROUND(IPMT($D3/12,1,$B$1-$B3+1,$C3),0)

    儲存格 G3 公式 =$E3+$F3

    儲存格 H3 公式 =SUM($G$3:$G3)

    並複製 D3 ~ H3 公式到儲存格 D4 ~ H4

    再將 A4 ~ H4下拉至 82 列,就完成了你的需求

    日期不管是西元或民國,都可以計算

    第 2 ~ 4 列如下:

    __A______B_________C_______________D______________E_____________F________________G_______________H

    日期____期數____貸款金額________貸款利率________還款本金______還款本利________本金加利息______累計已付金額

    97/12/10_1_______724,202_5.00%___-$7,646__-$3,018__-$10,664_______-$10,664

    98/1/10__2_______716,556_5.00%___-$7,678__-$2,986__-$10,664_______-$21,328

    2009-04-22 08:44:22 補充:

    你可以從貸款金額看出在那一期尚欠銀行本金多少,如第 2 期時,尚欠銀行 716,556

    2009-04-23 16:26:55 補充:

    1. 你說的對,C3 公式應該是 = $D$1。

    2. 我公式有些小問題,mod(月份+1,12) 傳回的值是 0~11,所以會出錯。

    其實 date() 公式已經能自動進位,所以 A4 公式改為 =IF($B4>0,DATE(YEAR($A3),MONTH($A3)+1,DAY($D$1)),"") 即可。

    參考資料: 上次作好的試算表, 修正後的試算表
  • 匿名使用者
    7 年前

    專辦:

    房屋貸款、民間二胎代償、代墊、土地貸款、民間二胎借款、

    汽車貸款、中古車貸款、房屋轉增貸、轉貸降息、信用貸款

    歡迎你的加入:LineID: hot777

    銀行貸款資訊 - 免費諮詢評估 - ( 0986 )-( 377 )-( 776 )

    YES貸款理財,提供您提供你最新理債及房屋貸款資訊建議,讓你生活免煩惱。

    - -歡迎同業配合- -- -歡迎同業配合- -

    銀行貸款資訊 - 免費諮詢評估 - ( 0986 )-( 377 )-( 776 )

    推...推....推-推...推....推,若有打擾之處,敬請見諒!

  • 1 0 年前

    提供依各另類解法,給你參考,以下公式提供你本期繳費的金額

    A B

    1 貸款金額 724202

    2 期數 80

    3 利率 0.05

    4 貸款日期 2008/12/10

    5

    6 今天 =TODAY()

    7 本月應繳利息 =-ROUND(IPMT($B$3/12,DATEDIF($B$4,$B$6,"M"),$B$2,B1),0)

    8 本月應還本金 =-ROUND(PPMT($B$3/12,DATEDIF($B$4,$B$6,"M"),$B$2,B1),0)

    EXCEL中,日期是以數字方式儲存,不管你用何種方式顯示,對EXCEL而言,沒有差別。

    你要計算下個月要繳多少利息及本金,只需在B6輸入2009/5/10就可以。

    至於你還欠銀行多少本金,你需要把借貸金額減去每個月的PPMT結果加總就可以。

    公式的安排,全靠你自行調整。

    希望這是你要的結果。

    2009-04-22 14:25:44 補充:

    上面的" 代表雙引號(不知為何會變成這樣!

    2009-04-22 14:26:39 補充:

    " ==> 雙引號

還有問題?馬上發問,尋求解答。