【Excel】エクセルで元金均等返済の計算式と作成方法（PPMT関数・IPMT関数・返済表作成）

住宅ローンや事業ローンの返済計画を考える際、元金均等返済という返済方式を聞いたことがある方も多いでしょう。

元金均等返済は毎月の元金返済額が一定で、利息は残高に応じて減っていくため、返済当初は支払いが多いものの総返済額が元利均等返済より少なくなるというメリットがあります。

エクセルではPPMT関数とIPMT関数を使うことで、元金均等返済の詳細な返済表を簡単に作成できます。

本記事では、エクセルで元金均等返済の計算式と返済表を作成する方法について、PPMT関数・IPMT関数・返済表作成の観点からわかりやすく解説していきます。

返済計画を自分でシミュレーションしたい方に役立つ内容ですので、ぜひ参考にしてください。

エクセルで元金均等返済を計算するにはPPMT関数とIPMT関数を使う

それではまず、元金均等返済の基本的な仕組みと、エクセルでの計算方法について解説していきます。

元金均等返済は毎回の元金返済額が一定で、それに各回の残高に応じた利息が加算されて返済額が決まる仕組みです。

エクセルではPPMT関数（元金分の計算）とIPMT関数（利息分の計算）を組み合わせることで、各回の返済内訳を正確に計算できます。

元金均等返済と元利均等返済の違い

元金均等返済と元利均等返済の違いを正しく理解しておくことが、返済表作成の前提として重要です。

比較項目	元金均等返済	元利均等返済
毎月の返済額	返済が進むにつれて減少	毎月一定
元金返済額	毎月一定	返済が進むにつれて増加
利息返済額	残高減少に伴い毎月減少	返済が進むにつれて減少
総返済額	少ない（利息総額が小さい）	多い（利息総額が大きい）
返済当初の負担	大きい	小さい

元金均等返済のほうが総返済額は少ないものの、返済初期の月々の支払いが大きくなるため、家計の余裕度を確認した上で選択することが大切です。

どちらの返済方式が自分に向いているかをエクセルでシミュレーションして比較検討することが、賢いローン選択につながります。

PPMT関数の基本構文と使い方

PPMT関数は「Principal Payment（元金返済）」の略で、元金均等返済の特定回の元金返済額を計算します。

たとえば、借入金額2000万円・年利2%・返済期間30年で、1回目の元金返済額を求める場合は次のようになります。

元金均等返済ではPPMT関数の結果はすべての回で同じ値になります。

これが「元金均等」と呼ばれる理由です。

IPMT関数の基本構文と使い方

IPMT関数は「Interest Payment（利息返済）」の略で、特定回の利息支払額を計算します。

IPMT関数の結果はマイナスの値で返ってくるため、表示する際はマイナスをつけて表示するか、ABS関数で絶対値に変換する方法が一般的です。

PPMT関数の結果（元金分）とIPMT関数の結果（利息分）を足し合わせると、その回の総返済額が求まります。

元金均等返済の詳細な返済表を作成する手順

続いては、PPMT関数とIPMT関数を使って元金均等返済の詳細な返済表を作成する手順を確認していきます。

返済表を作ることで、全期間にわたる返済の内訳・残高の推移・総返済額などを一目で把握できるようになります。

返済表の設計と条件入力セルの配置

まず、シート上部に返済条件を入力するセルエリアを設けます。

借入金額（例：B1）・年利（例：B2）・返済期間（年）（例：B3）の3つのセルを用意します。

続いて、返済表の列見出しを設定します。返済回数・返済年月・元金返済額・利息返済額・合計返済額・残高という6列が基本構成です。

条件セルを絶対参照（$B$1のようにドル記号をつける）で参照することで、数式を下にコピーしても参照がずれない返済表が作れます。

条件セルの値を変えるだけで全行が自動更新される仕組みを最初から組み込んでおくことが、実用的なシミュレーションツールを作る鍵です。

各列の数式を入力する手順

返済表の各列に入力する数式の例を説明します。

残高の計算式は1回目は借入金額から元金返済額を引き、2回目以降は前回残高から元金返済額を引く構造にします。

C列とD列の計算式にはABS関数を使ってマイナスの値を正の値に変換しています。

C2:F2に数式を入力したら、返済総回数の行まで数式をコピーするだけで返済表が完成します。

合計行と集計セルの追加

返済表の下部に集計行を追加することで、総返済額や総利息額などの重要な指標をひと目で確認できます。

元金返済額の合計（SUM(C列)）は借入金額と一致するはずなので、検算にも使えます。

利息返済額の合計がローンの「利息総額」で、これが元利均等返済との比較ポイントになります。

合計返済額の合計（元金＋利息の総額）がローンで支払う総額です。

集計行を追加することで、返済条件を変更したときの総支払額の変化がすぐに確認できる、実用性の高いシミュレーションツールになります。

元金均等返済と元利均等返済の比較シミュレーション表の作成

続いては、元金均等返済と元利均等返済の結果を並べて比較できるシミュレーション表の作成方法を確認していきます。

2つの返済方式を同じシート上で比較することで、どちらが自分に適しているかを判断しやすくなります。

2つの返済方式を並列表示するシートの構成

同じシート上に元金均等返済と元利均等返済の両方の返済表を並べて配置すると、比較が一目でできます。

左側（A〜F列）に元金均等返済の返済表、右側（H〜M列）に元利均等返済の返済表を配置します。

両方の返済表の借入金額・年利・返済期間には同じ条件セルを参照させることで、条件を変えたときに両方の表が同時に更新されます。

2つの返済方式の総支払額・利息総額・月々の返済額（初月・最終月）を比較するサマリーセクションを加えると、意思決定に役立つ資料が完成します。

返済推移グラフで視覚的に比較する方法

返済表のデータをもとにグラフを作成すると、2つの返済方式の月々の返済額推移を視覚的に比較できます。

折れ線グラフで横軸を返済回数・縦軸を月々の返済額にすると、元金均等返済が右肩下がりに減少していく一方、元利均等返済が一定水準を保つ様子が一目でわかります。

また、積み上げ棒グラフで元金分と利息分の割合の変化を表示すると、両方式の利息負担の構造の違いが視覚的に理解しやすくなります。

残高の推移を折れ線グラフで比較すると、元金均等返済のほうが早期に残高が減少していく様子が確認できます。

グラフによる比較は数字だけでは伝わりにくい違いを直感的に伝えることができ、家族や関係者への説明資料としても非常に有効です。

繰上げ返済を組み込んだ応用シミュレーション

元金均等返済の返済表に繰上げ返済の効果を組み込むことで、よりリアルな返済計画を立てることができます。

各月の行に「繰上げ返済額」の入力列を追加し、残高の計算式を「前月残高−毎月の元金返済額−繰上げ返済額」に変更します。

繰上げ返済を行った月以降は残高がさらに少なくなるため、以降の利息額も自動的に減少します。

残高がゼロになった時点が実質的な返済完了時期となるため、IF関数を使って残高がゼロ以下になったら表示を終了する工夫を加えると、完成度の高い返済シミュレーターになります。

繰上げ返済の効果を月単位でシミュレーションできるツールをエクセルで自作することは、総返済額の節約計画を具体的に立てるうえで非常に効果的です。

まとめ

本記事では、エクセルで元金均等返済の計算式と返済表を作成する方法について、PPMT関数・IPMT関数・返済表作成の観点から詳しく解説しました。

PPMT関数で元金分・IPMT関数で利息分をそれぞれ計算し、組み合わせることで毎回の返済内訳を正確に把握できます。

条件セルを絶対参照で設定した返済表を作成しておけば、借入金額・金利・返済期間を変えるだけで即座に試算結果が更新される実用的なシミュレーションツールが完成します。

元利均等返済との比較グラフや繰上げ返済シミュレーションを加えることで、さらに有用な意思決定ツールとして活用できます。

今回ご紹介した方法を参考に、自分だけのローン返済シミュレーターをぜひエクセルで作成してみてください。