Googleスプレッドシートでローン返済シミュレーション表の作り方

この数式は、返済シミュレーション表にある「元金分」と「利息分」を合計して、実際に支払う総返済額を計算しています。

SUM関数：

指定された範囲のセル内にある数値を合計します。

C6:D：

C列（元金分）とD列（利息分）の6行目以降のデータを範囲として指定し、全ての返済期間（借入期間の月数）をカバーします。

この数式は、GoogleスプレッドシートのARRAYFORMULA（配列数式）を使って、借入期間に応じた返済表を自動生成しています。
それぞれの部分の役割は次のとおりです。

ARRAYFORMULA：

複数セルにわたって一気に計算結果を出力できる関数です。ドラッグコピーが不要になり、自動化に最適です。

SEQUENCE(C2*12)：

借入期間（年）を月数に変換し（例えば5年の場合60）、その数だけ1から順番に数値（月）を生成します。

毎月の返済額の列：

ROUND(SEQUENCE(C2*12)/SEQUENCE(C2*12)*PMT(B2/12,C2*12,-A2),0)

• 全期間に渡り同一の毎月返済額を表示するため、配列として返済額を算出しています。
• ROUND関数で整数に四捨五入しています。

元金分（返済額のうち元本に充てられる部分）の列：

IF(SEQUENCE(C2*12)<C2*12,
   ROUND(PPMT(B2/12,SEQUENCE(C2*12),C2*12,-A2),0),
   ROUND(A2 - SUM(ROUND(PPMT(B2/12,SEQUENCE(C2*12-1),C2*12,-A2),0)),0)
)

• PPMT関数を使って各月の元金返済分を計算しています。
• 最終月だけ、元本からそれまでの元金返済の累計額を引いて端数調整を行っています。

利息分（返済額のうち利息に充てられる部分）の列：

ROUND(IPMT(B2/12,SEQUENCE(C2*12),C2*12,-A2),0)

• IPMT関数を使って、各月の利息分を計算しています。

借入残高の列：

A2 - MMULT(
  IF(SEQUENCE(C2*12)>=TRANSPOSE(SEQUENCE(C2*12)),1,0),
  IF(SEQUENCE(C2*12)<C2*12,
     ROUND(PPMT(B2/12,SEQUENCE(C2*12),C2*12,-A2),0),
     ROUND(A2 - SUM(ROUND(PPMT(B2/12,SEQUENCE(C2*12-1),C2*12,-A2),0)),0))
)

• 「元本」から、それまでの「元金分」の合計を引くことで各月の借入残高を計算しています。
• これにより最終的に残高が0円になります。