[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『段階金利』 （よし）
エクセル初心者です。
過去に同じような質問をされている方がいたのですが、
私には理解できなかったため、改めてお願いします。
住宅ローンのフラット３５Sなどのように、一定年数経過ごとに
金利が変わるものですが、
※11年〜20年はA2の金利＋0.7％ 21年〜35年はA2の金利＋1.1％となります。
A B C D E F
１ 金利 借入額 借入期間 １〜10年返済額 11年〜20年返済額 21年〜35年返済額
２ 1.9％ 4000万円 35年 毎月131,481円 …
…のような表を作成しており、D2には＝PMT(A1/12,C2*12,-B2*10000)
という式が入っています。
11年〜20年返済額（E2）、21年〜35年返済額（F2）に入る式がわかる方、
いらっしゃいましたらお願いします。
乱文ですみません。
こんにちは。
E2,F2がいくらになれば正解か示して下さい。 また、もし分かるなら 計算方法を示して下さい。 フラット３５Sの場合は、金利が変わるタイミングで一旦元金を完済して同額を借り直す という計算になっています。
−佳−
別サイトでのシュミレーションでは、概算ですが
D2が 130,461円 E2が 141,255円 F2が 145,267円
となっていました。
計算方法は残念ながら解りません。
すみません。
よろしくお願いします。
よし
こんばんは。 結論からいうと、E2,F2の数式はひとつには定まりません。
詳細は省きますが、D2値,E2値,F2値は次の算式を成り立たせる値になります。 定数１×D2値＋定数２×E2値＋定数３×F2値＝4000×10,000 このとき、D2値だけ決まっている状態では、E2値,F2値の組合せは無数にあるからです。
ということで、D2値,E2値を入力した場合に、F2値を求める数式を組んでみました。 ・3行目以下は、検証用です。 ・フラット３５Sは、知りませんので、反映していません。
※「_」セルは未入力または"" [R/C] [A] [B] [C] [D] [E] [F] [1] 金利 借入額 借入期間 １〜10年返済額 11年〜20年返済額 21年〜35年返済額 [2] 1.90% 4000 35 \130,462 \141,255 \145,268 [3] ↓検証用 _ _ _ _ _ [4] 金利 残額 年数 返済額 _ _ [5] 1.90% \39,187,409 1 \130,462 _ _ [6] 1.90% \38,359,244 2 \130,462 _ _ [7] 1.90% \37,515,206 3 \130,462 _ _ [8] 1.90% \36,654,991 4 \130,462 _ _ [9] 1.90% \35,778,289 5 \130,462 _ _ [10] 1.90% \34,884,784 6 \130,462 _ _ [11] 1.90% \33,974,153 7 \130,462 _ _ [12] 1.90% \33,046,069 8 \130,462 _ _ [13] 1.90% \32,100,197 9 \130,462 _ _ [14] 1.90% \31,136,197 10 \130,462 _ _ [15] 2.60% \30,240,049 11 \141,255 _ _ [16] 2.60% \29,320,321 12 \141,255 _ _ [17] 2.60% \28,376,394 13 \141,255 _ _ [18] 2.60% \27,407,630 14 \141,255 _ _ [19] 2.60% \26,413,376 15 \141,255 _ _ [20] 2.60% \25,392,960 16 \141,255 _ _ [21] 2.60% \24,345,696 17 \141,255 _ _ [22] 2.60% \23,270,876 18 \141,255 _ _ [23] 2.60% \22,167,775 19 \141,255 _ _ [24] 2.60% \21,035,649 20 \141,255 _ _ [25] 3.00% \19,908,078 21 \145,268 _ _ [26] 3.00% \18,746,212 22 \145,268 _ _ [27] 3.00% \17,549,005 23 \145,268 _ _ [28] 3.00% \16,315,385 24 \145,268 _ _ [29] 3.00% \15,044,243 25 \145,268 _ _ [30] 3.00% \13,734,438 26 \145,268 _ _ [31] 3.00% \12,384,794 27 \145,268 _ _ [32] 3.00% \10,994,099 28 \145,268 _ _ [33] 3.00% \9,561,105 29 \145,268 _ _ [34] 3.00% \8,084,525 30 \145,268 _ _ [35] 3.00% \6,563,034 31 \145,268 _ _ [36] 3.00% \4,995,264 32 \145,268 _ _ [37] 3.00% \3,379,810 33 \145,268 _ _ [38] 3.00% \1,715,220 34 \145,268 _ _ [39] 3.00% \-0 35 \145,268 _ _
F2 =(B2*10000 - PV(A2/12,10*12,-1)*D2 - PV(A2/12,10*12,0, -PV((A2+0.7%)/12,10*12,-1)*E2 ) ) / PV(A2/12,10*12,0, PV((A2+0.7%)/12,10*12,0, PV((A2+1.1%)/12,15*12,-1) ) )
※「10」がいっぱいでてきて分かりづらいですが、最初の10年をｍ、次の10年をｎと表すと、 ↓こういう使い方です。（利率と対応しています） F2 =(B2*10000 - PV(A2/12,ｍ*12,-1)*D2 - PV(A2/12,ｍ*12,0, -PV((A2+0.7%)/12,ｎ*12,-1)*E2 ) ) / PV(A2/12,ｍ*12,0, PV((A2+0.7%)/12,ｎ*12,0, PV((A2+1.1%)/12,15*12,-1) ) )
↓以下、検証用 A5 =A$2 →A5:A14フィルコピー A15 =A$2+0.7% →A15:A24フィルコピー A25 =A$2+1.1% →A25:A39フィルコピー B5 =FV(A5/12,12,D5,-B2*10000) B6 =FV(A6/12,12,D6,-B5) →B6:B39フィルコピー D5 =$D$2 →D5:D14フィルコピー D15 =$E$2 →D15:D24フィルコピー D25 =$F$2 →D25:D39フィルコピー （コタ）
コタさん こんにちは 佳さんのレスによりますと、 「フラット３５Sの場合は、金利が変わるタイミングで一旦元金を完済して同額を借り直すという計算」 と云うことですので、それが正しければ、全ての計算ファクタは揃っていますので、D2値とE2値は一つだと思います。 （小数点以下の端数の問題は無視しています）
(1) A3セル =A2+0.7%
(2) A4セル =A2+1.1%
(3) B3セル =$B2-SUMPRODUCT(PPMT($A2/12,ROW($A$1:$A$120),$C2*12,-$B2))
B4へフィルコピー
(4) C3セル =C2-10 C4までフィルコピー
(5) D2セル =PMT(A2/12,C2*12,-B2*10000) D4までフィル―コピー
<結果図 - レイアウトは変えさせて頂きました> 行 ___A___ _______B_______ ____C____ _____D_____ 1 金利 借入額（万円） 借入期間 毎月返済額 2 1.90% 4,000 35 130,462 3 2.600% 3,114 25 141,255 4 3.00% 2,104 15 145,268
(半平太) 2010/07/11 19:43
>全ての計算ファクタは揃っていますので ↑ ここ、ちょっと言い過ぎでした。m(__)m
「各期の計算が、返済期限までとする計算方法なら」と云う前提です。 実際、第1期の月額計算では、返済期限までの35年で計算が行われています。 （10年しか返済期間がないにも拘わらずです）
(半平太) 2010/07/11 19:52
半平太さん、こんばんは。 いつも素晴らしいご回答を、参考にさせていただいています。
＞ 住宅ローンのフラット３５Sなどのように、一定年数経過ごとに金利が変わるものですが、 なるほど、私は質問者さんの↑の文を読んで、金利が変わる例としてフラット３５Sが 挙げられただけかと思っていたのですが、その後のシミュレーション結果がそれだったのですね。 （確認を手抜きしちゃいました）
質問者さんへ ということで、私の回答はスルーしてくださいませ。
（コタ）
こんにちは。
> 別サイトでのシュミレーションでは、概算ですが > D2が 130,461円 E2が 141,255円 F2が 145,267円 > となっていました。
それで、ほんとうにその数字で「合っている」のですね？ それで間違いないなら、半平太さんの数式でよろしいかと。
--------------------- > 実際、第1期の月額計算では、返済期限までの35年で計算が行われています。 フラット３５Sの場合はその計算であっています。 ちなみに 住宅金融支援機構の前身である住宅金融公庫の「ステップ返済」では、 ３０年ローンの当初５年間は、返済期間を５０年として毎月の返済額を計算 となっていました。何年借りるかとは関係なく 何年で計算するかの世界です。 ご参考まで。 （関係なくは言い過ぎか）
−佳−
ですが引き続きお願いします(＞＜)
早速、半平太さんの式で試みたのですが、B3の答えが違うものに…(汗
おそらく【ROW】の使い方が違ったのかなと思うのですが、
ROW($A$1：$A$120)の範囲はどういう意味をもつのでしょう？
ROW($A$2：$A$4)ではダメなのですか？？
−佳−さん、シュミレーションの数字は合ってます。
大丈夫ですよ。
よし
皆さん有難うございました!!!
よし
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.