********** 04.10.14 **********************************************
Excelを楽しもう!
〜〜〜 関数で遊ぼう 〜〜〜
|
************************************************** No.40 ************
==== INDEX==========================================================
1. はじめに
2. 今日の関数 RATE
3. 便利なヒント集 セル幅で文字を折り返す方法
4. 編集後記
====================================================================
1.はじめに
またお会いできてうれしいです。発行者の 井上 誉史 です。
新たに登録頂いた読者の皆さま、ご購読ありがとうございます。
過去のバックナンバーは
http://gtz0.fc2web.com/backNo/no0.html
でご覧いただけます。
前回の FV 関数はお分かり頂けたでしょうか?
今回も財務関数をします。
読者のA.Kさんから励ましのメールを頂きました。
A.Kさんには前回にも励ましのメールを頂きこのメルマガを続ける力に
なっています。
嬉しいですね! 有り難いですね! ありがとう A.K様
もう終わったのかナアーと思って断念に思っていましたところ久し振りに
メールに入っておりホットしました。
井上様は、もっと効率よく、もっとユニークな教え方ができないか必死で
お考えのようですが、それはそれなりに試行錯誤していただくのは本当に
敬服します。
しかし、素人の私達にとっては、今までのようにいろんな関数を例題を
以って解説していただく、この方法は本当に身についていきます。
また、「便利なヒント集」も目からウロコに出くわします。
どうか時間の許す限り継続していただきたくお願い申し上げます。
一ファンより・・・・
★ご意見・ご質問などどんな些細なことでもいいので
メール下さい。
ご質問等の返答が遅れていますが、必ず返事は書きます。
また、取り上げて欲しい話題・ご質問などもお待ちしております。
「こんなときはどうするの?」という質問や「この前の解説だけどこうやった
ほうが便利だよ!!」というご意見がありましたら、便り下さい。
みなさんと一緒に関数を楽しみましょう。
2.今日の関数
クレジット等でローンを組む時、自分でいろいろとシュミレーションをしてみたいと
考えたことはありませんか?
今日は元利均等返済での利率と返済金額・支払回数の
シュミレーションを説明します。
120万円で自動車を購入し、100万円のローンを組み毎月の支払い額を
5万円・2年で支払が終わる場合の利率を計算してみましょう。
この場合、使う関数は、財務関数の RATE(レート)関数を使います。
RATE 関数は元利均等返済での利率を求める関数です。
B1に購入金額、B2に頭金、B3に借入金、B4に借入金利、B5に支払回数、
B6に最終残額、
B7に毎月の支払額と入力します。
C1に1200000、C2に200000、C3に1000000、C5に24、C6に0、C7に-50000と入力し
C4に式
=rate(c5,c7,c3)*12
を入力します。
C4の式は月利で算出されますから、年利換算するので*12にします。
財務関数では,出金はマイナスの付いた負の数字で、入金は正の数字で表します。
セルC4は18.16%となります。
次にA9に回数、B9に支払額、C9に支払利息、D9に元金返済,
E9に借入残高と入力します。
A10に0、A11に1、と入力し、以後順にA34に24を入力します。
B10は空白のままにして、B11に式
=-$c$7
を入力し、B34まで複写する。
C10は空白のままにして、C11に式
=e10*$c$4/12
を入力し、C34まで複写する。
D10は空白のままにして、D11に式
=b11-c11
を入力し、D34まで複写する。
E10に=c3と入力し、E11に式
=e10-d11
を入力し、E34まで複写する。
最後にC1からC7までと、B10からE列の最後尾までを範囲選択しカンマを付ける。
《注意》- 式は必ず半角英数で入力して下さい。
- 以下のシートは入力をあらわしており、実際には式は表示されず、
計算結果が表示されます。
. |
A |
B |
C |
D |
E |
1 |
. |
購入金額 |
1,200,000 |
. |
. |
2 |
. |
頭金 |
200,000 |
. |
. |
3 |
. |
借入金 |
1,000,000 |
. |
. |
4 |
. |
借入金利 |
=rate(c5,c7,c3)*12 |
. |
. |
5 |
. |
返済期間 |
24 |
. |
. |
6 |
. |
最終残額 |
0 |
. |
. |
7 |
. |
支払額 |
-50000 |
. |
. |
8 |
. |
. |
. |
. |
. |
9 |
回数 |
支払額 |
支払利息 |
元金返済 |
借入残高 |
10 |
0 |
. |
. |
. |
=c3 |
11 |
1 |
=-$c$7 |
=e10*$c$4/12 |
=b11-c11 |
=e10-d11 |
12 |
2 |
=-$c$7 |
=e11*$c$4/12 |
=b12-c12 |
=e11-d12 |
13 |
3 |
=-$c$7 |
=e12*$c$4/12 |
=b13-c13 |
=e12-d13 |
14 |
4 |
=-$c$7 |
=e13*$c$4/12 |
=b14-c14 |
=e13-d14 |
15 |
5 |
=-$c$7 |
=e14*$c$4/12 |
=b15-c15 |
=e14-d15 |
: |
: |
: |
: |
: |
: |
: |
: |
: |
: |
: |
: |
31 |
21 |
=-$c$7 |
=e30*$c$4/12 |
=b31-c31 |
=e30-d31 |
32 |
22 |
=-$c$7 |
=e31*$c$4/12 |
=b32-c32 |
=e31-d32 |
33 |
23 |
=-$c$7 |
=e32*$c$4/12 |
=b33-c33 |
=e32-d33 |
34 |
24 |
=-$c$7 |
=e33*$c$4/12 |
=b34-c34 |
=e33-d34 |
35 |
. |
. |
. |
. |
. |
セルA9からセルE28までの表示は下記のようになります。
. |
A |
B |
C |
D |
E |
9 |
回数 |
支払額 |
支払利息 |
元金返済 |
借入残高 |
10 |
0 |
. |
. |
. |
1,000,000 |
11 |
1 |
50,000 |
15,131 |
34,869 |
965,131 |
12 |
2 |
50,000 |
14,603 |
35,397 |
929,734 |
13 |
3 |
50,000 |
14,068 |
35,932 |
893,802 |
14 |
4 |
50,000 |
13,524 |
36,476 |
857,326 |
15 |
5 |
50,000 |
12,972 |
37,028 |
820,298 |
: |
: |
: |
: |
: |
: |
: |
: |
: |
: |
: |
: |
31 |
21 |
50,000 |
2,915 |
47,085 |
145,573 |
32 |
22 |
50,000 |
2,203 |
47,797 |
97,775 |
33 |
23 |
50,000 |
1,479 |
48,521 |
49,255 |
34 |
24 |
50,000 |
745 |
49,255 |
0 |
35 |
. |
. |
. |
. |
. |
セルC1の購入金額・セルC4の借入金利・セルC5の返済回数・セルC7の支払額を
いろいろと変えてシュミレーションをしてみましょう。
36号の財務関数の PMT(ペイメント)関数・37号のNPER(エヌパー)関数
38号のPV(ピーブイ)関数・39号の FV(エフブイ)関数
そして今回のRATE(レート)関数を一覧表にすると下の表のようになる。
. |
A |
B |
C |
D |
E |
F |
G |
1 |
関数 |
PMT |
NPER |
PV |
FV |
RATE |
. |
2 |
購入金額 |
1,200,000 |
1,200,000 |
1,200,000 |
1,200,000 |
1,200,000 |
. |
3 |
頭金 |
200,000 |
200,000 |
200,000 |
200,000 |
200,000 |
. |
4 |
借入金 |
1,000,000 |
1,000,000 |
PV |
1,000,000 |
1,000,000 |
. |
5 |
借入金利 |
5% |
5% |
5% |
5% |
RATE |
. |
6 |
返済期間 |
24 |
NPER |
24 |
24 |
24 |
. |
7 |
最終残額 |
0 |
0 |
0 |
FV |
0 |
. |
8 |
支払額 |
PMT |
-50000 |
-50000 |
-50000 |
-50000 |
. |
9 |
. |
. |
. |
. |
. |
. |
. |
各関数の式は下記になる。
返済金額 =pmt(c4/12,c5,c3,c6)
返済期間 =nper(c4/12,c7,c3,c6)
借入金 =pv(c4/12,c5,c7,c6)
借入残額 =fv(c4/12,c5,c7,c3)
借入金利 =rate(c5,c7,c3)*12
これら5つの関数は貸付・貯蓄・投資でも使えます。
3.便利なヒント集
今回は、セル幅で文字を折り返し,長文を入力する方法の説明です。
表など限られたスペースに長文を入力した時、枠からはみ出し見づらくなります。
この場合、セル幅で文字列を折り返すように設定しましょう。
セル幅で文字列を折り返したいセルを選択し、
書式ーセルをクリックし、セルの書式設定ダイアログボックスが表示されますから、
配置タブをクリックし、折り返して全体を表示するにチェックを入れ
OKをクリックする。
これで長文でもセル幅に収まります。
文字列の任意の場所で強制改行する方法は
強制改行したいセルをクリックし、改行したい場所をクリックしてカーソルを表示し、
Altキーを押しながらEnterキーを押し改行する。
再度、Enterキーを押し確定する。
4. 編集後記
このメルマガをお友達や周りの方々にも転送してあげてください。
⇒http://www.mag2.com/m/0000131398.htm
きっと喜ばれますよ!
また、メルマガの相互紹介をご希望の方連絡下さい。
◇マイナスイオン活水器の営業での気付きと起業でのパソコンの便利な使い方を
紹介しています。
今から起業を考えている方、起業したが今ひとつの方は必見です。
★☆―――――――<知って得する情報マガジン>――――――――☆★
『ないないづくしの起業日記』
●マイナスイオン活水器の営業での成功と失敗を共有出来ます。
●パソコンを便利に使いこなす!ちょっとの工夫が大きな差を生ことを
きっと実感できます!
登録はこちら⇒http://www.mag2.com/m/0000134750.htm
★☆―――――――――――――――――――――――――――――☆★
分かりにくい箇所がありましたら遠慮なく質問のメールを下さい。
最後まで読んでいただきありがとうございます。
なお、内容は十分吟味していますが、内容によってなんらかの障害、損害等が
発生した場合に
おいても一切責任は負いかねます。自己責任でお願いします。
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
編集・発行: 井上 誉史
問い合わせ: takasigtz0@ybb.ne.jp
Web サイト: http://gtz0.fc2web.com/
ご購読の停止・配信先の変更は下記でどうぞ
http://gtz0.fc2web.com/kansu/merumaga.html
当方では、代理での配信解除・変更をいたしかねますのでご了承下さい。
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー