住宅ローン計算に使える Google Spreadsheet 関数の使い方を毎回忘れてしまうのでメモ。
前提
利率: 利息の年率。 例) 0.65% (0.65% という意味)
年数: 完済まで年数。 例) 35 (35年ローン という意味)
借入額: 開始時の借入額。 例) 50000000 (5000万円 という意味)
何年目: 知りたい時点。 例) 10 (10年目時点 という意味)
元利均等
【元利均等】で一定期間に支払う【合計利息額】
=CUMIPMT(年利/12, 年数*12, 借入額, 1, 何年目*12, 0)
関数名の意味: Cumulative Interest Payment (累積利息の支払い)
例の場合
=CUMIPMT(0.65%/12, 35*12, 50000000, 1, 10*12, 0)
-2830968.769
10年目では合計で約283万円の利息を支払うことになる。
【元利均等】で一定期間に支払う元金の【合計返済額】
=CUMPRINC(年利/12, 年数*12, 借入額, 1, 何年目*12, 0)
関数名の意味: Cumulative Principal (累積元金)
例の場合
=CUMPRINC(0.65%/12, 35*12, 50000000, 1, 10*12, 0)
-13145159.08
10年目では合計で約1315万円の元金を返済することになる。
【元利均等】で【毎月の支払額】
=PMT(年利/12, 年数*12, 借入額)
関数名の意味: Payment (支払い)
例の場合
=PMT(0.65%/12, 35*12, 50000000)
-133134.40
毎月約13万3000円の支払いをすることになる。
元金均等
【元金均等】で一定期間に支払う【合計利息額】
※元金均等の場合、総額を求める関数がないので数式で対応する必要がある。
=借入額 * 年利 * 何年目 * ((何年目*12 - 1) / 2 / (年数*12) - 1)
例の場合
=50000000 * 0.65% * 10 * ((10*12 - 1) / 2 / (35*12) - 1)
-2789583.333
10年目では合計で約279万円の利息を支払うことになる。
【元金均等】で一定期間に支払う元金の【合計返済額】
=-借入額 / (年数*12) * (何年目*12)
例の場合
=-50000000 / (35*12) * (10*12)
-14285714.29
10年目では合計で約1429万円の元金を返済することになる。
【元金均等】で【毎月の支払額】
※元金均等では毎月の支払額が一定ではない。
=-借入額 / (年数*12) + ISPMT(年利/12, 何か月目 - 1, 年数*12, 借入額)
ISPMT関数の関数名の意味: 謎。Lotus 1-2-3 互換で存在しているらしい。
例の場合
1か月目:
=-50000000 / (35*12) + ISPMT(0.65%/12, 1 - 1, 35*12, 50000000)
-146130.9524
2か月目:
=-50000000 / (35*12) + ISPMT(0.65%/12, 2 - 1, 35*12, 50000000)
-146066.4683
10年目の最後の月の支払い:
=-50000000 / (35*12) + ISPMT(0.65%/12, 10*12 - 1, 35*12, 50000000)
-138457.3413
35年目の最後の月の支払い:
=-50000000 / (35*12) + ISPMT(0.65%/12, 35*12 - 1, 35*12, 50000000)
-119112.1032
毎月約14万6000～11万9000円の支払いをすることになる。