喜ばしい人生を

幸せな人生は、健康、所属愛、社会的地位、経済的時間的自由、趣味の楽しみから生まれてくる

【スポンサーリンク】

資産を管理する上での商品・通貨別管理の方法 エクセル活用法

 最近は、ブログの運営方法についての記事ばかりを書いていたので、ちょっとブログ運営話は離れて、久しぶりに資産運用について書きたいと思います。

 資産運用といっても、今回は僕が資産を管理している方法をご紹介したいと思います。 

f:id:So_delight:20170920195002p:plain

 

 

1.はじめに

 当初、自分の資産が、どの商品に、どの通貨で、どのぐらいあるのかを把握できない状態になっていました。そこで、資産を把握するためのソフトやアプリを探していたのですが、全体の資産を把握できるソフトが見当たらなく、結果的には僕が満足いくものが無かったのです。

 そこで、「では、自分で簡単に作ってみよう」と思い、エクセルで作りました。もちろん、アプリとかであれば、自動で口座残高を更新するなど、優れたものが多いと思いますが、今回の一番のポイントは、全財産の把握です。月単位ですべての商品に対して現財産の推移が把握できれば良いと思っています。同じような方は是非一度参考にしてみて下さい。

 

2.商品種類と通貨シートの作成(マスターシート)

 最初にマスターシートを作成しましょう。商品の種類と、通貨の表を作成します。実際にご自身が持っている商品だけで良いと思いますが、種類や通貨が増えたときに対応できるよう20ぐらいは表を作っておきましょう。参考画像は以下です。

f:id:So_delight:20170920165020p:plain

 

2.1 通貨

 通貨は、ご自身が持っている通貨のみでも結構ですが、円、ドル、豪ドル、ユーロ、ポンドなど主要な通貨は載せておいても良いと思います。ポイントとしては、通貨NOの欄を作り、通貨名、為替レートの3項目で作成します。後で、先ほどの商品リストに項目を選べるように関数を入れます。

 

2.2 商品の種類

 こちらも通貨と同じですが、商品NOと商品の2つの項目を一覧化しておきます。僕の場合は、商品を11項目に分けていますが、それぞれ必要な商品を入れてもらえれば結構です。例えば、保険の種類を細かくすることもできると思います。

 

3.一覧シートの作成

 次に、先ほどとは別のシートで、一覧シートを作成します。(シート名:商品リストとします)書き出す項目は、以下にまとめておきます。参考にエクセルの表もイメージで画像を載せておきます。

f:id:So_delight:20170920164209p:plain

 

3.1 項目

 項目数が多いですが、全ての商品に対応を考えるとこうなってしまいました。また、後述しますが、分からない項目などはそのまま空けておいても問題ありません。ポイントは、商品の種類と通貨の項目を別シート(後述します)から関数で引っ張ってきますので、商品NOと通貨NOの欄も一緒に作っておいてください。

 

  ・商品名(※):商品の実際の名前、通帳は銀行名など

  ・契約日時:保険や投信や債券など購入日が明確なもの

  ・商品の種類(※):上記2の商品の種類、後にエクセル関数を入力

  ・通貨(※):上記2の通貨、後にエクセル関数を入力

  ・口座番号:通帳など識別するための口座番号

  ・満期日:債券や保険や投信で満期がある商品。入力することで残存期間を表示

  ・更新日:時価を更新した時の日付

  ・金利:商品ごとの表面金利(後で見たときの参考資料)

  ・投資額(外貨と円):入力することで評価損益を表示

  ・購入手数料:評価損益で手数料を含めて計算表示

  ・基準価格(購入時と横に現基準価格):

    時価評価に現基準は必要、購入時の基準価格は、評価損益計算で必要となる

  ・数量(※):債券や投資信託や株などで数量があるものは必要

  ・為替レート(※)(購入時と横に現為替レート):外貨建ての場合は必要

  ・残高(外貨欄と円評価欄):実際の時価評価額の計算式で算出

  ・配当欄(外貨と円評価欄):累計配当を更新することで評価損益と利回りに利用

  ・損益:評価損益を数式で算出

  ・備考:忘れない様に必要項目を任意で入力

 

です。これをエクセルの項目として作成します。全体の画像では小さいので項目のみの画像を載せておきます。

f:id:So_delight:20170920163237p:plain

f:id:So_delight:20170920163246p:plain

 

3.2 商品と通貨列に数式の入力 VLOOKUP関数

 表が出来たら、次はマスターから商品と通貨の項目を引っ張ってくるように、以下の手順で、数式を入力します。

 ①セルD4を選択し、次の式を入力します。なお、IF文で空白時のエラー表記に文字列の””を入力するようにしています。

  =IF(C4<>"",VLOOKUP(C4,マスター!$E$4:$F$23,2),"")

 ②次に、この数式をD列下までコピー&ペーストして下さい。

 ③同様にセルF4に次の式を入力してください。

  =IF(E4<>"",VLOOKUP(E4,マスター!$A$4:$B$23,2),"")

 ④F列の下までコピー&ペーストして下さい。

f:id:So_delight:20170920181357p:plain

 

 ⑤為替の項目も同じように外貨建て商品の場合は必要になります。数式を同様にVLOOKUP関数で必要な場所に入れましょう。

 例えば、上記画像の4行の商品が仮に外貨建ての場合、時価評価額を出す上で、現為替が必要となります。S列の為替:現在の列に以下の式を入力します。

 =VLOOKUP(E4,マスター!$A$4:$C$23,3)

 

3.3 商品を入力

 実際に商品を入力していってください。通帳に関しては、銀行の口座ごとに普通、定期と分けてそれぞれ入力下さい。また、分からない項目や商品によっては関係のない項目は飛ばしていきましょう。商品によっては、上記項目説明で記載した必須項目は可能な限り拾えるようにすると、評価損益や配当・分配金の累計額なども明確になります。

 

3.4 評価額の数式

 評価額(残高)に関しては、商品の種類によって、手入力と計算式で算出するケースがあります。また、外貨建てについては、外貨を求めてから為替をかけて円換算した金額を表示するようにします。

 ・手入力:現金関係の普通預金、定期預金、MMFMRFは残高を手入力

 ・計算式:投信や株式や債券や保険などは現在の基準価格×数量

 ・保険は単純に満期時の金額でも良いですが(僕はそうしてます)、厳密に計算する場合は、各保険商品の経過月数による解約金などを引く計算を入れても良いです。

 ・不動産に関しては、土地は購入額を入力し、固定しておく。建物に関しては、簡単な経過年数によって減価償却した金額を入力してます。

 

3.5 配当に関して

 配当金に関しては、単純に累計配当金額を手入力し、円換算するために現為替レートを掛け合わせた金額を計算しています。

 

3.6 損益に関して

 これは、商品の購入金額から時価を引いて、配当金をプラスした金額で考えています。商品によってそれぞれ微妙に変わってきますので、都度必要な計算式を入れて下さい。

 

3.7 更新について

 一度、商品リストを完成させると、更新する項目は、最低限2項目です。

 ・現金関係は最終残高

 ・投信や債券や保険関係は基準価格

 なお、配当金については、総合計には関わってこないですが、商品毎の収益や利回りを見る時に必要になるので、必要であれば更新して下さい。

 

4. まとめシート

 上記した商品リストとマスターを元に、まとめの表を作成します。(シート名:まとめとします)商品の種類と通貨毎の表を作成します。

 画像のように立てに通貨、横に商品の種類の項目を作ります。参考に画像を貼っておきますので、同じように作成して下さい。

f:id:So_delight:20170920171735p:plain

 

4.1 エクセル関数で集計

 表の集計はエクセル関数を利用します。まずは上記画像のように、表の枠を作成して下さい。

 

4.1.1 各項目の集計 SUMIFS関数の利用

 ①各項目の集計は、「SUMIFS」関数を利用します。

 ②セルC3を選択してください。

 ③セルC3で次の式を入力してください

  =SUMIFS(商品リスト!$U$4:$U$59,商品リスト!$C$4:$C$59,まとめ!C$1,商品リスト!$E$4:$E$59,まとめ!$A3)

 ④次にセルC1~セルM7まで、上記式をコピー&ペーストすると、上の式の赤字の部分のみが自動的に変わり、全て集計してくれます。

f:id:So_delight:20170920173910p:plain

 ⑤これで、商品毎、通貨毎の集計が完了です。

 

4.1.2 各項目の合計 SUM関数の利用

 ・各項目(縦横共に)の合計は通常の「SUM」を利用して、集計して下さい。

f:id:So_delight:20170920174256p:plain

 

4.1.3 平均為替レート SUMIF関数の利用

 ①平均為替レート欄は、R1を選択の上、次の式を入力してください。

  =SUMIF(商品リスト!$E$4:$E$59,A3,商品リスト!$M$4:$M$59)/SUMIF(商品リスト!$E$4:$E$59,A3,商品リスト!$T$4:$T$59)

 ②セルR1に上記式を入力後、R1をコピーし、セルR2からセルR9までペーストして下さい。

f:id:So_delight:20170920174507p:plain

 

4.1.4 比率の計算式

 ①縦軸、横軸ともに、全体に対しての比率を計算します。

 ②セルC11を選択し、次の式を入力してください。

  =C10/$N10

 ③セルC11~セルM11までコピー&ペーストで式を入力してください。

 ④同様にセルO3を選択し、次の式を入力後、セルO9までペーストして下さい。

  =N3/N$10

f:id:So_delight:20170920174652p:plain

 以上で、数式の入力は終わりです。なお、比率の現状2と言う項目は、僕の場合は、現金としての比率を(普通、定期、MMFMRFなどの比率を足したもの)を出してます。同じく投資信託と株式を一つのグループにした比率も出しています。

 また、目標というのは全体のポートフォリオの比率です。ご自身が考えるバランスで数値を決めて手入力してください。

 

4.2 円グラフ作成

 次は円グラフを作成します。円グラフは比率として目標比率と実際の比率を比較できるグラフを作成します。商品比率と通貨比率として二つ用意しました。

f:id:So_delight:20170920180104p:plain

同様に、通貨比率も同じように作成します。

f:id:So_delight:20170920175810p:plain

 外側の円が目標比率で、内側が実際の比率です。これで、これからどの商品を購入するべきか、または購入しないべきかを判断しています。

 

 5 推移シート

 エクセルの別シートで推移シートを作成します。(シート名:推移)これは、単純にそれぞれ更新した時の日時と総合計の残高を入力していくだけです。更新したタイミング(頻度については、個人の必要に合わせて下さい)で、

 ・日付に更新日

 ・前回終了の金額を開始に(前回の終了金額)

 ・更新完了後の数字を終了(まとめシートの合計額)

に入れます。以下はサンプルの表です。

f:id:So_delight:20170920191515p:plain

 年間の増減率を損益累計から期初の金額で割ることで増加率が分かります。

 

6.まとめ

 今回は、エクセルの3シートを使って、自身の資産把握をするためのファイルを案内しました。最初作り上げるのに少し手間がかかりますが、一度作成すると更新する項目も決まってきますし、更新する日付が各商品違っても特に問題はないと思いますので、全体像を把握する上で、僕は非常に役に立っています。

 本当はエクセルのファイル自体を提供したいのですが、どの様にファイルを提供して良いのかが分からないため、今後、分かればアップします。

 また、個人的に欲しい人がいれば、コメントいただければ方法を考えます。