元利均等返済の支払利息総額を計算するには−CUMIPMT関数

「そんじゃーね」の決め台詞で有名な、おちゃらけ社会派ブログ「Chikirinの日記」さんで、「儲け方講座（おちゃらけ編）」という興味深い記事が掲載されています。

その中に、金利2%で3,000万円の住宅ローンを組んだ場合に、ローンの年数に応じて、銀行の儲けがどう変化するのか、住宅ローンを組む側からすると支払う利息の総額がどう変化するのか、といったことが記述されています。

Chikirinさんの記事の中では単純に計算結果だけが記述されていますが、Excelで元利均等返済の支払利息の総額を計算する方法をご紹介しておきます。

もちろん、償還表を作成して利息部分だけを合計すれば、支払利息の総額は計算できます。

が、CUMIPMT（CUMulative Interest PayMenT between two periods）関数を利用すれば償還表を作成しなくても支払利息・金利総額は計算できます。

CUMIPMT関数は、指定した期間の貸付金に対して支払われる利息の累計を返す関数です。

第1引数に利率
第2引数に期間（ローンを何回で返済するのか）
第3引数に現在価値（組んだローンの元本額）
第4引数に開始期（利息累計を求める最初がいつか）
第5引数に終了期（利息累計を求める最後がいつか）
第6引数に支払期日（支払が期末なのか期首なのか）
をそれぞれ指定します。

具体的に今回の場合は、
第1引数の利率には年利2%を月利にするために「$B$1/12」
第2引数の期間には35年で1年回に12回返済するので「A3*12」
第3引数の現在価値には組んだローンの額なので「B3」
第4引数には支払利息の総額を求めたいので「1」
第5引数には支払利息の総額を求めたいので「A3*12」
第6引数にはChikiriさんの記事で期末払いの例が計算されてるので「0」
を指定しています。

Excelの財務関数はマイナスの値を返すのが基本なので、プラスの値にするために「=」の直後、関数名「CUMIPMT」の直前に「-」を付加しています。

第1引数・第2引数・第3引数・第6引数については、Excelの財務関数をご存じの方であれば問題ないでしょう。（Excelの財務関数を一度も使ったことがないという方は、複利計算を行うFV関数あたりからちょっと勉強してください。）

第4引数・第5引数は、利息の累計を求める期間を指定する部分で、例えば1年目の12回分の支払い利息額累計を求めるのなら第4引数に「1」第5引数に「12」と指定します。2年目の12回分を求めたいのなら第4引数に「13」第5引数に「24」と指定します。

今回は利息の総額を求めたいので、第4引数に「1」第5引数に「A3*12」を指定しています。

▼サンプルファイル（003669.xls 267KByte）ダウンロード

サンプルファイルの「Chikirin」シートに上記の例を含んだ、「儲け方講座（おちゃらけ編）」と同じような表を作成してあります。

比較のために、「償還表」シートにはPMT関数を使った償還表を作成して、1回ごとの返済額のうち利息分がいくらなのかと、425行目にその合計を求めることで利息総額や、返済総額などを計算してあります。（ちなみに「償還表」シートはPMT関数を利用する方法で作成してあります。）

償還表をあまり見慣れていないという方は、1回目の返済額を計算してある4行目を見て、35年ローンだと「99379」円（C4セル）返済してる（銀行に払う）のに、そのうち半分以上が利息（D4セル）だということあたりもビックリしていただくといいんじゃないでしょうか。

2003までのExcelをお使いの方で「Chikirin」シートでエラーが出たという方は、分析ツールアドインを組み込んでください。

そんじゃーね！