錦子老師

貸款買房，現在已經是一件很平常的事情了。如何根據個人的付款情況，選擇適合自己的貸款方式並最大限度地減少利息的支出，這是很多人都關心的問題。本文介紹使用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鍵結束公式輸入。將儲存格格式設置為「日期」，此時在儲存格中將顯示還款日期，如下圖所示。

點取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」。

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

點取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」。

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