エクセル中級 仮番5

Excelの基礎■

財務関数は、利息に基くローンや貯蓄、債券や証券の価格計算などをするための関数です。標準設定では16種類、アドイン関数を追加すると56種類になります。
財務関数の中には、かなり専門的なものも多く含まれています。財務関係の仕事に従事しない限り縁のない関数もありますので、一般的に使うものを取り上げ要点を解説します。

■PMT関数

PMT関数は、財務関数の代表的な関数です。利率が一定であると仮定して、借りたお金を期間内に返済するための返済額（ローンの返済額）を求めることができます。また逆に期間内に目標金額の預金をするための、積み立て額などを計算をすることもできる関数です。PMT関数の計算は元利金等方式です。

書式で表すと、PMT(利率, 期間, 現在価値, 将来価値, 支払期日)となります。
利率は、期間内の一定の利率を指定します。期間は、期間全体での支払回数の合計です。現在価値とは、ローンでは借入額、つまり将来行われる一連の支払いを現時点で一括払いした場合の金額です。預金では、元金になります。将来価値とは、ローンでは、最後の支払いを行った後に残る金額です。預金では、目標預金額になります。将来価値 を省略すると、0 (ゼロ) を指定したと見なされ、将来価値は 0 になります。支払期日は、支払いがいつ行われるかを数値で指定します。期首払いは「1」、期末払いは「0」です。省略すると期末払い「0」として計算されます。この期首払い、期末払いというのは、その月から支払う（期首払い）のか、翌月から（期末払い）支払うのかと言う意味です。通常ローンの場合は期末払いになり、預金の場合は、期首払いとなります。
引数の説明でだけでは面倒に思えますが、実例で理解してください。

◆使用例 １

下図は、ローンの返済額を求めたものです。100万円を年利8%で借りて、2年間で返済する場合、月々の返済額はいくらかを求めたものです。（元利金等返済）

引数の利率は、「C3/12」となっていて、年利8%を12で割って月利にしています。これは、月々の支払いを求めるための処理です。

また期間も「C4*12」となっていて、返済年数2年に12を掛け、月数（返済回数）を求めています。このように利率と期間の２つの引数は、単位を合わせておく必要があります。ここでは、月単位に合わせています。
現在価値は、「C2」で 1,000,000円、将来価値は、全額返済ですから「0」、支払期日は、期末支払いのため「0」としています。
PMT関数を使って求められる答えは、自動的に「\-45,227」と通貨の書式で負の記号で表示されます。これは、支払額のためこのように表示されるのです。表示形式を切り替えても負の記号は消えません。表示上、負の記号を消したいのであれば、「=PMT(C3/12,2*12,C2,0,0)*-1」のように「-1」倍してください。なお、「\-45,227」には、小数点以下の端数が存在します。自動的に四捨五入されていますので、実際のローン計算と微妙に異なる場合があります。端数の取扱いは、実際に利用するローンによって異なりますので注意してください。
「=PMT(C3/12,2*12,C2,0,0)」の式は、将来価値と支払期日の引数を省略して「=PMT(C3/12,2*12,C2)」とすることもできます。

◆使用例 2

下図は、積立額を求めたものです。積立目標額200万円、元金（手持ち金）30万、年利1.5%で積立期間を3年とし、月々いくら積み立てればよいかを求めたものです。（元利金等積立）

引数の利率や期間は、月単位の積立のため、返済の場合と同じく、月単位にあわせる処理をしています。

現在価値は、元金が30万円ありますので「-C3」としています。これはすでに、口座に30万円あることを前提とするためです。「-」とするのは変な感じですが、PMT関数が返済を基本にしたものなので、元金がある場合はこのように表示します。仮に、元金がなく 0 円からスタートする場合は、「0」とします。
将来価値は、目標額が200万円なので、「C2」としています。支払期日は、期首払いの「1」とします。というのも積立の場合はローン契約と異なり、一般的に直ぐ第1回目の積立をするからです。

■PPMT、IPMT関数

PPMT関数は、借入金の返済額のうち、元金部分を求めるための関数です。IPMT関数は、同じく借入金の返済額のうち、利息を求めるための関数です。両方とも利息が一定であることが条件です。

書式で表すと、PPMT(利率, 期, 期間, 現在価値, 将来価値, 支払期日)
　　　　　　　　　 IPMT(利率, 期, 期間, 現在価値, 将来価値, 支払期日)　となり、全く同じです。
引数のうちPMT関数と異なるのは、「期」だけです。あとは、PMT関数と同じですので、上段のPMT関数を参照してください。
引数の期とは、何回目の返済を計算するのかを指定します。当然のことですが、元利金等返済の場合、月々の返済額は同じでも、その返済額に含まれる元金分と利息分は毎月異なります。よってこの期で、何回目の返済を計算するのかを指定します。

◆使用例

下図は、借入金の元金返済額と利息分を求めたものです。借入金50万、年利8%、返済年数1年の場合の毎月の返済額に含まれる元金返済額と利息分を求めています。
毎月の返済額は、PMT関数で 「-43,494」を求めています。

図の表は、C9へPPMT関数で元金返済額を求め、D9へIPMT関数で利息を求めて、それをオートフィルで12回までコピーしたものです。 借入残高は、本来であれば「借入額−元金返済額」ですが、PPMT関数で求めた元金返済額が「-」で表示されるため、 E9へ「E8+C9」で求め、オートフィルで12回までコピーしています。

引数の期は、返済回数を指定しますから、相対参照で入力しています。他の引数の取扱い要領は、上段のPPM関数と同じです。

なお、この表は、元金返済額、利息分とも関数で求めていますが、どちらか一方を関数で求めれば片方は、返済額からの差し引きによって算出することができます。
すなわち、「元金返済額＝毎月の返済額−利息分」 、「利息分＝毎月の返済額−元金返済額」です。

「中級・特別編トップへ」