Office Hack

ローンの金利計算をエクセルで求める方法(返済シミュレーション)

  • Release

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(利率,期,期間,現在価値,[将来価値],[支払期日])」のように指定します。

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関数

絶対値にするためにABS関数の引数にPPMT関数を指定します。

PPMT関数の結果

B5セルに元金が表示されました。

利息を求める

ExcelのIPMT関数を使用することで利息を求めることができます。

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関数の入力

絶対値にするためにABS関数の引数にIPMT関数を指定します。

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(利率, 期間, 現在価値, 開始期, 終了期, 支払期日)」のように指定します。

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関数

絶対値にするためにABS関数の引数にCUMIPMT関数を指定します。

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特製テンプレート(無料)

Office Hack特製テンプレート

本記事でご紹介したローン返済シミュレーションをダウンロードいただけます。

借入額、返済期間、金利を指定すれば自動で元金や利息、ローン残高がシミュレーションされます。返済期間は1年から35年までプルダウンメニューより選択可能です。

下記のボタンからエクセル版のローン返済シミュレーションをダウンロードいただけます。

エクセル版ローン返済シミュレーションのダウンロード

Microsoft公式テンプレート

ローン計算シート

Microsoft公式テンプレート内でローン計算シートがダウンロードできます。

このページを見ている人におすすめの商品

よろしければ参考にならなかった点をお聞かせください

CAPTCHA


Page Top