エクセルの財務関数－住宅ローンの返済計画立案
こんにちは。
前回は、老後資金の取り崩し計画を立案する場合を例にして、エクセルの財務関数の具体的な使い方をまとめました。今回は、住宅ローンの返済計画を立案する場合を例にして、エクセルの財務関数の具体的な使い方をまとめます。
老後資金を期末に取り崩す例
条件
以下の条件で住宅ローンを返済する場合を例に考えます。
- 住宅ローン金利1%
- ローン返済を3期行う
- 毎期100万円ずつ返済する
- ローンの借入額は2000万円
- 3期返済した後の借入残高は、1757万5920円
- 期末に返済を行う（その期分の金利が付いてから返済する）
将来価値の推移
期末に返済する場合と期首に返済する場合の違いを分かりやすくするために、一つの期における金利分と返済分を分けてグラフ化してみます。
エクセルの財務関数の引数
収支は、手持ちのお金が減るとマイナス、増えるとプラスと考えます。
お金を降り入れるとすぐに使える手持ちのお金が増えるので、ローン借入はプラスと考えます。
返済を行うと手持ちのすぐに使えるお金が減るので、ローン返済はマイナスと考えます。
したがって、今回の条件をエクセルの財務関数を使う場合に当てはめると以下の表になります。
エクセルの財務関数の計算式
エクセルの財務関数を使って、今回の条件を計算してみます。
利率
住宅ローンで借り入れた2000万円を、期末に100万円ずつ3期返済して、ローン残高が1757万5920円になるローン金利利はどれだけか？
RATE = RATE(nper, pmt, pv, fv, type, guess)
利率 = RATE(期間, 定期支払額, 現在価値, 将来価値, 支払期日, 推定値)
なので
RATE(3,-1000000,20000000,-17575920,0) = 1%
回数
老金利1%で借り入れた住宅ローン2000万円を、期末に100万円ずつ返済して。ローン残高を1757万5920円にするには、何期返済すればよいか？
Number of PERiods = NPER(rate,pmt,pv,fv,type)
期間 = NPER(利率,定期支払額,現在価値,将来価値,支払期日)
なので
NPER(1%,-1000000,20000000,-17575920,0) = 3
返済額
金利1%で借り入れた住宅ローン2000万円を、期末に返済して、3期後にローン残高を1757万5920円にするには、毎期どれだけ返済すればよいか？
PayMenTs = PMT(rate, nper, pv, fv, type)
定期支払額 = PMT(利率, 期間, 現在価値, 将来価値, 支払期日)
なので
PMT(1%,3,20000000,-17575920,0) = -1,000,000
最初の借入額
金利1%で借り入れた住宅ローンを、期末に100万円ずつ3期返済して、ローン残高が1757万5920円になるには、最初にどれだけ借り入れることができるか？
Present Value = PV(rate, nper, pmt, fv, type)
現在価値 = PV(利率, 期間, 定期支払額, 将来価値, 支払期日)
なので
PV(1%,3,-1000000,-17575920,0) = 20,000,000
ローン残高
金利1%で借り入れた住宅ローン2000万円を、期末に100万円ずつ3期返済すると、3期後のローン残高はどれだけか？
Future Value = FV(rate,nper,pmt,pv,type)
将来価値 = FV(利率,期間内支払回数,定期支払額,現在価値,支払期日)
なので
FV(1%,3,-1000000,20000000,0) = -17,575,920
老後資金を期首に取り崩す例
条件
以下の条件で住宅ローンを返済する場合を例に考えます。
- 住宅ローン金利1%
- ローン返済を3期行う
- 毎期100万円ずつ返済する
- ローンの借入額は2000万円
- 3期返済した後の借入残高は、1754万5619円
- 期首に返済を行う（その期分を返済してから金利が付く）
将来価値の推移
期末に返済する場合と期首に返済する場合の違いを分かりやすくするために、一つの期における金利分と返済分を分けてグラフにしてみます。
エクセルの財務関数の引数
今回の条件をエクセルの財務関数を使う場合に当てはめると以下の表になります。
エクセルの財務関数の計算式
エクセルの財務関数を使って、今回の条件を計算してみます。
利率
住宅ローンで借り入れた2000万円を、期首に100万円ずつ3期返済して、ローン残高が1754万5619円になるローン金利利はどれだけか？
RATE = RATE(nper, pmt, pv, fv, type, guess)
利率 = RATE(期間, 定期支払額, 現在価値, 将来価値, 支払期日, 推定値)
なので
RATE(3,-1000000,20000000,-17545619,0) = 1%
回数
老金利1%で借り入れた住宅ローン2000万円を、期首に100万円ずつ返済して。ローン残高を1754万5619円にするには、何期返済すればよいか？
Number of PERiods = NPER(rate,pmt,pv,fv,type)
期間 = NPER(利率,定期支払額,現在価値,将来価値,支払期日)
なので
NPER(1%,-1000000,20000000, -17545619,0) = 3
返済額
金利1%で借り入れた住宅ローン2000万円を、期首に返済して、3期後にローン残高を1754万5619円にするには、毎期どれだけ返済すればよいか？
PayMenTs = PMT(rate, nper, pv, fv, type)
定期支払額 = PMT(利率, 期間, 現在価値, 将来価値, 支払期日)
なので
PMT(1%,3,20000000, -17545619,0) = -1,000,000
最初の借入額
金利1%で借り入れた住宅ローンを、期首に100万円ずつ3期返済して、ローン残高が1754万5619円になるには、最初にどれだけ借り入れることができるか？
Present Value = PV(rate, nper, pmt, fv, type)
現在価値 = PV(利率, 期間, 定期支払額, 将来価値, 支払期日)
なので
PV(1%,3,-1000000, -17545619,0) = 20,000,000
ローン残高
金利1%で借り入れた住宅ローン2000万円を、期首に100万円ずつ3期返済すると、3期後のローン残高はどれだけか？
Future Value = FV(rate,nper,pmt,pv,type)
将来価値 = FV(利率,期間内支払回数,定期支払額,現在価値,支払期日)
なので
FV(1%,3,-1000000,20000000,0) = -17,545,619
今回は、住宅ローンの返済計画を立案する場合を例にして、エクセルの財務関数の具体的な使い方をまとめました。次回は、リボルビング払いを例にして、エクセルの財務関数の具体的な使い方をまとめます。
