ISPMT 関数は、元金均等返済（毎回同じ額の元金を返す方法）で、ある回に払う利息を求める関数です。
元金均等では、残りの借入額が毎回へっていくので、利息も毎回へります。この記事では、ISPMT の使い方と、元利均等（IPMT）とのちがいを、実際の Excel で確かめた結果で説明します。
練習用Excelファイル（無料ダウンロード）
借入額・年利・返済回数の表と、この記事の ISPMT・IPMT の式をまとめた練習ファイルです。
Excelファイル（.xlsx）／登録不要／数式・練習データ入り
目次
ISPMT 関数の書き方
=ISPMT(利率, 期, 期間, 現在価値)| 引数 | 意味 |
|---|---|
| 利率 | 1 回あたりの利率（年利÷12） |
| 期 | 何回目か。0 から数える |
| 期間 | 返済回数 |
| 現在価値 | 借入額 |
元利均等返済（毎回の返済額が同じ）の利息は IPMT関数で出します。
1. 1 回目の利息を出す
ISPMT は払うお金をマイナスで返すので、前に – を付けています。1 回目は期を 0 にします。
=-ISPMT(B3/12,0,B4,B2)
結果は 3,000 円でした。120 万円×3%÷12 と同じです。
2. 毎月の利息の表を作る
SEQUENCE(B4)-1 で 0〜11 を作って、12 回分をまとめて出します。
=-ISPMT(B3/12,SEQUENCE(B4)-1,B4,B2)
1 回目は 3,000 円、2 回目は 2,750 円、12 回目は 250 円で、毎回同じ額ずつへりました。
3. 利息の合計を出す
12 回分の利息を SUM で合計します。
=-SUM(ISPMT(B3/12,SEQUENCE(B4)-1,B4,B2))
結果は 19,500 円でした。
4. 元利均等（IPMT）の利息とくらべる
IPMT は 1 から数えるので、SEQUENCE(B4) をそのまま入れます。
=-SUM(IPMT(B3/12,SEQUENCE(B4),B4,B2))
元利均等の利息の合計は 19,589 円で、元金均等の 19,500 円より多くなりました。
【実機で検証】ISPMT でつまずくところ
| ためしたこと | 式 | 結果 |
|---|---|---|
| 期を 1 にした ISPMT | =-ISPMT(例!B3/12,1,例!B4,例!B2) | 2,750 |
| 期を 12 にした ISPMT | =-ISPMT(例!B3/12,12,例!B4,例!B2) | 0 |
| 期を 13 にした ISPMT | =-ISPMT(例!B3/12,13,例!B4,例!B2) | -250 |
| 回数を 0 にした ISPMT | =-ISPMT(例!B3/12,0,0,例!B2) | #DIV/0! |
| 毎回の元金（借入額÷回数） | =例!B2/例!B4 | 100,000 |
| 年利を 12 で割らない | =-ISPMT(例!B3,0,例!B4,例!B2) | 36,000 |
期を 1 にすると 2,750 円で、2 回目の利息になりました。ISPMT は 0 から数えます。期を 12 にすると 0、13 にすると -250 です。
回数を 0 にすると #DIV/0! になりました。毎回返す元金は 100,000 円です。
年利を 12 で割らずに入れると 36,000 円で、12 倍の利息になってしまいました。
よくある質問
元利均等の利息と元金を分けたいときは？
IPMT・PPMT 関数で説明しています。
毎月の返済額を出したいときは？
PMT 関数で、元利均等の毎月の返済額を出せます。
1 年分の利息をまとめて出したいときは？
CUMIPMT 関数で、元利均等の利息の合計を出せます。
練習用Excelファイル（無料ダウンロード）
この記事の例をまとめた練習ファイルです。
Excelファイル（.xlsx）／登録不要／数式・練習データ入り
コメント