元利均等返済方式の返済済み元本の計算−CUMPRINC関数

CUMIPMT関数を使うと、元利均等返済方式でローンを組んだときの、支払った利息の額を計算することができることをご紹介しました。

利息の累計ではなく、ローンの借り換えをする場合や、繰り上げ返済のシミュレーションをする場合など、ある一定の期間に元本はいくら返済したのか、返済済み元本の累計の計算が必要になることもあります。

そんな計算をする関数もありますのでご紹介しておきます。

CUMPRINC（CUMulative PRINCipal）関数を使うと、元利均等返済方式の返済済み元本の累計額を計算することができます。

CUMPRINC関数は、指定された貸付期間内で元金の累計額を計算してくれます。

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

具体的に上記の例では、
第1引数の利率には年利を月利にするために「$B$1/12」
第2引数の期間には1年回に12回返済するので「A3*12」
第3引数の現在価値には組んだローンの額なので「B3」
第4引数には返済開始10年後の返済元金の累計を求めたいので「1」
第5引数には返済開始10年後の返済元金の累計を求めたいので「10*12」
第6引数には期末払いとしているので「0」
を指定しています。

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

前回ご紹介した利息額の累計を計算するCUMIPMT関数の引数と同じ考えですから、CUMIPMT関数をご理解いただけていれば問題ないでしょう。

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

サンプルファイルの「Chikirin改」シートは、前回ご紹介したサンプルファイルの「Chikrin」シートに少し手を加え、今回ご紹介した方法で10年後の返済済み元本をE3:E8セルに、10年後の支払利息累計をCUMITMP関数を使ってF3:F8セルに計算してあります。

上記の計算の確認の意味も込めて、「償還表」シートの125行目にSUBTOTAL関数を使って、返済開始10後までの合計を計算してありますので合わせてご確認ください。

なお、今回ご紹介したCUMPRINC関数を使うと、元利均等返済の元金の累計を計算できるわけですが、元利均等返済の1回あたりの返済額を計算するにはPMT関数を使って、1回あたりの返済の中で元金部分がいくらなのかを計算するにはPPMT関数を使ってそれぞれ計算します。