ローン返済額の計算を教えてください。

元利金等返済で、利子率１０％、償却期間１５年で５０００万円の
毎月の支払い額と期末残高を計算したいのです。
ただし、支払いは５年ごとに３０％の割合で上昇するものとして計算する場合、
エクセルではどのように計算式をいれればいいでしょうか？

よろしくお願いいたします。

No.3 ベストアンサー 回答者： Major123 回答日時： 2007/06/25 16:51

#1です。

再度、エクセルで表を作ってみました。ところでこれってFP試験の課題か何かですか？

1度に12ヶ月x15年分を作成するのは大変なので、各年ごとに作成しましょう。
先ず、B1からM1に1,2,3,・・・と数字を振ります（各月度の意味）
また、A列は各月返済額や前年度返済残高など参照しやすいような数字を入れます。
1年目（A2～M4）：
　A2に1（年目）
　A3はとりあえず空白（各月返済額）
　A4は50,000,000（借入額、2年目以降は前年度末返済残高）
　B2：=A4*0.1/12（前年度返済残高の年利10%の1ヶ月分）
　B3：=A3
　 B4：=A4+B2-B3 (当月分返済後の返済残高）

　B2～B4をM列までオートフィル機能で自動入力

2年目（A5～M7）
　A5に2（年目）
　A6：=A3
　A7：=M4　（前年度末返済残高）
　B5：=A7*0.1/12（前年度返済残高の年利10%の1ヶ月分）
　B6：=A6
　B7：=A7+B5-B6(当月分返済後の返済残高）

　B5～B7をM列までオートフィル機能で自動入力

このようにして各年ごとを作成します。3行ごとに同様の計算を行なう処理が登場しますので、
Ctrl＋D機能と、各列ごとにオートフィル機能をうまく用いると入力がかなり楽になります。
6年目と11年目のところで、A18（6年目の各月返済額）、A33（11年目の各月返済額）のところで、
　A18：=A15*1.3　（1～5年目の30%増しの返済額）
　A33：=A30*1.3　（6～10年目の30%増しの返済額）
と入力しなおせば、返済額についての条件もクリアできます。

ここまで入力したところで、A3つまり初年度から5年目までの各月返済額を入力すると各返済残高が計算されます。
この返済残高が最終返済時に０に近ければいいのです。大体44万円くらいですかね。

この回答へのお礼

ありがとうございます、
なんとか表は出来ました。
当初は、財務関連の関数（PPMT関数やIPMT関数）等利用して
表を作成したかったのですが、
この方法で行きたいと思います。

本当に有難うございました。

お礼日時：2007/06/26 11:24

No.2 回答者： Major123 回答日時： 2007/06/25 15:27

＃1です。

毎月返済なのに、毎年返済と勘違いしていました。済みません、改めて考えてみます。
ところで利子率１０％って年利ということですよね。

この回答への補足

ご回答、有難うございます。
毎月なので１８０期（月）で計算したいんです。
利子率は年利です。

よろしくお願いいたします。

補足日時：2007/06/25 17:03

No.1 回答者： Major123 回答日時： 2007/06/25 15:14

エクセルを使うって事は、表を作るってことで考えれば良いですね。

A4に50,000,000を入力（元金）　A1,A2,A3は空白にしておきます
B1,C1,D1・・・と順番に１,２,３・・・とP1に15まで入力（年数表示）
B2に"=A4*0.1"と入力し、オートフィル機能を使ってP2まで自動入力する
　（前年度期末残高に対する当年度の利息額）
B3をとりあえず空白にし、（初年度返済額ですがあとで調整の為空白）
　C3に"=B3"と入力しF3までオートフィル機能で自動入力
　　（5年目まで返済額を同一にする）
　G3には"=B3*1.3"と入力（6年目の返済額は5年目までの返済額の1.3倍）
　　H3に"=G3"と入力し、K3までオートフィル機能で自動入力
　　（6年目から10年目までの返済額を同一にする）
　L3には"=G3*1.3"と入力（11年目の返済額は10年目までの返済額の1.3倍）
　　M3に"=L3"と入力し、P3までオートフィル機能で自動入力
　　（11年目から15年目までの返済額を同一にする）
B3に="A4+B2-B3"と入力しP4までオートフィル機能で自動入力
　（各年の期末残高の算定）

これでP4の数字つまり最終年の支払後の残高が０以下になるようにB3の数字（初年度返済額）を調整すればいいのです。
求めたい毎月の支払い額は２の行に、と期末残高は４の行に出てきます。
初年度返済額は540万円ほどになるはずです。