Excelでの計算ロジック 経済性工学 (その4)

更新日

投稿日

 

【経済性工学 連載目次】

 前回は、第3回 、経済性工学の原理原則でした。今回はExcelを使って経済性を計算してみます。

 

1. 正味現在価値法(NPV: Net Present Value)

 プロジェクトなどの投資案件では、耐用年数(n)の投資(I)がもたらす将来の正味キャッシュフロー(Net Cash Flow)を資本コスト(r)で割り引いた値の合計値と、投資額の合計との差額で評価します。ここでは計算を簡便とするために、無借金で金利が発生しないと仮定したフリー・キャッシュフロー(FCF)が用いられます。NPV法は将来の時間的価値を現在価値に換算しているため、現時点での客観的比較が可能となります。正確には次の式で表されますが、設備の残存価額の計算項目は省略される場合もあります。

  正味現在価値1

 ここで、基本的にはNPV>0ならば投資を行う判断となります。

 なお、一般的にはここまで計算しませんが、数年にわたって継続的に投資を行う場合の現在価値に対する投資総額は次の式で計算できます。

 正味現在価値2

 

2. 内部収益率法(IRR: Internal Rate of Return)

 耐用年数(n)の投資(I)がもたらす将来のフリー・キャッシュフロー(FCF)を、資本コスト(r)で割り引いた値の合計値と、投資額の合計値が等しいとした時の割引率(r)を、内部収益率(IRR)または投資利益率といいます。IRRはNPVと双子の関係にあり、投資の管理指標として優れています。次の式で示しエクセルを用いて計算します。

  内部収益率 

 一般的な投資判断としては、環境変化がほとんどない時は投資利益率が投資しようとしている企業の資本コスト以上ならば、投資を行う判断となります。通常は感度分析を行い、例えば資本コスト10%の企業では、20%や30%などとリスクを加味した数字を設定します。

 

3. 投資回収期間法(Payback)

 投資回収期間法は、投資金額が何年で回収できるかを評価する方法です。簡易的には投資額を年間のキャッシュフローで割った値で求められます。ここでは少し難解かもしれませんが、現在価値に換算する投資回収期間(n)を次式に表示しExcelを用いて計算してみます。利益よりも回収が早いほうが有利と判断すると、ほとんどの案は投資しないほうがよいとなってしまうため、特にリスクの高い案件の場合に投資回収期間法を用いるとよいでしょう。

   投資回収期間砲

 

4.フリー・キャッシュフローを用いた投資効果の計算例

 以上の考え方に基づき、Excelを用いて計算する例で初年度に投資する場合を図1、継続的に投資が発生する場合を図2に示しました。これらの例は、前述のIRRをROIと書き換えていますが考え方は同じです。実践的にするため、IRR、NPV、回収期間および許容投資額をお互いに逆算できるようにしてあります。また、計算式だけでなくキャッシュフローが目で追えて理解しやすいようにしたのも特徴です。

投資評価1

図1.初年度に投資を行う場合の計算例

投資評価2

図2.継続的に投資が発生する場合の計算例

 

 みなさんの理解の便宜を図るため、主要な項目についてExcelでの計算ロジックをできるかぎり公開し、簡単に説明しておきます。

① 投資額:I

 ここには、初年度の投資額を入力します。ここで注意すべき点は、既に投資済のコストは埋没原価といって、新しい投資案件評価の投資額には含めません。

② 効果金額

 直接および間接人員削減、材料費削減、エネルギー費削減、歩留向上費用、ランニングコスト削減などの効果金額を入力します。

③ 減価償却年数

 設備等の耐用年数のことを言います。従来は会計上の法定償却年数を用いていましたが、現在は、ビジネス的な実質的な寿命を設定して評価します。 また、IT産業のように設備投資サイクルの短い分野では、増加償却が認められています。その場合には、次式で計算します。

最終償却率=(1-0.1^(1/n))*(1+増加償却率)

 0.1:残存価額比率

 n:耐用年数(減価償却年数)

例えば、5年の18.7%増が4年、5年の45.3%増が3年の償却率に相当します。

④ 減価償却率

 定率法にて計算する時の比率を言い、次式で示します。

償却率=1-0.1^(1/n)

 0.1:残存価額比率

 n:耐用年数(減価償却年数)

減価償却率の計算は次の式で表し、本例の減価償却年数5年の場合、0.369と計算されました。Excelでは、POWER関数を用いています。

 B15=1-POWER(0.1、1/B14)

⑤ 減価償却費

 減価償却費は、減価償却年数と減価償却率から次式で計算できます。ただし、「$B14

 C6=IF($B14

⑥ Net cash flow

 Net cash flowは効果金額+減価償却費にて表します。

 C7=IF(C5<=0、""、SUM(C5:C6))

⑦ 税引き後純cash flow:FCF

 企業の法人税、事業税、固定資産税等税金の総計を実効税率と言い、約40%が税引き後純cash flowに相当します。

 C8=IF(C5<=0、""、C7*0.4)

⑧ cash flowの現在価値

 投資がもたらす税引き後純cash flow の価値を資本コストで割り引いた値で表します。Excelでの計算ではC9の式となります。

 Σ[FCF /(1+r)n ] FCF :税引き後純cash flow

 r:資本コスト

 n:期待する寿命

 C9=IF(C5<=0、""、C8/POWER(1+$B13、C3))

⑨ 累積cash flow

 cash flowの現在価値から投資額(K円)を引いた数式で表されます。

 C10=IF($B14

⑩ 正味現在価値 NPV(K円)

 EXCELでの計算ではNPV関数を用いるため、B16式になります。

 B16=NPV($B13、C8:I8)+B8

⑪ 税引き後利益率 ROI(内部利益率)

 EXCELでの計算ではIRR関数を用いるため、B17式になります。

 B17=IRR(B8:I8)

⑫ 投資回収年数:n

 簡易的には、投資額/利益でも求められますが、現在価値の考え方を盛り込み、より正確性を期すため次式を使います。

 n=log(FCF/FCF-r*I)/log(1+r)

 FCF:Free Cash flow

 I:投資額

 r:資本コスト

 Excelでの計算ではNPVおよびIRRと厳密には一致しないため、補正係数を加味...

 

【経済性工学 連載目次】

 前回は、第3回 、経済性工学の原理原則でした。今回はExcelを使って経済性を計算してみます。

 

1. 正味現在価値法(NPV: Net Present Value)

 プロジェクトなどの投資案件では、耐用年数(n)の投資(I)がもたらす将来の正味キャッシュフロー(Net Cash Flow)を資本コスト(r)で割り引いた値の合計値と、投資額の合計との差額で評価します。ここでは計算を簡便とするために、無借金で金利が発生しないと仮定したフリー・キャッシュフロー(FCF)が用いられます。NPV法は将来の時間的価値を現在価値に換算しているため、現時点での客観的比較が可能となります。正確には次の式で表されますが、設備の残存価額の計算項目は省略される場合もあります。

  正味現在価値1

 ここで、基本的にはNPV>0ならば投資を行う判断となります。

 なお、一般的にはここまで計算しませんが、数年にわたって継続的に投資を行う場合の現在価値に対する投資総額は次の式で計算できます。

 正味現在価値2

 

2. 内部収益率法(IRR: Internal Rate of Return)

 耐用年数(n)の投資(I)がもたらす将来のフリー・キャッシュフロー(FCF)を、資本コスト(r)で割り引いた値の合計値と、投資額の合計値が等しいとした時の割引率(r)を、内部収益率(IRR)または投資利益率といいます。IRRはNPVと双子の関係にあり、投資の管理指標として優れています。次の式で示しエクセルを用いて計算します。

  内部収益率 

 一般的な投資判断としては、環境変化がほとんどない時は投資利益率が投資しようとしている企業の資本コスト以上ならば、投資を行う判断となります。通常は感度分析を行い、例えば資本コスト10%の企業では、20%や30%などとリスクを加味した数字を設定します。

 

3. 投資回収期間法(Payback)

 投資回収期間法は、投資金額が何年で回収できるかを評価する方法です。簡易的には投資額を年間のキャッシュフローで割った値で求められます。ここでは少し難解かもしれませんが、現在価値に換算する投資回収期間(n)を次式に表示しExcelを用いて計算してみます。利益よりも回収が早いほうが有利と判断すると、ほとんどの案は投資しないほうがよいとなってしまうため、特にリスクの高い案件の場合に投資回収期間法を用いるとよいでしょう。

   投資回収期間砲

 

4.フリー・キャッシュフローを用いた投資効果の計算例

 以上の考え方に基づき、Excelを用いて計算する例で初年度に投資する場合を図1、継続的に投資が発生する場合を図2に示しました。これらの例は、前述のIRRをROIと書き換えていますが考え方は同じです。実践的にするため、IRR、NPV、回収期間および許容投資額をお互いに逆算できるようにしてあります。また、計算式だけでなくキャッシュフローが目で追えて理解しやすいようにしたのも特徴です。

投資評価1

図1.初年度に投資を行う場合の計算例

投資評価2

図2.継続的に投資が発生する場合の計算例

 

 みなさんの理解の便宜を図るため、主要な項目についてExcelでの計算ロジックをできるかぎり公開し、簡単に説明しておきます。

① 投資額:I

 ここには、初年度の投資額を入力します。ここで注意すべき点は、既に投資済のコストは埋没原価といって、新しい投資案件評価の投資額には含めません。

② 効果金額

 直接および間接人員削減、材料費削減、エネルギー費削減、歩留向上費用、ランニングコスト削減などの効果金額を入力します。

③ 減価償却年数

 設備等の耐用年数のことを言います。従来は会計上の法定償却年数を用いていましたが、現在は、ビジネス的な実質的な寿命を設定して評価します。 また、IT産業のように設備投資サイクルの短い分野では、増加償却が認められています。その場合には、次式で計算します。

最終償却率=(1-0.1^(1/n))*(1+増加償却率)

 0.1:残存価額比率

 n:耐用年数(減価償却年数)

例えば、5年の18.7%増が4年、5年の45.3%増が3年の償却率に相当します。

④ 減価償却率

 定率法にて計算する時の比率を言い、次式で示します。

償却率=1-0.1^(1/n)

 0.1:残存価額比率

 n:耐用年数(減価償却年数)

減価償却率の計算は次の式で表し、本例の減価償却年数5年の場合、0.369と計算されました。Excelでは、POWER関数を用いています。

 B15=1-POWER(0.1、1/B14)

⑤ 減価償却費

 減価償却費は、減価償却年数と減価償却率から次式で計算できます。ただし、「$B14<C3、" "」はExcel表示上のエラー防止条件式です。

 C6=IF($B14<C3、" "、DB($B4、$B4*0.1、$B14、C3))

⑥ Net cash flow

 Net cash flowは効果金額+減価償却費にて表します。

 C7=IF(C5<=0、""、SUM(C5:C6))

⑦ 税引き後純cash flow:FCF

 企業の法人税、事業税、固定資産税等税金の総計を実効税率と言い、約40%が税引き後純cash flowに相当します。

 C8=IF(C5<=0、""、C7*0.4)

⑧ cash flowの現在価値

 投資がもたらす税引き後純cash flow の価値を資本コストで割り引いた値で表します。Excelでの計算ではC9の式となります。

 Σ[FCF /(1+r)n ] FCF :税引き後純cash flow

 r:資本コスト

 n:期待する寿命

 C9=IF(C5<=0、""、C8/POWER(1+$B13、C3))

⑨ 累積cash flow

 cash flowの現在価値から投資額(K円)を引いた数式で表されます。

 C10=IF($B14<C3、0、B10+C9)

⑩ 正味現在価値 NPV(K円)

 EXCELでの計算ではNPV関数を用いるため、B16式になります。

 B16=NPV($B13、C8:I8)+B8

⑪ 税引き後利益率 ROI(内部利益率)

 EXCELでの計算ではIRR関数を用いるため、B17式になります。

 B17=IRR(B8:I8)

⑫ 投資回収年数:n

 簡易的には、投資額/利益でも求められますが、現在価値の考え方を盛り込み、より正確性を期すため次式を使います。

 n=log(FCF/FCF-r*I)/log(1+r)

 FCF:Free Cash flow

 I:投資額

 r:資本コスト

 Excelでの計算ではNPVおよびIRRと厳密には一致しないため、補正係数を加味してお互いの整合をとっています。また、許容投資額の計算でも補正係数を加味してお互いの整合をとっています。図1の初年度に投資を行う場合のExcelの入力例は、減価償却年数5年の時、NPVが限りなく0に近づいたとした場合に投資額10、000k円と許容投資額10、000k円を一致させ、ここでは逆算も可能としました。その時、資本コスト10%と税引き後利益率ROI(内部利益率) 10%も一致します。図2の継続的に投資が発生する場合の例では、初年度に10、000k円、次年度に3、000k円、次年度に1、000k円の投資額を入力しました。この時、いったん各投資額を現在価値に換算しています。具体的には、次式で計算しました。その後の計算ロジックは、初年度に投資を行う場合のようにキャッシュフローで追っています。

 C39=IF(C38<=0、""、C38/POWER(1+$B48、C37))

 なお、このExcelのソフトウェアは、筆者のホームページからダウンロードできます。

 

参考文献

  • 1) 千住鎮雄/伏見多美雄:経済性工学の基礎、日本能率協会マネジメントセンター、2000
  • 2) 鎮雄/伏見多美雄:経済性工学の応用、日本能率協会マネジメントセンター、1982
  • 3) 粕谷茂:プロエンジニア(コンピテンシー構築の極意)、株式会社テクノ、2002

   続きを読むには・・・


この記事の著者

粕谷 茂

「感動製品=TRIZ*潜在ニーズ*想い」実現のため差別化技術、自律人財を創出。 特に神奈川県中小企業には、企業の未病改善(KIP)活用で4回無料コンサルを実施中。

「感動製品=TRIZ*潜在ニーズ*想い」実現のため差別化技術、自律人財を創出。 特に神奈川県中小企業には、企業の未病改善(KIP)活用で4回無料コンサルを...


「経済性工学」の他のキーワード解説記事

もっと見る
設計標準の必要性と作り方 (その1)

 設計標準の必要性と作り方について、2回に分けて解説します。今回はその1として、ベストコストでの設計を実現する設計標準の作り方についてです。   ◆ ...

 設計標準の必要性と作り方について、2回に分けて解説します。今回はその1として、ベストコストでの設計を実現する設計標準の作り方についてです。   ◆ ...


製品開発とコストダウン 【連載記事紹介】

  製品開発とコストダウンの連載が無料でお読みいただけます!   ◆製品のコストは、設計段階でその80%が決まる 従来のコ...

  製品開発とコストダウンの連載が無料でお読みいただけます!   ◆製品のコストは、設計段階でその80%が決まる 従来のコ...


経済性工学概要 経済性工学 (その2)

  【経済性工学 連載目次】 R&Dを定量評価する 経済性工学とは 経済性工学の原理原則 Excelでの算出ロジック(NP...

  【経済性工学 連載目次】 R&Dを定量評価する 経済性工学とは 経済性工学の原理原則 Excelでの算出ロジック(NP...


「経済性工学」の活用事例

もっと見る
経済性工学で豊洲移転問題を分析する

 2017年6月現在、東京都は、中央卸売市場を築地から豊洲に移転して、築地への一部希望業者のリターンも考慮した案を選択しました。この意思決定は、正しい...

 2017年6月現在、東京都は、中央卸売市場を築地から豊洲に移転して、築地への一部希望業者のリターンも考慮した案を選択しました。この意思決定は、正しい...


コストを明確にして、評価できる人材を育成する(その1)

 コストを明確にして、評価できる人材を育成する過程を、次のS社を例に、二回に分けて解説します。   1.S社の背景  S社は、印刷機械の受注の窓...

 コストを明確にして、評価できる人材を育成する過程を、次のS社を例に、二回に分けて解説します。   1.S社の背景  S社は、印刷機械の受注の窓...


設計段階で加工品見積ソフトを生かす方法(その2)

3. コストは分けて考えること  ある企業からコストダウンの検討依頼があった時のことです。その会社(A社とします)は、包装機械を製造・販売している会...

3. コストは分けて考えること  ある企業からコストダウンの検討依頼があった時のことです。その会社(A社とします)は、包装機械を製造・販売している会...