- 公開日：
- 更新日：
ローンの金利計算をエクセルで求める方法（返済シミュレーション）
Excelを使ってローン返済シミュレーションを作成する例をご紹介します。関数を使えば毎月の返済額や元金、利息を簡単に求めることができます。
本記事で作り方をご紹介するローン返済シミュレーションはテンプレートとして無料でダウンロードいただけます。
借入額、返済期間、金利を入力するだけでシミュレーションできますのでお試しください。
住宅ローンの返済方法
住宅ローンの返済方法として「元利均等返済」と「元金均等返済」があります。
元利均等返済とは？
毎回の返済額が一定となる返済方法です。
「元金均等返済」より総返済額が多くなりますが、毎回の返済額が変わらないので返済計画を立てやすくなります。
元金均等返済とは？
毎回の元金を一定にする返済方法です。
返済当初の返済額は多くなりますが、総返済額は「元利均等返済」より少なくなります。
Excelを使ってローン返済シミュレーションを作成する
Excelでは「元利均等返済」において毎月の返済額や元金、利息を求める関数があります。関数を使用してローン返済シミュレーションを作ってみましょう。
ボーナス返済は考慮していませんのでご注意ください。
毎月の返済額を求める
ExcelのPMT関数を使用することで毎月の返済額を求めることができます。
PMT関数の書式は「=PMT(利率,期間,現在価値,[将来価値],[支払期日])」のように指定します。
毎月の返済額を入力したい【セル（例：D2）】を選択し、『=PMT(』と入力します。
利率を指定します。毎月の返済額を求めるので金利（年利）を12で割って月利を計算します。「=PMT(」に続き『$C$2/12,』と入力します。
C2と入力し、F4を押すと絶対参照になります。絶対参照については以下の記事で説明しております。
期間を指定します。返済期間（年）に12を掛けます。「=PMT($C$2/12,」に続き『$B$2*12,』と入力します。
B2と入力し、F4を押すと絶対参照になります。
借入額を指定します。「=PMT($C$2/12,$B$2*12,」に続き『$A$2)』と入力し、Enterを押します。
毎月の返済額がD2セルに表示されました。返済額なのでマイナスでも構いませんが、絶対値にしたい場合はABS関数を使用します。
①【D2セル】を選択し、②『=ABS(PMT($C$2/12,$B$2*12,$A$2))』と入力します。ABS関数の引数に先ほど入力したPMT関数を指定します。
ABS関数の使い方は以下の記事をご参照ください。
AMS関数を使用することで毎月の返済額が絶対値で表示されるようになりました。
支払回数を求める
返済期間（年）から支払回数を求めます。
E2セルを選択し、『=$B$2*12』と入力してEnterを押します。
B2と入力し、F4を押すと絶対参照になります。
E2セルに支払回数が表示されました。
元金を求める
ExcelのPPMT関数を使用することで元金を求めることができます。
PPMT関数の書式は「=PPMT(利率,期,期間,現在価値,[将来価値],[支払期日])」のように指定します。
元金を入力したい【セル（例：B5）】を選択し、『=PPMT(』と入力します。
利率を指定します。金利を12で割ります。「=PPMT(」に続き『$C$2/12,』と入力します。
C2と入力し、F4を押すと絶対参照になります。
期を指定します。「=PPMT($C$2/12,」に続き『A5,』と入力します。
期間を指定します。「=PPMT($C$2/12,A5,」に続き『$E$2,』と入力します。
E2と入力し、F4を押すと絶対参照になります。
借入額を指定します。「=PPMT($C$2/12,A5,$E$2,」に続き『$A$2)』と入力します。
A2と入力し、F4を押すと絶対参照になります。
絶対値にするためにABS関数の引数にPPMT関数を指定します。
B5セルに元金が表示されました。
利息を求める
ExcelのIPMT関数を使用することで利息を求めることができます。
IPMT関数の書式は「=IPMT(利率,期,期間,現在価値,[将来価値],[支払期日])」のように指定します。
元金を入力したい【セル（例：C5）】を選択し、『=IPMT(』と入力します。
利率を指定します。金利を12で割ります。「=IPMT(」に続き『$C$2/12,』と入力します。
C2と入力し、F4を押すと絶対参照になります。
期を指定します。「=IPMT($C$2/12,」に続き『A5,』と入力します。
期間を指定します。「=IPMT($C$2/12,A5,」に続き『$E$2,』と入力します。
E2と入力し、F4を押すと絶対参照になります。
借入額を指定します。「=IPMT($C$2/12,A5,$E$2,」に続き『$A$2)』と入力します。
A2と入力し、F4を押すと絶対参照になります。
絶対値にするためにABS関数の引数にIPMT関数を指定します。
C5セルに利息が表示されました。
ローン残高を求める
返済回数ごとにローン残高を求めます。
毎月の返済額を参照します。D5セルを選択し、『=$D$2』と入力し、Enterを押します。
毎月の返済額を参照できました。
ローン残高を入力したいセル（例：E5）を選択し、『=$A$2-B5』と入力し、Enterを押します。
E5セルにローン残高が表示されました。
①【元金から毎月の返済額（B5～D5セル）】までを選択します。②選択範囲の右下にマウスポインターを移動すると十字に変わります。ドラッグしたままポインターを下まで移動します。
他の行にも数式が反映されました。
E6セルを選択し、『=E5-B6』と入力し、Enterを押します。
①【E6セル】を選択します。②選択範囲の右下にマウスポインターを移動すると十字に変わります。ドラッグしたままポインターを下まで移動します。
他の行にも数式が反映されました。
同じように支払回数分、数式を反映した行を作成します。例では支払回数120回目にローン残高が0になりました。
利息の累計を求める
ExcelのCUMIPMT関数を使用することで利息の累計を求めることができます。
CUMIPMT関数の書式は「=CUMIPMT(利率, 期間, 現在価値, 開始期, 終了期, 支払期日)」のように指定します。
利息の累計を入力したい【セル（例：F2）】を選択し、『=CUMIPMT(』と入力します。
利率を指定します。金利を12で割ります。「=CUMIPMT(」に続き『$C$2/12,』と入力します。
C2と入力し、F4を押すと絶対参照になります。
期間を指定します。「=CUMIPMT($C$2/12,」に続き『$E$2,』と入力します。
E2と入力し、F4を押すと絶対参照になります。
借入額を指定します。「=CUMIPMT($C$2/12,$E$2,」に続き『$A$2,』と入力します。
A2と入力し、F4を押すと絶対参照になります。
開始期と終了期を指定します。「=CUMIPMT($C$2/12,$E$2,$A$2,」に続き『1,$E$2,』と入力します。
E2と入力し、F4を押すと絶対参照になります。
支払期日を指定します。「=CUMIPMT($C$2/12,$E$2,$A$2,1,$E$2,」に続き『0)』と入力します。
絶対値にするためにABS関数の引数にCUMIPMT関数を指定します。
F2セルに利息の累計が表示されました。
ローン返済額早見表（100万円の場合）
毎月の返済額を金利と返済期間で比較できるようにローン返済額早見表を用意しました。
借入額が100万円（元利均等返済）の場合です。返済額の目安にお使いください。
| 金利/返済期間 | 15年 | 20年 | 25年 | 30年 | 35年 |
|---|---|---|---|---|---|
| 0.6% | ¥5,811 | ¥4,423 | ¥3,590 | ¥3,036 | ¥2,640 |
| 0.8% | ¥5,897 | ¥4,510 | ¥3,679 | ¥3,125 | ¥2,731 |
| 1.0% | ¥5,985 | ¥4,599 | ¥3,769 | ¥3,216 | ¥2,823 |
| 1.2% | ¥6,073 | ¥4,689 | ¥3,860 | ¥3,309 | ¥2,917 |
| 1.4% | ¥6,163 | ¥4,780 | ¥3,953 | ¥3,403 | ¥3,013 |
| 1.6% | ¥6,253 | ¥4,872 | ¥4,047 | ¥3,499 | ¥3,111 |
| 1.8% | ¥6,343 | ¥4,965 | ¥4,142 | ¥3,597 | ¥3,211 |
| 2.0% | ¥6,435 | ¥5,059 | ¥4,239 | ¥3,696 | ¥3,313 |
ローン計算テンプレート
Office Hack特製テンプレート（無料）
本記事でご紹介したローン返済シミュレーションをダウンロードいただけます。
借入額、返済期間、金利を指定すれば自動で元金や利息、ローン残高がシミュレーションされます。返済期間は1年から35年までプルダウンメニューより選択可能です。
下記のボタンからエクセル版のローン返済シミュレーションをダウンロードいただけます。
Microsoft公式テンプレート
Microsoft公式テンプレート内でローン計算シートがダウンロードできます。