住宅ローンなど、借入金を月々返済していくとかかってくる利子。
ローンの多くは「元利均等返済方式」という、返済期間中ずっと一定の返済額で返済していく方法を採用しています。
エクセルには、この「元利均等返済方式」を求める関数式が入っています。
実際にローンを組む前に、借入金、利率、返済回数からシミュレートしてみてはどうでしょうか？
月々の返済額を求めるなら、PMT関数！
借入金に対して、利率と返済回数を入力することで、1回あたりの返済額を求める際は、PMT関数を利用します。
PMT関数の算式は、以下のように書きます。
=PMT(利率,期間,現在価値,将来価値,支払期日）
項目 | 意味 |
|---|---|
| 利率 | ％で入力し、利率と期間の単位をそろえる。 （例:月毎の返済額の算出でならば、年利／12ヶ月で指定） |
| 期間 | 回数で入力し、利率と単位をそろえる。 （例：月毎の返済額の算出なら、年数×12ヶ月で指定） |
| 現在価値 | 借入金の場合は、借入額を指定します。 |
| 将来価値 | 返済後の残金を指定します。完済の場合は「０」と入力します。 |
| 支払期限 | 期首払いは「1」、期末払いは｢０｣または省略します。 |
では、実際に入力して計算してみましょう。
今回は例として、借入金120万円、返済回数60回（5年間）、利率3％と仮定します。
「=PMT（）」と手打ちでも入力できますが、関数挿入ボタンから「財務」を選択すると簡単にPMT関数を使えます。
下図が表示されますので、各数値をそのまま入力していけばOKです。
式の入力が完了すると、毎月の返済額が表示されます。
=PMT(C4/12,B4,A4,0)
利率がC4/12となっているのは、ここでの利率が年利で表示されており、月単位で返済する際にかかる利率（月利）を計算するためです。
同じ借入金でも返済期間（5年（60ヶ月）、７年（84ヶ月）、10年（120ヶ月）、15年（180ヶ月）、20年（240ヶ月））を複数設定し、利率も合わせて変更すると、毎月の返済額のシミュレートを行なうことができます。
返済額の内、元金はどれだけ返済されているかを求めるならPPMT関数！
「元利均等返済方式」は、毎月の返済額は一定です。しかし、毎月返済している元金は一定ではありません。「元金＋利息」の支払いが、支払い期間で一定になるように振り分けているのです。
では、一回毎の支払いで、元金をどれだけ支払っているのでしょうか。
これはPPMT関数を使えばわかります。
PPMT関数の算式は、以下のように書きます。
＝PPMT（利率,期,期間,現在価値,将来価値）
項目 | 意味 |
|---|---|
| 期 | 返済回数の何回目か。 |
※その他の数値は、PTMの説明と同じです。
前例と同じく借入金120万円、返済回数60回（5年間）、利率3％と仮定して計算してみます。
式を入力すると、その回数での元金が算出されます。
式を入力する際に注意したいのは、利率、期間、現在価値をセルで指定する場合は「$」をつけること。「$」をつけることで、オートフィルを行なっても、そのセルは固定されます。
それぞれのセルを指定せず、直接数値を式に入力するのも手です。
あとは、オートフィルでドラッグしていくと、各回数の支払い金額に対する元金を見ることができます。
返済額の内、利息分はいくらなのか求めるならIPMT関数！
IPMT関数は、PPMT関数の逆で、支払いの内の利息分を計算する関数です。
月々の「＝（返済額）－（PPMTの結果）」でも算出することもできますが、覚えておいて損はないと思います。
IPMT関数の算式は、以下のように書きます。
＝IPMT（利率,期,期間,現在価値,将来価値）
入力するそれぞれの数値は、PPMTと全く同じです。
先ほどのPPMTの式の横に利息分の式をつくり、実際に数値を入力してみます。
オートフィルで、各回数を調べてみると、回数を重ねる毎に毎月の元金の割合が上がり、利息分が減っていることが分かります。
ローン返済表（シミュレーター）
毎月の返済額、元金、利息が分かれば、ローンシミュレーターの完成です。
あとは、好みに合わせて「総返済額」や「元金残」などの項目をつくると、より使いやすいローンシミュレーターになっていきます。
「総返済額」はFV関数を使って計算できます。詳しくは、下記サイトをご参照ください。
- 元利均等返済での最終返済金額を計算する方法（エクセル関数リファレンス）
下図の「元金（元金残）」の項目は、次のように計算しています。
D7の計算式: =A4-B7
D8の計算式: =D7-B8
D8以下、オートフィル
このように返済シミュレーターを作り、自分の使いやすいようにカスタマイズすると、必要な情報がひと目で見られるようになり、便利です。
