仮想通貨の確定申告でちょっと勉強する必要があり、勢い計算シートも作ってしまったので公開します。テストはしましたがなんか間違ってる可能性はあり、法的な正確性、妥当性も保証できません。使用は自己責任でお願い致します。
通貨ごとに別のシートを使用し、取得と売却のみ実装としてます。間に行を挿入すると式が崩れることがあるので、必要に応じて式を上からオートフィルするなどしてご対応ください。
総平均法の計算
![excel 移動平均法 総平均法1](https://sunagitsune.com/wp-content/uploads/2022/03/bb2fb8efe3a02d731afda92e59c9c650.png)
総平均法の完成品のイメージはこのようになります。購入、売却を入力していくと、年度ごとの実現損益が自動で計算されます。枚数×単価は掛け算で求めます。
![excel 移動平均法 総平均法2](https://sunagitsune.com/wp-content/uploads/2022/03/e5427583665ed4fc2cdf9c6651393cb3.png)
このまま使う場合、J1セル(2019年の総購入枚数)には
=SumIfs(C:C,B:B,
と記述します。B列が「買」かつ2020年未満2019年以上の日付のC列(枚数)のみカウントするという式です。
=SumIfs(C:C,B:B,
"
買"
,A:A,"
<2020/1/1"
,A:A,"
>=2019/1/1"
)と記述します。B列が「買」かつ2020年未満2019年以上の日付のC列(枚数)のみカウントするという式です。
![excel 移動平均法 総平均法3](https://sunagitsune.com/wp-content/uploads/2022/03/ad6da376d9235f19bf9f712cc452d3d5.png)
J2は枚数×単価を合計し、上のセルの総購入枚数で割ることで、平均購入額を算出しています。
式としては
=(SumIfs(E:E,B:B,
となります。
式としては
=(SumIfs(E:E,B:B,
"
買"
,A:A,"
<2020/1/1"
,A:A,"
>=2019/1/1"
))/J1となります。
![excel 移動平均法 総平均法4](https://sunagitsune.com/wp-content/uploads/2022/03/fe141fe0f477156a227b883b7794766c.png)
売却枚数は、購入枚数の式をコピーして「買」を「売」にすればOK。よって
=SumIfs(C:C,B:B,
のようになります。
=SumIfs(C:C,B:B,
"
売"
,A:A,"
<2020/1/1"
,A:A,"
>=2019/1/1"
)のようになります。
![excel 移動平均法 総平均法5](https://sunagitsune.com/wp-content/uploads/2022/03/6b1339641f15e10da3d9516386f68e4a.png)
総売却額は枚数×時価のE列をカウントしたいので、
=SumIfs(E:E,B:B,
となります。
=SumIfs(E:E,B:B,
"
売"
,A:A,"
<2020/1/1"
,A:A,"
>=2019/1/1"
)となります。
![excel 移動平均法 総平均法6](https://sunagitsune.com/wp-content/uploads/2022/03/4da38fd9162f76550ffec20ade51135b.png)
総売却額 -(総売却枚数×平均購入額)=その年の実現損益となります。よって、式としては
=J4-(J3*J2)
で求められます。
=J4-(J3*J2)
で求められます。
![excel 移動平均法 総平均法7](https://sunagitsune.com/wp-content/uploads/2022/03/30b280243880c5c4f067168c7852ede5.png)
持ち越し枚数は、最初の年は購入枚数-売却枚数、それ以降は昨年の持ち越し枚数⁺購入枚数-売却枚数で計算できます。
![excel 移動平均法 総平均法9](https://sunagitsune.com/wp-content/uploads/2022/03/0b8b912c21a0b154fbfd1570b8e54b98-500x270.png)
翌年以降は、概ね式中の年度を左右1ずつ上げればそのまま使い回せますが、上記の通り持ち越し枚数を修正するのと、
![excel 移動平均法 総平均法10](https://sunagitsune.com/wp-content/uploads/2022/03/6c57aa48879da1167ec33fb33944aeff-500x191.png)
取得価額を修正する必要があります。式としては
=((J2*J6)+(SumIfs(E:E,B:B,
のようになります。最初のJ2*J6は前年度の取得価額×持ち越し枚数で、持ち越した分をその年の取得価額にも使用するための調整です。
=((J2*J6)+(SumIfs(E:E,B:B,
"
買"
,A:A,"
<2021/1/1"
,A:A,"
>=2020/1/1"
)))/(J8+J6)のようになります。最初のJ2*J6は前年度の取得価額×持ち越し枚数で、持ち越した分をその年の取得価額にも使用するための調整です。
移動平均法の計算
![excel 移動平均法 総平均法11](https://sunagitsune.com/wp-content/uploads/2022/03/1dd07fbebaf21c080dcb2eede8e0c593.png)
移動平均法はこんな感じです。左から行くと、
![excel 移動平均法 総平均法12](https://sunagitsune.com/wp-content/uploads/2022/03/89f369a81e9e86df97b907d9947380d2.png)
このへんは普通に入力して、枚数×取引価格を掛け算で求めています。
![excel 移動平均法 総平均法13](https://sunagitsune.com/wp-content/uploads/2022/03/ab5fc1862eaa34a3c2d3521b6ac0fa4e.png)
平均単価は最初だけ手打ちで入力し、
![excel 移動平均法 総平均法14](https://sunagitsune.com/wp-content/uploads/2022/03/ea6d40b4384bf3932888becc3d0857e4.png)
その下からは式で求めます。5行目の場合、
=If($B5 = “買”,($E5+(F4*($I5-$C5)))/$I5,F4)
となり、「買」の時だけ((今購入した価格×購入数)⁺(以前の平均単価×元の所次数))÷合計所持枚数、という計算をして、その時その時の平均単価を算出しています。
=If($B5 = “買”,($E5+(F4*($I5-$C5)))/$I5,F4)
となり、「買」の時だけ((今購入した価格×購入数)⁺(以前の平均単価×元の所次数))÷合計所持枚数、という計算をして、その時その時の平均単価を算出しています。
![excel 移動平均法 総平均法15](https://sunagitsune.com/wp-content/uploads/2022/03/d1075660b4c8c5b507acbfd7fe27499c-500x238.png)
計算で使うので、平均単価×取引枚数も掛け算で求め、
![excel 移動平均法 総平均法16](https://sunagitsune.com/wp-content/uploads/2022/03/ff51c17285af0676f08385a3a7476b47-500x101.png)
売却時のみ取引損益を個別に算出します。
5行目であれば、=IF(B5=
のようになります。E5-G5は(枚数×取引額)-(枚数×平均取得単価)で、つまり売却差額を求めています。
5行目であれば、=IF(B5=
"
売"
,E5-G5,""
)のようになります。E5-G5は(枚数×取引額)-(枚数×平均取得単価)で、つまり売却差額を求めています。
![excel 移動平均法 総平均法17](https://sunagitsune.com/wp-content/uploads/2022/03/8b190a1416745f5c640d9da4932b4d67-500x84.png)
所有枚数は最初の行だけ手打ち。その後は買った時は足し算、売った時は引き算で、5行目なら
=If(B5 =
のようにしてここでは求めています。
=If(B5 =
"
買"
,I4+C5,I4-C5)のようにしてここでは求めています。
![excel 移動平均法 総平均法18](https://sunagitsune.com/wp-content/uploads/2022/03/50433d7c2ea641e56a4800f15c2fd022.png)
右に移ります。
![excel 移動平均法 総平均法19](https://sunagitsune.com/wp-content/uploads/2022/03/610d2cc72dd9e95295e49dbcf775b668-500x118.png)
![excel 移動平均法 総平均法20](https://sunagitsune.com/wp-content/uploads/2022/03/a5ed41661eb3ac66628d8d00d6561ec8.png)
それをVlookUpして最終日の平均単価を求めています(翌年の計算に使用)。が、最終日に複数回取引をすると間違った数値になるので、ここは手打ちでもいいかもしれません。
最後の取引だけ時間をつけて、12/31 23:00とかして最大値にする方法もあります。
最後の取引だけ時間をつけて、12/31 23:00とかして最大値にする方法もあります。
![excel 移動平均法 総平均法21](https://sunagitsune.com/wp-content/uploads/2022/03/9762e064999f4a74872bcab2fc531f14-500x101.png)
総購入額はその年の枚数×平均単価の合計なので、
=(SumIfs(G:G,B:B,
=(SumIfs(G:G,B:B,
"
買"
,A:A,"
<2020/1/1"
,A:A,"
>=2019/1/1"
))、![excel 移動平均法 総平均法22](https://sunagitsune.com/wp-content/uploads/2022/03/4c555958d5a35fa8ed34e604e8d00b3f-500x96.png)
購入枚数は
=(SumIfs(C:C,B:B,
のようにして求められます。
=(SumIfs(C:C,B:B,
"
買"
,A:A,"
<2020/1/1"
,A:A,"
>=2019/1/1"
))のようにして求められます。
![excel 移動平均法 総平均法23](https://sunagitsune.com/wp-content/uploads/2022/03/5376815311959a3319da18867eecfcd0-500x174.png)
実現損益は個別に出したのを合計すればいいので、
=(SumIfs(H:H,A:A,
となります。
=(SumIfs(H:H,A:A,
"
<2020/1/1"
,A:A,"
>=2019/1/1"
))となります。
![excel 移動平均法 総平均法24](https://sunagitsune.com/wp-content/uploads/2022/03/a6e6daa1d55570e7e28109cb9563f864-500x117.png)
翌年以降は年だけ増やせばそのまま使い回せます。
比較テスト
![excel 移動平均法 総平均法25](https://sunagitsune.com/wp-content/uploads/2022/03/b707e840a7d1718e4b3165105988a559.png)
少し値を変えて、2019年に200万円ほど購入し、2021年にまとめて手放すものとしてみます。
どの程度変わるかと思ったんですが、
どの程度変わるかと思ったんですが、
![excel 移動平均法 総平均法26](https://sunagitsune.com/wp-content/uploads/2022/03/ff62d731ad6616e1986f663660d56797.png)
総平均法でこれぐらい、
![excel 移動平均法 総平均法27](https://sunagitsune.com/wp-content/uploads/2022/03/499b7c68a074d12d7f4ef7b763c5bee8.png)
移動平均でこのぐらいで、ほぼ同一の値が出ます。
![excel 移動平均法 総平均法28](https://sunagitsune.com/wp-content/uploads/2022/03/322ed7cdd5745d245aafe2b07ca2fba5.png)
ありがちなのはこのように、高値で売買した後安値で購入した場合、
![excel 移動平均法 総平均法29](https://sunagitsune.com/wp-content/uploads/2022/03/a464a17326684171867d86eac5e76eed.png)
総平均法では未決済の買値も取得価額の平均に使われるので、実態は500万で買って400万で売ったのが平均300万で取得して400万で売った計算になり、100万円損失を出してるのに100万円利益を上げたことになります。
逆に言えば安値買いすることで、損失を翌年に繰り越すことが可能です。
逆に言えば安値買いすることで、損失を翌年に繰り越すことが可能です。
![excel 移動平均法 総平均法30](https://sunagitsune.com/wp-content/uploads/2022/03/356cf9f31c607607eeffbf8d5f69e2a9.png)
移動平均法ではその時その時の時価で清算するので、こういった事態は起こりにくいです。
![](https://sunagitsune.com/wp-content/uploads/2018/10/gui-2311261_1280.png)
やりたいことから方法を探すエクセル(Excel)操作・関数・VBA(マクロ)逆引きまとめ
逆引き(やりたいことから探す)Excel記事まとめ
コメント