仕事や普段の生活の中で、Excelを使っている人も多いのではないでしょうか。エクセルはほとんど使ったことがないという人でも、やり方を覚えてしまえば、複雑なローンの金利計算も簡単に行うことができます。ローンの月々の返済額や返済額の中の元金や金利に相当する分の金額を計算する方法、金利の累計額を計算する方法などをご紹介します。金利計算は、関数を使って計算しますので、エクセルのバージョンには関係なく計算することができます。なお、今回は、計算式が複雑にならないように金利は固定金利、返済方式は元利均等返済として計算をしています。

PMT関数でローンの月々の返済額を計算する方法

ローンを組む場合、借り入れた額、金利、返済期間から月々の返済額を計算する方法をご紹介します。PMT関数に必要な引数は5つです。

1. 利率 利率を指定します
2. 期間 返済期間を指定します
3. 現在価値 借入額を指定します
4. 将来価値 借入金を完済する場合は「0」を指定します
5. 支払期日 月末払いの場合は「0」または「省略」、月初払いの場合は「1」にします

PMT関数の計算式はこのような形になります

＝PMT（利率, 期間, 現在価値, 将来価値, 支払期日）

今回は、年利5％、返済期間20年、借入金額1,000万円、月末払いの場合の月々の返済額の計算を例に説明します。エクセルをほとんど使ったことがない方でも簡単に計算ができるように、下記のエクセルの図を見ながら、手順を確認してみてください。なお、エクセルの計算式を入力する場合は、半角英数を使用します。

返済額を表示させたいE9のセルに計算式を入力します。計算式は自動入力させることができますが、今回は説明がわかりやすいように手入力していきます。

　1.返済額を表示させたいセルをクリックします（E9）
　2.半角で「=PMT(」と入力します
　3.年利の数字が入っているセルをクリック（B9）したあと「/12」と入力します
　「/12」を入力する理由は、月々の返済額を計算したいので、年利を1か月単位の金利に変換するためです
　4.「,」カンマを入力します
　5.返済期間が入力されているセルをクリックしたあと（C9）「⋆12」と入力します
　「⋆12」を入力する理由は返済期間の単位が「年」なので「月」単位に変更するためです
　6.「,」カンマを入力します
　7.借入額が入力されているセルをクリックします（D9）
　8.「,」カンマを入力します
　9.月末払いの場合は「0」を月初払いの場合は「1」を入力し「）」を入力してかっこを閉じます

※　返済額は借入金なので、計算結果はマイナスで表示されます

このようにエクセルを使うと、簡単に月々の返済額や金利分を計算することができます。

IPMT関数で指定した期の金利相当分の金額を計算する方法

IPMT関数はローンの返済中の指定した期の金利相当分の金額を計算するときに使うことができます。

上記と同じ条件で借り入れた金額のうち、ローン返済を初めて2年目の最初の月の返済額の金利部分だけを計算する場合を例に計算方法をご紹介します。

IPMT関数に必要な引数は6つです。

1. 利率 利率を指定します
2. 期 金利を求めたい期を指定します（ローンの返済から何か月目にあたる月なのかを指定します）
3. 期間 返済期間を指定します
4. 現在価値 借入額を指定します
5. 将来価値 借入金を完済する場合は「0」を指定します
6. 支払期日 月末払いの場合は「0」または「省略」、月初払いの場合は「1」にします

IPMT関数の計算式はこのような形になります

＝IPMT（利率, 期, 期間, 現在価値, 将来価値, 支払期日）

計算方法は、PMT関数とほぼ同じですが、利率のあとに「期」という引数を入力する点がPMT関数と異なります。「期」は、ローンの返済を初めて何か月目の金額を計算するのかを指定します。たとえば、返済2年目の1か月目の金利を知りたい場合は、13か月目になるのでC4のセルに「13」を入力します。

 

CUMIPMT関数で指定した開始～終了期の金利の累計額を計算する方法

IPMT関数は、指定した期（ローン返済から何か月経過したかを指定）の金利分を計算することができますが、CUMIPMT関数を使うと、指定した期間の金利の累計額を計算することができます。

CUMIPMT関数に必要な引数は6つです。

1. 利率 利率を指定します
2. 期間 返済期間を指定します
3. 現在価値 借入額を指定します
4. 開始期 金利を計算したい最初の月を指定します
5. 終了期 金利を掲載したい期間の最後の月を指定します。
6. 支払期日 月末払いの場合は「0」または「省略」、月初払いの場合は「1」にします

CUMIPMT関数の計算式はこのような形になります

＝CUMIPMT（利率, 期間, 現在価値, 開始期, 終了期, 支払期日）

CUMIPMT関数もIPMT関数とほぼ同じように計算しますが、開始期と終了期を指定する点が異なります。

ローン返済1か月目から6か月目までの金利の累計額を計算する方法をご紹介します。

=CUMIPMT(B3/12,D3*12,E3,1,C3,0)

赤字の部分の引数を金利の計算したい期間を指定します。

この場合、ローン返済1か月目を指定したいのでE3のセルのあとに「1」を指定します。次に半年後を指定したいので、半年目の月数の数字が入力されている「C3」を指定します。

※これ以外の期間を計算したい場合は、赤字の部分を変更して、計算したい期間を指定してください。

最後に

エクセルの関数を使ったことがない人がみると、計算式が一見複雑に見えますが、使い方を覚えてしまうと、他の関数にも応用することができます。金利などの金額を計算するときのポイントは、「月」「年」の値を統一することです。金利の数字は、年利で月々の返済額を知りたい場合、年利は1年間の金利の数字なので12で割ると1か月の金利で計算することができます。また返済期間も20年のように年単位で入力した場合は、月数に変換するために12を掛けます。関数のしくみを覚えてしまうと、誰でも簡単に金利や返済額を計算することができるので、エクセルを活用してみてください。

不動産担保ローンについてより詳細にご覧になりたい場合は、こちらをご参照ください。