シナリオマネージャーを使用してローン計算をモデル化する方法

シナリオマネージャーは、[データ]タブの[仮定の分析]セクションでゴールシークおよびデータテーブルと一緒にグループ化されます。非常に便利な他のツールとグループ化すると、ScenarioManagerも知っておくべき重要なツールであると信じることができます。ただし、その便利な名前と優れた会社にもかかわらず、Scenario Managerはその機能がかなり制限されており、名前が示すように役立ちます。したがって、専門の財務モデラーによって頻繁に使用されることはありません。

モデルの設定

シナリオマネージャーの使用方法を示すために、簡単なローン計算モデルに適用してみましょう。ローン計算の背後にある理論は非常に複雑ですが、幸いなことに、Excelはローンを非常に簡単に処理します。

以下に示す例では、金利とローン条件の変化に対する毎月の返済の感度をテストできる金利計算機が表示されます。次の手順を実行します：

ファイル0801.xlsxをダウンロードして開き、8-14というラベルの付いたタブを選択するか、ハードコードされた入力仮定を使用してモデルを設定します。

毎月のローン返済額を計算するためのPMT関数の設定。

セルB11に、= PMT（と入力し、Ctrl + Aを押します。

[関数の引数]ダイアログボックスが表示されます。

PMT機能には、次の入力が必要です。

• 利率：利率。
• Nper：ローンの全期間にわたる期間の数。
• Pv：ローンの現在価値（借入額）。
• Fv：ローン期間の終了時に残っている金額。（ほとんどの場合、ローン期間中に全額を返済したいので、これは空白のままにしておくことができます。）
• タイプ：支払いを期間の最初または最後に行うかどうか。（この演習では、これを空白のままにすることができます。）

[関数の引数]ダイアログボックスのフィールドをモデルの入力にリンクします。

PMT関数は、年間返済額を返します。毎月の返済額を計算するため、数式全体を12で除算するだけで済みますが、利息が複利であるため、数式内の各フィールドを12で除算する方が正確です。したがって、最初のフィールドのレートは月額レートに変換され、2番目のフィールドの期間数も月額レートに変換されます。

[OK]をクリックします。

式は= PMT（B7 / 12、B9 * 12、B5）です。

これは費用であるため、この関数は負の値を返します。ここでの目的のために、関数の前にマイナス記号を付けて正に変更します。

シナリオマネージャーの適用

これで、ScenarioManagerを使用していくつかのシナリオを追加できます。入力の変更が毎月の返済に与える影響を知りたい。次の手順を実行します：

[データ]タブのリボンの[予測]セクションで、[仮定の分析]アイコンをクリックし、ドロップダウンリストから[シナリオマネージャー]を選択します。

[シナリオマネージャー]ダイアログボックスが表示されます。

[追加]ボタンをクリックして、新しいシナリオを作成します。

[シナリオの追加]ダイアログボックスが表示されます。

シナリオマネージャーを使用してシナリオを構築します。

[シナリオ名]ボックスに最初のシナリオの名前を入力します（たとえば、シナリオ1）。

[セルの変更]ボックスに変数セルのセル参照を入力します。

各参照はコンマで区切ります（複数ある場合）が、スペースは使用しないでください。Ctrlキーを押しながらスプレッドシートの各セルをクリックして、参照をボックスに挿入することもできます。

[OK]をクリックします。

[シナリオ値]ダイアログボックスが表示され、既存の値（金利の場合は0.045、年の場合は25）が示されます。

[OK]をクリックして、これらの値をシナリオ1として受け入れます。

[追加]をクリックして、別のシナリオを追加します。

[シナリオの追加]ダイアログボックスが再び表示されます。

[シナリオ名]ボックスに2番目のシナリオの名前を入力します（たとえば、シナリオ2）。

[OK]をクリックします。

[シナリオ値]ダイアログボックスが再び表示されます。

このシナリオの変数の値を入力します（たとえば、利率の場合は0.05、年の場合は30）。

シナリオマネージャを使用してシナリオ値を入力します。

[OK]をクリックします。

[シナリオマネージャー]ダイアログボックスに戻ります。

追加のシナリオを作成するには、手順7〜9を再度実行します。

すべてのシナリオを作成したら、シナリオマネージャーを使用して、下部にある[表示]ボタンをクリックして各シナリオを表示できます。

シナリオを表示するために、入力が自動的に変更されます。

シナリオはシート固有です。つまり、シナリオは作成したシートにのみ存在します。したがって、作成したシナリオを探すときは、モデルで正しいシートを選択する必要があります。