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

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

【Excel財務関数】PMT関数とは分割払いの1回あたりの支払金額を求める関数である


ExceのPMT関数の「PMT」とは分割払いの1回あたりの支払金額のことであり、支払いを表すペイメント(paymentの略です。一括払いの時にPMTを使うことはありません。分割払いにはいろんな種類があります。

  • 預金の場合・・・積立預金の1回あたりの積立金
  • 借入金の場合・・・分割返済金額
  • 貸し付けの場合・・・返済で受け取る金額
  • 投資の場合・・・配当金

言葉は違いますが、複利の計算方法は同じなのですべてPMT関数で求めることができます。ただし、プラスマイナスを間違えないように注意しなければなりません。そこで、今回は、定期支払額を求めるPMT関数について出題します。

f:id:waenavi:20190420205704j:plain

 

 

目次

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

1.現在価値と将来価値について

現在価値FV、将来価値PV、定期支払額、期首払い・期末払い、複利計算の詳しい説明についてはこちらの記事をご覧ください。

 

2.PMT関数とは何か

問題

年利0.6%で3年間積立をして、最終的に100万円にしたい場合、月々いくら積み立てたらよいか。

f:id:waenavi:20190420190733j:plain

 

解説

月々の積立金額である定期支払額を求めるにはPMT関数を使います。PMTとはPayment(ペイメント)、つまり支払いのことであり、定期的に支払う金額を求めているということを理解しなければなりません。

f:id:waenavi:20190420191544j:plain

 

積立の場合、最初にお金を預け入れるのであれば、それが現在価値です。頭金が無ければ0円です。支払う場合はマイナスです。定期的に積立をする金額が、定期支払額です。そして、将来価値は、将来満期になった時に受け取れる金額のことで、受け取りの場合はプラスです。

f:id:waenavi:20190420191548j:plain

 

PMT関数は、5つの引数がありますが、利率、期間、現在価値、支払期日は、FV関数と同じです。

f:id:waenavi:20190420191555j:plain

 

最初に預け入れる金額が無い(問題文に記載がない)ので、現在価値PVは0円です。将来価値は積立によって最終的に貯めたい目標金額のことなので、プラス100万円です。支払回数は3年間で3*12=36回払いです。

f:id:waenavi:20190420191558j:plain

 

財務関数の中から、PMT関数を表示します。

f:id:waenavi:20190420191601j:plain

 

毎月の積立の場合、利率は年利0.006の12分の1です。0.6%/12です。期間とは支払い回数のことです。支払期日は省略です。

f:id:waenavi:20190420191605j:plain

 

月々の支払額は27535円となります。支払額なのでマイナスで表示されます。

f:id:waenavi:20190420191609j:plain

 

期首の場合は支払期日を1にします。

f:id:waenavi:20190420191612j:plain

 

期首払いのほうが利息が多くつくので支払額が少し減ります。

f:id:waenavi:20190420191616j:plain

 

3.積立(頭金)

問題

最初に10万円を預け入れた後、目標金額50万円を目指して年利0.6%で5年間、半年ごとに積立をする場合、積立金額はいくらか。

f:id:waenavi:20190420190810j:plain

 

解説

現在価値は、最初に10万円を頭金として預け入れる支払金額(支出)なのでマイナス10万円にします。将来価値は積立の目標金額であり、満期に払い戻されるお金(収入)なのでプラス50万円です。半年ごとに5年間積み立てをするので、支払回数は10回です。

f:id:waenavi:20190420192234j:plain

 

PMT関数を表示します。利率は、半年分なので年利6%の半分です。期間は10回です。

f:id:waenavi:20190420192237j:plain

 

期首、期末が1と0で入力できるようになっている場合は、セルを参照します。

f:id:waenavi:20190420192239j:plain

 

4.積立(絶対参照)

問題

次の図で期末払いで積み立てた場合の月々の積立額を求めなさい。

f:id:waenavi:20190420190835j:plain

 

解説

上の2つのデータは絶対参照にします。

f:id:waenavi:20190420192618j:plain

 

PMT関数を表示します。毎月の積立の場合、利率は年利の12分の1です。期間は5年とあるので5*12で60回払いです。

f:id:waenavi:20190420192819j:plain

 

現在価値はマイナス10万円です。将来価値は300万円です。

f:id:waenavi:20190420192621j:plain

 

支払期日は期末払いなので省略です。

f:id:waenavi:20190420192626j:plain

 

5.積立(複合参照)

問題

次の図で積み立てた場合の月々の積立金額を求めなさい。

f:id:waenavi:20190420193006j:plain

 

解説

月々の積立金額を求めるにはPMT関数を使います。このとき、数式はオートフィルをしますので、上に入力されているものは絶対参照、表の項目になっているものは複合参照にします。

f:id:waenavi:20190420193428j:plain

f:id:waenavi:20190420193431j:plain

 

利率は絶対参照にしてから12で割ります。現在価値は0円です。将来価値は目標金額のことです。

f:id:waenavi:20190420193436j:plain

 

これで完成です。

f:id:waenavi:20190420193441j:plain

 

6.積立パターンの注意点

問題

次の表で期首払いで積み立てをする場合、月々の預入金額をプラスにした額(円)を求めなさい。

f:id:waenavi:20210222213757j:plain

 

解説

PMT関数を表示します。月々の支払なので利率は年利の12分の1です(B7/12)。期間は、5年間の12倍をして60回払いとなります($C$3*12)。絶対参照です。

f:id:waenavi:20210222214502j:plain

 

<補足>年利の数値にパーセントがついていない場合はさらに100で割る必要がありますので注意が必要です(後述)。

f:id:waenavi:20210222215232j:plain

 

現在価値は頭金なので15万円ですが、これは支払なのでマイナスを付けなければなりません(PV=-150000)。絶対参照です。

f:id:waenavi:20210222214535j:plain

 

将来価値は300ではなく300万円なので10000倍をします。絶対参照にします($C$4*10000)。受取額なのでプラスで良いです。

f:id:waenavi:20210222214608j:plain

 

最後の支払期日は期首払いなので1にします。

f:id:waenavi:20210222214629j:plain

 

月々の積立金額は、支払額なのでマイナスで表示されます。問題文に特に指示がなければマイナスの金額になっているのが正解です。

  • =PMT(B7/12,$C$3*12,-$C$2,$C$4*10000,1)

f:id:waenavi:20210222214655j:plain

 

これを、あえてプラスで表示するのであれば、数式の先頭にマイナスを付けます。これで完成です。

  • =PMT(B7/12,$C$3*12,-$C$2,$C$4*10000,1)

f:id:waenavi:20210222214744j:plain

 

7.借入と返済(基本)

問題

年利4.2%で100万円を借り入れた。3年間で全額返済する場合、月々の返済金額を求めなさい。

f:id:waenavi:20190420194316j:plain

 

解説

ローンで借り入れて、毎回同じ金額を支払って返済する場合、その支払額は定期支払額であり、PMT関数を使って求めます。

f:id:waenavi:20190420195349j:plain

 

現在価値は借り入れた金額のことです。借りた時点ではお金が増えます。収入なのでプラスです。ただし、定期的に返済するお金の他に最初に支払う頭金があったらその金額を差し引きます。

将来価値とは、残っている借入金のことです。通常、借金を残すことなく完済するので将来価値は0円です。ただし、分割払いをした後で借入残高を残す場合は、最後に残った借入金を一括返済することになります。この金額は最終的な支出になりますのでマイナスです。 

これによって求められる定期支払額は、毎回の返済金額であり支払なのでマイナスとなるはずです。

f:id:waenavi:20190420195352j:plain

 

毎月の返済の場合、利率は年利の12分の1です。期間とは支払い回数のことです。3年間で3*12=36回払いです。最初に100万円借り入れていますが、この借り入れは収入なので、現在価値PVはプラス100万円です。将来価値は最終的な借入残高ですが、問題文に特に指示がなければ完済するという意味なので、将来価値は0円です。0円の場合は省略することができます。

f:id:waenavi:20190420195355j:plain

 

月々の返済額PMTは月々の支払(支出)なので、マイナスになります。

f:id:waenavi:20190420195358j:plain

 

期首払いの場合は支払期日を1にします。

f:id:waenavi:20190420195403j:plain

f:id:waenavi:20190420195409j:plain

 

8.借入と返済(頭金)

問題

住宅を購入するため、期末払いで5000万円のローンを組んだ。次の図で月々の返済金額を求めなさい。

f:id:waenavi:20190420194400j:plain

 

解説

PMT関数を表示します。毎月の返済の場合、利率は年利の12分の1です。期間は、30年の12倍で360回払いです。

f:id:waenavi:20190420195806j:plain

 

住宅ローンはいったん5000万円の入金があって、それを住宅の購入にあてたものと考えます。

f:id:waenavi:20190420195809j:plain

 

したがって、この借り入れはプラス5000万円の収入です。しかし、頭金として1000万円を支払っており、実質4000万円を返済すればよいので現在価値PVはプラス4000万円です。

f:id:waenavi:20190420195813j:plain

 

将来価値と支払期日は省略できます。

f:id:waenavi:20190420195816j:plain

 

9.借入と返済(複合参照)

問題

次の図で期末払いの場合で毎月返済する場合の、支払額を求めなさい。

f:id:waenavi:20190420194425j:plain

 

解説

数式はオートフィルをしますので、上に入力されているものは絶対参照、表の項目になっているものは複合参照にします。

f:id:waenavi:20190420200018j:plain

f:id:waenavi:20190420200022j:plain

 

支払年数は数式バーで数値が表示されていることから、数値データに表示形式が設定されていることが分かります。数値データは引数として使えます。

f:id:waenavi:20190420200026j:plain

 

PMT関数を表示します。毎月の返済の場合、利率は年利の12分の1です。支払年数の12倍です。

f:id:waenavi:20190420200030j:plain

 

将来価値と支払期日は省略できます。

f:id:waenavi:20190420200033j:plain

 

10.借入と返済パターンの注意点

問題

次の表で借り入れる場合、月々の返済金額をプラスにした額(円)を求めなさい。

f:id:waenavi:20190420200416j:plain

 

解説

数式はオートフィルをしますので、上に入力されているものは絶対参照、表の項目になっているものは複合参照にします。

f:id:waenavi:20190420200830j:plain

f:id:waenavi:20190420200834j:plain

 

年利がパーセンテージではなく通常の数値になっていること、借入金が100万円の単位になっていることに注意します。

f:id:waenavi:20190420200837j:plain

f:id:waenavi:20190420200840j:plain

 

また、支払期日は期首と期末が選べるようになっています。

f:id:waenavi:20190420200843j:plain

 

PMT関数を表示します。毎月の返済の場合、利率は年利の12分の1ですが、パーセンテージではないので100で割ります。複合参照です。期間は、支払年数の12倍で、絶対参照です。

f:id:waenavi:20190420200845j:plain

 

現在価値は借入金ですが、受取金額なのでプラスにしなければなりません。先頭にマイナスを付けて100万倍にします。複合参照です。将来価値は省略できます。

f:id:waenavi:20190420200849j:plain

 

期首の時に1にするには比較演算子を使うかIF関数を使います。

f:id:waenavi:20190420200853j:plain

 

返済額は月々の支払(支出)なので、マイナスになるのが正しいのですが、あえてプラスにするには数式の先頭にマイナスを付けます。これで完成です。

f:id:waenavi:20190420200857j:plain

f:id:waenavi:20190420200900j:plain

 

11.貸付金

問題

100万円貸してほしいと頼まれたので、年利5%、10回分割、月末返済の条件で貸し付けた。毎月の受取額を求めなさい。

f:id:waenavi:20190420201337j:plain

 

解説

一般の個人や企業がお金を貸し付けることはそんなにないと思いますが、借入返済とプラスマイナスが逆になるだけで考え方は同じです。現在価値は貸し付けた金額でマイナスです。月々の返済で受け取る金額はプラスで、最後に一括返済がある場合は将来価値としてプラスの受け取りとなります。

f:id:waenavi:20190420201737j:plain

 

PMT関数を表示します。毎月の返済の場合、利率は年利の12分の1です。期間とは支払い回数のことです。10回払いです。現在価値は貸付金のことですが、支出なのでマイナスを付けなければなりません。将来価値は0円です。0円の場合は省略することができます。支払期日は期末払いなので省略できます。

f:id:waenavi:20190420201741j:plain

 

返済額は月々の受取額なので、プラスになります。

f:id:waenavi:20190420201744j:plain

 

12.ボーナス払い

問題

年利4.2%で500万円を借り入れた。そのうち100万円をボーナス2回払いとし、5年間、期末払いで完済する場合、月々の返済金額とボーナス払いの金額を求めなさい。

f:id:waenavi:20190420201406j:plain

 

解説

ボーナス併用払いとは、借入金を通常の返済とボーナス払いの返済に分け、通常の返済は月々支払い、ボーナス払いはボーナスが出た後に支払う分割の方法です。つまり、支払回数の異なる2種類の借り入れをしたのと同じです。

f:id:waenavi:20190420202205j:plain

 

PMT関数を表示します。毎月の返済の場合、利率は年利の12分の1です。期間とは支払い回数のことです。支払年数の12倍です。現在価値は借入金のうち、ボーナス払いをしない400万円です。

f:id:waenavi:20190420202208j:plain

 

将来価値と支払期日は省略できます。

f:id:waenavi:20190420202212j:plain

 

もう一度、PMT関数を表示します。年2回払いの場合、利率は年利の半分です。支払い回数は支払年数の2倍です。現在価値はボーナス払いの100万円です。

f:id:waenavi:20190420202214j:plain

f:id:waenavi:20190420202219j:plain

 

13.配当金

問題

100万円を出資して10年後に120万円が払い戻され、さらに毎年配当金が得られる場合、年利6%として毎年得られる配当金はいくらか。

f:id:waenavi:20190420201435j:plain

 

解説

投資または有価証券の購入などで出費して、その配当を毎年得る場合、その受取額は定期支払額PMTであり、PMT関数を使って求めます。現在価値は最初に出費をする金額で支出です。その後、利息や運用利益などの配当金を毎年受け取って、最後に預けたお金を返してもらうというものです。最後の払い戻しが将来価値です。

f:id:waenavi:20190420202449j:plain

 

年1回の受取なので利率は6%です。期間とは支払い回数のことです。配当を10年間で10回受け取ります。最初に100万円出費(支出)しているので、現在価値PVはマイナス100万円です。満期になったら120万円が払い戻される(収入)ので、将来価値FVはプラス120万円です。

f:id:waenavi:20190420202451j:plain

f:id:waenavi:20190420202457j:plain 

 


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


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

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

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

 

 


Copyright(C)2018-2021 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]
Youtube | 公式サイト | twitter | 著作権 | 運営者情報 | お問い合わせ