Excel：ローン計算〜途中で金利が変わる（当初固定金利等）場合（CUMPRINC関数）

住宅ローンでは、「当初●年は○％、それ以降は○％…」という条件で返済されることが多くあります。

この場合の月々の返済額の計算法は以下の通りです。(※ 例：当初固定3年）

さて、1はPMT関数で出せばいいだけですが（PMT関数は元利均等返済なので、期間内なら何年分でも返済金額は一定）、2は3年分の元金を出す為にPPMT関数で1〜○回分の元金を出して合計しそれを借入金から引いた残高に対してPMT関数を使う方も多いと思います。

ただ、PPMT関数で3年分の元金を出すのは時間がかかります。（支払回ごとに元金を返す関数なので、合計して3年分（36回分）の元金を出さないといけない）

そこでCUMPRINC関数を使えば、簡単に3年分（36回分）の元金の合計を出すことができます。

■ CUMPRINC関数…返済期間中、○回〜○回返済分の元金の合計を返す
（例えば全30回返済のローンで、1回〜10回分の元金返済額の合計を返す）

• CUMPRINC (利率※1 , 期間※2 , 現在価値※3 , 開始期※4, 終了期※5 ,支払期日※6）

• 利率※1 …期間（返済回数）のうち の1回あたりの利率を入力します。 月々返済するのであれば1回＝1か月であり、利率 を月単位にする必要があります。（例えば、年利が2%ローンなら、2%÷12＝0.16667%ということになります。）

• 期間※2 …返済回数を入力します。30年ローンを月々返済するのであれば回数は30×12＝360回です。

• 現在価値※3 …借入金を入力します。

• 開始期※4 …対象の返済回数のうち、最初の期を返します。（1回〜10回なら1）

• 終了期※5 …対象の返済回数のうち、最後の期を返します。（1回〜10回なら10）

• 支払期日※6 … 支払日が月初めの場合は「1」、月末の場合は「0」とします。
  （注！支払期日はPMT、PPMT、IPMTで省略できましたが、CUMPRINCでは省略できません。）

■ 例： 金利変更後（4年目以降）の月々の返済額は？

	A	B
1	借入金	30,000,000円
2	年利（当初3年）	1.95%
3	年利（4年目以降）	2.5%
4	返済回数（年）	30年

　

◆ まずは当初3年間(1回〜36回）で支払った元金を出す ◆

• CUMPRINC ( B2/12 , B4*12, B1,  1, 12*3 , 0 ) →  -- 2,273,959円
  　　　　　　　　 　　利率,　返済回数,　借入金,　初回,　最終回,  返済日

これを借入金から引くので、30,000,000-2,273,959＝27,726,041（借入残高）

　

◆ 借入残高に対し、4年目以降の月返済額を返す ◆

• PMT (B3/12 , (B4*12)-(3*12) , 27,726,041 , 0 ) → -117,766 円

※ もし、1つの数式で金利変更後（4年目以降）の月返済額を出す場合は、

• PMT (B3/12 , (B4*12)-(3*12) , B1+CUMPRINC(B2/12 , B4*12 , B1, 1, 12*3 ,0) , 0 )

となります。