Excelでローン計算をマスター!無料テンプレートとPMT・RATE・NPER関数の使い方

家の購入を検討していますか?車を買ったばかりですか?大学卒業が近づいていますか?不動産、車、学費といった高額な買い物には、通常、金融機関からの借入が必要になります。Microsoft Excelを使えば、ローンの計算から管理までを効率的に行えます。

支払いスケジュールの作成から、無理なく返済できる金額の把握まで、ローン計算テンプレートやExcelの組み込み財務関数をぜひ活用してみてください。

Excelのローン計算テンプレート

ローンの残高、支払い、利息をしっかり管理するには、Excel用のローン計算ツール(テンプレート)が最適です。以下で紹介するテンプレートは、最も一般的なローンの種類に対応しており、すべて無料で利用できます。

シンプル ローン計算機

個人向けローンなら、このシンプルなローン計算テンプレートを使えば、完済までの推移をグラフと償還(アモーチゼーション)スケジュールで確認できます。

借入金額、金利、期間、開始日などのローン詳細を入力すると、サマリーテーブル、償還スケジュール、縦棒グラフが自動的に更新されます。

サマリーテーブルでは総利息額とローン総コストがひと目でわかるため、全体像の把握にとても便利です。

自動車ローン計算機

すでにカーローンを組んでいる方も、これから検討中の方も、このテンプレートで対応できます。ワークブックには「自動車ローン詳細」「支払い計算」「ローン比較」の3つのタブが用意されており、契約前から返済中まで一貫して活用できます。

最適なローンを選ぶ際は、まず「Loan Comparisons(ローン比較)」タブから始めましょう。さまざまな金利における支払額を確認できるほか、支払回数、支払頻度、頭金の額ごとの比較も可能です。

次に「Auto Loan Calculator(自動車ローン計算機)」タブに、車両価格、借入金額、頭金、下取り価格、名義変更費用、消費税、その他の諸費用を入力します。すると「Payment Calculator(支払い計算機)」タブに、日付、利息、元金、残高を示す表とともに支払いスケジュールが表示されます。

夢のマイカーを購入する際は、このテンプレートでローン全体をしっかり管理しましょう。

住宅ローン計算機

いくらまでなら返済できるのか把握したい方や、固定金利の住宅ローンの審査に通ったばかりの方には、このExcelテンプレートがぴったりです。

借入金額、金利、期間、期間あたりの支払回数、開始日を入力すると、毎月の支払額、支払回数、総支払額と総利息、そして最終支払日まで確認できます。

さらに、全日程と金額をまとめた便利な一覧表も表示されるので、時間の経過に伴う支払済み利息と残高を継続的に追跡できます。

バルーン ローン計算機

住宅ローンや自動車ローンが固定金利ではなく、バルーン型(最後に残額を一括返済する方式)というケースもあります。このExcel計算テンプレートを使えば、最終の一括返済額を含むサマリーと償還スケジュールを確認できます。

当初の借入額と日付、金利、償還期間、期間あたりの支払回数などのローン詳細を入力し、月々の支払額、バルーン支払額、総利息、総支払額を示すサマリーをチェックしましょう。

償還スケジュールがあれば、ローンの最初から最後まで管理漏れを防げます。

学生ローン(奨学金)計算機

学生ローンの返済を始める時期が来たら、Microsoft 365向けのこのテンプレートが最適です。収入に占める返済割合や推定月収を事前に確認できます。

年収、返済開始日、各学生ローンの基本情報を入力すると、各ローンの返済年数と完済予定日、支払詳細、総利息などの返済情報が表示されます。

実用的な学生ローン返済プランとして、このテンプレートで計画通りに返済を進めましょう。

クレジットライン(融資枠)計算機

クレジットライン(リボルビング型の融資枠)については、この計算機で必要な情報をすべて確認できます。発生利息や支払済み回数を把握し損ねる心配はもうありません。

融資限度額、当初金利、据置期間(ドロー期間)、利息起算日、支払頻度、日数を入力します。金利変更の詳細を加えたり、追加支払いを組み込んだりすることも可能です。

クレジットラインの詳細を確認する際は、このシンプルなツールで計算と管理を行いましょう。

ローン償還スケジュール

Microsoft 365ユーザーなら、このローン償還スケジュールを活用できます。追加支払いオプション付きで、総利息と総支払額を計算できます。

借入金額、年利、期間、期間あたりの支払回数、ローン開始日などの基本情報を入力し、続いて支払情報と、必要に応じて追加支払いを入力します。

償還表は即座に更新され、支払額、元金、残高、利息、累積利息など、返済管理に必要なすべての項目を一目で確認できます。

Excelのローン関連関数で支払額・金利・期間を計算

ローンの支払額、金利、期間を算出したい場合は、便利なExcel関数をいくつか使うことができます。これらの関数を活用すれば、さまざまな金額、金利、期間の組み合わせで、自分にどれだけの返済負担が可能かをシミュレーションできます。

各関数の数式には、いくつかの情報を入力して結果を表示します。入力値を調整すれば異なる結果を得られ、簡単な比較検討にも役立ちます。

※注意：以下の数式には、貸し手が請求する各種手数料や諸費用は含まれていません。

ローンの支払額を計算する(PMT関数)

特定の借入額と金利で審査が通っている場合、支払額は簡単に算出できます。その結果をもとに、より低い金利の商品を探すべきか、借入額を見直すべきか、支払回数を増やすべきかを判断できます。この計算には、ExcelのPMT関数を使用します。

数式の構文は PMT(rate, num_pay, principal, future_value, type) で、最初の3つの引数が必須です。

• Rate(利率):年間金利(数式内で12で割る)
• Num_pay(支払回数):ローンの支払回数
• Principal(元金):借入総額
• 年金型ローンの場合、future_valueで最終支払後の価値を、typeで支払期日(期初・期末)を指定できます。

ここでは、年間金利をセルB2、支払回数をセルB3、借入額をセルB4に入力しています。セルB5に次の数式を入力して支払額を求めます:

=PMT(B2/12,B3,B4)

数式を設定したら、入力した数値を変更して支払額への影響を確認できます。より低い金利や多い支払回数を試して、希望の支払額に調整しましょう。結果は自動的に更新されます。

金利を計算する(RATE関数)

現在のローンの年間金利が不明な場合は、数分で計算できます。必要なのは、ローン期間、支払額、元金と、ExcelのRATE関数だけです。

数式の構文は RATE(term, pmt, principal, future_value, type, guess_rate) で、最初の3つの引数が必須です。

• Term(期間):期間(年)(数式内で12を掛ける)または支払総回数
• Pmt(支払額):月々の支払額(負の数として入力)
• Principal(元金):借入総額
• 年金型ローンの場合、前述のfuture_valueとtype引数を使用でき、guess_rateで推定金利を指定できます。

ここでは、ローン期間(年)をセルB2、月々の支払額をセルB3(負の数として入力)、借入額をセルB4に入力しています。セルB5に次の数式を入力して金利を計算します:

=RATE(B2*12,E3,E4)*12

これで、シンプルな数式だけでローンに適用されている実際の金利がわかります。

※ヒント:月利を計算したい場合は、数式末尾の *12 を削除してください。

返済期間を計算する(NPER関数)

自分の状況に最適なローン期間を検討している方もいるでしょう。金利、支払額、借入額がわかれば、返済期間(年)を算出できます。これにより、金利や支払額を調整して、返済年数を増減させるシミュレーションが可能です。ここでは、ExcelのNPER(期数)関数を使用します。

数式の構文は NPER(rate, pmt, principal, future_value, type) で、最初の3つの引数が必須です。

• Rate(利率):年間金利(数式内で12で割る)または固定金利
• Pmt(支払額):月々の支払額(負の数として入力)
• Principal(元金):借入総額
• 年金型ローンの場合、前述のfuture_valueとtype引数を使用できます。

ここでは、年間金利をセルB2、月々の支払額をセルB3(負の数として入力)、借入額をセルB4に入力しています。セルB5に次の数式を入力してローン期間を計算します:

=NPER(B2/12,B3,B4)

同様に、基本となるローン条件を調整すれば、自分に合った期間と金額に応じて結果が自動的に更新されます。

お金としっかり向き合おう

ローンを組むかどうか迷っている方も、すでに返済中の方も、これらのテンプレートや関数を覚えておくか、ぜひ一度試してみてください。金利から返済期間まで、ローンの詳細を正しく把握することは、支払いを続けることと同じくらい重要です。

さらに詳しく知りたい方は、ローン計算機を含む各種オンライン計算ツールもあわせてチェックしてみてください。