【意外と簡単!】エクセルを使ってローン返済の計算をする方法

「エクセルでローン返済の計算をしてみたいんだけど、どうすればいいんだろう。難しい関数とかがいっぱいあるから、あんまりやりたくないな・・・。」

最近は、スマホでローン返済の計算ができるアプリもありますが、自分でエクセルを使って作りたいという人もいますよね。

この記事では、エクセルを使ったローン返済の計算方法のやり方を説明していきます。手順通り行っていけば、意外と簡単にできるものです。

自分で入力していくことでローン返済の理解を深め、無理して借金をする恐れを未然に防げるので、おすすめできる方法ですよ。

(アイキャッチ画像出典:https://pixabay.com/ja/photos/%E5%AD%90%E4%BE%9B-%E5%8B%9D%E5%88%A9-%E6%88%90%E5%8A%9F-593313/)

ローンの返済方法は2種類

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

元利均等返済

https://www.nomu.com/loan/story/05.html

  • 毎月の支払う返済額が一定
  • 返済計画が立てやすく、「元金均等返済」よりも借り入れ当初の返済額が少なくて済む
  • 借り入れ当初の利息が大きいので、元金部分の返済スピードは遅い

出典:https://www.nomu.com/loan/story/05.html

元金均等返済

https://www.nomu.com/loan/story/05.html

  • 毎月の返済額のうち、元金の額を一定にして支払う
  • 元金の減少が早いため、返済が進むにつれて、毎月の返済額は少なくなる
  • 元金の減少が早いため、総支払利息や総支払額が少なくて済む
  • 借り入れ当初の返済額が大きくなるので、初めのうちは返済負担が大きくなる

出典:https://www.nomu.com/loan/story/05.html

主に元利均等返済によるローンが一般的となっていて、元金均等返済を選択する人は少数です。

この記事では、「元利均等返済」と「元金均等返済」の両方に対応するために、それぞれのエクセルでの計算方法を紹介しますので安心して下さい。

スポンサーリンク

エクセルでローン返済計算①元利均等返済の計算方法

まずは、エクセルで元利均等返済のローン返済計算について解説していきましょう。

具体例として、「金利3%、期間60回(5年)、借入残高1000万円」とします。

元利均等返済をエクセルで計算するためには、PMT関数を使います。関数が出てきて難しいなと感じてしまうかもしれませんが、手順ごとに解説していきますので安心して下さい。

Step1

まずは、セルA1に「利率」、B1に「期間」、C1に「借入残高」を入力して下さい。次に、A2に「3%」、B2に「60」、C2に「10,000,000」と入力します。

編集部の画像

Step2

次に、セルD1に元利均等返済の計算結果を出すための項目、「月々の返済額」と入力して下さい。これからPMT関数を使って、D2に計算結果を出します。

次に「数式」のタブをクリックし、「関数の挿入」をクリックします。

編集部の画像

Step3

次に、関数の検索で「PMT」と入力して下さい。下の関数名に「PMT」が出てきますので、クリックしてOKを押します。

編集部の画像

Step4

次に、「利率」「期間」「現在価値」「将来価値」「支払期日」という項目が出てきます。今回は「将来価値」と「支払期日」は使いませんので無視して大丈夫です。

利率は「A2」をクリックし、12で割る必要があるので「A2/12」と入力します。期間は「B2」をクリックし、借入残高に当たる、現在価値という項目は「C2」をクリックして下さい。

編集部の画像

Step5

手順通り入力しOKをクリックすると、セルD2に元利均等返済の月々の返済額の結果「¥-179,687」が出ます。これが、元利均等返済の月々の返済額です。

編集部の画像

エクセルでローン返済計算②元利均等返済の元金を計算

「PMT関数」を使って、元利均等返済のローン返済計算を出すことができました。ですが、「PMT関数」は元利均等返済の「元金 + 利息」の計算結果が出てきます。

元金、利息それぞれの部分だけを知ることができません。ここではエクセルの「PPMT関数」を使って、元利均等返済の元金部分の計算方法を解説していきます。

Step1

元利均等返済の計算結果を出すために使ったシートを使っていきます。セルB1の部分に「期」と入力して下さい。「期」とは、何回目のローン返済なのかを計算するのに必要な項目です。

編集部の画像

Step2

元利均等返済の計算結果を出した時と同じように、「数式」をクリックし、「関数の挿入」をクリックして下さい。

編集部の画像

Step3

関数の検索のところで、「PPMT」と入力します。そうすると、関数名のところに「PPMT」が出てきますのでクリックして、OKを押します。

編集部の画像

Step4

「利率」のところにA2セルをクリックし、12で割る必要があるので「A2/12」と入力してください。

「期」のところはB2をクリックし、「期間」はC2をクリック、借入残高にあたる「現在価値」はD2をクリックします。

編集部の画像

Step5

それぞれの項目を入力しOKを押すと、セルE2に元利均等返済の元金部分である「¥-154,687」の計算結果が出てきます。

編集部の画像

スポンサーリンク

エクセルでローン返済計算③元利均等返済の利息を計算

次は、元利均等返済の利息部分をローン返済の計算をする方法を紹介します。「元金 + 利息」の利息に当たる部分です。

元利均等返済の利息部分を出すには、エクセルの「IPMT関数」を使えば計算結果が出ますよ。

Step1

元利均等返済の元金部分を計算する時に使った「利率」、「期」、「期間」、「現在価値(借入残高)」の項目を使います。

「関数の挿入」をクリックし、関数の検索で「IPMT」を入力すると、下に出てきます。

編集部の画像

Step2

先ほどと同じように「利率」、「期」、「期間」、「現在価値(借入残高)」を該当するセルを各項目にそれぞれ入力して下さい。

編集部の画像

Step3

入力後、OKをクリックしたあと、元利均等返済の利息部分の計算結果である「¥-25,000」が出てきます。

編集部の画像

エクセルでローン返済計算④元金均等返済の計算方法

元利均等返済をエクセルの関数を使ってローン返済の計算をしていきました。ここから、元金均等返済について計算していきます。

元金均等返済は、元利均等返済の時のように1つの関数を使って一発では計算できません。自分で、元金均等返済の利息部分と固定されている元金部分を計算し、両方を足さないといけません。

元金均等返済の利息部分については、「ISPMT関数」を使えば結果が分かります。

Step1

元利均等返済の元金部分を計算する時に使った「利率」、「期」、「期間」、「現在価値(借入残高)」の項目を使って下さい。

「関数の挿入」をクリックし、関数の検索で「ISPMT」を入力すると、下に出てきます。

編集部の画像

Step2

先ほどと同じように「利率」、「期」、「期間」、「現在価値(借入残高)」を該当するセルを各項目にそれぞれ入力して下さい。

編集部の画像

Step3

入力後、OKをクリックしたあと、元金均等返済の利息部分の計算結果である「¥-24,583」が出てきます。

編集部の画像

スポンサーリンク

エクセルでローン返済計算⑤元金均等返済の計算結果

元金均等返済の固定されている元金部分を計算したい場合は、今まで紹介した特別な関数を使う必要はありません。

前提条件は、借入残高の1000万円を、期間である60回でローンの返済をすることになってるので割り算をします。

元金部分の計算結果が出たら、先ほど求めた利息部分を足して合計を出せば、元金均等返済の返済額が分かります。

Step1

セルF1に月々の返済額(元金部分)の項目を作り、F2にD2の借入残高10,000,000と、C2の期間60を選択し割り算をして下さい。

編集部の画像

Step2

割り算の結果、元金均等返済の月々の返済額(元金部分)の計算結果である、「¥166666.667」が出ます。

編集部の画像

Step3

次に、セルG1に月々の元金均等返済額の項目を作り、先ほど計算した利息部分の「¥-24,583」と元金部分の「¥166,666.667」を足して合計値を出します。

注意して欲しいのは、利息部分がマイナスになっていてプラスに直す必要があるので、プラスの「24583」で入力して下さい。

編集部の画像

Step4

元金均等返済の利息部分と、元金部分を足し合わせた結果、元金均等返済額の計算結果である「¥191,250」が出ます。

エクセルでローン計算を行うメリット

https://www.pakutaso.com/20160548144post-7932.html

ここまで、エクセルを使ったローン返済計算を紹介していきました。

パソコンのエクセルを使ってローン返済計算を行うメリットとデメリットをそれぞれ紹介します。メリットは以下の点が考えられます。

ココがおすすめ
  • 自分で借入残高や支払い回数などを入力していくので、ローン返済の理解に繋がる
  • 慣れてきたら自分で好きにカスタマイズできる
  • パソコンのエクセルは、スマホのローン返済計算アプリとは違って広告が表示されない
  • スマホのローン返済計算アプリはエラーで動作が終了してしまうことが多いが、パソコンのエクセルには心配ない

エクセルでローン計算を行うデメリット

https://www.pakutaso.com/20190644177post-21621.html

次に、パソコンのエクセルを使ってローン返済計算を行うデメリットを挙げていきます。

ココがダメ
  • 自分で入力して作らないといけないので、手間がかかる
  • エクセルの関数を使わないと計算結果を求められない
  • 場合によってはパソコンを持ってない人もいる
  • 外出先でエクセルのデータを確認するのに不便

スマホでローン計算のエクセルのファイルを見る方法

https://apps.apple.com/jp/app/microsoft-excel/id586683407?ign-mpt=uo%3D8

パソコンのエクセルでローン返済の計算をしてきたけど、せっかく作ったのに、外でパソコンを持っていかないと確認できないのかしら・・・。

安心して下さい。iPhoneとAndroid向けにエクセルの公式アプリがありますので、ダウンロードすれば利用可能です。無料で利用できます。ただし、以下の注意点もあるので気をつけて下さい。

  • ダウンロードした後、無料登録をしないと編集ができない
  • スマホサイズだと小さすぎて操作しにくい。タブレットサイズがちょうどいい
  • 本格的にやりたいなら、外付けのキーボードが必須

まとめ

ここまで、エクセルを使ってローン返済の計算をする方法を紹介していきました。まとめますと、以下のようになります。

エクセルで元利均等返済を計算する方法
  • 元利均等返済を求めるには、「PMT関数」を使う
  • 元利均等返済の元金部分だけを求めるには、「PPMT関数」を使う
  • 元利均等返済の利息部分だけを求めるには、「IPMT関数」を使う
エクセルで元金均等返済を計算する方法
  • 元金均等返済の利息部分だけを求めるには、「ISPMT関数」を使う
  • 元金均等返済の元金部分だけを求めるには、借入残高を期間で割り算すれば結果が出る
  • それぞれ求めた利息部分と元金部分を足し合わせると、元金均等返済の計算結果が出る

エクセルでローンの返済金額を求めるのは少し手間がかかってしまうかもしれませんが、自分で入力していくことで、ローン返済の理解が進みます。

理解を深めることで、無理して借金をする恐れを未然に防ぐことができます。ゆっくりでもいいので、この記事を参考にして自分だけのローン返済計算表を作っていきましょう。

ABOUTこの記事をかいた人

ふぉむ

本業の介護職正職員(6年目)として、毎日奮闘しながらwebライティングをしています。 出勤前、帰宅後、休日の時間に月10記事から20記事をメドに(1記事あたり文字数平均3,000~4,000字)納品しています。 現在掲載させていただいているサイト。 Cuterest(芸能) やすたび(観光) DEBIT INSIDER(金融) ルアナ占い(占い) 高級家具.com(インテリア) エグゼクティブナビ(転職) ジャンルにとらわれずに、自分でチャレンジするよう心がけています。 twitterにて、私の書いた記事を毎月3つ公開しています。