CUMIPMT 関数は、ローンの決まった期間に払う利息の合計を、CUMPRINC 関数は元金（借りたお金を返した分）の合計を求める関数です。1 年目の 12 か月分の利息なら =CUMIPMT(B3/12,B4*12,B2,1,12,0) と書きます。
「1 年でどれだけ利息を払うか」「5 年後までにどれだけ元金が減るか」が 1 つの式で分かります。この記事では、2 つの関数の使い方を、実際の Excel で確かめた結果で説明します。
CUMIPMT・CUMPRINC 関数の書き方
=CUMIPMT(利率, 回数, 借入額, 開始, 終了, 支払いの時期)
=CUMPRINC(利率, 回数, 借入額, 開始, 終了, 支払いの時期)| 引数 | 意味 |
|---|---|
| 利率 | 1 回あたりの利率（毎月なら 年利÷12） |
| 回数 | 返済の回数（毎月なら 年数×12） |
| 借入額 | 借りた金額（プラスで入れる） |
| 開始・終了 | 合計したい回の、はじめとおわり（1 年目なら 1 と 12） |
| 支払いの時期 | 0＝期末（ふつうのローン）、1＝期首。省略できない |
結果は払うお金なので、マイナスで返ります。プラスで見たいときは前に「-」を付けます。
1. 1 年目に払う利息の合計（CUMIPMT）
毎月返すので、利率は 年利÷12、回数は 年数×12 にします。1 年目は 1 回目から 12 回目です。
=CUMIPMT(B3/12,B4*12,B2,1,12,0)
結果は -43,079 でした。マイナスは払うお金という意味です。
2. 1 年目に返す元金の合計（CUMPRINC）
同じ引数で CUMPRINC にすると、元金の合計になります。
=CUMPRINC(B3/12,B4*12,B2,1,12,0)
結果は -280,170 でした。1 年目の返済のうち、元金がこれだけ減ります。
3. 1〜5 年目の利息を表にする
D2:D6 に 1〜5 を入れ、開始を (年目-1)*12+1、終了を 年目*12 にします。前に「-」を付けてプラスで表示します。
=-CUMIPMT($B$3/12,$B$4*12,$B$2,(D2-1)*12+1,D2*12,0)
1 年目は 43,079、5 年目は 25,765 でした。元金が減るので、利息は年ごとに少なくなります。
4. PMT の 12 か月分とくらべる
1 年目の利息と元金を足すと、1 年分の返済額になるはずです。
=CUMIPMT(B3/12,B4*12,B2,1,12,0)+CUMPRINC(B3/12,B4*12,B2,1,12,0)
結果は -323,249 で、=PMT(B3/12,B4*12,B2)*12 の -323,249 と同じでした。
【実機で検証】CUMIPMT でつまずくところ
| ためしたこと | 式 | 結果 |
|---|---|---|
| PMT の 12 か月分 | =PMT(CUMIPMT!B3/12,CUMIPMT!B4*12,CUMIPMT!B2)*12 | -323,249 |
| 年利を 12 で割り忘れた | =CUMIPMT(CUMIPMT!B3,CUMIPMT!B4*12,CUMIPMT!B2,1,12,0) | -530,571 |
| 開始を 0 にした | =CUMIPMT(CUMIPMT!B3/12,CUMIPMT!B4*12,CUMIPMT!B2,0,12,0) | #NUM! |
| 借入額をマイナスにした | =CUMIPMT(CUMIPMT!B3/12,CUMIPMT!B4*12,-CUMIPMT!B2,1,12,0) | #NUM! |
| 支払いの時期を 2 にした | =CUMIPMT(CUMIPMT!B3/12,CUMIPMT!B4*12,CUMIPMT!B2,1,12,2) | #NUM! |
| 10 年間の利息の合計 | =CUMIPMT(CUMIPMT!B3/12,CUMIPMT!B4*12,CUMIPMT!B2,1,120,0) | -232,494 |
年利を 12 で割り忘れると -530,571 になりました。月 1.5% として計算されるので、利息が大きくなりすぎます。
開始を 0 にすると #NUM!、借入額をマイナスにすると #NUM!、支払いの時期を 2 にすると #NUM! でした。開始は 1 から、借入額はプラスで入れます。
10 年間に払う利息の合計は -232,494 でした。
よくある質問
毎月の返済額を知りたいときは？
PMT 関数で出せます。
ある 1 回の利息と元金を知りたいときは？
1 回ずつなら IPMT・PPMT 関数を使います。
ボーナス払いがあるときは？
CUMIPMT・CUMPRINC は毎回同じ額の返済の計算です。ボーナス払いの分は別のローンとして計算して足します。
コメント