=PMT(B2/12,B3*12,-B1)は、B1の借入をB2の年利でB3年かけて返すときの、毎月の返済額を返します。返済が毎月なので、利率を12で割り、年数に12を掛けています。
| A | B | |
|---|---|---|
| 1 | Loan amount | $250,000 |
| 2 | Annual rate | 6.5% |
| 3 | Years | 30 |
| 4 | ||
| 5 | Monthly payment | $1,580.17 |
| 6 | Total paid | $568,861.22 |
| 7 | Total interest | $318,861.22 |
250,000を年6.5%で30年借りると毎月$1,580.17かかり、全期間の利息は$318,861.22で、借入額そのものより多くなります。B3を15に変えると、返済額は$2,177.77に上がりますが、総利息は半分未満に下がります。
PMT関数の構文
=PMT(rate, nper, pv, [fv], [type])
rate(利率): 1期間あたりの利率です。年利で毎月返済するならrate/12を使います。nper(期間): 返済の回数です。30年の毎月返済なら30*12、つまり360です。pv(現在価値): 現在価値、つまり借りた金額です。fv(将来価値、省略可): 最後に残る金額です。全額を返すローンなら0で、これが既定です。積立計画なら積立の目標額です。type(支払期日、省略可): 0(既定)は各期間の終わりの支払いで、ほとんどのローンがこれです。1は各期間の始めで、家賃やリースがこれです。
PMTは利率が一定で返済額が均等であることを前提にしています。貸し手が住宅ローンの返済に加える税金、保険、手数料は含まれません。
PMTがマイナスの数を返す理由
Excelの財務関数(PMT、PV、FV、NPV、IRR)は、数の符号でお金の向きを表します。受け取るお金はプラス、支払うお金はマイナスです。借入は受け取るお金なので、=PMT(B2/12,B3*12,B1)は-1,580.17を返します。出ていく返済です。
このページの数式に-B1があるのはそのためです。借入額をマイナスで入れると、返済額がプラスで出てきます。=-PMT(B2/12,B3*12,B1)でも同じです。どちらかを選んでどこでもそれを使ってください。同じ符号の規則がfvも決めるからです。受け取ることになる積立の目標額は、現在価値を0にして、マイナスの将来価値で入れます。
ローンの期間を比べる
期間を列に並べ、金額と利率を絶対参照にし、数式を下にコピーすれば、返済額と総コストを並べて比べられます。
| A | B | C | |
|---|---|---|---|
| 1 | Amount | $28,000 | |
| 2 | Rate | 7.9% | |
| 3 | Years | Monthly | Total interest |
| 4 | 3 | $876.13 | $3,540.58 |
| 5 | 4 | $682.25 | $4,747.92 |
| 6 | 5 | $566.40 | $5,984.00 |
| 7 | 6 | $489.56 | $7,248.66 |
ローンを3年から6年に延ばすと、返済額は$876.13から$489.56に下がりますが、利息は$3,540.58から$7,248.66に増えます。$記号については絶対参照を参照してください。
毎月いくら積み立てるか
pvを0にして将来価値を指定すると、PMTは逆の問いに答えます。目標に届くには毎月いくら積み立てればよいかです。目標額は受け取ることになるお金なので、積立額をプラスにするためにマイナスの数で入れます。
| A | B | |
|---|---|---|
| 1 | Goal | $20,000 |
| 2 | Annual rate | 4% |
| 3 | Years | 5 |
| 4 | ||
| 5 | Monthly deposit | $301.66 |
年4%で毎月$301.66を60か月積み立てると20,000に届きます。積立の合計は約18,100で、残りは利息がまかないます。B2を0%に変えると、積立額はちょうど20,000を60で割った額になります。
利息と元金: IPMTとPPMT
各回の返済は、一部が利息に、一部が借入の返済に充てられます。IPMTは1回の返済のうちの利息部分を、PPMTは元金部分を返し、2つを足すとPMTになります。どちらも2つ目の引数に返済の回を取るので、下にコピーすれば返済予定表になります。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Payment | Interest | Principal | Balance |
| 2 | 1 | $1,580.17 | $1,354.17 | $226.00 | $249,774.00 |
| 3 | 2 | $1,580.17 | $1,352.94 | $227.23 | $249,546.77 |
| 4 | 3 | $1,580.17 | $1,351.71 | $228.46 | $249,318.31 |
| 5 | 4 | $1,580.17 | $1,350.47 | $229.70 | $249,088.61 |
| 6 | 5 | $1,580.17 | $1,349.23 | $230.94 | $248,857.67 |
1か月目は、$1,580.17の返済のうち$1,354.17が利息で、残高を減らすのは$226.00だけです。元金部分は毎月少しずつ増えます。実際のブックでは、利率、期間、金額をセルに置き、これまでのシートのように$で参照してください。
やってみよう: 自動車ローンの返済額
| A | B | |
|---|---|---|
| 1 | Loan | $32,000 |
| 2 | Annual rate | 6.9% |
| 3 | Years | 5 |
| 4 | ||
| 5 | Monthly payment |
やってみよう: B5に、B1のローンをB2の年利でB3の年数かけて返すときの毎月の返済額を計算してください。プラスの数で表示します。
ヒント: 利率と返済回数の両方を月単位にする必要があります。
よくある間違い: 毎月の返済に年利を使う
利率とnperは同じ期間を使う必要があります。月数で数えているのに利率を年利のままにすると、毎月6.5%を課すことになり、返済額は約10倍高くなります：
| A | B | |
|---|---|---|
| 1 | Loan amount | $250,000 |
| 2 | Annual rate | 6.5% |
| 3 | Years | 30 |
| 4 | ||
| 5 | Wrong | $16,250.00 |
| 6 | Right | $1,580.17 |
間違った数式は毎月$16,250.00を求めます。これは月6.5%の利息そのものです。同じ間違いのもう1つの形は、利率を6.5%や0.065ではなく6.5と入力することで、それは年650%という意味になります。返済額がおかしく見えたら、まず利率の単位を確かめてください。年1回の返済なら、年利と年数をそのまま使います。
よくある質問
Excelでローンの毎月の返済額を計算するには？
=PMT(rate/12, years*12, -amount)を使います。たとえば250,000を年6.5%で30年借りるなら=PMT(6.5%/12,30*12,-250000)で、毎月約1,580.17になります。
ExcelのPMTがマイナスになるのはなぜですか？
Excelの財務関数は符号でお金の向きを表します。受け取るお金はプラス、支払うお金はマイナスです。借入は自分に入ってくるので、出ていく返済はマイナスになります。借入額の前(-B1)かPMTの前にマイナスを付けると、プラスの数で表示されます。
Excelでローンの総利息を計算するには？
返済額に返済回数を掛け、借入額を引きます：=PMT(B2/12,B3*12,-B1)*B3*12-B1。1年目に払う利息なら=-CUMIPMT(B2/12,B3*12,B1,1,12,0)を使います。
PMT、IPMT、PPMTの違いは何ですか？
PMTは返済額全体です。IPMTは1回の返済のうちの利息部分、PPMTは元金部分で、2つを足すとPMTになります。最初のうちの返済は、ほとんどが利息です。
Excelで毎月いくら積み立てればよいかを計算するには？
PMTに現在価値ではなく将来価値を渡します。=PMT(4%/12,5*12,0,-20000)は、年4%で5年後に20,000になる毎月の積立額です。
