１－⑤Excel関数による減価償却費計算の解説

１．フローチャートと減価償却費計算の解説
注）別途解説する改訂取得価額と連携して計算します。

①金型の使用開始月の処理
平成19年3月30日に公布された法人の減価償却制度に関する規定（法３１、令４８等）により、私の勤めていた会社では金型は12ヶ月の均等償却を行い、取得した月によらず1年後には簿価1円（ゼロにすると、金型の存在が帳簿から消えてしまうので1円を残す）にしていました。
金型の使用開始月だけは後の式で処理出来ないので、最初に判定して処理します。

②転換点を過ぎているかを判定
別のグループで計算している改訂取得価額の結果を使い、転換点を過ぎているかどうかをここで判定します。
転換点前では改訂取得価額の結果に数値は入っていませんのでNoとなり、転換点を過ぎていれば改訂取得価額の値が入っているのでYesとなります。

③均等償却最後の月の処理
転換点を過ぎている場合で、償却を行う最後の月かどうかを判定します。
均等償却なので、前月には1ヶ月分の簿価が残っており、これから前月で計算した償却費を引くと償却最後の月ではゼロになります。ここで1より小さい（1以下ではなく、1を含まない）にしているのが肝心です。次の月では1残っているのを判定する事になるからです。

④転換点を過ぎて均等償却中の場合
改訂取得価額から償却費を計算します。
前月簿価＞1の条件は、償却が全部終わった後で、償却費をゼロにするためです。

⑤転換点前である使用開始月の処理
取得価額×償却率÷12がその月の償却費となります。





⑥転換点前で1月の処理
1月は期首簿価（前月簿価）から計算します。







⑦転換点前の１月以外の処理
償却費は前の月と同じ値です












＜転換点と改訂取得価額について＞
この計算式を作成するにあたって、以下のＨＰと会社の経理システムのプログラム仕様書を参照しました。この解説だけで本が一冊出来そうなので、以下を参照して下さい。

正式には国税庁の以下のＨＰに解説されています。
http://www.nta.go.jp/taxanswer/shotoku/2106.htm

もっと詳しい平成19年4月1日以降に取得した減価償却資産の解説は国税庁の以下ＨＰに有ります
http://www.nta.go.jp/shiraberu/zeiho-kaishaku/joho-zeikaishaku/hojin/h19/genkaqa.pdf

＜償却費計算の切り換えポイント図＞
３年償却の治工具の例（治工具は種類により耐用年数が異なるが、ここでは３年のサンプル）
処理が切り換わるポイントの月のみ取り出すと以下の図の様になります。（クリックすると大きくなります）






２．実際の関数の解説
N6セルの式
=IF(($K5=1)*($B5=N$4),$D5/12,IF(N7>1,IF((M5-M6)<1,M5-1,N7*$K7/12*(M5>1)),IF($B5=N$4,$D5*$K5/12,IF(MONTH(N$4)=1,M5*$K5/12*(M5*$K5>=$D5*$K6),M6))))

長いので分解して考えることにします。上のフローチャートとマルの番号が一致しています。
①金型の使用開始月の処理
=IF(($K5=1)　　　*　　　　　　($B5　　　　　=N$4),　　　　　$D5/12,
          ↓　　　　　　↓　　　　　　　　↓　　　　　　　　↓　　　　　　　　　↓
    償却率　　　AND　　　使用開始年月　　求める月　　　取得価額（投資金額）÷12

*のAND条件について
Excelの括弧による入れ子は８段までしか出来ません。このため、論理関数のANDを使って条件を増やしています。償却率が１で、使用開始年月が求める月と等しい場合に（両方の条件を満足する場合）、取得価額÷12を計算します。

②転換点を過ぎているかの判定
IF(N7>1,
      ↓
改訂取得価額　　　改訂取得価額に１を超える値が入っている場合は転換点を過ぎている。


③均等償却最後の月の処理
IF((M5　　　-M6)<1,　　　　　　　M5-1,
       ↓　　　　　↓　　　　　　　　　　　↓
前月簿価　　前月償却費　　　　前月簿価－1で１を償却しないで残す
↓
償却最後の月の前月は、簿価が償却費１ヶ月分しか残っていない


④転換点を過ぎて均等償却中の場合
N7　　　　　　　　　　*$K7/12　　　　　　*(M5>1)),
  ↓　　　　　　　　　　　  ↓　　　　　　　　　　　↓
改訂取得価額　　×改訂償却率÷12　　追加のAND条件で、前月簿価が１を越える場合
（まだ償却中の場合）



⑤転換点前である使用開始月の処理
IF($B5　　　　　　　　=N$4,　　　　　　　　$D5*$K5/12,
      ↓　　　　　　　　　　　↓　　　　　　　　　　↓
使用開始年月　　　求める月　　　　　　取得価額×償却率÷12　


⑥転換点前で1月の処理
IF(MONTH(N$4)=1,
             ↓
日付から月のみを取り出す関数。１月の場合は、


M5　　　　　　　　　　　*$K5/12　　　　　*(M5*$K5　　　　　　　>=$D5*$K6),
   ↓　　　　　　　　　　　　　↓　　　　　　　　　↓　　　　　　　　　　　　　　↓
前月12月の簿価　　×償却率÷12　但し、前月簿価×償却率　　取得価額×保証率以上の場合
（期首簿価）　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（転換点を過ぎていない場合）


⑦転換点前の１月以外の処理
M6))))
  ↓
前月の償却費に同じ

ご自分で式を改造される場合は、最後の右括弧の数に注意して下さい。数が合わないとエラーになったり、変な値が出たりします。Excelの括弧は８段（入れ子８段）が限界ですが、その場合もこうやって分解して考えると間違いなく出来ます。

以上で減価償却費の計算式解説を終わります。分かり難い所はまた後で直すかもしれませんが、とりあえず公開します。

次回は改訂取得価額の計算（計算用の中間データで、均等償却に移行した時の値を記憶する）を解説します。

１－④Excel による簿価の計算解説

１．フローチャートと計算の仕組み解説

こんな簡単な処理で出来るのか？と思われるかもしれませんが、ある月の簿価を求めるのに必要な処理は三つです。

①使用開始年月と求める月が同じ時
使用開始の月から償却が始まりますから、取得価額からひと月分の償却費を引いたのがその月の簿価となります。



②その他（使用開始年月より後）
前月の簿価から今月の償却費を引いたのが今月の簿価になります。
前月簿価はExcelは既に計算していますので、結果を利用することが出来ます。（循環にはなりません）


③使用開始年月より前の月
②と同じ処理ですが、前月の簿価はゼロで、今月の償却費もゼロですから０－０＝０となります。










２．実際の関数の解説
N5セルの式
=IF($B5=N$4,$D5-N6,M5-N6)

これを分解して考えます
=IF($B5=N$4　　　　　　　　　　　　,$D5-N6,　　　　　　　　　　　　M5-N6)
もし検収時期（正確には　　　　　投資金額（取得価額）　　　　そうでなければ
使用開始時期）と求める　　　　　－今月（この場合　　　　　　前月（ﾀﾞﾐｰでﾌﾞﾗﾝｸ）簿価－償却費
月（この場合は１月）が同じ　　　は１月）の償却費　　　　　　　最初の月はM列がダミー
だったら　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　でブランクにする必要が
あります

N5セルを右側にずっとコピーしたので、O5セルの式は以下の様になります
=IF($B5=O$4,$D5-O6,N5-O6)

これも同上に分解して考えると
=IF($B5=O$4　　　　　　　　　　　　,$D5-O6,　　　　　　　　　　　　　N5-O6)
もし検収時期（正確には　　　　　投資金額（取得価額）　　　　そうでなければ

使用開始時期）と求める　　　　　－今月（この場合　　　　　　　前月(１月）簿価－償却費
月（この場合は２月）が同じ　　　は２月）の償却費　　　　　　　最初の月はM列がダミー
だったら　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　でブランクにする必要が
あります

この様に、$マークの付いた後の絶対アドレスはコピーしても変化しないが、$マークの無い相対アドレスはコピーした方向に＋１ずつ足されて変化する事で、求める月と前月が順番に足されて自動的に変化します。この様に、絶対アドレスと相対アドレスをうまく使う事で、一箇所だけ式を作りコピーする事で、あっという間に何年分でも式が出来てしまいます。

では、次回は減価償却費の計算にいきます。

１－③Excel関数による減価償却計算の全体フローチャート

Excel関数による減価償却計算の全体フローチャートを添付します。プログラムのプロではないのでフローチャートの正しい書き方ではないかもしれませんが、ご容赦下さい。
このブログではExcelファイルの添付は出来ないみたいなので、画面を貼り付けます。
そのままでは小さくて見えないと思いますが、クリックすると大きくなります。

大雑把には、三つの関数のグループ（簿価計算、償却費計算、改訂取得価額）が連携しながら計算を進めています。一つ一つ独立して計算すると、同じ計算が重複し非常に長い無駄の多い式になってしまいます。このため、他のグループの結果を貰って計算する仕組みです。

この様に計算結果を別の所で利用すると、ループになって「循環」という現象が起こる事があります。計算の処理が同じ所をぐるぐる回り始めて永久に終わらない現象です。この式を考えるにあたっては、循環との戦いでした。循環を避けるにはExcelがどの様な仕組みや順序で計算してくれているかを知る必要がありますが、素人に分かりやすく解説した資料はまだ見つかっていません。私はどうやってこの問題を解決したのか忘れてしまったのですが、電卓で表の計算をするプロセスがヒントになったかも知れません。

では、次回は具体的な関数と計算の解説に進みます。

１－②Excel関数で設備投資の減価償却費を計算

まずは関数のみで計算できる事を試してみよう（２）

第一弾は下の画像に様な表を作成します。３行を一塊で使用するので、間違えない様に緑、黄、灰と色分けしています。
Excelの画面をコピーしたものです。クリックすると大きなサイズになります。

＜計算の為に入力が必要な項目＞
Excelを起動し、計算に必要な式やデータを入力します。Ｍ列のダミーと書いてある列は何も入れないで下さい。
式はここに記載されているのをコピーしてExcelの同じ位置のセルにコピーするのが間違えなくて早いです。
一文字でも間違えるとエラーになったり正しく計算できません。

①カレンダーの入力

画面の様に、計算を始めたい年の年月カレンダーを”Ｎ４”セルから横に作ります。
今年から始める時は、2012/1と2012/2を手で２個所入力し、この二つを同時に選択したまま右に引っ張っていくと（選択したまま右にずらす）どんどん自動で月毎のカレンダーが出来ます。

②簿価計算式を"N5"セルに入力（以下の式をコピーして貼り付ける）

=IF($B5=N$4,$D5-N6,M5-N6)

③償却費計算式を"N6"セルに入力（以下の式をコピーして貼り付ける）

=IF(($K5=1)*($B5=N$4),$D5/12,IF(N7>1,IF((M5-M6)<1,M5-1,N7*$K7/12*(M5>1)),IF($B5=N$4,$D5*$K5/12,IF(MONTH(N$4)=1,M5*$K5/12*(M5*$K5>=$D5*$K6),M6))))

④改訂取得価額計算式を"N7"セルに入力（以下の式をコピーして貼り付ける）

=IF(($K5=1)*($B5=N$4),$D5,IF(MONTH(N$4)=1,IF(M7>1,M7,IF(M5*$K5<$D5*$K6,M5,M7)),M7))

式を入れ終わったら、"N5"から"N7"までを選択して横方向にコピーする

次に３行をまとめて必要なアイテム分下にコピーする（行のコピーが便利）

その他必要項目を入力（Ｅ列からＪ列は後で利用する為に空けてあります。不要な場合は式を入れてから列を削除すると、自動でその分の式のセル位置が修正されます）

⑤投資する固定資産の種類で決まる３つの値

型、治工具、機械装置等で異なる。また、同じ治工具とかでも、あなたの会社が税務署に届けてある耐用年数により異なったりする。（私の勤めていた会社では、事業毎に異なりました）
分からないときは、経理担当の人に確認して下さい。

・償却率   "K5"セル以下３行おきに

・保証率   "K6"セル以下３行おきに

・改訂償却率  "K7"セル以下３行おきに

設備投資計画の入力

・検収時期（実際には或る月だけで検収が全部あがるとは限りませんが、簡易的に検収月のピークを入力する様にします）。使用開始時期が検収時期と異なる時は、使用開始時期を入力します。

・投資金額
千円単位で入力します。表示は百万単位で表示する様に書式を設定します。（予算は百万単位で見やすくするためです。千円単位のままでも構いません）

投資金額を入れた途端に、右のカレンダーの下に計算結果が表示されます。
月毎の減価償却費の合計を求めるには３行毎の合計を求める関数式を作ります（後に解説）。
また、この表の下に続いて現有固定資産のデータを入れて加える事で、未来の全体の減価償却費を計算でき、これをグラフにすると分かりやすくなります。
更に、今作った表にフィルタをかけて計算したり、条件付で合計を求めることで、機種別の減価償却費グラフを作ったり色々な分析ができます。

では、次回からはここに出てくる関数や書式の設定について順次解説をしていきます。

１－①まずは関数のみで計算できる事を試してみよう

関数を貼り付けて、設備予算から将来の減価償却費を計算（投資時期と投資額から）してみよう。
ここで使用している関数の解説は後から行います。

こんな分析が出来ます。
・機種別の減価償却費（月別、年度別）→グラフで一目瞭然
・投資時期がずれた場合の影響（新製品開発が遅れた場合や、意図的にある年の投資を減らしたい）
・投資額を変えてシュミレーション（生産数が減って型→ＮＣＴに変更したら等）

１．式の概要

　・未来の月別減価償却費を簡易的に求める式です。（全資産対応）

　・定率法のみ対応で、定額法（建物、ソフトウェア等）には非対応です。

　・通常、型・治工具の検収時期は或る広がりをもったものになりますが、簡易的に

　　一点で全ての検収が上がるものとして計算します。

　　これにより、日程が変更になった場合でも、検収時期を入力し直すだけで新しい

　　月別償却費を瞬時に求める事ができます。

　・立上の時期と検収の広がりの中心の関係を事前に調べておく必要があります。

　・式を右側にコピーしていく事で、何年先でもExcelの限界まで計算できます。

・計算アイテムが増えた場合は、３行毎にコピーを繰り返せばアイテム追加が可能

　です。

必ず３行をペアでコピーや行削除を行うこと。（下部の償却合計は３行おきの

集計を行っている）

　・式の左側に挿入しても相対アドレスで自動修正されます。但し、例として挙げて

　　いるセル位置とずれてきます。

２．計算に必要なデータ

　・設備投資金額（償却率の異なる物は別々に必要）入力は千円単位で、表示は

　　百万円単位

　・検収時期（正しくは使用開始年月）日付関数で入力。例：2009/12

　・償却率

　・保証率（平成１９年度の税制改正から新たに導入された数値です）

　　耐用年数省令に定める「別表第八　平成１９年４月１日以後に取得をされた

　　減価償却資産の償却率、改定償却率及び保証率の表｝を参照

        リンクはここから┐

        　　　　　　　　↓

別表第八　平成十九年四月一日以後に取得をされた減価償却資産の償却率、改定償却率及び保証率の表

　・改訂償却率（金型も１を入力のこと）

　　転換点を過ぎて、均等償却費に切り換わった後の償却率です。

では、次回は実際にExcelに関数を貼り付けて減価償却費を計算してみましょう。

はじめに：仕事で活用していたExcel関数で出来たこと

＜Excelとの生活が始まった日＞
定年を１３年後に控えたある日、突然全く畑違いの工場の予算と実績の管理を行う事になりました。それも、新規の事業で海外も含むものです。また、新規事業に伴い終息する事業の固定資産や予算の管理も行う事になりました。

会社は割と大きかったですが、規模の小さな事業で、これらを一人で（アシスタントも付きましたが入れ替わりが激しく、教育に時間を割かれました）考えて行わなければなりませんでした。
一人で全部を任されるという事は、最終結果としては責任が重いですが、自分で途中のやり方を
自由に考えたり、どうしたら自動で処理できるか考えるきっかけになりました。

会社の標準ソフトであるExcelとの戦いが始まりました。せっかく入れたデータがどこかに行ってしまったり、思うとおりに動かず〆切が迫ってきたり。
今までプログラム作成を経験したのは、工場で回路や部品のチェック用に作成していたマシン語（マイコンのレジスタやポート等を２進数で直接操作する）と設計から貰うアセンブラのリスト解析位で、あとは趣味でベーシックでポケコンを動かしていたものでした。

でも、これらの経験がExcelの関数を使うのに役に立つ事になりました。Excelの関数はベーシックと共通性が有り、覚えるのが楽でした。しかし、関数が一番異なるのはループが出来ない事です。逆に言えばループを使わないので、非常に高速に処理をする事が出来ます。
＜今後の予定＞
以下の処理を全てExcelの関数のみで行ってきたので、その解説を順次おこなっていきたいと思います。
但し、現役を引退して３年を過ぎて、昔の資料を振り返りながらですのでのんびりマーペースでいきます。

１．設備予算から将来の減価償却費を計算（投資時期と投資額から）
・機種別の減価償却費を計算することができ、新製品の投資計画をシュミレーションできる
・上記１.の現有固定資産の減価償却費と合算する事で、将来の月毎の減価償却費を
シュミレーションできる。


２．固定資産台帳から将来の減価償却費と簿価を計算
・現有固定資産の将来の月毎の減価償却費と廃却予定の特別損失を計算できる。


３．サービス用のユニットや部品を作成する為の固定資産を構成部品のツリーから解析する
・ユニットとそれを構成する単部品とは部品番号が異なる為に、注意しないとサービスで必要な
ユニットの単部品を作る為の固定資産を誤って廃却してしまい、再作成が必要となって
しまいます。これを人出で解析するのは製品規模や流用製品が多くなるとほぼ不可能に
なります。この作業を関数でリアルタイムに行って、ある固定資産の廃却可否を判断できます。

減価償却の仕組みについては、私の愛読ＨＰ（国税庁）の以下リンクを参照

＜減価償却のあらまし＞
http://www.nta.go.jp/taxanswer/shotoku/2100.htm

次号に続く