イシバシランブログ

「エクセル関数」と「お金に関する情報」をメインテーマに書いています。

【エクセル】定率法の減価償却費計算を関数を組み合わせて算出してみた

先日、下図を用いてDB関数の使い方の記事を発信しました。

f:id:ishibashiran:20210414065733j:plain

 

以前の記事では、旧定率法の計算のみ解説しましたが、現時点の定率法は「200%定率法」という上記とは違う計算方法となっております。

今回は、200%定率法を計算する数式を作成しましたので、解説したいと思います。

 

 

200%定率法の減価償却費推移

以前の記事と同様に、取得額:600,000円・耐用年数5年の時の、減価償却費推移表は下図になります。

f:id:ishibashiran:20210416220648j:plain

 200%定率法の計算方法は、旧定率法と比較すると細かい箇所は変わっていますが、旧定率法の特徴であった最初に多くの減価償却費が発生し、徐々に減価償却費が少なくなる内容となっています。

 

200%定率法の減価償却費の算出数式

D4セルに入力する時の関数は下記になります。

=IF(D3="-","-",IFVALUE(LEFT(A4,1))>C4,"-",IF(LEFT(A4,2)="1年",B4*2/C4,IF(B4<=D3,B4-1,IF(D2=D3,D3,IF($B$4*$B$18>(B4*2/C4),B4*$B$17,B4*2/C4))))))

 

条件式1:昨年の減価償却費が計算対象外ならば、"-"と表示

違うならば条件式2に進む

条件式2減価償却費の年数が耐用年数を超えると、"-"と表示

違うならば条件式3に進む

条件式3減価償却費計算が1年目ならば、「今年の未償却残高×2÷耐用年数」の値を表示

違うならば条件式4に進む

条件式4:未償却残高が昨年の減価償却費以下ならば、「今年の未償却残高-1」の値を表示

違うならば条件式5に進む

条件式5:昨年と一昨年の減価償却費の値が同じならば、昨年の減価償却費を表示

違うならば条件式6に進む

条件式6:「1年目の未償却残高(取得額)×償却保証率」が「今年の未償却残高×2÷耐用年数」の値以上ならば、「今年の未償却残高×改定償却率」の値を表示

違うならば「今年の未償却残高×2÷耐用年数」の値を表示

D5セル以降も、この数式をコピー貼り付けして使います。

 

数式の解説

数式の意味を時系列で並べたら、下記の通りとなります。

条件式6で、耐用年数に応じて、減価償却費の計算方法を指示しています。

「今年の未償却残高×2÷耐用年数」が1年目から一定の年数まで行う計算となります。

この数式が第1段階の計算方法です。

一定の年数と表現している理由は、「1年目の未償却残高(取得額)×償却保証率」>=「今年の未償却残高×2÷耐用年数」になった年は、減価償却費を「今年の未償却残高×改定償却率」で算出するためです。

この数式が第2段階の計算方法です。

条件式5で、今年の未償却残高が第2段階の減価償却費以下の値になるまで、第2段階の計算で算出した減価償却費を使い続けます。

この数式が第3段階の計算方法です。

条件式4で、今年の未償却残高<=第3段階の減価償却費になったら、「今年の未償却残高-1」の値を減価償却費とします。

この数式が第4段階の計算方法であり、最後の計算となります。

条件式3については、耐用年数の最初の数字を読み取り、減価償却費計算をさせていることから、何もしないと10年や11年などを1年と数式が解釈するため、計算ミスを防ぐために優先順位が高い場所に設定しています。

条件式1条件式2については、減価償却費計算の対象外の年に計算をしないように指示をしています。

 

IF関数を使う時の注意点

IF関数の条件式は、最初の条件が優先順位が高く、後ろに行くほど優先順位が低くなります。

そのため、IF関数を作成する時は、優先順位が低い条件式から作成すると、処理の漏れを防ぎやすいです。

エクセル関数は、組み合わせ次第では、資料作成に掛かる工数を大幅に減らすことが出来ますので、上手く使いこなしていきましょう。