その①：エクセルVBAからPythonを操作して投資利益率シミュレーションを行う

今回はこれまでの内容を総合して、いわば集大成のような形の部分まで仕上げていきます。複数回に分けますが、最初からVBAとPythonを確認したい方のために有料エリアですが、ファイルを添付しました。

エクセルVBAからPythonを操作してモンテカルロ法による投資シミュレーションをしてみよう

に挑戦していきます。

物価上昇率が２％だったら、半分ローンをして買った投資物件が儲かるか？

というシミュレーションができるようになります。

本マガジンは、エクセルVBA×Pythonで作業効率化を実施していく目的で書いているもので、１本目に目指すイメージを詳細に書いているので、まだの方はぜひ目を通して見てください。

では早速本題です。今回は定形のフォーマットを使って、VBAとPythonでより実ビジネスに直結していくシミュレーションを行っていきます。

１．投資効率のモデリングとは

まずは、今回実現する投資効率のモデリングを説明します。

対象資産の
①収入
②投資金額
③年間の推移
④結果いくら儲かるか
について、見積もっていくことを全体として、エクセルで実施していきます。

その中で、予測数値が入る部分について、Pythonと連動させてシミュレーションをPython上で実施し、エクセルに連携するということを行っていきます。それは①収入、②投資金額、④結果いくら儲かるかの３つの部分です。

①収入
物価上昇率を考慮したモデリングにするため、物価上昇率を一定で固定するのではなく、正規分布で実施したモデリング結果を利用する

②投資金額
発生するコストについては、状況に応じて異なるため、正規分布で実施したモデリング結果を利用する

④結果いくら儲かるか
最後にどれくらいの倍率で売約できるかという掛け目については状況に応じて異なるため、正規分布で実施したモデリング結果を利用する

この部分でPythonを利用していきます。このシミュレーションを行うことで以下のようなエクセルの動きを実施していきます。

投資した金額と将来のキャッシュフローを比較して、将来のキャッシュフローが大きいかどうかが判断できる状態にまでエクセルのレベルを引き上げる

ことを目標にします。

２．投資シミュレーションを行うためのエクセル解説

a. 投資関連情報

まずは投資関連情報について入力していきます。具体的には投資金額、手数料、税金、コストを確定情報として入力していきます。ちなみに、他に追加の項目があれば、追加することでも対応できます。

以下の例では、投資金額を１億円として想定してまずは大きな金額を入れます。また、手数料率を３％として、税金を５％としています。また、その他のコストは、100万円として入力して合計1億９百万円という状況としました。

税金について
日本の不動産の場合には、税金の中で消費税は建物にはかかり、土地にはかからないと言った事項や、不動産所得税と言った税金もありますので、詳細な金額を把握するにはいくつか、調べておく必要はありますが、今回はそのような情報は詳細にはせず、簡単に入力する方針としました。

b.銀行借入と自己資金情報
次に、その金額を銀行借入で支払うか、自己資金で支払うかの情報を入れていきます。例としては、自分の財産状態を考慮してそれを更新していく形なのですが、あまり自己資金がないため、9０％を銀行借入としています。実際、頭金として約２０万円を用意するということは難しい状況ではありますが。。

c. 資産情報

例としての条件としては２０２１年１月１日から購入し、年間のキャッシュインフローを400万で、残存期間を20年、物価上昇率を2.5%に設定しています。1億の物件で、年間400万円の賃料収入を得るという形になるとイメージいただけると考えやすいかもしれません。これをもっと上昇させるや下落させると言った判断は貸主側である自分にあるので、どこの土地に物件を持っているかをベースにして考えていきましょう。

d.売却時の条件

10年経過したら、売りたいと思っていて、そのときにいくらで売れるように価格を設定しておくかという情報をここでは入力していきます。
以下の例だと、10年後に、その時の年間賃料と物価上昇率に対して15年続くだろうとと見積で売りたいと考えたため、それを入力しています。

e. 借り入れ条件とその他コスト

最終のインプット情報としては借り入れ条件とその他コストを入力しています。借入金額については９０％を借り入れにしているため、１億の９０％分が借金として銀行から借り入れて、利率が0.5％にしています。これは、ご自身の信用状況に応じて銀行から通知があるものであるため、そこを参照して入力します。

他の情報としては、借り入れ期間15年として、投資期間の10年で半分まで返す計画としており、投資期間経過後残高を当初借り入れ金額の半分としています。

f.凡例と投資パフォーマンス

では、今回のエクセルと最後に凡例と投資パフォーマンスの部分をご紹介します。

投資利益は、次回解説するシートからデータを持ってきており、物価上昇率を含む投資金額回収金額と、借り入れ返済と利息支払金額の差から計算します。またその下にある内部収益率（IRR）は投資の効果を計測する指標です。

内部収益率は、投資資金をどれくらいの期間で回収できるかを考慮して上で、投資の効率性を測る指標です。同じ金額を回収できる投資でも、短い期間で利益を得られるほどIRRは高くなるため、投資効率を考慮した目安指標としてよく使われます。

上記は難しいものの、エクセルはそれを簡単に計算してくれます。=XIRR()という関数での計算が可能となるので、キャッシュインとキャッシュアウトのデータを紐つけて計算を実施しています。

３．全体まとめ

今回の全体のエクセルシートの見た目を以下で最後、ご紹介します。

ここから更に次のシートと連携させるため、エクセル自体の解説はこれで終わりではないのですが、最初のステップとして今日、ご紹介するのは以上です。

以下のセクションでは、有料エリアとなります。実際のエクセルとPythonコードを添付しますので、先に全部確認したいという方はご購入の上で動かしてみてください。なお、以下のファイルを実施する際には、マクロ有効化によるファイル形式として、monte_carlo()の関数や、restore()の関数をRunPythonで実行するため、ボタンへの登録が必要となります。詳しくは別途解説していくのですが、これまでの投稿の２つが参考になります。