Excel
spreadsheet

住宅ローン計算に使う Google Spreadsheet (Excel) 関数

住宅ローン計算に使える 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円の支払いをすることになる。