情報処理論　1996 ... 2002年度講義資料
[講義の流れ(index)], [Prev], [Next], [教科書& 参考書(references)]
 
  1. Excelの主な機能とその活用
２.２ 表計算機能の活用（単利と複利）

　表計算機能を使うと日常的な計算問題のかなりのものをワークシート上で解くことがで きる。あまり高度な機能など使わずに、"紙と鉛筆"だけで自分の頭で考えてみようと言うわけである。ここではまず、単利と複利に関する問題を考える。
　例として、 １年間の金利を５％とするとき、元金１０万円で、１０年間でどのように元利合計が増えるかを調べる。単利と複利による元利合計の増え方の仕組みを知ってい ることを前提とする。《参考書�い�3.3章》

　練習１：　【ブック名＝金利計算】

　練習２：　Ｂ列に単利計算のデータ、Ｃ列に複利計算のデータを求める。

  1. １年目は，セルB2とC2に元金となる1,000,000を入れる．単利の２年目のセルB3には，= B2 + B$2 * 0.05 と入れる．これは１年目の元利合計に利子として固定的な部分を加えたものである．
  2. 複利の場合は，利子部分が１年目の元利合計×利率になるので，２年目の元利合計は，= C2 + C2 * 0.05 である．セルＣ３には， = C2 * 1.05 を入力する．
  3. セル範囲Ｂ３〜Ｃ３をセル範囲Ｂ４〜Ｃ１１に複写する．

　練習３：　練習２の結果をグラフで表す。（元利合計の比較）

以下のグラフを作成するには、グラフィザードで得られたグラフにいくつかの修正が必要である。
�．織ぅ肇襪竜�入、�∨淮磴良充┛銘屬髻眠蔀次佑吠儿后�
��Ｘ軸の文字（数値）の方向を、横向きから縦向きに変更。
あとから�△鉢�を行なうには凡例やＸ軸をダブルクリックして該当するダイアログボックスを表示させるとよい。

　練習４：　単利と複利における利子の比較

練習問題 (2-7)

元金１００万円を１０年間貯蓄する。年間金利が３％、 ５％、７％のときについてそれぞれ単利と複利の場合で元利合計のがどのように異なるかを計算しなさい。そして単利と複利や、利子率の結果の違いが分かるよ うなグラフを作成しなさい。

 

２.３ 表計算機能の活用（ローン返済）

　次に、ローン返済の問題を考える。 ローン返済の基本的な考え方は、その年の始めにある借入残高に、その年の金利部分を加えたものからその年の返済金額を引くことである。得られた残額が次年 度に繰り越されて、次年度もその年と同じように計算される。これを残高が無くなるまで繰り返す。

　例として、100万円を年利率 3.0% の定額返済で借りた場合、10年間の返済額と残高を計算する。毎年の返済額は12万円とする。 今年初めに100万円の残高があり、年末に12万円返済すれば、年末の残高は 100*1.03-12 = 91 万円となる。 その残高が来年に繰り越され、その時に3.0%分の利子が加算されるので、来年の期首残高は、91*1.03 = 93.73 万円となる。《参考書�い�3.4章》

　練習１：　計算式の指定　【ブック名＝ローン返済】

  1. 準備として項目名やデータを入れる。セル C1 に利率 0.03、D1 に借入金100万円、C3に12万円を入れる。
  2. １年目の計算式を指定する。返済直前の年末の残高は、B3 に = D1 *（ 1 + $C$1 ）、 年末の残高は D3 に = B3 - C3、となる。この計算式で $C$1 は年利率 0.03 を絶対参照している。
  3. ２年目は C4 に =C$3とする。他の計算式はやはり B4 に = D3 *（ 1 + $C$1 ）、 D4 に = B4 - C4、でよいからことが分る。同様に考えると、３年目からは２年目の計算式 (B4..D4) を複写すれば求まることが分る。

    　その結果をみると、10年目の期末残高が負となっている。これは12万円を返済として用意したら、借入金額がなくなり12万円の一部が余 り、貯蓄に変 わったことを意味する。

  4. このローン場合の返済総額は、9年間の毎年12万円と 10年目の期首残高を最後に支払うことになるので、 120,000×9 + B12 円 ＝ 1,168,250.86 円となる。10年返済で支払う利子が約168,250 円である。

　練習２：　返済額のうち、元金と利子の支払に向けられている割合を調べる。

  1. 元金の支払分は期首残高の減少分であるから、１年目はセル G3 に = B3 - B4 とする。 利子の支払分はその年の返済額（12万円）の残りの部分であるから、セル F3 に = C3 - G3 として計算する。
  2. これを10年目まで複写する。更に、元金と利子の支払分の合計をセル G13 と F13 に求める。
  3. 元金の合計が103万円になることを確認する。これは１年目の期首残高である。

　練習３：　面グラフの活用

    毎年の返済における元金と利子の返済額の割合を表すために図のような面グラフを作成する。
  1. H3〜H11のセルに０を、H12 に式 = C12 - G12 を入れる。H2 には項目名を入れる。ここでは空白にしてある。
  2. セル範囲 A2〜A12 と F2〜H12 を指定し、グラフウィザードを使って下のような面グラフを作成する。

    　金額の大きさが面積で表されている。これから利子の支払部分が年を経るに従って減少していく様子が分る。

     

　練習４：　ちょうど１０年後に返済を完了するためには毎年いくら返せばよいのでしょうか。

  1. 答えを”手作業”で求めるには、毎年の返済額を１つのセル、例えばセル C3 に入力するようにワークシート内の式（セルC3からC12の式）を = C$3 に変更する。
  2. 毎期の返済額を、120000, 118000, 116000, ... と適当な幅で変えてみる。目標は10期目の期末残高をゼロにすることである。 118000と116000のとき期末残高がプラスからマイナスに変わる。
  3. この２つの値の間に答えがあるはずであるから、今度は幅を小さくして 117500, 117000, 116500, ... などとして再び期末残高がプラスからマイナスに変わる区間を探す。 この作業を繰り返せば人間が変更する幅の範囲内で、すなわち近似的に10期目の期末残高をゼロにする返済額を予測することができる。
    （後の章では、財務関数を使う方法を紹介する）

　練習５：　ゴールシークという方法で練習４の近似解を求める。

　ゴールシークを利用した解法
  1. セル C3 に最適な返済額が入っていると仮定する。これは最初は未知数である。 練習４の表を使うのであれば既に準備完了である。
  2. 新たに表を作るのであれば以下の準備をする。１年目の式は B3に = D$1 * ( 1 + $C$1 ) と期首残高の計算があれば良い。
    1. ２年目の期首残高は、B4に = ( B3 - C$3 ) * ( 1 + $C$1 ) で計算される。
    2. これを10年後つまり11年目の期首残高まで複写する。今はまだ返済年額が０円の状態なので残額は複利で累積していることに注意！。C3 の値を12万円など勝手な値にしておく。
    3. B13の式が正しく = ( B12 - C$3 ) * ( 1 + $C$1 ) となっているか確認する。以上で 準備が終了。
  3. ゴールシークの処理に入る。メニューバーで、〔ツール (T)〕− 〔ゴールシーク (G)。。。〕−の順に選択するとゴールシークの窓が開く。

    表4を利用してセルE1を変化させる場合は上図で「変化させるセル(C):」を $E$1 に読み替えて下さい。
  4. ゴールシークの窓で、11年目の期首残高に当たる B13 を”数式入力セル(S)”に、その”目標値(V)”を"0"にするようにセル C3 の値を”変化させる”ことの３つを指示する。そして〔OK〕を選択する。
  5. すると、「．．．収束値を検索しています。．．．解答が見つかりました」という画面が出てから、次の計算結果が表示される。この表示から目標 値"0"に対して現在値は 6E-11（≒5.99539×10-11 ）であることが分る。これは 10桁の正確さで近似的に"0"と見なせるので、十分な精度で近似的な解が求められたと判断できる。

    　
  6. ゴールシークの結果，下図のセルＣ３には、答えの返済額117,230.5円が求められている。

　

練習問題 (2-8)

100万円を年利率 5.0% と 7.0% の定額返済で借りた場合、練習５のゴールシークの方法で以下の問に答えなさい。

  1. ちょうど１０年後に返済を完了するためには、それぞれ毎年いくら返せばよいのでしょうか。1枚のシートに、毎年の返済額、返済総額、及び 3.0%と5.0%の返済総額の差額を求めなさい。
  2. 年利率5.0%で、返済期間を20年にした場合、返済期間10年との返済総額の差額を、上と同じ１枚のシート内で求めなさい。
  3. 年利率5.0%で、返済期間を20年にした場合、毎年の返済額のうち元金と利子への支払額の割合をしらべ、練習３に習って面グラフを作成しな さい。 上と同じ１枚のシート内で印刷したときＡ４判用紙１枚に納まるように調整しなさい。
lec.03) Go to TOP -----(c)TK