わえなび ワード&エクセル問題集

当サイトにリンクを張るのは自由です。WordとExcelの練習問題の動画を「Youtube」で無料公開しています。みんなで勉強しましょう!

【Excel財務関数序論】現在価値PVと将来価値FVと複利計算の基本的な考え方

Excelの財務関数(初歩のもの)では、現在価値や将来価値という言葉が頻繁に登場します。

お金の貸し借りや投資をすると、利息や相場の変動などによって元の金額より増えたり減ったりします。現在価値や将来価値は現金の増減や収支を表す概念ですから、プラスやマイナスがあるのは当たり前のことです。しかし、残念なことに、一部の書籍やサイトでは、現在価値や将来価値がプラスになったりマイナスになったりするのは「Excelのきまり」だから暗記するしかない(?)などと意味不明な説明をしています。とんでもない話です。

Excelの財務関数は現金収支の基本的な考え方にしたがって正しく作られており、マイクロソフトが勝手にプラスマイナスを決めているわけではありません。そこで、財務関数を学ぶ前に知っておきたい、現金収支の基本的な考え方についてどこよりも詳しく解説します。

 

 

目次

問題文のあとに簡単な操作方法を解説していますが、静止画では、わかりにくいと思いますので、最後に動画を載せています。ぜひご覧ください。

1.現金収支と残高の考え方

問題

5月30日の時点で預金残高が0円であるが、100万円の入金と70万円の出金があるため、残高が30万円に増える予定である。次の[A][B]のうち、事前に残高を増やしておかなければならないのはどちらか。

[A]
5/30 残高0円
5/31 収入100万円
6/30 支出70万円

[B]
5/30 残高0円
5/31 支出70万円
6/30 収入100万円

解説

Excelの財務関数のうち基本的な関数については、時間の経過とともに、自由に使える現金または預金の残高がどのように変わるかを計算しています。

f:id:waenavi:20190419082503j:plain

 

[A]のように、100万円の収入の後で70万円を支払うことは可能です。支払いの時点で100万円あって、そこから70万円を支払うからです。

f:id:waenavi:20190419082507j:plain

 

しかし、[B]のように100万円の収入の前に70万円を支払うことは不可能です。支払いの時点で自由に使えるお金(残高)がないからです。その1か月後に100万円の収入があっても遅いです。

f:id:waenavi:20190419082510j:plain

 

支払いの前日までに、借金や定期預金の解約などで70万円を調達して、残高を70万円以上にしておかなければなりません。

f:id:waenavi:20190419082514j:plain

 

会社の場合、支払いのために準備しているお金が無くなることを「資金ショート」といい、黒字でも倒産の危険があります。特に、支払いが多い場合は資金が足りるかどうか注意しなければなりません。

f:id:waenavi:20190419082516j:plain

 

支払いの時点での残高を計算しておくことによってショートを防ぐことができます。

f:id:waenavi:20190419083605j:plain

 

2.プラスとマイナス

問題

次の項目をExcelに入力して、その時点での残高をそれぞれ求めなさい。

5/1 残高0円
5/2 70万円の借金(借り入れ)
5/3 株式購入のため20万円の出費
5/4 5万円の積立(引き落とし)
5/5 定期預金満期で30万円の払い戻し
5/6 利息5円の受け取り

f:id:waenavi:20190419084907j:plain

 

解説

理由や目的にかかわらず残高が増えたら収入(プラス)、お金を使ったら支出(マイナス)です。

f:id:waenavi:20190419103409j:plain

 

この残高とは支払いのために準備をしている手元の現金や普通預金のことであって、財産、負債、利益とはまったく関係ありません。収入が多いから得(良い)、支出が多いから損(悪い)という意味ではありません。

f:id:waenavi:20190419084040j:plain

 

例えば、70万円の借金をした場合、負債という意味ではマイナスのイメージがあるかもしれませんが、支払いのための残高を増やそうとしているという意味ではプラスです。会社の財産としてはマイナスですが、現金収支としてはプラスです。

f:id:waenavi:20190419084045j:plain

 

逆に、返済をしたら借金は減りますが、自由に使えるお金も減るのでマイナスです。会社の財産としてはプラスですが、現金収支としてはマイナスです。

f:id:waenavi:20190419084048j:plain

 

したがって、70万円の借入は収入です。

f:id:waenavi:20190419084051j:plain

 

株式に限らず、購入・権利取得・投資・出資・寄付・貸し付けなどでお金を出したらマイナスにします。後日、そのお金が戻ってきたらプラスです。投資によって得したか損したかは無関係です。

f:id:waenavi:20190419084056j:plain

 

将来のために、定期預金(3か月を超えるもの)の契約をしたり、財布の中のお金を貯金箱に入れたり、旅行のために積み立てをしたり、別の口座に移して資金を確保しておく場合、自由に使えなくなる(使用目的を制限している)ので、マイナスとなります。

f:id:waenavi:20190419084101j:plain

 

逆に、定期預金を解約したり、満期になったり、または積み立てたお金が払い戻される場合は残高が増えるのでプラスとなります。

f:id:waenavi:20190419084105j:plain

 

銀行にお金を預けていると利息(受取利息)がつきますが、これは純粋な収益なのでプラスです。

f:id:waenavi:20190419084109j:plain

 

残高を求めます。

f:id:waenavi:20190419084114j:plain

f:id:waenavi:20190419084118j:plain

 

収入と支出を分けるとこのようになります。25万円の支払いがあり、その後で30万円の収入がありますが、それでは25万円の支払いができません。

f:id:waenavi:20190419084121j:plain

 

そこで残高を増やして、さらに今後の突然の出費に備えるためにあらかじめ70万円の借り入れをしています。

f:id:waenavi:20190419084124j:plain

 

3.現在価値PVと将来価値FV

問題

甲さんが乙さんに70万円を貸し、1年後に77万円にして返済する契約をしたとする。貸付時と返済時の収支を述べなさい。

f:id:waenavi:20190419084813j:plain

 

解説

貸し付けた時点では、甲は自由に使えるお金(残高)が70万円減ります。乙は70万円の資金を調達したことになりますので、甲はマイナス70万円、乙はプラス70万円です。これを現在価値ということがあります。最初に発生するお金の移動のことです。英語で言うとPresent Value(プレゼント・バリュー)なので、Excelでは略してPVといいます。

f:id:waenavi:20190419084821j:plain

 

乙は、借り入れによって資金不足が解消されます。甲は、自由に使えるお金が70万円少ない状態で1年間我慢するので、その手数料として金利7万円を要求しているのです。

f:id:waenavi:20190419084824j:plain

 

1年後、乙が借金を返済すると、甲の残高はプラス77万円、乙の残高はマイナス77万円となります。これを将来価値ということがあります。最後に発生するお金の移動のことです。英語で言うとFuture Value(フューチャー・バリュー)なので、Excelでは略してFVといいます。

f:id:waenavi:20190419084828j:plain

 

甲は1年間の貸し付けによって、PV=-70万円、FV=+77万円で、合計して7万円の利益を得たこととなります。これを年利10%といいます。

f:id:waenavi:20190419084831j:plain

 

4.利率RATE

問題

甲さんが乙さんに年利6%で1年間、70万円を貸し付けた。PVとFVを述べなさい。また、貸付期間が1か月の場合はどうか。

f:id:waenavi:20190419084817j:plain

 

解説

PVは、甲-70万、乙+70万円です。1年の利息が6%の場合、6%増やすということは1.06倍(=106%)です。

f:id:waenavi:20190419091954j:plain

 

FVは、甲+74.2万円、乙-74.2万円です。

f:id:waenavi:20190419091957j:plain

 

年利6%のように1年間の利息が定められていて、1年未満の期間の利息を計算するときには、商慣習として、単純な日割り計算をすればよいことになっています。1か月の場合は12で割ります。

f:id:waenavi:20190419092000j:plain

 

1年の利息が6%の場合、1か月の利率は6%/12=0.5%です。つまり1.005倍となります。

f:id:waenavi:20190419092005j:plain

 

このように、貸付期間が1年より短い場合は、利率をその期間に合わせて換算します。この換算した利率のことをExcelではRATE(レート)といいます。

f:id:waenavi:20190419092009j:plain

 

FVは、甲+70万3500円、乙-70万3500円です。

f:id:waenavi:20190419092012j:plain

 

5.定期支払額PMT

問題

次の各設問で、現在価値、定期支払額、将来価値はそれぞれいくらか。

  1. 70万円の借入金を、11万円の7回払いで返済する。
  2. 10万円ずつ7回預ける積立預金で、満期に77万円を受け取る。

解説

いっぱんに、ある期間の初めのことを「期首」といい、期間の終わりのことを「期末」といいます。

f:id:waenavi:20190419092621j:plain

 

また、分割払いの時に毎回支払う金額のことを「定期支払額」といいます。支払いのことを英語でPayment(ペイメント)と言い、Excelでは略してPMTといいます。

f:id:waenavi:20190419092624j:plain

 

分割払いで、毎回期末に支払うことを期末払い、期首に支払うことを期首払いといいます。Excelでは原則として期末払いとして計算をしますが、設定を変えることによって期首払いの計算をすることもできます。

f:id:waenavi:20190419092627j:plain

 

70万円の借金をした後で、返済を11万円の7回払いで期末に支払う場合、1回目の期首に70万円を借り入れ、1回目~7回目の期末に11万円を支払います。

f:id:waenavi:20190419092631j:plain

 

現在価値+70万円、定期支払額-11万円、7回払いなので総支払額は77万円となります。

f:id:waenavi:20190419092634j:plain

 

定期支払額によって完済しており、さらに追加で支払うものが無い(最終的に資金の移動が無い)という意味で、将来価値は0円と考えます。

f:id:waenavi:20190419092636j:plain

 

期末に10万円ずつ7回預けて、満期に77万円返してもらう積立預金の場合、1回目から7回目の期末が10万円のマイナスで、7回目の期末が77万円のプラスです。

f:id:waenavi:20190419092640j:plain

 

最初に預け入れが無ければ、定期的な支払いの前に支払いが無い(最初の資金移動が無い)という意味で、現在価値は0円と考えます。

f:id:waenavi:20190419092643j:plain

 

定期支払額-10万円が7回、将来価値が+77万円です。

f:id:waenavi:20190419092648j:plain

 

6.複利計算

問題

100万円を一括で預け入れ、年利6%の利息が付くと1年後には何円になるか計算しなさい。また、12年後にはいくらになるか計算しなさい。

f:id:waenavi:20190419093817j:plain

 

解説

100万円の預け入れは、-100万円です。6%増やすということは1.06倍(=106%)です。

f:id:waenavi:20190419095855j:plain

 

ただし、払い戻しはプラスなので、数式の先頭にマイナスを付けます。

f:id:waenavi:20190419095859j:plain

 

106万円となります。あわせて6万円の利得になります。

f:id:waenavi:20190419095903j:plain

 

次に2年以上預ける場合について考えます。この場合は、1年ごとに払い戻すものと考えます。

f:id:waenavi:20190419095907j:plain

 

まず、100万円を預け入れて、1年後に払い戻す場合は+106万円となります。これをもう一度預け入れると考えると、-106万円となります。106万円として預け入れるので、利息も少し多くなります。このように、発生した利息を、次の元金に含めて利息を計算しなおすことを「複利」といいます。

f:id:waenavi:20190419095911j:plain

 

2年後には112万3600円をあずけるので、さらに利息が多くなります。これによって雪だるま式に利息が増えていきます。

f:id:waenavi:20190419095916j:plain

 

106万円を1年間預けると、106万円x1.06=112万3600円となります。さらにもう1年延長する場合は、これを1.06倍します。

f:id:waenavi:20190419095920j:plain

 

12年間の場合、1.06倍を12回繰り返すことになります。12乗です。

f:id:waenavi:20190419095924j:plain

 

201万2196円となります。元の金額の2倍以上となります。

f:id:waenavi:20190419095928j:plain

 

7.積立複利計算と期首払い・期末払い

問題

最初に100万円を預けた後、毎年10万円を12年間積み立てたとする。年利6%のときPV、PMT、FVはそれぞれ何円か。

f:id:waenavi:20190419093821j:plain

 

解説

積立預金をする前に、頭金として最初にいくらか預け入れることがあります。最初に預けるお金は現在価値で、単純に12年間預けて払い戻しがあるだけです。現在価値はマイナス100万円となり、12年預けると、さきほどの複利計算により201万2196円となります。

f:id:waenavi:20190419101539j:plain

 

さらに、定期支払額-10万円を「期末」で12回積み立てる場合、積立1回目の10万円は11年間預けることになりますが、その後預ける期間が1年ずつ減っていきます。最後の10万円は預けた直後に払い戻すので利息は付きません。

f:id:waenavi:20190419101543j:plain

 

こちらの表を使って計算してみましょう。現在価値は、頭金-100万円で、定期支払額は、毎年-10万円となります。

f:id:waenavi:20190419101546j:plain

 

預ける年数は最初12年ですが、1年ずつ減ります。

f:id:waenavi:20190419101550j:plain

 

複利計算によって払い戻し金額を計算します。先頭にマイナスを付けて、-100万円x1.06倍をして、その12乗です(=-元金x(1+年利)^年数)。なお、利率を絶対参照にしてオートフィルをします。

f:id:waenavi:20190419101555j:plain

 

合計すると将来価値は約370万円となります。

f:id:waenavi:20190419101600j:plain

 

預入の合計が220万円なので、約150万円が利息となります。合わせて約370万円の払い戻しとなります。

f:id:waenavi:20190419101604j:plain

f:id:waenavi:20190419101608j:plain

 

元本と利息の合計金額のことを「元利合計(がんりごうけい)」といいます。

f:id:waenavi:20190419101612j:plain

 

ちなみに、期首で積み立てをする場合、定期支払額の預ける期間が1年ずつ長くなりますので、利息も多くなります。

f:id:waenavi:20190419101615j:plain

 

預入年数は1年ずつ多くなります。預入額を複合参照にします。

f:id:waenavi:20190419101619j:plain

 

コピーします。定期支払額の払い戻しがそれぞれ6%ずつ増えます。最終的な払戻し額は約380万円となります。

f:id:waenavi:20190419101622j:plain

 

8.積立複利計算(月利)

問題

毎月10万円を1年間積み立てたとする。年利6%のときPV、PMT、FVはそれぞれ何円か。

f:id:waenavi:20190419093823j:plain

 

解説

積立預金の問題で最初に預け入れる額の記載がない場合は、預け入れないという意味であり現在価値は0円です。

f:id:waenavi:20190419102323j:plain

 

定期支払額は10万円です。

f:id:waenavi:20190419102326j:plain

 

毎月の「期末」で積み立てる場合、積立1回目の10万円は11か月預けることになりますが、その後預ける期間が1か月ずつ減っていきます。この支払は月単位です。月単位で積み立てをする場合、利率も月単位にします。年利が6%ならば、12で割って0.5%となります。

f:id:waenavi:20190419102331j:plain

f:id:waenavi:20190419102341j:plain

 

合計すると将来価値は+約123万円となります。

f:id:waenavi:20190419102343j:plain

 

期首の場合は約124万円となります。

f:id:waenavi:20190419102346j:plain

 


解説は以上です。借りすぎに注意しましょう。収入と支出のバランスを大切に。契約内容をご確認のうえ無理のない返済計画を。

 


9.動画版はこちら(無料)

この記事は、わえなび実力養成講座「Excel関数スタンダード演習」Program 1-1、1-2、1-3 の3本のYoutube動画を書き起こしたものです。

また、PV関数、PMT関数などの説明はこちらの動画をご覧ください。

 

 


Copyright(C)2018-2019 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]