close

470

使用Excel製作還貸計算器的方法

示範檔

範例檔

470.XLSX

結果檔

貸款買房,現在已經是一件很平常的事情了。如何根據個人的付款情況,選擇適合自己的貸款方式並最大限度地減少利息的支出,這是很多人都關心的問題。本文介紹使用Excel分別計算等額本金還款法和等額本息還款法的月還款額、每月應還利息值以及每期本金額等資料的方法。

啟動Excel並打開470.XLSX活頁簿。

在「工作表1」工作表,點取A3:E3儲存格中分別輸入貸款日期、坪數、每坪單價、貸款百分比、貸款金額、貸款年數、年利率和月付款

點取A7儲存格輸入「0」。

框選A8:A367儲存格範圍,在編輯列中輸入公式:「=IF(A7<$F$3*12,A7+1,"")」,按Ctrl + Enter鍵結束公式輸入。。

點取B7儲存格輸入公式:「=A3」。

框選B8:B367儲存格範圍,在編輯列中輸入公式:「=IF(A8<=$F$3*12,DATE(YEAR(B7) , MONTH(B7)+1,DAY(B7)),"")」,按Ctrl + Enter鍵結束公式輸入。將儲存格格式設置為「日期」,此時在儲存格中將顯示還款日期,如下圖所示。

image

點取C8儲存格,在編輯列中輸入公式「=$E$3/($F$3*12)」。

點取D8儲存格,在編輯列中輸入公式「=H7*$G$3*$D$3/120」。

選擇E8儲存格,在編輯列中輸入公式「=D8」。

選擇F8儲存格,在編輯列中輸入公式「=C8+E8」。

選擇G8儲存格,在編輯列中輸入公式「=SUM($F7:F8)」。

 

選擇H8儲存格,在編輯列中輸入公式「=H7-C8」。

將這些公式填充到其下的儲存格中,此時將獲得等額本金還款法的月還本金、應付利息、月還利息、月還款額、還款合計額以及本金餘額的值,如下圖所示。

image

點取I8儲存格輸入公式:「=M7*$D$3*$G$3/120」。

點取J8儲存格輸入公式:「=K8-C8」。

點取K8儲存格輸入公式:「=-PMT($D$3*$G$3/120,$F$3*12,$E$3)」。

點取L8儲存格輸入公式:「=SUM($K$7:K8)」。

點取M8儲存格輸入公式:「=M7+I8-C8-J8」。

將這些公式向下填充到對應的儲存格中,此時將獲得等額本息還款法的應付利息、月還利息、月還款額、還款合計額以及本息餘額的值,如下圖所示。

image


arrow
arrow
    創作者介紹

    錦子老師 發表在 痞客邦 留言(0) 人氣()