Excel(エクセル):総平均法・移動平均法計算シートの作り方(仮想通貨用)

arm-wrestling IT

仮想通貨の確定申告でちょっと勉強する必要があり、勢い計算シートも作ってしまったので公開します。テストはしましたがなんか間違ってる可能性はあり、法的な正確性、妥当性も保証できません。使用は自己責任でお願い致します。
通貨ごとに別のシートを使用し、取得と売却のみ実装としてます。間に行を挿入すると式が崩れることがあるので、必要に応じて式を上からオートフィルするなどしてご対応ください。

スポンサーリンク

総平均法の計算

excel 移動平均法 総平均法1

総平均法の完成品のイメージはこのようになります。購入、売却を入力していくと、年度ごとの実現損益が自動で計算されます。枚数×単価は掛け算で求めます。

excel 移動平均法 総平均法2
このまま使う場合、J1セル(2019年の総購入枚数)には
=SumIfs(C:C,B:B,"",A:A,"<2020/1/1",A:A,">=2019/1/1")
と記述します。B列が「買」かつ2020年未満2019年以上の日付のC列(枚数)のみカウントするという式です。
excel 移動平均法 総平均法3
J2は枚数×単価を合計し、上のセルの総購入枚数で割ることで、平均購入額を算出しています。
式としては
=(SumIfs(E:E,B:B,"",A:A,"<2020/1/1",A:A,">=2019/1/1"))/J1
となります。
excel 移動平均法 総平均法4
売却枚数は、購入枚数の式をコピーして「買」を「売」にすればOK。よって
=SumIfs(C:C,B:B,"",A:A,"<2020/1/1",A:A,">=2019/1/1")
のようになります。
excel 移動平均法 総平均法5
総売却額は枚数×時価のE列をカウントしたいので、
=SumIfs(E:E,B:B,"",A:A,"<2020/1/1",A:A,">=2019/1/1")
となります。
excel 移動平均法 総平均法6
総売却額 -(総売却枚数×平均購入額)=その年の実現損益となります。よって、式としては
=J4-(J3*J2)
で求められます。
excel 移動平均法 総平均法7
持ち越し枚数は、最初の年は購入枚数-売却枚数、それ以降は昨年の持ち越し枚数⁺購入枚数-売却枚数で計算できます。
excel 移動平均法 総平均法9
翌年以降は、概ね式中の年度を左右1ずつ上げればそのまま使い回せますが、上記の通り持ち越し枚数を修正するのと、
excel 移動平均法 総平均法10
取得価額を修正する必要があります。式としては
=((J2*J6)+(SumIfs(E:E,B:B,"",A:A,"<2021/1/1",A:A,">=2020/1/1")))/(J8+J6)
のようになります。最初のJ2*J6は前年度の取得価額×持ち越し枚数で、持ち越した分をその年の取得価額にも使用するための調整です。

移動平均法の計算

excel 移動平均法 総平均法11

移動平均法はこんな感じです。左から行くと、

excel 移動平均法 総平均法12
このへんは普通に入力して、枚数×取引価格を掛け算で求めています。
excel 移動平均法 総平均法13
平均単価は最初だけ手打ちで入力し、
excel 移動平均法 総平均法14
その下からは式で求めます。5行目の場合、
=If($B5 = “買”,($E5+(F4*($I5-$C5)))/$I5,F4)
となり、「買」の時だけ((今購入した価格×購入数)⁺(以前の平均単価×元の所次数))÷合計所持枚数、という計算をして、その時その時の平均単価を算出しています。
excel 移動平均法 総平均法15
計算で使うので、平均単価×取引枚数も掛け算で求め、
excel 移動平均法 総平均法16
売却時のみ取引損益を個別に算出します。
5行目であれば、=IF(B5="",E5-G5,"")
のようになります。E5-G5は(枚数×取引額)-(枚数×平均取得単価)で、つまり売却差額を求めています。
excel 移動平均法 総平均法17
所有枚数は最初の行だけ手打ち。その後は買った時は足し算、売った時は引き算で、5行目なら
=If(B5 = "",I4+C5,I4-C5)
のようにしてここでは求めています。
excel 移動平均法 総平均法18
右に移ります。
excel 移動平均法 総平均法19
日付はMaxIfs関数で、
=MAXIFS(A:A,A:A,"<2020/1/1")
のように求め、
excel 移動平均法 総平均法20
それをVlookUpして最終日の平均単価を求めています(翌年の計算に使用)。が、最終日に複数回取引をすると間違った数値になるので、ここは手打ちでもいいかもしれません。
最後の取引だけ時間をつけて、12/31 23:00とかして最大値にする方法もあります。
excel 移動平均法 総平均法21
総購入額はその年の枚数×平均単価の合計なので、
=(SumIfs(G:G,B:B,"",A:A,"<2020/1/1",A:A,">=2019/1/1"))
excel 移動平均法 総平均法22
購入枚数は
=(SumIfs(C:C,B:B,"",A:A,"<2020/1/1",A:A,">=2019/1/1"))
のようにして求められます。
excel 移動平均法 総平均法23
実現損益は個別に出したのを合計すればいいので、
=(SumIfs(H:H,A:A,"<2020/1/1",A:A,">=2019/1/1"))
となります。
excel 移動平均法 総平均法24
翌年以降は年だけ増やせばそのまま使い回せます。

比較テスト

excel 移動平均法 総平均法25
少し値を変えて、2019年に200万円ほど購入し、2021年にまとめて手放すものとしてみます。
どの程度変わるかと思ったんですが、
excel 移動平均法 総平均法26
総平均法でこれぐらい、
excel 移動平均法 総平均法27
移動平均でこのぐらいで、ほぼ同一の値が出ます。
excel 移動平均法 総平均法28
ありがちなのはこのように、高値で売買した後安値で購入した場合、
excel 移動平均法 総平均法29
総平均法では未決済の買値も取得価額の平均に使われるので、実態は500万で買って400万で売ったのが平均300万で取得して400万で売った計算になり、100万円損失を出してるのに100万円利益を上げたことになります。
逆に言えば安値買いすることで、損失を翌年に繰り越すことが可能です。
excel 移動平均法 総平均法30
移動平均法ではその時その時の時価で清算するので、こういった事態は起こりにくいです。
やりたいことから方法を探すエクセル(Excel)操作・関数・VBA(マクロ)逆引きまとめ
逆引き(やりたいことから探す)Excel記事まとめ

コメント