設備投資の回収期間について

質問

よろしくお願いいたします。
現状の生産ラインの人件費が毎年Ｘ円かかっています。
ここにZ円の投資をすることで人件費が毎年Y円に下がります。
投資してもしなくても、人件費は毎年10%アップします。
つまり
当年の効果額：X-Y円
2年目の効果額：X(1.1)-Y(1.1)円
3年目の効果額：X(1.1)^2-Y(1.1)^2円
・
・
n年目の効果額：X(1.1)^(n-1)-Y(1.1)^(n-1)円

上記の場合の各年の効果額の累計が、投資Z円を上回る年数nを
計算したいのですが、これを解く方程式、具体的にはエクセルで
解ける方程式はどのようになるでしょうか。
逐次計算して累計するのではなく、式一つで解く方法が分かりません。

以上、よろしくお願いいたします。

回答No.3

kagakusuki · ベストアンサー

回答No.2です。
　先程の回答において、関数を入力するセルのセル番号を書き間違えておりました。（関数自体には間違いは御座いません）

>B4セルに回収に要する年数：nの年数をExcelのワークシート関数を使って自動的に表示させるものとします。
>その場合、B4セルには次の様な関数を入力されると良いと思います。

という記述の中に2回現れている「B4セル」という箇所は、「B5セル」の間違いです。

　それから、書き忘れておりましたが、B4セルの書式設定の表示形式は、[パーセンテージ]に設定して下さい。
　尚、小数点以下の桁数を何桁まで表示するのかは、質問者様の方で適時設定なさって下さい。（回答No.2の添付画像の例では、小数点以下の桁数を2桁までの表示としています）
　それから、B1、B2、B3、B5セルの書式設定の表示形式も、[通貨]或いは[会計]とされた上で、「記号」欄を\とされた方が、見栄えが良くなると思います。

お礼

大変丁寧にご回答いただき、ありがとうございました。
例外処理までご考慮いただいた式を提示いただき、大変助かりました。
どうもありがとうございました。

回答No.2

kagakusuki

等比数列の和の公式は

a×1^0＋a×1^1＋a×1^2＋a×1^3＋・・・＋a×1^(n-1)＝n×a

a×r^0＋a×r^1＋a×r^2＋a×r^3＋・・・＋a×r^(n-1)＝a(1－r^n)／(1－r)＝a(r^n－1)／(r－1)

なのですから、n年目の効果額の累計は

(X－Y)＋(X×1.1－Y×1.1)＋(X×1.1^2－Y×1.1^2)＋・・・＋(X×1.1^(n-1)－Y×1.1^(n-1))
＝(X－Y)×1.1^0＋(X－Y)×1.1^1＋(X－Y)×1.1^2＋・・・＋(X－Y)×1.1^(n-1)
＝(X－Y)(1.1^n－1)／(1.1－1)

になります。
　そして、「各年の効果額の累計が、投資Z円を上回る年数n」を求める方程式は以下の様になります。

Z≦(X－Y)(1.1^n－1)／(1.1－1)

　ここまでは、回答No.1様の受け売りですが、上記の方程式を解きますと、

Z(1.1－1)／(X－Y)≦1.1^n－1
1.1^n≧Z(1.1－1)／(X－Y)＋1
n≧log[底=1.1](Z(1.1－1)／(X－Y)＋1)

となりますから、回答No.1様の御解答の式の右辺はn+1ではなくnではないでしょうか？

>具体的にはエクセルで解ける方程式はどのようになるでしょうか。

　今仮に、
B1セルに現状の人件費：Xの金額を、
B2セルに投資後の1年目の人件費：Yの金額を、
B3セルに投資金額：Zの金額を、
B4セルに人件費上昇率のパーセンテージを
それぞれ入力し、
B4セルに回収に要する年数：nの年数をExcelのワークシート関数を使って自動的に表示させるものとします。
　その場合、B4セルには次の様な関数を入力されると良いと思います。

=IF(COUNT(B1,B2,B3,B4)=4,IF(B3*B4>B2-B1,IF(B4,LOG(B3*B4/(B1-B2)+1,B4+1),B3/(B1-B2)),"回収出来ません"),"")

回答No.1

alice_44

エクセルよりも、関数電卓の出番でしょう。
n 年目の効果が (X-Y)(1.1)^2 円ですから、
1～n 年の合計は、等比数列の和
(X-Y){1 - (1.1)^(n+1)}/(1 - 1.1) 円です。
参考→ http://www.kwansei.ac.jp/hs/z90010/sugakua/suuretu/touhisum/touhisum.htm

(X-Y){1-(1.1)^(n+1)}/(1-1.1)≧Z を解くと、
n+1≧log(1+{(0.1)Z/(x-Y)})/log(1.1) です。
右辺の log の底は、共通であれば 10 でも e でも ok。

お礼

お礼が遅くなり、申し訳ありません。
上げていただいたサイトも大変参考になりました。
どうもありがとうございました。