先日、下図を用いてDB関数の使い方の記事を発信しました。
前回の記事では、旧定率法の計算が全て出来ていなかったため、この点に対応した数式を作成しましたので、解説したいと思います。
旧定率法の減価償却費推移
前回の記事と同様に、取得額：600,000円・耐用年数5年の時の、減価償却費推移表は下図になります。
DB関数のみでは、耐用年数経過後の減価償却費の計算が非常に難しいです。
その理由は、旧定率法の計算が複雑であることが背景にあります。
(1)減価償却費の累計が取得額の90％に達するまでは、「未償却残高×償却率」でその年の減価償却費を算出
(2)減価償却費の累計が取得額の95％に達する年は、「期首帳簿価額-取得価額×5％」でその年の減価償却費を算出
(3)その次の年から残りの簿価を5年で均等償却(5年目だけ償却額を-1して簿価が1円になるようにする)
出展：国税庁『旧定額法と旧定率法による減価償却(平成19年3月31日以前に取得した場合)』
DB関数は、(1)は残存価額に正しい数値を入れることで算出が出来ますが、(2)と(3)は算出が出来ません。(出来るかもしれませんが、私は分かりませんでした。)
固定資産の減価償却が終わるまでの推移表を作成するためには、DB関数以外の関数を組み合わせて作る必要があります。
旧定率法の減価償却費の算出数式
①E4セルの場合
=DB(B4,C4,D4,1)
未償却残高：固定資産の減価償却が未完了の金額
残存価額：耐用年数を経た後の資産価格
耐用年数：固定資産を使える通常の年数
期間：減価償却費計算期間
※期間の後に何ヶ月分の計算をするか判定するために、月の入力もできますが、入力無しならば12ヶ月で計算するため、入力を省略しています。
E4セルに入力する数式は、前回の記事と同様の内容となります。
固定資産の減価償却は、2年以上行うものですので、1年目の減価償却費計算では、複雑な数式は組みませんでした。
②E5セルの場合
=IF(SUM($B$4,-SUM($E$4:E4))<=1,"-",IF(B5<=E4,E4-1,(IF(SUM($B$4,-SUM($E$4:E4))<$B$4*0.05,E4,IF($B$4*0.05=B5,B5/5,IF($B$4*0.95<=SUM($E$4:E4,DB(B5,C5,D5,1)),B5-$B$4*0.05,DB(B5,C5,D5,1)))))))
条件式1：取得額から昨年までの減価償却費累計を差し引いて1以下ならば、"-"と表示
違うならば条件式2に進む
条件式2：未償却残高が昨年の減価償却費以下であれば、昨年の減価償却費から1を差し引いた値を表示
違うならば条件式3に進む
条件式3：取得額から昨年までの減価償却費累計を差し引いた値が取得の5％未満ならば、昨年の減価償却費を表示
違うならば条件式4に進む
条件式4：今年の未償却残高が取得額の5％ならば、未償却残高を5で割った値を表示
違うならば条件式5に進む
条件式5：1年目から今年までの減価償却費の累計値が取得額の95％以下ならば、未償却残高から取得額の5％を差し引いた値を表示
違うならば、DB関数で計算される減価償却費の値を表示
E4セルに入力する数式は、前回の記事と違い、減価償却費の累計値を確認して、計算方法が変わる時に、新しい処理をするように指示した関数となります。
E5セル以降も、この数式をコピー貼り付けして使います。
数式の解説
②の数式の意味を時系列で並べたら、下記の通りとなります。
条件式5で、タイミングが来たら、減価償却費の計算を、最初の「未償却残高×償却率」から「未償却残高-取得額×5％」に、計算方法を変更するように指示をしています。
条件式4で、昨年「未償却残高-取得額×5％」で減価償却費の計算をしたら、「未償却残高÷5」に、計算方法を変更するように指示をしています。
条件式3で、条件式4の計算を始めて4年目までならば、昨年と同じ計算をするように指示をしています。
条件式2で、減価償却費の計算の最後の年になったら、4年目の数字から1を差し引いた値を減価償却費とするように指示をしています。
条件式1で、減価償却費の計算が終わったならば、翌年からは"-"と表示するように指示をしています。
IF関数を使う時の注意点
IF関数の条件式は、最初の条件が優先順位が高く、後ろに行くほど優先順位が低くなります。
そのため、IF関数を作成する時は、優先順位が低い条件式から作成すると、処理の漏れを防ぎやすいです。
エクセル関数は、組み合わせ次第では、資料作成に掛かる工数を大幅に減らすことが出来ますので、上手く使いこなしていきましょう。
