PPMT・IPMT関数でExcelのローン元利内訳を制する｜返済回ごとの元金・利息算出とCUMIPMT・CUMPRINC累積計算の実務パターンとMOS試験対策

「毎月の返済額は一定なのに、なぜ返済初期は利息が多くて後になるほど元金が増えるのか？」「住宅ローン控除の申告に必要な年間利息の合計をExcelで計算したい」――こうした疑問に答えるのがPPMT関数・IPMT関数です。

PMT関数で求められる毎月の返済額は一定ですが、その内訳は元金（Principal）と利息（Interest）に分かれており、返済が進むにつれて構成比が変化します。PPMT関数はある特定回の元金部分を、IPMT関数は利息部分を個別に取り出せます。さらにCUMIPMT・CUMPRINC関数を使えば、任意期間分の累積利息・累積元金をまとめて計算できます。

本記事では、PPMT・IPMT関数の構文から、返済スケジュール表の自動作成・年間利息の集計・CUMIPMT・CUMPRINCによる累積計算・MOS Excel試験での出題ポイントまでを体系的に解説します。

PMT・PPMT・IPMTの関係：返済額の内訳が変わる仕組み

元利均等返済（毎月同額を返済する方式）では、毎月の返済総額は常に一定です。しかし内訳を見ると、最初は利息の割合が高く、回を重ねるごとに元金の割合が増えていきます。

この仕組みは、利息が毎月のローン残高に対して計算されるためです。返済初期は残高が大きいため利息も大きく、元金返済分が少ない。元金が減るにつれて残高が小さくなり、利息も小さくなって、その分だけ元金への充当が増えます。

' PMT（毎月の返済総額）= PPMT（元金部分）+ IPMT（利息部分）
' 第n回の返済額内訳
=PPMT(月利, n, 総回数, 借入額) + IPMT(月利, n, 総回数, 借入額) = PMT(月利, 総回数, 借入額)

PPMT・IPMTはいずれも返済（支出）を表すため、計算結果は負の値になります。実務では-PPMT(…)またはABS(PPMT(…))の形で正の値として表示することが多いです。

PPMT関数の構文と引数

PPMT関数は、指定した返済回の元金返済額を返します。

=PPMT(rate, per, nper, pv, [fv], [type])

引数	内容	必須
rate	1期間あたりの利率。年利3%・月払いなら 3%/12 または 0.03/12	必須
per	元金を求めたい返済回（1～nper の整数）	必須
nper	総返済回数。10年・月払いなら 10×12 = 120	必須
pv	現在価値（借入額）。正の値で指定する	必須
fv	将来価値。完済なら省略（既定値0）	省略可
type	0＝期末払い（既定）、1＝期首払い	省略可

IPMT関数の構文と引数

IPMT関数は、指定した返済回の利息支払額を返します。引数はPPMTと完全に同じです。

=IPMT(rate, per, nper, pv, [fv], [type])

引数	内容	必須
rate	1期間あたりの利率	必須
per	利息を求めたい返済回（1～nper の整数）	必須
nper	総返済回数	必須
pv	現在価値（借入額）	必須
fv	将来価値（省略可、既定値0）	省略可
type	0＝期末払い（既定）、1＝期首払い	省略可

基本計算例：元利の内訳がどう変化するか（想定例）

以下は「借入額3,000,000円・年利2.4%・返済期間60か月（5年）・月払い」の想定例です。月利は 2.4%÷12 = 0.2% = 0.002 です。

' 毎月の返済総額（参考）
=PMT(0.002, 60, 3000000)   ' → 約 -53,191 円

' 第1回の元金・利息
=PPMT(0.002, 1, 60, 3000000)  ' → 約 -47,191 円（元金）
=IPMT(0.002, 1, 60, 3000000)  ' → 約  -6,000 円（利息）

' 第30回の元金・利息
=PPMT(0.002, 30, 60, 3000000) ' → 約 -50,536 円（元金が増加）
=IPMT(0.002, 30, 60, 3000000) ' → 約  -2,655 円（利息が減少）

' 第60回（最終回）の元金・利息
=PPMT(0.002, 60, 60, 3000000) ' → 約 -53,083 円（ほぼ全額が元金）
=IPMT(0.002, 60, 60, 3000000) ' → 約    -108 円（利息は極わずか）

返済回	返済総額（円）	元金（PPMT）	利息（IPMT）	元金比率
第1回	53,191	47,191	6,000	約89%
第12回	53,191	48,296	4,895	約91%
第30回	53,191	50,536	2,655	約95%
第60回	53,191	53,083	108	約99.8%

返済が進むにつれて元金比率が上昇し、利息は急速に小さくなることが数字で確認できます。返済初期ほど利息負担が重い構造が、PPMT・IPMT関数を使うことで一目瞭然になります。

実務シナリオ1：PPMT・IPMTで返済スケジュール表を作成する

PPMT・IPMT関数を各行に入力するだけで、全期間の返済スケジュール表を自動生成できます。各行が1回の返済に対応し、以下のような列構成にするのが定番です。

ヘッダー行（1行目）：A列「返済回」／B列「返済総額」／C列「元金」／D列「利息」／E列「ローン残高」

' 設定セル（例: C2=借入額3000000 / C3=年利0.024 / C4=返済回数60）
' A5セルから下に 1,2,3…60 と入力（または =ROW()-4 等で自動番号）

' B5（返済総額）: 毎月一定
=ABS(PMT($C$3/12, $C$4, $C$2))

' C5（元金）: 返済回ごとに異なる
=-PPMT($C$3/12, A5, $C$4, $C$2)

' D5（利息）: 返済回ごとに異なる
=-IPMT($C$3/12, A5, $C$4, $C$2)

' E5（ローン残高）: 前残高から元金を引く
=E4 - C5   ' E4 の初期値は借入額 $C$2

' 確認式: 元金+利息=返済総額（端数が出る場合あり）
=C5 + D5 = B5  ' TRUE になるはず

テーブル機能と組み合わせると、返済回数を変更した瞬間に全行が自動更新されます。PPMT・IPMTの第2引数（per）にA列の行番号を参照させることで、数式を下にコピーするだけで全回数分が埋まります。

実務シナリオ2：住宅ローン控除で役立つ年間利息の集計

住宅ローン控除の計算では、各年の利息支払合計が参考情報として必要になる場面があります。IPMT関数とSUM関数・SUMPRODUCT関数を組み合わせると、任意の年の利息合計を1式で求められます。

' 1年目（第1回～第12回）の利息合計
' 方法1：SUMPRODUCT＋IPMT（配列対応）
=SUMPRODUCT(-IPMT(C3/12, ROW(INDIRECT("1:12")), C4, C2))

' 方法2：SUM の配列数式（Ctrl+Shift+Enter で確定）
{=SUM(-IPMT(C3/12, ROW(INDIRECT("1:12")), C4, C2))}

' 2年目（第13回～第24回）の利息合計
=SUMPRODUCT(-IPMT(C3/12, ROW(INDIRECT("13:24")), C4, C2))

ROW(INDIRECT("1:12"))は{1,2,3,…,12}という配列を生成します。IPMT関数がその配列を受け取り、各回の利息を配列として返すため、SUMPRODUCTでまとめて合計できます。スケジュール表を作らなくてもよいため、概算確認に便利な手法です。

CUMIPMT関数で累積利息を計算する

CUMIPMT関数は、指定した返済回の範囲における利息の合計（累積利息）を返します。IPMT関数をSUMPRODUCTでまとめる代わりに、よりシンプルな構文で同じ結果を得られます。

=CUMIPMT(rate, nper, pv, start_period, end_period, type)

引数	内容
rate	1期間あたりの利率
nper	総返済回数
pv	現在価値（借入額）
start_period	累積を開始する返済回（1以上）
end_period	累積を終了する返済回（start_period以上、nper以下）
type	0＝期末払い、1＝期首払い（省略不可）

CUMIPMTはPPMT・IPMTと同様、結果が負の値で返ります。正の値で使いたい場合は -CUMIPMT(…) と書きます。

' 全期間（第1回～第60回）の利息総額（想定例: 3,000,000円・月利0.002・60回）
=-CUMIPMT(0.002, 60, 3000000, 1, 60, 0)
' → 約 191,460 円（60か月間に支払う利息の総計）

' 1年目（第1回～第12回）の利息合計
=-CUMIPMT(0.002, 60, 3000000, 1, 12, 0)
' → 約 64,827 円

' 2年目（第13回～第24回）の利息合計
=-CUMIPMT(0.002, 60, 3000000, 13, 24, 0)
' → 約 51,855 円（残高が減った分だけ利息も減少）

CUMPRINC関数で累積元金を計算する

CUMPRINC関数は、指定した返済回の範囲における元金の合計（累積元金）を返します。引数の構造はCUMIPMTと同一です。

=CUMPRINC(rate, nper, pv, start_period, end_period, type)

' 全期間（第1回～第60回）の元金返済総額（想定例）
=-CUMPRINC(0.002, 60, 3000000, 1, 60, 0)
' → 3,000,000 円（完済時に借入額と一致する）

' 1年目に返済する元金の累計
=-CUMPRINC(0.002, 60, 3000000, 1, 12, 0)
' → 約 573,765 円

' 2年目に返済する元金の累計
=-CUMPRINC(0.002, 60, 3000000, 13, 24, 0)
' → 約 586,735 円（1年目より元金への充当が増えている）

CUMIPMT＋CUMPRINCの合計は、その期間の返済総額（PMT×回数）に一致します。両者を確認することで、返済期間のどの部分が利息負担のピークかを把握できます。

実務シナリオ3：繰り上げ返済前後の比較試算（想定例）

繰り上げ返済を検討する際、「今後支払う利息の総額がいくら変わるか」を数字で確認したいケースがあります。CUMIPMTを使うと、繰り上げ返済後の残りの返済回数に合わせた累積利息を即座に計算できます。

以下は想定例として考え方を示します。

• 現行プラン：月利0.002、残り48回、残高2,400,000円 → 残利息 = -CUMIPMT(0.002, 48, 2400000, 1, 48, 0)
• 100万円繰り上げ後プラン：月利0.002、残り48回、残高1,400,000円 → 残利息 = -CUMIPMT(0.002, 48, 1400000, 1, 48, 0)
• 利息削減効果 = 現行プランの残利息 – 繰り上げ後プランの残利息

実際の繰り上げ返済は返済期間の短縮か月々の返済額の減額かによっても計算が異なります。上記はあくまで概算の考え方として示した想定例であり、金融機関のシミュレーターとあわせて確認することを推奨します。

よくあるつまずきとエラー対処

PPMT・IPMT・CUMIPMT・CUMPRINCを使うときに発生しやすいミスと対処法をまとめます。

症状	原因	対処法
#NUM!エラー	CUMIPMTのstart_periodがend_periodより大きい、またはper引数が範囲外	start_period≦end_period、1≦per≦nper を確認
#VALUE!エラー	引数に文字列が混入している	rate・per・nper・pvがすべて数値か確認
結果が正の値でなく負の値になる	PPMT・IPMT・CUMIPMT・CUMPRINCは支出（負の値）を返す	先頭に「-」を付けるか ABS() で絶対値に変換
利率を年利のまま入力している	rateは1期間あたりの利率（月払いなら年利÷12）	=PPMT(年利/12, …) と除算を忘れずに
元金+利息が返済総額とわずかにずれる	浮動小数点の演算誤差	ROUND()で桁数を揃えるか、差を誤差として無視

PPMT・IPMTと他の財務関数との使い分け

関数	求めるもの	典型的な用途
PMT	毎期の返済総額	月々の返済額の確認
PPMT	特定回の元金額	返済スケジュール表の元金列
IPMT	特定回の利息額	返済スケジュール表の利息列・年間利息の集計
CUMPRINC	期間累積元金	数年間に返済した元金の合計
CUMIPMT	期間累積利息	数年間に支払った利息の合計・繰り上げ効果の試算
NPER	返済回数	完済までに何回かかるかを逆算
RATE	利率	実質年利を逆算

MOS試験での財務関数の出題ポイント

MOS Excel試験（特にエキスパートレベル・MO-211）では、財務関数を正確に入力する操作が出題されます。PPMT・IPMT・CUMIPMT・CUMPRINCについて、以下の点を確認しておきましょう。なお、出題範囲に含まれます。分野別の出題数・配点は公表されていません。

• rate引数は1期間あたりの利率：年利を直接入力せず、月払いなら年利÷12と書けること
• per引数は1から始まる整数：0や nper を超える値を指定するとエラーになる
• 結果が負の値になる理由を理解している：支出を負として扱うExcelの財務関数の慣習
• CUMIPMT・CUMPRINCのtype引数は省略不可：PPMT・IPMTと異なり、0か1の明示が必要
• PMT＝PPMT＋IPMT の関係を理解している：数式の整合性チェックに活用できる

MOS試験 財務関数（元利内訳）操作チェックリスト

確認ポイント	操作内容	難易度
PPMT関数の基本入力	=PPMT(月利, 返済回, 総回数, 借入額)を正しく入力できる	★★☆
IPMT関数の基本入力	=IPMT(月利, 返済回, 総回数, 借入額)を正しく入力できる	★★☆
rate引数の変換	年利を月利に変換（÷12）する計算式を引数内に書ける	★★☆
PPMT+IPMT=PMT の確認	2つの関数の合計が PMT と一致することを確認できる	★★☆
返済スケジュール表の作成	行ごとに per を変化させながら全期間の元金・利息列を自動入力できる	★★★
CUMIPMT関数の入力	=CUMIPMT(月利, 総回数, 借入額, 開始回, 終了回, 0)を正しく入力できる	★★☆
CUMPRINC関数の入力	=CUMPRINC(月利, 総回数, 借入額, 開始回, 終了回, 0)を正しく入力できる	★★☆
負の値を正で表示する処理	ABS()または先頭の「-」で正の値に変換できる	★☆☆

まとめ：PPMT・IPMTで返済内訳を見える化し、CUMIPMT・CUMPRINCで累積を把握する

本記事のポイントをまとめます。

• PPMT関数：毎月一定の返済額のうち「元金部分」を返済回ごとに取り出せる。結果は負の値で返るため、表示する際は-PPMT(…) または ABS() を使う
• IPMT関数：同様に「利息部分」を返済回ごとに取り出せる。引数はPPMTと同一
• PMT = PPMT + IPMT：どの回でも元金＋利息は返済総額に一致する。この関係を確認式として使うと数式ミスを検出しやすい
• 返済スケジュール表：per引数を行番号セルに参照させ、数式を下方向にコピーするだけで全期間分が自動生成できる
• CUMIPMT関数：任意の期間に支払う利息の合計を1式で計算できる。type引数（0か1）は省略不可
• CUMPRINC関数：任意の期間に返済する元金の合計を1式で計算できる。引数はCUMIPMTと同一
• よくあるミス：rateに年利をそのまま入れる（月払いは÷12が必要）・start_period=0で#NUM!が出る
• MOS試験では：PPMT・IPMT・CUMIPMT・CUMPRINCの構文と引数の意味、rate変換、type引数の扱いが出題の中心

PPMT・IPMT関数を使いこなすと、ローンの返済内訳をExcel上で完全に可視化できます。返済スケジュール表の自動生成から年間利息の集計まで、PMT関数だけでは得られない精度の高い財務分析が可能になります。CUMIPMT・CUMPRINCとあわせて習得することで、繰り上げ返済の試算や住宅ローン計画の精度を大きく高められます。