IPMT 関数はローンの返済額のうちの利息、PPMT 関数は元金（借りたお金を返した分）を、何回目かを指定して求める関数です。=IPMT(B3/12,1,B4*12,B2) なら、1 回目の返済に含まれる利息が出ます。
毎月の返済額が同じ（元利均等返済）でも、はじめは利息が多く、だんだん元金が多くなります。この記事では、IPMT・PPMT の書き方と返済予定表の作り方を、実際の Excel で確かめた結果で説明します。
IPMT・PPMT 関数の書き方
=IPMT(利率, 何回目, 期間, 現在価値)
=PPMT(利率, 何回目, 期間, 現在価値)| 引数 | 意味 |
|---|---|
| 利率 | 1 回あたりの利率（毎月払いなら年利÷12） |
| 何回目 | 利息・元金を知りたい回（1〜期間） |
| 期間 | 返済の回数（毎月払いなら年数×12） |
| 現在価値 | 借入額 |
PMT 関数に「何回目」が 1 つ増えた形です。PMT と同じく、結果はマイナスで出るので、前に - を付けるとプラスで表示できます。毎月の返済額そのものは PMT 関数で求めます。
1. 1 回目の利息を出す（IPMT）
借入額 300 万円・年利 1.5%・10 年の、1 回目の返済に含まれる利息です。
=-IPMT(B3/12,1,B4*12,B2)
結果は 3,750 でした。300 万円×1.5%÷12 と同じで、「残っている借入額×月の利率」が利息になります。
2. 1 回目の元金を出す（PPMT）
同じ書き方で、PPMT にすると元金の分が出ます。
=-PPMT(B3/12,1,B4*12,B2)
結果は 23,187 でした。利息 3,750 と足すと、毎月の返済額（PMT の結果）になります（下の検証）。
3. 12 回分の利息を 1 つの式で並べる
「何回目」に SEQUENCE(12)（1〜12 の連番）を入れると、12 回分の利息が縦に並びます（Microsoft 365・Excel 2021）。
=-IPMT(B3/12,SEQUENCE(12),B4*12,B2)
利息は 3,750・3,721…と少しずつ減り、12 回目は 3,429 でした。元金を返した分だけ、次の月の利息が減るためです。PPMT で同じようにすると、元金は少しずつ増えていきます。
4. 1 年目の利息の合計を出す（CUMIPMT）
○回目から○回目までの利息の合計は CUMIPMT 関数で出せます。書き方は CUMIPMT(利率, 期間, 現在価値, 開始, 終了, 支払期日) で、支払期日（0 か 1）は省略できません。
=-CUMIPMT(B3/12,B4*12,B2,1,12,0)
結果は 43,079 で、上の 12 回分の利息を足した値と同じです。元金の合計は CUMPRINC 関数で同じように出せます。
【実機で検証】IPMT でつまずくところ
| ためしたこと | 式 | 結果 |
|---|---|---|
| 1 回目の IPMT＋PPMT | =IPMT(IPMT!B3/12,1,IPMT!B4*12,IPMT!B2)+PPMT(IPMT!B3/12,1,IPMT!B4*12,IPMT!B2) | -26,937 |
| PMT（毎月の返済額） | =PMT(IPMT!B3/12,IPMT!B4*12,IPMT!B2) | -26,937 |
| 最後（120 回目）の利息 | =-IPMT(IPMT!B3/12,120,IPMT!B4*12,IPMT!B2) | 34 |
| 元金の合計（1〜120 回） | =-CUMPRINC(IPMT!B3/12,IPMT!B4*12,IPMT!B2,1,120,0) | 3,000,000 |
| 何回目に 0 | =IPMT(IPMT!B3/12,0,IPMT!B4*12,IPMT!B2) | #NUM! |
| 何回目に 121（期間より大きい） | =IPMT(IPMT!B3/12,121,IPMT!B4*12,IPMT!B2) | #NUM! |
| CUMIPMT の借入額をマイナスで | =CUMIPMT(IPMT!B3/12,IPMT!B4*12,-IPMT!B2,1,12,0) | #NUM! |
1 回目の IPMT＋PPMT は -26,937 で、PMT の -26,937 と同じでした。最後の 120 回目の利息は 34 まで減ります。1〜120 回の元金を合計すると 3,000,000 で、借入額と同じです。
何回目に 0 を入れると #NUM!、期間より大きい 121 を入れると #NUM! になりました。何回目は 1〜期間の数にします。
CUMIPMT は、借入額をマイナスで入れると #NUM! になりました。IPMT・PPMT とちがい、CUMIPMT・CUMPRINC は借入額をプラスで入れます。
よくある質問
返済予定表（何回目・返済額・利息・元金・残高）を作るには？
A 列に回（1〜120）を並べ、利息は =-IPMT(年利/12,A2,回数,借入額)、元金は PPMT、残高は「借入額－元金の累計」で出せます。累計は SCAN 関数でも 1 つの式で出せます。
元金均等返済のときは？
IPMT・PPMT は元利均等返済（毎月の返済額が同じ）の計算です。元金均等返済は、元金が毎月同じ（借入額÷回数）で、利息は「残高×月の利率」で計算します。
ISPMT 関数とのちがいは？
ISPMT は元金均等返済の利息を求める関数で、IPMT とは計算のしかたがちがいます。元利均等返済には IPMT を使います。
コメント