住宅ローンや自動車ローンなどの返済計画を立てる際、毎月の返済額がいくらになるのかを事前に把握しておくことは非常に重要です。
エクセルを使えば、PMT関数を活用することで元利均等返済の毎月の返済額を簡単に計算できます。
金融機関のウェブサイトにある計算ツールを使わなくても、自分でカスタマイズした返済シミュレーションが作成できる点が大きな魅力です。
本記事では、エクセルで元利均等返済を計算する方法について、PMT関数の使い方・ローン計算の基礎・返済シミュレーション表の作成方法までわかりやすく解説していきます。
ローン計算をエクセルで行いたいすべての方に役立つ内容ですので、ぜひ参考にしてください。
エクセルのPMT関数で元利均等返済の毎月返済額を計算できる
それではまず、元利均等返済とは何か、そしてPMT関数を使ってどのように計算するかについて解説していきます。
元利均等返済とは、毎月の返済額（元金＋利息）が一定になる返済方法のことです。
住宅ローンで最も多く採用されている返済方式で、毎月の支払額が変わらないため家計管理がしやすいという特徴があります。
PMT関数の基本構文と引数の意味
PMT関数は「Payment（支払い）」の略で、一定期間の均等返済額を計算する関数です。
【PMT関数の構文】
=PMT(利率, 期間, 現在価値, [将来価値], [支払期日])
利率 ：1回の返済期間あたりの利率（年利を月払いなら12で割る）
期間 ：返済の総回数（年数×12で月数に変換）
現在価値：借入金額（ローン元本）をマイナスで入力
将来価値：返済終了後の残高（通常は0または省略）
支払期日：0または省略＝期末払い、1＝期首払い
たとえば、借入金額3000万円、年利1.5%、返済期間35年（月払い）の場合のPMT関数は次のようになります。
【PMT関数の入力例】
=PMT(1.5%/12, 35*12, -30000000)
または
=PMT(0.015/12, 420, -30000000)
この場合の計算結果は約91,000円前後（概算）
借入金額をマイナスで入力することで、返済額がプラスの値として表示されます。
プラスで入力するとマイナスの返済額が表示されてしまうため、注意が必要です。
PMT関数で年払い・半年払いを計算する方法
PMT関数は月払いだけでなく、年払いや半年払いにも対応しています。
年払いの場合は「利率」に年利をそのまま入力し、「期間」に返済年数を入力します。
半年払いの場合は「利率」に年利÷2を、「期間」に返済年数×2を入力します。
支払い頻度に応じて利率と期間を正しく合わせることが、正確な計算の基本です。
月払いが最も一般的なため、通常は年利を12で割り、返済年数を12倍した値を使います。
セル参照を使ったPMT関数の活用方法
PMT関数の引数に直接数値を入力するのではなく、セル参照を使うことで、条件を変更した際に自動的に再計算される便利な設定が可能です。
【セル参照を使ったPMT関数の例】
A1セル：借入金額（例：30000000）
A2セル：年利（例：0.015）
A3セル：返済期間（年）（例：35）
B1セル：=PMT(A2/12, A3*12, -A1)
この設定であれば、A1〜A3の値を変更するだけで自動的に返済額が再計算されます。
セル参照を活用したPMT関数は、さまざまな条件でのシミュレーションを行う際に非常に重宝します。
元利均等返済の返済シミュレーション表を作成する方法
続いては、元利均等返済の詳細な返済シミュレーション表の作成方法を確認していきます。
毎月の返済額の内訳（元金分と利息分）を月別に一覧化した返済シミュレーション表を作成することで、返済計画をより詳細に把握できます。
PPMT関数とIPMT関数を使った返済内訳の計算
PMT関数が毎月の返済額合計を計算するのに対し、PPMT関数は返済額の中の「元金分」を、IPMT関数は「利息分」を別々に計算します。
【PPMT関数・IPMT関数の構文】
=PPMT(利率, 期, 期間, 現在価値)
=IPMT(利率, 期, 期間, 現在価値)
「期」は何回目の返済かを表す（1回目なら1、2回目なら2）
これら3つの関数を組み合わせることで、各月の返済額・元金分・利息分・残高を計算した詳細な返済一覧表が作成できます。
元利均等返済では返済が進むにつれて利息分が減り、元金分が増えていく構造になっています。
この変化をシミュレーション表で可視化することで、ローンの全体像を把握しやすくなります。
返済シミュレーション表の基本構造
実用的な返済シミュレーション表の基本的な構造を説明します。
| 列 | 内容 | 使用する関数 |
|---|---|---|
| A列 | 返済回数（1〜返済総回数） | 連番（1, 2, 3…） |
| B列 | 毎月返済額 | PMT関数（絶対参照で固定） |
| C列 | 元金返済分 | PPMT関数 |
| D列 | 利息返済分 | IPMT関数 |
| E列 | 返済後残高 | 前月残高−元金返済分 |
1行目に条件（借入金額・年利・返済期間）を入力するセルを設け、各計算式でそのセルを絶対参照で使用します。
条件セルの値を変更するだけで全行の計算が更新される構造を作ることが、使いやすいシミュレーション表のポイントです。
繰上げ返済のシミュレーションを加える方法
繰上げ返済（ある月に通常以上の返済を行う）のシミュレーションを組み込むには、残高の計算式を少し工夫する必要があります。
各月の行に「繰上げ返済額」の入力列を追加し、残高の計算式に「前月残高−元金返済分−繰上げ返済額」を組み込みます。
繰上げ返済を行った月以降は残高が減少するため、以後の利息分も連動して変化します。
繰上げ返済シミュレーションを組み込むことで、総返済額の削減効果や返済完了時期の短縮効果を視覚的に確認できる実用的なツールになります。
実際のローン契約前に複数の繰上げ返済パターンを試算しておくことは、賢い返済計画を立てるうえで非常に有益です。
金利・借入額・期間を変えた返済比較シミュレーション
続いては、条件を変えた複数パターンの返済額比較シミュレーションの作成方法を確認していきます。
ローンを検討する際は、一つの条件だけで計算するのではなく、複数の条件を比較することで最適なプランを選びやすくなります。
データテーブルを使った一括シミュレーション
エクセルの「データテーブル」機能を使うと、1つの変数（借入金額・金利・返済期間のいずれか）を変化させたときの返済額を一覧表形式で計算できます。
まず基本となるPMT関数の計算式をセルに入力します。
次に、変数となる値を縦または横に並べた表を作成し、「データ」タブ→「What-If分析」→「データテーブル」を選択します。
行または列の入力セルに変数セルを指定することで、自動的に全パターンの計算結果が表示されます。
データテーブルを使ったシミュレーションは、手動で条件を変えるより圧倒的に効率的で、比較検討に最適な方法と言えます。
シナリオ機能を使った複数条件の保存と比較
「データ」タブ→「What-If分析」→「シナリオ マネージャー」を使うと、複数の条件パターン（シナリオ）を保存して比較できます。
たとえば、「フラット35（1.8%）」「変動金利（0.5%）」「固定10年（1.2%）」という3つのシナリオを保存しておき、ワンクリックで各シナリオの計算結果に切り替えられます。
また、「シナリオの概要」機能を使うと、すべてのシナリオの比較表を自動生成することも可能です。
複数の金融機関のローン条件を比較検討する場合に、このシナリオ機能は非常に役立ちます。
グラフを使った返済推移の可視化
返済シミュレーション表のデータをグラフ化することで、元金残高の減少推移や利息総額の変化を視覚的に確認できます。
縦軸に金額、横軸に返済回数をとった積み上げ棒グラフを作成すると、各月の元金分と利息分の割合の変化がひと目でわかります。
折れ線グラフで残高の推移を表示すると、ローンの減り具合を直感的に把握できます。
グラフによる可視化は、返済計画を家族や金融機関に説明する際にも非常に有用です。
見た目がわかりやすい資料を作ることで、返済計画の共有がスムーズになるでしょう。
まとめ
本記事では、エクセルで元利均等返済を計算する方法について、PMT関数の使い方・ローン計算の基礎・返済シミュレーション表の作成方法を詳しく解説しました。
PMT関数を使えば毎月の返済額を簡単に計算でき、PPMT関数・IPMT関数を組み合わせることで元金分・利息分の内訳まで詳細に把握できます。
データテーブルやシナリオ機能を活用することで、複数の条件を比較した本格的な返済シミュレーションがエクセル上で完結します。
グラフによる可視化を加えることで、返済計画をより深く理解し、関係者への説明にも役立てることができます。
今回ご紹介した方法を活用して、エクセルで実用的なローン返済シミュレーションをぜひ作成してみてください。
