マイクロソフトで住宅ローン計算機を作成する方法

エクセルの最大の特長のひとつは、金利や月々の返済額など、住宅ローン関連の支出を計算できることです。エクセルの関数にあまり慣れていなくても、エクセルで電卓を作るのは簡単です。このチュートリアルでは、Microsoft Excelで住宅ローンの電卓と償却スケジュールを作成する方法をご紹介します。

方法1

住宅ローン電卓を作成する

1. 開く.お使いのコンピュータにExcelがインストールされていない場合は、OutlookのオンラインExcel拡張機能を代わりに使用することができます。最初に作成する必要があるかもしれません。
2. 空白のワークブック」を選択します。新しいExcel .NETが開きます。
3. カテゴリー」列を作成します。これは「A」列に入ります。そのためには、まず「A」列と「B」列の間にある区切り線をクリックし、少なくとも3つ分右にドラッグします。以下のカテゴリーのために合計8つのセルが必要です：

   • ローン金額
   • 年利率
   • ローン期間（年）
   • 年間の支払い回数
   • 総支払回数
   • 期間あたりの支払額
   • 支払総額
   • 金利コスト
4. を入力してください。これらは、「カテゴリー」欄の右側にある「B」欄に入力します。あなたの住宅ローンに適切な値を入力する必要があります。

   • ローン金額の値は、あなたが借りている総額です。
   • 年利率」の値は、毎年発生する利息の割合です。
   • Life Loan」の値は、ローンを完済するまでの年数です。
   • 年間お支払い回数」は、1年間に何回お支払いいただくかを表しています。
   • 総支払回数は、ライフ・ローン価値に年間支払回数を掛けたものです。
   • 期間ごとのお支払い額とは、1回あたりのお支払い額です。
   • 支払総額は、ローンの総費用をカバーします。
   • 金利コスト」は、ライフ・ローン期間中の金利コストの合計です。
5. 総支払回数を計算します。これは Life Loan Value に Payments Per Year を掛けたものなので、計算式は必要ありません。

   • 例えば、30年ローンで毎月1回支払う場合、ここに「360」と入力します。
6. .を計算します。 住宅ローンを毎月いくら支払わなければならないかを計算するには、以下の式を使用します："=-PMT(金利/年間支払額,総支払回数,ローン額,0)".

   • 提供されたスクリーンショットの場合、計算式は「-PMT(B6/B8,B9,B5,0)」です。 値が若干異なる場合は、適切なセル番号で入力してください。
   • PMTの前にマイナス記号を付けられるのは、PMTが支払額から差し引かれる金額を返すからです。
7. .総費用を計算する これを行うには、単純に「期間ごとの支払い」の値に「支払い総数」の値を掛けます。

   • 例えば、$600.00を360回支払う場合、ローンの総費用は$216.000となります。
8. 総費用を計算します。ここで必要なのは、上記で計算したローンの総費用から、最初のローン額を差し引くことです。これができたら、住宅ローン計算機は完成です。

方法2

支払いスケジュール（償却）を作成する

1. 住宅ローン計算テンプレートの右側に、支払いスケジュールのテンプレートを作成します。支払い予定表は、住宅ローン計算機を使って、月々いくら借りるか／いくら返済するかを正確に評価するものなので、これらは同じ文書に入れるべきです。以下のカテゴリーごとに別の欄が必要です：

   • 日付 - 問題の支払いが行われた日付。
   • 支払い（番号）-支払い回数のうちの支払い番号（例：「1」、「6」など）。
   • 支払額（ドル）- 支払総額。
   • 利子 - 支払総額のうち利子の金額。
   • 元金 - 支払総額のうち利息以外の金額（ローンの支払いなど）。
   • 追加支払い - 追加支払いの金額。
   • ローン - 支払い後に残るローンの金額。
2. 元のローン額を支払い予定額に加えます。これは "Loan "列の一番上にある最初の空のセルに入ります。
3. 支払い（番号）」列の最初の3つのセルを設定します。日付」欄には、ローンを組んだ日付と、月々の支払いを予定している最初の2つの日付（例：2005年2月1日、2005年3月1日、2005年4月1日）を入力します。支払い欄には、最初の3つの支払い番号（例：0、1、2）を入力します。
4. Fill "機能を使って、残りの "Payment "と "Date "を自動的に入力します。そのためには、以下の手順を実行する必要があります：

   • 支払い(番号)欄の最初の項目を選択します。
   • カーソルを下にドラッグし、支払回数に該当する数字（例：360）までハイライトします。0」から始めるので、「362」の行までドラッグダウンします。
   • エクセル・ページの右上にある「塗りつぶし」をクリックする。
   • 系列」を選択する。
   • 種類」の項目で「線形」がチェックされていることを確認する（日付列を作成するときは、「日付」がチェックされているはずである）。
   • OKをクリックする。
5. 支払い ($)」列の最初の空セルを選択する。
6. 期間あたりの支払額を入力します。期間あたりの支払額」の計算式は、以下の書式で入力された情報に基づきます：「期間あたりの支払額<ローン総額+(ローン総額*(年利率/年間支払回数)),期間あたりの支払額,ローン総額+(ローン総額*(年利率/年間支払回数)))」。

   • この計算式の前に"=IF "タグをつけて計算を完了させる必要があります。
   • 年利率」、「年間支払い回数」、「期間ごとの支払い回数」の値は、次のように記述する必要があります：文字$数字。例： $B$6
   • このスクリーンショットの場合、計算式は次のようになります："=IF($B$10
7. Enter ↵ を押す。これで、選択したセルに「期間ごとの支払い」数式が適用されます。

   • この列の後続のすべてのセルにこの数式を適用するには、先ほど使用した「フィル」機能を使用する必要があります。
8. 利息」列の最初の空のセルを選択します。
9. 利息の計算式を入力します。利息の計算式は、以下の情報を以下のフォーマットで入力します：「Total Loan*Annual Interest Rate/Number of Payments per Year（ローン総額*年利率/年間支払回数）」。

   • この計算式が機能するためには、"="記号を前に付ける必要があります。
   • 提供されたスクリーンショットでは、数式は次のようになります："=K8*$B$6/$B$8"（引用符なし）。
10. Enter ↵ を押します。選択したセルに Interest の数式が適用されます。

    • この列の後続のすべてのセルにこの数式を適用するには、先ほど使用した「フィル」機能を使用する必要があります。
11. Principal」列の最初の空のセルを選択する。
12. プリンシパル」数式を入力する。この数式で必要なことは、「支払額（ドル）」の値から「利息」の値を引くことだけです。

    • 例えば、「利息」セルがH8で「支払い（$）」セルがG8の場合、引用符なしで「=G8 - H8」と入力します。
13. Enter ↵ を押します。これで、選択したセルにPrincipalの数式が適用されます。

    • この列の後続のすべてのセルにこの数式を適用するには、先ほど使用した「フィル」機能を使用する必要があります。
14. Loan "列の最初の空のセルを選択する。これは、最初に借り入れたローン額の真下にあるはずです（例えば、この列の2番目のセル）。
15. Loan」の計算式を入力します。Loanの計算式は以下の通りです："Loan"-"Principal"-"Extra".

    • スクリーンショットの場合、"=K8-I8-J8 "と入力します。
16. Enter ↵ を押します。選択したセルにローン数式が適用されます。

    • この列の後続のすべてのセルにこの数式を適用するには、先ほど使用した「フィル」機能を使用する必要があります。
17. 塗りつぶし」機能を使って、計算式の列を完成させてください。 支払額はずっと同じになるはずです。 利息とローン額は減少し、元金の値は増加するはずです。
18. 支払いスケジュールを合計します。 表の一番下に、支払額、利息、元本を合計します。 これらの値を住宅ローン計算機と照合します。 これらの値が一致すれば、計算式は正しいことになります。

    • 元金は当初の借入額と正確に一致するはずです。
    • 支払額は、住宅ローン計算機で求めたローンの総費用と一致するはずです。
    • あなたの利息は、住宅ローン計算機から利息コストと一致する必要があります。

住宅ローン支払い計算機のサンプル

この記事は、CC BY-NC-SAの下で公開されている " How to Create a Mortgage Calculator With Microsoft Excel " を改変して作成しました。特に断りのない限り、CC BY-NC-SAの下で利用可能です。