スプレッドシートのISPMT関数の使い方｜元金均等返済の利息を一発計算する方法

スポンサーリンク

「事業融資を元金均等で借りたんだけど、毎月の利息だけをスプレッドシートで出したい」という場面はありませんか。

元利均等返済（毎月の返済額が一定のローン）の利息は IPMT 関数で求められます。しかし元金均等返済（毎月の元金が一定のローン）の利息は、専用の財務関数を使う必要があります。

GoogleスプレッドシートのISPMT関数は、元金均等返済における指定回の利息額を一発で求められる関数です。設備投資ローンや事業融資、自治体の制度融資など、元金均等型のローン管理で活躍します。

この記事では、ISPMT関数の書式から、完全な返済スケジュール表の作り方、IPMTとの累計利息比較、よくあるエラー対処までを実務目線で解説します。

スプレッドシート ISPMT関数とは？読み方と何ができるか

ISPMT（読み方：アイ・エス・ピー・エム・ティー）は、元金均等返済における指定した回の利息額を計算する財務関数です。

関数名は「Interest on Straight-line Principal payMenT」に由来します。直訳すると「直線的な元金支払いに対する利息」という意味です。元金が直線（一定額）で減っていく返済方式を前提にした関数だとわかります。

ISPMT関数でできることをまとめると次の通りです。

  • 元金均等返済の第N回の利息額を計算できる
  • 全120回などの返済スケジュール表に利息列を作れる
  • IPMT関数（元利均等）と比較して、どちらが有利かシミュレーションできる
  • 事業融資・設備投資ローン・公的融資など元金均等型ローンの内訳を把握できる

ISPMT関数は引数が4つだけのシンプルな構文なので、財務関数のなかでも初心者が手を出しやすい部類に入りますよ。

元金均等返済と元利均等返済の違い（前提知識）

ISPMTを正しく使うには、「元金均等返済」と「元利均等返済」の違いを理解しておく必要があります。

返済方式毎回の元金毎回の利息毎回の返済総額主な用途
元利均等変動（初期は少ない）変動（初期は多い）一定住宅ローン
元金均等一定変動（回を追うごとに減少）変動（初期は多い）事業融資・設備投資

住宅ローンの多くは元利均等です。一方、事業融資・設備資金融資・自治体の制度融資などでは元金均等が選ばれる傾向があります。理由は「総支払利息が元利均等より少なくなることが多い」ためです。

元金均等を使う場面で活躍する関数が ISPMT です。一方、元利均等の利息は IPMT 関数で計算します。

元金均等が選ばれやすいローンの例
日本政策金融公庫の設備資金、信用金庫の事業融資、地方自治体の制度融資、リース契約の一部など、事業向け融資では元金均等が標準のことが多いです。

ISPMT関数の書式と引数

ISPMT関数の書式は次の通りです。

=ISPMT(利率, 期, 期間数, 現在価値)

各引数の意味を整理します。

引数必須内容単位の注意点
利率必須1期間あたりの利率年利の月払いなら÷12が必要
期必須利息を求めたい回数（1始まり）1〜期間数の整数
期間数必須返済の総回数月払いなら年×12
現在価値必須借入元本正の値で入力

引数はすべて必須です。元金均等返済を前提とした関数なので、IPMT/PPMT/CUMIPMTに必要な「将来価値」「支払期日」の引数はありません。シンプルな分、覚えるのも楽ですよ。

ISPMT関数の基本的な使い方｜第5回の利息を計算する

具体的な数値で試してみましょう。

条件設定（セルに入力）

項目値セル
年利2.4%B1
借入期間（年）10B2
借入元本（円）12,000,000B3

第5回（つまり借入から5か月目）の利息を求めるには、次の数式を入力します。

=ISPMT(B1/12, 5, B2*12, B3)

引数を分解するとこうなります。

  • B1/12：月利（年利2.4% ÷ 12 = 0.2%）
  • 5：第5回の利息を求める
  • B2*12：総返済回数（10年 × 12か月 = 120回）
  • B3：元本1,200万円

この数式の結果は 約 −23,000円 になります。マイナスは「支払い（キャッシュアウト）」を意味する符号ルールです。詳しい理由は後述の「なぜマイナスになる？」セクションで解説します。

プラス表示にしたい場合は ABS 関数で囲みます。

=ABS(ISPMT(B1/12, 5, B2*12, B3))
→ 23,000（プラス表示）

給与明細やローン明細など、ユーザー向けの表示では ABS で整えるのが一般的です。

ISPMT関数の計算ロジック｜内部式を理解する

ISPMT関数は、内部的に次の計算式で利息を求めています。

利息 = −元本 × 利率 × (1 − 期 / 期間数)

元金均等返済では「残高が均等に減少する」ため、利息も直線的に減少します。第1回は元本全額に近い残高にかかる利息で最大、最終回は残高ほぼゼロで利息は0円になります。

先ほどの条件で第5回利息を手計算するとこうなります。

利息 = −12,000,000 × (0.024 / 12) × (1 − 5 / 120)
     = −12,000,000 × 0.002 × 0.9583...
     ≈ −23,000円

第1回の利息計算に注意
「第1回の利息は、まだ1回も返済していないので元本全額にかかるのでは？」と考える人もいます。しかしISPMTは「期末残高ベース」で計算する仕様です。第1回の利息は元本全額ではなく、第1回目の元金返済後の残高にかかります。実務での厳密な利息計算とは若干異なる場合があるため、銀行の返済予定表と突合する際は注意してください。

完全な返済スケジュール表を作る方法

実務でよく作るのが「全120回分の返済スケジュール表」です。元金・利息・返済額・残高の4列で構成します。ISPMTを使えば一気に展開できます。

スケジュール表の構成

A列に回数（1〜120）を入力し、B〜E列に次の数式を入れます（B$1〜B$3 に条件が入っている前提）。

列内容数式（A列が回数の場合・1行目）
B列元金返済額=$B$3/($B$2*12)
C列利息=ABS(ISPMT($B$1/12, A1, $B$2*12, $B$3))
D列返済額合計=B1+C1
E列残高=$B$3-$B$3/($B$212)A1

これを120行分オートフィルすると、月別の元金・利息・返済額・残高がそろった完全な返済予定表が完成します。

各列の特徴を補足します。

  • 元金返済額（B列）: 全期間一定（元本÷総回数）
  • 利息（C列）: 第1回が最大、最終回はほぼ0
  • 返済額合計（D列）: 第1回が最大、徐々に減少
  • 残高（E列）: 直線的に減少、最終回で0になる

この表をグラフ化すると、利息のなだらかな減少カーブと残高の直線減少が一目でわかります。社内プレゼンや稟議書に添付すると説得力が増しますよ。

元利均等（IPMT）と元金均等（ISPMT）の累計利息比較

「結局どっちが安いの？」という疑問は、累計利息を比較すれば答えられます。

同じ条件（1,200万円・年利2.4%・10年）での比較

項目元利均等（IPMT）元金均等（ISPMT）
月返済額一定（約112,469円）第1回が最大、徐々に減少
第1回利息約 −24,000円約 −24,000円
第60回利息約 −12,800円約 −12,000円
累計利息約 1,496,300円約 1,452,000円

このケースでは元金均等のほうが約44,300円利息が少なく済みます。借入期間や金利によって差額は変わります。ローン契約前に必ず両方の試算を作って比較しましょう。

累計利息をスプレッドシートで一気に出すには、SUMで合算します。

=SUMPRODUCT(ABS(ISPMT(B1/12, ROW(INDIRECT("1:"&B2*12)), B2*12, B3)))
→ 元金均等の累計利息

元金均等のデメリットも理解しておく
累計利息は少なくて済む反面、序盤の月返済額が大きくなります。事業のキャッシュフローが立ち上がる前の時期に返済負担が集中するため、開業直後・赤字フェーズの企業には向かないこともあります。

実務シナリオ｜設備投資ローン・運転資金融資の試算

シナリオ1: 設備投資ローン（1,500万円・年利1.8%・7年）

新しい製造機器の導入で1,500万円を元金均等で借りた場合、月返済額の内訳を試算します。

年利: 1.8% → B1
期間: 7年 → B2
元本: 15,000,000 → B3

毎月の元金返済額: =B3/(B2*12) → 178,571円（一定）
第1回利息: =ABS(ISPMT(B1/12, 1, B2*12, B3)) → 22,232円
第1回返済額合計: 178,571 + 22,232 = 200,803円

第84回（最終回）の利息は仕様上ほぼ0円になります。月返済額は第1回の約20万円から最終回の約17.8万円まで徐々に減っていきます。

シナリオ2: 運転資金融資（300万円・年利2.5%・3年）

短期の運転資金を元金均等で借りた場合の試算です。

年利: 2.5% → B1
期間: 3年 → B2
元本: 3,000,000 → B3

毎月の元金返済額: =B3/(B2*12) → 83,333円（一定）
第1回利息: =ABS(ISPMT(B1/12, 1, B2*12, B3)) → 6,076円
第18回利息: =ABS(ISPMT(B1/12, 18, B2*12, B3)) → 3,125円

期間が短い融資では、序盤と中盤の利息差が大きく出ます。資金繰り表に組み込む際は、月別に利息額を出して計上しましょう。

なぜマイナスになる？符号ルールと ABS 関数の使い方

ISPMTが返す値がマイナスになる理由は、Sheets/Excel の財務関数がキャッシュフロー方向で符号を決めているためです。

キャッシュフロー方向符号例
受取（手元に入る）＋借入元本、配当受取
支払（手元から出る）−利息支払い、元金返済、保険料

ISPMT関数では「現在価値（元本）」を正の値で入力すると、利息は「支払い」なのでマイナスで返ります。逆に元本を負の値で入力すると、利息はプラスで返ります。

符号ルールの2パターン

パターンA: 借り手目線（おすすめ）
=ISPMT(B1/12, 5, B2*12, B3)  ← B3は正
→ 利息は−（マイナス）

パターンB: 貸し手目線
=ISPMT(B1/12, 5, B2*12, -B3)  ← B3を反転
→ 利息は＋（プラス）

実務的にはパターンAの「元本を正、利息はマイナス」で統一し、表示時にABS関数で絶対値にするのが見やすくておすすめです。

=ABS(ISPMT(B1/12, 5, B2*12, B3))
→ プラス表示

会計ソフトとの突合や銀行の返済予定表と比較する際にABSで揃えておくと、ミスを減らせますよ。

ISPMT関数でよくあるエラーと対処法

#VALUE!エラー

引数に文字列や日付など、数値以外が入っているときに発生します。利率は数値（例: 0.024）または％（例: 2.4%）で入力してください。セル参照先に空欄や文字列が含まれていないかも確認しましょう。

#NUM!エラー

期間数が0以下、期が0以下のときに発生する可能性があります。1始まりの整数で、期間数の範囲内に収まっているか確認します。

計算結果が想定より大幅にずれる

ほぼ100%「年利を月利に変換し忘れ」が原因です。月払いのローンでは、利率には必ず年利÷12を入れます。

NG: =ISPMT(2.4%, 5, 120, 12000000)     ← 年利2.4%が月利として扱われる
OK: =ISPMT(2.4%/12, 5, 120, 12000000)  ← 月利0.2%で計算

IPMT/PPMTと結果が一致しない

これはエラーではなく正常です。ISPMT（元金均等）とIPMT/PPMT（元利均等）は返済方式が根本的に異なるため、結果が違って当然です。どちらの方式のローンかを契約書で確認してから関数を選んでください。

第1回利息が銀行の返済予定表と一致しない

銀行や貸金業者が使う実務的な利息計算は「期初残高ベース」「日割り計算」など独自ルールを使うことが多いです。ISPMT（期末残高ベース）と一致しないことがあります。概算用の試算ツールとして使い、契約上の正確な金額は必ず貸し手の返済予定表で確認してください。

ISPMT関数のよくある質問（FAQ）

Q1. ISPMTの結果がマイナスになるのはなぜ？

スプレッドシートの財務関数は「キャッシュフロー方向」で符号を決めるためです。元本を正の値で入力すると、利息は「支払い（マイナス）」として返ります。表示用にプラスにしたい場合は ABS 関数で絶対値にしてください。

Q2. 第1回の利息はなぜ元本全額×利率にならないの？

ISPMTは「期末残高ベース」で計算する仕様だからです。第1回終了時には1回分の元金が返済済みになっているため、その残高に対して利息がかかります。実務の銀行金利と若干ずれることがあるので、概算用と割り切って使うのがおすすめです。

Q3. 年利と月利、どちらを入れればいい？

支払いの周期に合わせます。月払いなら「年利÷12」、四半期払いなら「年利÷4」、年払いなら年利そのまま。期間数も同じ周期で揃えるのがポイントです（月払いなら年×12、四半期払いなら年×4）。

Q4. 最終回の利息が0円になるのは正しい？

正しいです。ISPMTの計算式 −元本 × 利率 × (1 − 期/期間数) で、期=期間数のときに (1 - 1) = 0 となり、利息は0円になります。実務上は1円〜数十円の調整利息が発生することもありますが、関数の仕様としては0円が正解です。

Q5. CUMIPMT のような累計利息を出すには？

ISPMT には累計版がないため、SUMで合算します。

=SUMPRODUCT(ABS(ISPMT(B1/12, ROW(INDIRECT("1:"&B2*12)), B2*12, B3)))

または返済スケジュール表のC列（利息）を =SUM(C1:C120) で集計してもOKです。

関連する財務関数まとめ

ISPMTと一緒に押さえておくと、ローンや投資の試算が一気に楽になります。

関数対応する返済方式できること
IPMT元利均等各回の利息
PPMT元利均等各回の元金
PMT元利均等毎期返済額
CUMIPMT元利均等累計利息
ISPMT元金均等各回の利息（本記事）
RATE共通利率を逆算
FV共通将来価値
RRI共通実効年率の逆算

元金均等返済の月々の返済額は「元金 + 利息」で計算します。元金は「元本 ÷ 総返済回数」で一定になるため、元利均等用の PMT 関数は使えません。PMT は元利均等専用です。元金均等のスケジュール表を作るときは、ISPMTで利息列を出して合算する方法が基本になります。

まとめ

スプレッドシートのISPMT関数は、元金均等返済における指定回の利息額を一発で求められる便利な財務関数です。

要点を整理します。

  • ISPMT関数は元金均等返済専用（元利均等はIPMT）
  • 書式は =ISPMT(利率, 期, 期間数, 現在価値) のシンプルな4引数
  • 年利の月払いなら「利率÷12・期間数×12」で揃える
  • 結果はマイナス符号で返るため、表示用にはABS関数で整える
  • 返済スケジュール表は「元金・利息・返済額・残高」の4列で構築できる
  • 累計利息はSUMPRODUCT＋ABS で合算

事業融資や設備投資ローンの試算、社内稟議の添付資料作成にISPMTを活用してみてください。元利均等（IPMT）との比較表を一緒に作ると、経営判断の材料として一段と説得力が増しますよ。

スポンサーリンク
タイトルとURLをコピーしました