見出し画像
Photo by fukkuraborisat

GAS/ローン計算書（複利・元利均等償還）を簡単に作成

活用できる忘備録

事業資金や車のローンなどの返済計画や資金計画を立てる際に、いつもブラウザでググってから誰かが提供しているローン計算式を利用していたけど、ブラウザを行ったり来たり、試算した金額をその都度入力したりと手間だったのでスプレットシート上で簡単に管理したいと思い「GAS」を使ってプログラムを組んでみた！


このスクリプトをGoogleスプレッドシートに組み込むことで、メニューに「ローン計算」オプションが表示され、ユーザーが計算を実行できるようになります。

スクリプトエディタの使用:

  • Google スプレッドシートを開きます。

  • ツールバーの「拡張機能」をクリックし、「Apps Script」を選択します。

  • スクリプトエディタが開きますので、提供したコードをここに貼り付けます。コードを貼り付けた後、「保存」アイコンをクリックして、プロジェクトに名前をつけます。

利用の仕方として、スプレッドシートの

  • B1セルに元金

  • B2セルに年利率（パーセンテージ）

  • B3セルに返済期間（年）

を入力する必要があります。
A列にそれぞれタイトルを付けて管理しましょう。
計算結果は

  • B5セル（月々の返済額）

  • B6セル（総返済額）

  • B7セル（総利息額）

に表示されます。また、それぞれ表示形式を通貨やパーセント、年などの表記にすると分かりやすくなります。

表示例

プログラムコード

function calculateLoan() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var principal = sheet.getRange("B1").getValue(); // 元金（B1セル）
  var annualInterestRate = sheet.getRange("B2").getValue(); // 年利率（B2セル）
  var years = sheet.getRange("B3").getValue(); // 返済期間（年数）（B3セル）

  var numberOfPayments = years * 12; // 総返済回数（月数）
  var monthlyPayment;
  var totalPayment;
  var totalInterest;

  if (annualInterestRate === 0) {
    // 年利率が0％の場合
    monthlyPayment = principal / numberOfPayments;
    totalPayment = principal;
    totalInterest = 0;
  } else {
    // 年利率が0％以上の場合
    var monthlyInterestRate = annualInterestRate / 100 / 12;
    monthlyPayment = (principal * monthlyInterestRate) / (1 - Math.pow(1 + monthlyInterestRate, -numberOfPayments));
    totalPayment = monthlyPayment * numberOfPayments;
    totalInterest = totalPayment - principal;
  }

  // 結果をスプレッドシートのB列に出力
  sheet.getRange("B5").setValue(monthlyPayment);
  sheet.getRange("B6").setValue(totalPayment);
  sheet.getRange("B7").setValue(totalInterest);
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('ローン計算')
      .addItem('計算を実行', 'calculateLoan')
      .addToUi();
}

スクリプトの実行と承認:

  • スクリプトエディタで、関数 onOpen を選択し、「実行」ボタンをクリックします。

  • 最初の実行時には、Googleからスクリプトの実行を承認する必要があります。指示に従って承認してください。

上記を実行して「ローン計算」メニューが見当たらない場合、以下の手順を確認してみてください。

スプレッドシートの再読み込み:

  • スクリプトを保存して承認した後、スプレッドシートを再読み込みしてください。

  • スプレッドシートを再読み込みすると、通常はメニューバーに「ローン計算」という新しいメニュー項目が追加されます。

これらのステップを実行してもまだメニューが表示されない場合は、ブラウザの問題である可能性があります。異なるブラウザで試すか、ブラウザのキャッシュをクリアしてから再度試してみてください。

計算式仕様/複利

このスクリプトは ”複利で計算” されています。ローン計算の一般的な方法は複利をベースにしており、毎月の返済額は固定され、利息と元金の割合が時間とともに変化します。

複利計算では、返済期間全体を通じて利息が元金に再投資されると考えられます。つまり、利息が発生すると、その利息が次回の利息計算の基礎となる元金に追加されます。

提供したコードでは、以下の式を使用して月々の返済額を計算しています：

計算式

ここで、月利率は年利率を12で割ったものです。

一方、単利の場合、利息は元金に再投資されず、毎期の利息計算は常に初期の元金に基づきます。単利でのローン計算は一般的ではなく、特に住宅ローンや自動車ローンなどの長期ローンにおいてはほとんど使用されません。

返済方法/元金均等償還と元利均等償還

返済方法には元金均等償還（元金の償還額を均等にする方法）と元利均等償還（元金と利息を合わせた償還額を毎回均等にする方法）があります。
このコードは「元利均等償還」（元金と利息を合わせた償還額を毎回均等にする方法）で計算しています。元利均等償還では、ローンの返済期間全体を通じて、毎月の返済額（元金と利息の合計）が一定になるように計算されます。

元利均等償還の特徴は以下の通りです：

  • 返済の初期では、支払う金額の大部分が利息として計上され、元金の返済額は比較的少ない。

  • 返済が進むにつれて、利息の割合が減少し、元金の返済額が増加する。

  • 毎月の返済額（元金＋利息）は一定。

一方、「元金均等償還」では、元金の返済額が毎月一定であり、利息が減少するため、返済額も徐々に減少していきます。

頭金や据置期間を設定したパターン

ケースによって、頭金や返済までの据置期間を設定した資金計画をつくる場合は以下のコードを活用します。

  • 据置期間中の利息額を参考に出力

  • 据置期間中の利息は元金に加算し、返済がスタートした際に支払う

function calculateLoan() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var principal = sheet.getRange("B1").getValue(); // 元金
  var downPayment = sheet.getRange("B2").getValue(); // 頭金
  var annualInterestRate = sheet.getRange("B3").getValue(); // 年利率
  var years = sheet.getRange("B4").getValue(); // 返済期間（年数）
  var gracePeriod = sheet.getRange("B5").getValue(); // 据置期間（月数）

  var loanAmount = principal - downPayment; // 実際のローン額
  var monthlyInterestRate = annualInterestRate / 100 / 12;

  // 据置期間中の利息の蓄積
  var accumulatedInterest = 0;
  for (var i = 0; i < gracePeriod; i++) {
    accumulatedInterest += loanAmount * monthlyInterestRate;
  }
  loanAmount += accumulatedInterest; // 据置期間後の新たなローン額

  var numberOfPayments = years * 12 - gracePeriod; // 実際の返済回数
  var monthlyPayment;
  var totalPayment;
  var totalInterest;

  if (annualInterestRate === 0) {
    monthlyPayment = loanAmount / numberOfPayments;
    totalPayment = loanAmount;
    totalInterest = accumulatedInterest; // 頭金を除いた元金にかかった利息
  } else {
    monthlyPayment = (loanAmount * monthlyInterestRate) / (1 - Math.pow(1 + monthlyInterestRate, -numberOfPayments));
    totalPayment = monthlyPayment * numberOfPayments;
    totalInterest = totalPayment - loanAmount;
  }

  // 結果をスプレッドシートのB列に出力
  sheet.getRange("B6").setValue(accumulatedInterest); // 据置期間中の累積利息
  sheet.getRange("B7").setValue(monthlyPayment); // 月々の返済額
  sheet.getRange("B8").setValue(totalPayment); // 総返済額
  sheet.getRange("B9").setValue(totalInterest); // 総利息額
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('ローン計算')
      .addItem('計算を実行', 'calculateLoan')
      .addToUi();
}

このコードでは、

据置期間中の累積利息をB6セルに出力
月々の返済額（B7セル）
総返済額（B8セル）
総利息額（B9セル）

を計算し、それぞれの結果をスプレッドシートに表示します。

まとめ

うまく組み込んで資金計画を立てて良いお金の使い方をしてくれることを願っています！

この記事が気に入ったらサポートをしてみませんか？