【エクセル】IPMT関数の使い方_ローン返済額の利息部分の算出

エクセルで、ローン返済額の利息部分を算出するのが『IPMT関数』です。

下図を用いて、IPMT関数の使い方を解説します。

目次

• ローンの返済方法の種類
  
• IPMT関数の構文
• IPMT関数の使い方
• 元利均等返済の利息部分の計算式
• ローンの利息部分の算出時はIPMT関数を使う

ローンの返済方法の種類

ローンの返済方法には、「元利均等返済」と「元金均等返済」の2種類があります。

元利均等返済とは、毎月の返済額が一定になる返済方法を指します。

返済額は一定になりますが、元金部分の返済スピードが遅いため、元金均等返済と比較すると、返済総額が多くなります。

元金均等返済とは、毎月の元金の返済額が一定になる返済方法を指します。

元利均等返済と比較すると、前半の返済額は大きいですが、後半になるにつれて返済額が小さくなり、返済総額は少なくなります。

IPMT関数の構文

次に、IPMT関数の構文は、次のようになっております。

=IPMT(利率,期,期間,現在価値,将来価値,支払期日)

エクセル関数を使う時は、2つの使い方があります。

1つ目は、黄色の○で囲っている「fxをクリック→すべて表示を選択→該当関数を選択→OK」と入力後に、入力内容を指示した枠が表示されるので、そこに入力することです。

2つ目は、緑色の枠に構文を直接入力となります。

IPMT関数の使い方

次に、「条件1：ローンの定期支払額のうち利息部分をIPMT関数を使い算出」の時の構文です。

H3セルに入力する時の関数は下記になります。

=IPMT(B3÷12,1,C3×12,D3,0)

利率：期間あたりの利率(年単位ならば年利率、月単位ならば月利率)

期：元金支払額を求める期

期間：返済回数

現在価値：返済前の金額

将来価値：返済後の金額

※IPMT関数は、手元に入る金額が「+」表記、手元から出る金額が「-」表記で記載されるため、返済額がマイナス表記されています。

プラス表記にしたい場合は、関数の設定時に、「=IPMT」を「=-IPMT」とします。

※将来価値の後に、支払期日の入力が出来ますが、期末支払の前提で省略しています。

例題は、元利均等返済時の毎月の返済額のうち、利息部分の算出となります。

240,000円の借入金額を、年利0％で、2年後に、返済する場合の毎月の返済額のうち、利息部分は、0円

240,000円の借入金額を、年利3％で、2年後に、返済する場合の毎月の返済額のうち、利息部分は、600円

240,000円の借入金額を、年利5％で、2年後に、返済する場合の毎月の返済額のうち、利息部分は、1,000円

元利均等返済の利息部分の計算式

元利均等返済の利息部分の算出数式は下記の通りです。

利息=現在価値×月利

この数式を、先程の例題に照らし合わせると

240,000円の借入金額を、年利0％で、2年後に、返済する場合の毎月の返済額の利息部分は、年利が0％のため、0円

240,000円の借入金額を、年利3％で、2年後に、返済する場合の毎月の返済額の利息部分は、240,000円×3％÷12ヶ月=600円

240,000円の借入金額を、年利5％で、2年後に、返済する場合の毎月の返済額の利息部分は、240,000円×5％÷12ヶ月=1,000円

となり、IPMT関数で設定を正しく行うと、自動計算が出来ることが分かります。

ローンの利息部分の算出時はIPMT関数を使う

ローンの支払方法を検討する時に、利息の大きさは重要な判断材料になります。

実際の返済額に何円利息が含まれているかは、業者の見積もりを取らないと、明らかになりませんが、返済額のシミュレーションをしたい時は、IPMT関数は有効な確認手段となります。

利息部分の目星を付けたい時は、IPMT関数を使ってみましょう。

【エクセル】PMT関数の使い方_ローン返済額の算出

エクセルで、ローンの定期支払額を算出するのが『PMT関数』です。

下図を用いて、PMT関数の使い方を解説します。

目次

• ローンの返済方法の種類
• PMT関数の構文
• PMT関数の使い方
• 元利均等返済の計算式
• 元利均等返済の定期支払額の算出時はPMT関数を使う

ローンの返済方法の種類

ローンの返済方法には、「元利均等返済」と「元金均等返済」の2種類があります。

元利均等返済とは、毎月の返済額が一定になる返済方法を指します。

返済額は一定になりますが、元金部分の返済スピードが遅いため、元金均等返済と比較すると、返済総額が多くなります。

元金均等返済とは、毎月の元金の返済額が一定になる返済方法を指します。

元利均等返済と比較すると、前半の返済額は大きいですが、後半になるにつれて返済額が小さくなり、返済総額は少なくなります。

PMT関数の構文

次に、PMT関数の構文は、次のようになっております。

=PMT(利率,期間,現在価値,将来価値,支払期日)

エクセル関数を使う時は、2つの使い方があります。

1つ目は、黄色の○で囲っている「fxをクリック→すべて表示を選択→該当関数を選択→OK」と入力後に、入力内容を指示した枠が表示されるので、そこに入力することです。

2つ目は、緑色の枠に構文を直接入力となります。

PMT関数の使い方

次に、「条件1：ローンの定期支払額をPMT関数を使い算出」の時の構文です。

H3セルに入力する時の関数は下記になります。

=PMT(B3,C3,D3,E3,F3)

利率：期間あたりの利率(年単位ならば年利率、月単位ならば月利率)

期間：返済期間

現在価値：返済前の金額

将来価値：返済後の金額

支払期日：支払いがいつ行われるか(期首ならば1、期末ならば0)

※PMT関数は、手元に入る金額が「+」表記、手元から出る金額が「-」表記で記載されるため、返済額がマイナス表記されています。

プラス表記にしたい場合は、関数の設定時に、「=PMT」を「=-PMT」とします。

例題は、元利均等返済時の毎月の返済額の算出となります。

240,000円の借入金額を、年利0％で、2年後に、返済する場合の毎月の返済額は、10,000円

240,000円の借入金額を、年利3％で、2年後に、返済する場合の毎月の返済額は、10,315円

240,000円の借入金額を、年利5％で、2年後に、返済する場合の毎月の返済額は、10,529円

元利均等返済の計算式

毎月の返済額の算出数式は下記の通りです。

毎月の返済額={借入金額×月利×(1+月利)^返済回数}÷{-1+(1+月利)^返済回数}・・・(^は累乗)

この数式を、先程の例題に照らし合わせると

240,000円の借入金額を、年利0％で、2年後に、返済する場合の毎月の返済額は、年利が0％のため、利率を考慮する必要がありませんので、240,000円÷24ヶ月=10,000円

240,000円の借入金額を、年利3％で、2年後に、返済する場合の毎月の返済額は、{240,000円×(3％÷12ヶ月)×(1+(3％÷12ヶ月)^24}÷{-1+(1+(3％÷12ヶ月)^24}=10,315円

240,000円の借入金額を、年利5％で、2年後に、返済する場合の毎月の返済額は、{240,000円×(5％÷12ヶ月)×(1+(5％÷12ヶ月)^24}÷{-1+(1+(5％÷12ヶ月)^24}=10,529円

となり、PMT関数で設定を正しく行うと、自動計算が出来ることが分かります。

元利均等返済の定期支払額の算出時はPMT関数を使う

元利均等返済での毎月の支払額は、住宅購入を検討している人は、支払方法を検討する時に、目にする機会があると思います。

実際の返済額は、業者の見積もりを取らないと、明らかになりませんが、返済額のシミュレーションをしたい時は、有効な関数となります。

毎月の返済額の目星を付けたい時は、PMT関数を使ってみましょう。

【エクセル】PERMUTATIONA関数の使い方_重複順列の算出

エクセルで、指定した数値の重複を許した順列を算出するのが『PERMUTATIONA関数』です。

下図を用いて、PERMUTATIONA関数の使い方を解説します。 

目次

• 重複順列とは
• PERMUTATIONA関数の構文
• PERMUTATIONA関数の使い方
• 0の重複順列が1となる理由
• 重複順列を計算する場合はPERMUTATIONA関数を使う

重複順列とは

異なるn個からr個を取り出した時に重複も含めた並べ方を指します。

順列との違いは、並べ方に重複を許すことです。

PERMUTATIONA関数で扱える数値は、正の整数のみとなります。

PERMUTATIONA関数の構文

次に、PERMUTATIONA関数の構文は、次のようになっております。

=PERMUTATIONA(数値,抜取数)

エクセル関数を使う時は、2つの使い方があります。

1つ目は、黄色の○で囲っている「fxをクリック→すべて表示を選択→該当関数を選択→OK」と入力後に、入力内容を指示した枠が表示されるので、そこに入力することです。

2つ目は、緑色の枠に構文を直接入力となります。

PERMUTATIONA関数の使い方

次に、「条件1：PERMUTATIONA関数を使い重複順列の値を算出」の時の構文です。

E3セルに入力する時の関数は下記になります。

=PERMUTATIONA(B3,C3)

数値：全体の数

抜取数：全体から取り出す数

No.1について、3個から2個を取り出し、重複を許した並べ方になるので、3×3=9

No.2について、6個から3個を取り出し、重複を許した並べ方になるので、6×6×6=216

No.3について、5個から4個を取り出し、重複を許した並べ方になるので、5×5×5×5=625

No.4について、5個から5個を取り出し、重複を許した並べ方になるので、5×5×5×5×5=3,125

No.5について、6個から4個を取り出し、重複を許した並べ方になるので、6×6×6×6=1,296

No.6について、7個から0個を取り出す時は、1

0の重複順列が1となる理由

No6で、0の重複順列が1になるとPERMUTATIONA関数で算出された理由は、下記になります。

重複順列とは、全体の数のうち指定された数を取り出し、重複が許された並びを指しますが、取り出す数が「0」ということは、何も取り出さないということになります。

何も取り出さないという選択肢は、1通りしかないため、取り出す数が「0」の時は、「1」が答えと定義されています。

重複順列を計算する場合はPERMUTATIONA関数を使う

社会人になると、重複順列を計算する機会は滅多にないと思いますが、いざ計算となると、数が大きいほど苦戦することは必須です。

電卓を用いて、計算することもできますが、数が大きいほど手間が掛かります。

その点、PERMUTATIONA関数を使えば、すぐに算出することが出来ます。

使い方もすぐに分かる内容となっていますので、もし、滅多にない重複順列の計算をしなければならなくなった時は、PERMUTATIONA関数を使って対処してください。

【エクセル】ISOWEEKNUM関数の使い方

エクセルで、日付のISO週番号を表示するのが『ISOWEEKNUM関数』です。

下図を用いて、ISOWEEKNUM関数の使い方を解説します。

目次

• ISO週番号とは
• WEEKNUM関数の構文
• WEEKNUM関数の使い方

ISO週番号とは

ISO8601で定義されている週の数え方を指します。

月曜日を週の始まりと考え、年の第1週は木曜日が含まれている週とされています。

ISOWEEKNUM関数の構文

次に 、ISOWEEKNUM関数の構文は、次のようになっております。

=ISOWEEKNUM(日付)

エクセル関数を使う時は、2つの使い方があります。

1つ目は、黄色の○で囲っている「fxをクリック→すべて表示を選択→該当関数を選択→OK」と入力後に、入力内容を指示した枠が表示されるので、そこに入力することです。

2つ目は、緑色の枠に構文を直接入力となります。

ISOWEEKNUM関数の使い方

次に、「条件1：ISOWEEKNUM関数でISO週番号を算出」の時の構文です。

C3セルに入力する時の関数は下記になります。

=ISOWEEKNUM(A3)

日付：ISO週番号を確認する日付

日付「2021/5/9」は、18週が答えとなります。

内訳は、1月は4週、2月は4週、3月は5週、4月は4週、5月は1週なので、4+4+5+4+1=18となります。

日付「2022/7/10」は、27週が答えとなります。

内訳は、1月は5週、2月は4週、3月は4週、4月は4週、5月は5週、6月は4週、7月は1週なので、5+4+4+4+5+4+1=27となります。

日付「2022/8/20」は、33週が答えとなります。

内訳は、1月は5週、2月は4週、3月は4週、4月は4週、5月は5週、6月は4週、7月は4週、8月は3週なので、5+4+4+4+5+4+4+3=33となります。

日付「2020年8月31日」は、36週が答えとなります。

1月は5週、2月は4週、3月は5週、4月は4週、5月は4週、6月は5週、7月は4週、8月は5週なので、5+4+5+4+4+5+4+5=36となります。

日付「2021年4月13日」は、15週が答えとなります。

内訳は、1月は4週、2月は4週、3月は5週、4月は2週なので、4+4+5+2=15となります。

日付「2022年1月8日」は、1週が答えとなります。

内訳は、1月6日が2022年の最初の木曜日になることから、1月は1週なので、答えは1週となります。

日付については、「Y/M/D」表示と「年月日」表示のどちらでも、関数で読み取ることが出来ますので、使いやすい方で一覧を作成してください。

ISO番号で何週目であるかの算出は、カレンダーを捲り調べると、手間が掛かりますので、ISOWEEKNUM関数で算出するようにしましょう。

【エクセル】PERMUT関数の使い方_順列の算出

エクセルで、指定した数値の順列を算出するのが『PERMUT関数』です。

下図を用いて、PERMUT関数の使い方を解説します。 

目次

• 順列とは
• PERMUT関数の構文
• PERMUT関数の使い方
• 0の順列が1となる理由
• 順列を計算する場合はPERMUT関数を使う

順列とは

異なるn個からr個を取り出した時の重複のない並べ方を指します。

4個から2個を取り出す時は、₄P₂と表わし、4×3=12となります。

PERMUT関数で扱える数値は、正の整数のみとなります。

PERMUT関数の構文

次に、PERMUT関数の構文は、次のようになっております。

=PERMUT(標本数,抜取数)

エクセル関数を使う時は、2つの使い方があります。

1つ目は、黄色の○で囲っている「fxをクリック→すべて表示を選択→該当関数を選択→OK」と入力後に、入力内容を指示した枠が表示されるので、そこに入力することです。

2つ目は、緑色の枠に構文を直接入力となります。

PERMUT関数の使い方

次に、「条件1：PERMUT関数を使い順列の値を算出」の時の構文です。

E3セルに入力する時の関数は下記になります。

=PERMUT(B3,C3)

標本数：全体の数

抜取数：全体から取り出す数

No.1について、3個から2個を取り出す時は、₃P₂と表わし、3×2=6

No.2について、6個から3個を取り出す時は、₆P₃と表わし、6×5×4=120

No.3について、5個から4個を取り出す時は、₅P₄と表わし、5×4×3×2=120

No.4について、5個から5個を取り出す時は、₅P₅と表わし、5×4×3×2×1=120

No.5について、6個から4個を取り出す時は、₆P₄と表わし、6×5×4×3=360

No.6について、7個から0個を取り出す時は、₇P₀と表わし、1

0の順列が1となる理由

No6で、0の順列が1になるとPERMUT関数で算出された理由は、下記になります。

順列とは、全体の数のうち指定された数を取り出し、順番に並べることを指しますが、取り出す数が「0」ということは、何も取り出さないということになります。

何も取り出さないという選択肢は、1通りしかないため、取り出す数が「0」の時は、「1」が答えと定義されています。

順列を計算する場合はPERMUT関数を使う

社会人になると、順列を計算する機会は滅多にないと思いますが、いざ計算となると、数が大きいほど苦戦することは必須です。

電卓を用いて、計算することもできますが、数が大きいほど手間が掛かります。

その点、PERMUT関数を使えば、すぐに算出することが出来ます。

使い方もすぐに分かる内容となっていますので、もし、滅多にない順列の計算をしなければならなくなった時は、PERMUT関数を使って対処してください。

【エクセル】WEEKNUM関数の使い方

エクセルで、日付がその年の何週目かを表示するのが『WEEKNUM関数』です。

下図を用いて、WEEKNUM関数の使い方を解説します。

目次

• WEEKNUM関数の構文
• WEEKNUM関数の使い方
• WEEKNUM関数の他の種類

WEEKNUM関数の構文

 まず、WEEKNUM関数の構文は、次のようになっております。

=WEEKNUM(シリアル値,週の基準)

エクセル関数を使う時は、2つの使い方があります。

1つ目は、黄色の○で囲っている「fxをクリック→すべて表示を選択→該当関数を選択→OK」と入力後に、入力内容を指示した枠が表示されるので、そこに入力することです。

2つ目は、緑色の枠に構文を直接入力となります。

WEEKNUM関数の使い方

次に、「条件1：WEEKNUM関数を使いその年の何週目であるかを算出」の時の構文です。

D3セルに入力する時の関数は下記になります。

=WEEKNUM(B3,A3)

シリアル値：数値に変換する日付文字列

週の基準：週の始まりを何曜日とするかの選択

WEEKNUM関数に入力する週の基準には、いくつか種類があり、例題では「基準1」と「基準2」を採用しています。

「基準1」は、1月1日を第1週とし、日曜日を年間で何回迎えたかをカウントしています。

「基準2」は、1月1日を第1週とし、月曜日を年間で何回迎えたかをカウントしています。

戻り値の返し方が分かったところで、例題の答えを算出してみます。

日付「2021/5/9」は、基準「1」を選択しています。

1月は6週、2月は4週、3月は4週、4月は4週、5月は2週なので、答えは20週

日付「2022/7/10」は、基準「2」を選択しています。

1月は6週、2月は4週、3月は4週、4月は4週、5月は5週、6月は4週、7月は1週なので、答えは28週

日付「2022/8/20」は、基準「1」を選択しています。

1月は6週、2月は4週、3月は4週、4月は4週、5月は5週、6月は4週、7月は4週、8月は3週なので、答えは34週

日付「2020年8月31日」は、基準「2」を選択しています。

1月は5週、2月は4週、3月は5週、4月は4週、5月は4週、6月は5週、7月は4週、8月は5週なので、答えは36週

日付「2021年4月13日」は、基準「1」を選択しています。

1月は6週、2月は4週、3月は4週、4月は2週なので、答えは16週

日付「2022年1月8日」は、基準「2」を選択しています。

1月は2週なので、答えは2週

WEEKNUM関数の他の種類

WEEKNUM関数では、種類に応じて同じ日付でも、違う値が表示されると解説しました。

例題では、1と2の基準を選んだ場合の戻り値を解説しましたが、基準は下図の通り、多様に用意されています。

種類の中に「11」～「17」が存在することで、好きな曜日を軸にした週のカウントが可能となります。

WEEKNUM関数は、カレンダーをめくり、日付が何週目に該当するか数える手間を無くすことが出来ますので、ぜひ覚えておきましょう。

【エクセル】TIMEVALUE関数の使い方

エクセルで、文字列の時刻を数値に変換するのが『TIMEVALUE関数』です。

下図を用いて、TIMEVALUE関数の使い方を解説します。

目次

• TIMEVALUE関数の構文
• TIMEVALUE関数の使い方
• 時刻文字列を選択してもエラーが発生する場合

TIMEVALUE関数の構文

 まず、TIMEVALUE関数の構文は、次のようになっております。

=TIMEVALUE(時刻文字列)

エクセル関数を使う時は、2つの使い方があります。

1つ目は、黄色の○で囲っている「fxをクリック→すべて表示を選択→該当関数を選択→OK」と入力後に、入力内容を指示した枠が表示されるので、そこに入力することです。

2つ目は、緑色の枠に構文を直接入力となります。

TIMEVALUE関数の使い方

次に、「条件1：TIMEVALUE関数を使い文字列の時刻を数値に変換」の時の構文です。

D3セルに入力する時の関数は下記になります。

=TIMEVALUE(B3)

時刻文字列：数値に変換する時刻文字列

TIMEVALUE関数は、「0時0分1秒」という時刻の文字列を「0.000011～」という数値に変換する関数です。

※「0.000011～」をエクセルで計算できる範囲で表示すると、0.000011574074074となります。

0時01分0秒ならば数値の「0.000694～」に、1時00分0秒ならば数値の「0.041666～」に変換されます。

上記を踏まえた例題の回答は、次の通りです。

No.1は、7:18:32なので、0.304537となります。(小数点第6位まで表示)

算出式は、(0.000011～×60分×60秒×7時間)+(0.000011～×60秒×18分)+(0.000011～×32秒)となります。

No.2は、9:32:05なので、0.397280となります。(小数点第6位まで表示)

算出式は、(0.000011～×60分×60秒×9時間)+(0.000011～×60秒×32分)+(0.000011～×5秒)となります。

No.3は、13:35:50なので、0.566551となります。(小数点第6位まで表示)

算出式は、(0.000011～×60分×60秒×13時間)+(0.000011～×60秒×35分)+(0.000011～×50秒)となります。

No.4は、16時02分14秒なので、0.668218となります。(小数点第6位まで表示)

算出式は、(0.000011～×60分×60秒×16時間)+(0.000011～×60秒×2分)+(0.000011～×14秒)となります。

No.5は、20時40分22秒なので、0.861366となります。(小数点第6位まで表示)

算出式は、(0.000011～×60分×60秒×20時間)+(0.000011～×60秒×40分)+(0.000011～×22秒)となります。

No.6は、0時20分13秒なので、0.014039となります。(小数点第6位まで表示)

算出式は、(0.000011～×60分×60秒×0時間)+(0.000011～×60秒×20分)+(0.000011～×13秒)となります。

時刻文字列を選択してもエラーが発生する場合

TIMEVALUE関数を使う時に、時刻文字列を選んだが、エラーと算出される場合は、選んだ時刻が文字列と認識されていませんので、下図を試してみてください。

時刻文字列については、「：」を用いての時刻表示でも、「～時～分～秒」の時刻表示でも認識されましたので、好きな方を使ってください。