[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『完済までの利子・元本の年度別集計（元金均等・元利均等）』（イボイボ）
エクセル2003を使用しています。
元利均等返済・元金均等返済の毎月の返済予定表はなんとか完成する事ができました。
その表からDSUM関数で、集計開始日付、終了日付により年度別集計をしています。
借入金1000万円・返済期間10年（120回）・利率２％の条件で返済一覧表を作成せずに年度別（閏年も考慮）に下記を集計する方法を教えて下さい。
?@元利均等返済（元本及び利息）
?A元金均等返済（利息）
＜ 使用 Excel：Excel2003、使用 OS：Windows7 ＞
＞元利均等返済・元金均等返済の毎月の返済予定表はなんとか完成する事ができました。 ＞その表からDSUM関数で、集計開始日付、終了日付により年度別集計をしています。
なら、その表でシミュレーションするのが一番確かです。
何故って、円未満の丸め処理まで正確に反映されるからです。
＞借入金1000万円・返済期間10年（120回）・利率２％の条件で返済一覧表を作成せずに ＞年度別（閏年も考慮）に下記を集計する方法を教えて下さい。
閏年でも、そうじゃなくても、月利計算なので、算出金額は変わらないですけど？
（半平太） 2018/02/26(月) 08:38
当方が作成した返済一覧表は日数計算もしており、かなり正確だろうとは思いますが出来れば千円単位くらいで計算式のみで対応したいと考え、試行錯誤しております。
計算式のみで各年度別の利息合計、返済合計を出す方法はないでしょうか?
（イボイボ） 2018/02/26(月) 09:06
＞当方が作成した返済一覧表は日数計算もしており、かなり正確だろうとは思いますが
ローンの月次返済の話ですよね？
そのような計算は銀行ではしないハズですけど。 (借入日が月の途中だった場合だけ、日割り計算にすることはあり得ますが、 普通は返済日に合わせて、貸出日も同じになるように調整すると思います）
計算は、年利掛けて１２で割るだけです。 ※日数を考慮したら却って不正確になります。
ちょっとこれから外出します。
（半平太） 2018/02/26(月) 09:41
毎月の返済予定一覧表（120回）を作らずに下記を集計したいのです。
Ａ）元利均等返済（元金＋利息＝毎月一定額）
１年 元利均等の年間元本返済合計 元利均等の年間支払利子合計
２年 元利均等の年間元本返済合計 元利均等の年間支払利子合計
３年 元利均等の年間元本返済合計 元利均等の年間支払利子合計
４年 元利均等の年間元本返済合計 元利均等の年間支払利子合計
Ｂ）元金均等返済（元金返済額＝毎月一定額）
１年 元金均等の年間支払利子合計
２年 元金均等の年間支払利子合計
３年 元金均等の年間支払利子合計
４年 元金均等の年間支払利子合計
（イボイボ） 2018/02/26(月) 13:58
ネット検索してみた （GobGob） 2018/02/26(月) 14:08
日数に拘るなら、私は降ります。 m(__)m
（半平太） 2018/02/26(月) 14:15
日数にはこだわっておりません。千円単位の概算でかまわないのです。
返済表を作らずに、１０年間の年度別の集計が可能なのか知りたいのです。
（イボイボ） 2018/02/26(月) 16:40
(1) D1セル =PMT(B2/12,B3*12,-B1) (2) F1セル =B1/B3/12
(3) B5セル =IF(OR(B4="",N(B4)>=$B$3),"",N(B4)+1) (4) C5セル =IF($B5="","",-CUMPRINC($B$2/12,$B$3*12,$B$1,($B5-1)*12+1,$B5*12,0)) (5) D5セル =IF($B5="","", -CUMIPMT($B$2/12,$B$3*12,$B$1,($B5-1)*12+1,$B5*12,0)) (6) E5セル =IF($B5="","",$F$1*12) (7) F5セル =IF($B5="","",$B$1*(1-(B5*12-6.5)/$B$3/12)*$B$2)
B5:F5を一括して、10行下(14行目)までコピー
(8) C4セル =SUM(C5:C14)
これをF4セルまでコピー
<結果図> 行 _______A_______ _____B_____ _____C_____ ____D____ _____E_____ ____F____ 1 元金 10,000,000 元利均等 92,013 元金均等 83,333 2 利率(表面年率) 2% 3 期間(単位年) 10 元金 利息 元金 利息 4 計 10,000,000 1,041,614 10,000,000 1,008,333 5 年度 1 912,496 191,666 1,000,000 190,833 6 2 930,914 173,247 1,000,000 170,833 7 3 949,704 154,458 1,000,000 150,833 8 4 968,873 135,288 1,000,000 130,833 9 5 988,429 115,732 1,000,000 110,833 10 6 1,008,380 95,782 1,000,000 90,833 11 7 1,028,733 75,428 1,000,000 70,833 12 8 1,049,498 54,664 1,000,000 50,833 13 9 1,070,681 33,480 1,000,000 30,833 14 10 1,092,292 11,869 1,000,000 10,833
（半平太） 2018/02/26(月) 17:23
ありがとうございます。
元利均等払の利息、返済元本を累計する関数があるのを知りませんでした。
毎月の返済予定表で計算した値と近似値となりました。
もう少しご教授ください。
Ａ）年度を算出する計算式について
(3) B5セル =IF(OR(B4="",N(B4)>=$B$3),"",N(B4)+1)の「N」はどのような意味なのでしょうか?
Ｂ）元金均等返済の年間利息の計算式
(7) F5セル =IF($B5="","",$B$1*(1-(B5*12-6.5)/$B$3/12)*$B$2)の「$B$1*(1-(B5*12-6.5)」の「-6.5」はどこから出てくる数字なのなのでしょうか?
（イボイボ） 2018/02/27(火) 04:26
ただ、どうしても疑問が解けません。
上記と質問と重複しますが、どなたか教えていただけませんでしょうか?
Ａ）年度を算出する計算式について
(3) B5セル =IF(OR(B4="",N(B4)>=$B$3),"",N(B4)+1)の「N」はどのような意味なのでしょうか?
Ｂ）元金均等返済の年間利息の計算式
(7) F5セル =IF($B5="","",$B$1*(1-(B5*12-6.5)/$B$3/12)*$B$2)の「$B$1*(1-(B5*12-6.5)」の「-6.5」はどこから出てくる数字なのなのでしょうか?
（イボイボ） 2018/02/27(火) 13:10
「N」に限ってでしたらExcelの一般関数にＮ関数があります。
使い方はわたしは知りません。
ＨＥＬＰをご確認下さい。
では
m(__)m
（隠居じーさん） 2018/02/27(火) 13:21
「N 引数を数値に変換する」こんな関数があるとは知りませんでした。
ちなみに N(文字が入力されているセル) を指定すると「0」が返されました。
後、半平太様の教えて下さった Ｂ）の疑問が解決しません。
Ｂ）元金均等返済の年間利息の計算式
(7) F5セル =IF($B5="","",$B$1*(1-(B5*12-6.5)/$B$3/12)*$B$2)の「$B$1*(1-(B5*12-6.5)」の「-6.5」はどこから出てくる数字なのなのでしょうか?
（イボイボ） 2018/02/27(火) 14:32
各年度の積数に年利を掛ければ、年度別の利息が求められます。
各年度の積数は前年末残から当該年度の返済額積数を差し引いたものです。
当該年度の返済額積数(12月換算)は 毎月返済額*(0*12+1*11+2*10+・・+10*2+11*1)/12ヶ月 つまり、 毎月返済額*5.5である。
毎月返済額をC で表すと求める年度利息は
(当初元本-(年度-1)*C*12 - 5.5*C ）* 年利 となる ~~~~~~~~~↑~~~~~~~~~~~~ ~~↑~~~~ 前年末残 当該年度の返済額積数
これでもいいんですが、(年度-1）と言うのが気に食わなかったので、-1を外に出し
(当初元本-(年度)*C*12 + (12-5.5)*C ）* 年利 ↓ (当初元本-(年度)*C*12 + 6.5*C ）* 年利 としました。
それを当てはめると、下の数式になります。 後はシート上のどのセルを使うかとか、カッコでどう括るかぐらいの違いです。 ↓ =($B$1-$B5*$F$1*12+6.5*$F$1)*$B$2
（半平太） 2018/02/27(火) 20:49
おんぶにダッコで申し訳ありませんでした。
やっと、疑問が解決できます。
当方、エクセルの前身であるマルチプラン（マイクロソフト社製）の頃から表計算ソフトを使用していますが、使用しているだけで素人同然です。
今後とも宜しくお願い申し上げます。
（イボイボ） 2018/02/28(水) 00:53
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.