【簡単にできる！】エクセルのローン返済シミュレーションの作り方

「エクセルを使って、ローン返済シミュレーションを作りたい。でも、どうやって作ればいいのか全然分からない。関数なんて難しそうだし・・・。」

今の時代、スマホのアプリでローン返済シミュレーションができますが、自分で作ってみたい人もいますよね。

この記事では、エクセルを使ったローン返済シミュレーションの作り方を解説してきます。関数の使い方が分からない人でも、安心して作れるように解説していきます。

自分でローン返済シミュレーションを作って、借金に困らない生活を実現させていきませんか。

エクセルでローン返済シミュレーション①返済方式

ローンの返済方法には「元利均等返済」と「元金均等返済」の2種類があります。それぞれの特徴を見ていきましょう。

ローン返済は返済額が一定で計画的に返済しやすい、「元利均等返済」を選択する人が多いです。「元金均等返済」は最初のうちの返済額が大きいことから選択する人が少なくなりがちです。

この記事では両方の方法に対応できるように、エクセルでそれぞれのローン返済のシミュレーションができるようにします。

次の項目で、エクセルで「元利均等返済」のローン返済シミュレーションのやり方ついて説明していきます。

エクセルでローン返済シミュレーション②元利均等返済の計算

エクセルで「元利均等返済」のローン返済シミュレーションをするには、「PMT関数」を使います。

具体例として、「借入残高1000万円、返済期間60回(5年)、金利2%」として説明していきますね。

手順.1

まずは、セルA3に「借入残高」、B3に「返済期間」、C3に「利率」、D3に「返済額」と入力して下さい。

セルA4に「10,000,000」、B4に「60」、C4に「2%」とそれぞれ数値を入力していきます。

手順.2

それぞれのセルの項目を入力し終わったら、セルD4をクリックして下さい。次に数式タブをクリックし、次に関数の挿入をクリックします。

手順.3

関数の検索に「PMT」と入力し、関数名の「PMT」をクリックし、次にOKをクリックして下さい。

手順.4

「利率」にセルC4を選択し12で割り算をする必要があるので、「C4/12」と入力してください。

「期間」はB4を選択し、「現在価値」は借入残高に当たるので、A4を選択します。「将来価値」と「支払期日」は、無視してしまってかまいません。

手順.5

それぞれの項目に対応する数値を入力し、OKをクリックすると返済額に当たる「￥-175,278」の計算結果が出ます。

手順.6

返済期間60回(5年)以外にもさらにシミュレーションしたい場合は、「120回(10年)」、「180回(15年)」、「240回(20年)」、「300回(25年)」を自分で入力しましょう。

利率も2%以外にシミュレーションしたい場合、「2.20%」、「2.50%」、「2.80%」、「3.00%」を入力しましょう。

 

手順.7

返済額の計算結果であるセルD4の右下隅にカーソルを合わせると「＋」というカーソルが表れます。これを「オートフィル」と呼びます。

「オートフィル」をクリックし、カーソルをセルD8まで持って行くと、それぞれの返済期間と利率に合わせた計算が自動で出てきます。

エクセルでローン返済シミュレーション③元利均等返済の元金部分

元利金当返済は「元金 + 利息」で成り立っていて、元金部分だけをエクセルでローン返済のシミュレーションをしたい場合は、「PPMT関数」が必要です。

手順.1

セルA5に回数の項目、B6に元金部分の項目を作ります。次に、B7をクリックして下さい。

手順.2

先ほどと同じように数式タブをクリックし、関数の挿入をクリックします。関数の検索から「PPMT」と入力し、「PPMT」をクリックして、OKをクリックして下さい。

手順.3

「期」のところは、セルA7を選択して下さい。「期」の項目以外は先ほどと同じようにそれぞれの項目に対応する数値を選択すればいいのですが、1つだけ注意点があります。

セルC4、B4、A4のままにするのではなく、固定する必要があります。固定するためには、C4をクリックし「F4キー」を押して下さい。

そうすれば、「$C$4/12」となります。これでセルが固定されたことになります。同様に他のセルも「F4キー」で固定していきましょう。

手順.4

それぞれの項目の入力が終わった後、OKを押すと「￥-158,611」と元金部分の計算結果が出てきます。

手順.5

セルA8に「2」と入力し、A8からオートフィルをすると順番に数字が出てきます。同じように、元金部分の計算結果であるセルB7からオートフィルしていくと、自動で計算結果が表れますよ。

エクセルでローン返済シミュレーション④元利均等返済の利息部分

元利均等返済の利息部分だけを、エクセルを使ってローン返済のシミュレーションをするには、「IPMT関数」を使っています。

手順.1

セルC6に利息部分の項目を作ります。次にC7に計算結果を出すので、C7をクリックして下さい。

手順.2

先ほどと同じように数式タブをクリックし、関数の挿入をクリックして下さい。関数の検索で「IPMT」と入力し、「IPMT」をクリックし、OKをクリックします。

手順.3

先ほどと同じようにそれぞれの項目に当たるセルを選択し、「F4キー」で対応するセルを固定して下さい。

手順.4

それぞれの入力が終わり、OKをクリックするとセルC7に利息部分の計算結果である「￥-16,667」が出てきます。

手順.5

先ほどと同じように、オートフィルを使って利息部分の計算結果を自動で出していきます。

手順.6

ここで、元利均等返済の元金残を出す方法を説明していきますね。

セルD6に元金残の項目を作り、D7に借入残高の数値「10,000,000」と元金部分の「￥-158,611」を足し合わせるため、Ｄ7をクリックし「A4+B7」を選択して下さい。

手順.7

すると、検索結果でD7に「￥984,138,907」が出てきます。

次に、セルD8に計算結果であるD7の「￥984,138,907」とB8の「￥-158,875」を足し合わせるため、D8をクリックし「D7+B8」を選択して下さい。

手順.8

すると、セルD8に計算結果である「￥9,682,514」が出てきます。次に、D8からオートフィルを使って、計算を自動で出していきます。

エクセルでローン返済シミュレーション⑤元金均等返済の計算

ここまで、エクセルを使って「元利均等返済」のローン返済シミュレーションを作っていきました。次は、「元金均等返済」のローン返済シミュレーションを作っていきます。

具体例は元利均等返済の時と同じ、借入れ残高、返済期間、利率の数値を使って下さい。

手順.1

「元金均等返済」の元金部分の計算は、セルB7をクリックし、A4の「10,000,000」とB4の「60」を選択し、割り算をします。

この時「F4キー」を押し、A4とB4をそれぞれ固定して下さい。

手順.2

「$A$4/$B$4」を割り算した結果、B7に「￥166,667」の計算結果が出ます。

手順.3

「元金均等返済」の利息部分は、「ISPMT関数」を使って計算します。数式タブをクリックし、関数の挿入をクリックして下さい。

手順.4

それぞれの項目に対応するセルを選択し、対応するセルを「F4キー」で固定して下さい。

手順.5

対応するセルの入力が終わり、OKをクリックするとセルC7に計算結果である「￥-16,389」が出てきます。

手順.6

「元金均等返済」の利息部分を自動で計算していくために、オートフィルを使って下さい。

手順.7

セルD4の返済額を計算するためにD4をクリックし、「B7+16389」と入力すると返済額の計算結果「￥183,055.67」が出てきます。

手順.8

元金残を計算するために、セルD7を選択し、A4の「10,000,000」とB7の「￥166,667」を引き算して下さい。計算結果の「￥9,833,333.33」が出ます。

手順.9

セルD8をクリックし、D7の「￥9,833,333.33」とB8の「￥166,667」を引き算して下さい。D8に計算結果の「￥9,666,667」が出てきます。

手順.10

「元金均等返済」の元金残を自動で計算していくために、オートフィルを使って下さい。

実践！エクセルでローン返済シミュレーション「住宅ローン」①

「元利均等返済」と「元金均等返済」をエクセルを使って、ローン返済のシミュレーションをする方法をそれぞれ紹介しました。

ここからは住宅ローンを利用することを想定し、具体例として「金利0.5%、期間35年、借入れ金額3000万円」の「元利均等返済」と「元金均等返済」をそれぞれシミュレーションします。

まずは、「元利均等返済」を選択した場合のローン返済をシミュレーションしていきます。

手順.1

それぞれのセルに対応する数値を入力し、返済額を「PMT関数」を使って計算して下さい。計算結果として「￥-77,875.61」が出ます。

手順.2

次に、元金部分を求めるために「PPMT関数」を使って計算して下さい。計算結果として、「￥-65,376」が出ます。

手順.3

次に、利息部分を求めるために「IPMT関数」を使って計算して下さい。計算結果として、「￥-12,500」が出ます。

手順.4

元金部分と利息部分を自動で計算するために、オートフィルを使って自動で計算結果を出していきます。

手順.5

元金残を計算するために、セルD7をクリックし「A4+B7」を計算し、D8をクリックし「D7+B8」を計算していきます。

元金残の部分を自動で計算するために、オートフィルを使って自動で計算結果を出していきます。

手順通り行い、住宅ローンの元利均等返済のローン返済シミュレーションが完成しました。

 

実践！エクセルでローン返済シミュレーション「住宅ローン」②

次は、住宅ローンの「元金均等返済」をエクセルを使って、ローン返済シミュレーションをしていきます。具体例は先ほど使ったものと同じものを使います。

手順.1

元金部分を計算するために、セルB7に「$A$4/$B$4」を計算して、計算結果である「￥71,429」を出して下さい。

手順.2

次に、利息部分を求めるために「ISPMT関数」を使って計算して下さい。計算結果として、「￥-12,470」が出ます。

 

手順.3

返済額を計算するために、セルD4に「B7+12470」を入力し、計算結果である「￥83,898.57」を出して下さい。

 

 

手順.4

元金部分と利息部分を自動で計算するために、オートフィルを使って自動で計算結果を出していきます。

手順.5

元金残を計算するために、セルD7をクリックし「A4+B7」を計算し、D8をクリックし「D7+B8」を計算していきます。

元金残の部分を自動で計算するために、オートフィルを使って自動で計算結果を出していきます。

手順通り行い、住宅ローンの元金均等返済のローン返済シミュレーションが完成しました。

実践！エクセルでローン返済シミュレーション「自動車ローン」①

エクセルを使った、住宅ローンの返済シミュレーションを行いました。次は、自動車ローンの「元利均等返済」と「元金均等返済」をそれぞれ行っていきますね。

まずは、「元利均等返済」のローン返済シミュレーションをします。具体例は「金利3%、期間24回(2年)、借入れ金額300万円」とします。

手順.1

それぞれのセルに対応する数値を入力し、「PMT関数」を使ってD4に返済額の計算額を出して下さい。

手順.2

元金部分を「PPMT関数」を使って求め、利息部分を「IPMT関数」を使ってそれぞれ求めて下さい。

手順.3

オートフィルを使って元金部分と利息部分を自動で計算し、元金残も計算してオートフィルで自動で計算結果を出します。

これで、元利均等返済のローン返済シミュレーションが完成しました。

実践！エクセルでローン返済シミュレーション「自動車ローン」②

次に、エクセルを使って「元金均等返済」のローン返済シミュレーションを行います。具体例は先ほどと同じものを使います。

手順.1

それぞれの項目に対応する数値を計算し、C4の利息部分は「ISPMT関数」を使って求めます。計算した後、オートフィルで自動で計算結果を求めて下さい。

手順.2

D4の返済額を、B7の「250,000」とC7の利息部分の「1,198」を足し合せて計算結果を出して下さい。

手順.3

オートフィルを使って元金部分と利息部分を自動で計算し、元金残も計算してオートフィルで自動で計算結果を出します。

これで、元金均等返済のローン返済シミュレーションが完成しました。

まとめ

ここまで、エクセルを使ったローン返済シミュレーションの作り方を解説していきました。

最初は、完成するまで時間がかかってしまうかもしれませんが、ゆっくりひとつずつ進んでいけば、誰でも完成させることが可能です。

自分でローン返済シミュレーションを作ることで、より借金の理解が深まります。そうすることで、未然に借金の借りすぎなどを防げるので、手間がかかりますがおすすめです。

自分でローン返済シミュレーションを作って、借金に困らない生活を実現させていきましょう。