EXCEL 計算式 関数 5年返済 据置額

素人です。元本５００万円、年利３％、月払６０回、月額均等払、６０回目に１００万円を払い０円の残となる計算式を、元本と利息を分解して表示できるようにしたいです。利息は後払いのため、最後の１００万円（据置額）の中身は、利息と元本がわかるようにしたいです。ＥＸＣＥＬの関数の前提は、最後の額は０円でしょうか。最後に大きな額が来る前提の為、おしえてください

　　　　均等額　　　　元本残高
０回　　　０円　　　　５００万円
１回　　　？円　　　　　？万円
２回
。
。
６０回　１００万円　　　　０円

No.6 ベストアンサー 回答者： goomania 回答日時： 2020/11/17 01:15

No.5です。

前回の回答で漏らしてしまったことを追記させていただきます。
同じ元利均等方式なのに、No.1、2のずんだしらたまさんのご回答と異なる金額となるのは、最終回の返済額の考え方の違いです。
ずんだしらたまさんのお考えは、
「最終回の<支払額>が最も100万円に近くなるような毎月支払額」
を求めています。
私の回答は100万円は据置額(元金は減らない)として、
「最終回の<残元金>が最も100万円に近くなるような毎月支払額」
を求めています。
このため、回答に差がでるわけです。
借入金返済のパターンの比較するのに、支払総額の比較という考え方があります。
返済の全期間に亘って支払った総額を比較するもので、元金をできるだけ早く返す方が、支払総額が少なくなり借り手の負担が減ります。(元金を早く返すのだからあたりまえといえばあたりまえ)
この比較では支払総額の少ない順でいうと、
No.3、4さん→No.1、2さん→No.4、5の順になります。

この回答へのお礼

とにかくありがとうございます
良い情報をいただきました。
利息は後払いですから
最終回の月初における元本かける年率金利に相当する利息が100万の中に含まれることを想定しています。

お礼日時：2020/11/21 20:52

No.7 回答者： goomania 回答日時： 2020/11/17 01:18

失礼しました。

No.6の回答に誤記がありました。最終行を以下のとおり訂正します。
(誤)No.3、4さん→No.1、2さん→No.4、5の順になります。
(正)No.3、4さん→No.1、2さん→No.5、6の順になります。

この回答へのお礼

誠にありがとうございます

お礼日時：2020/11/17 03:27

No.5 回答者： goomania 回答日時： 2020/11/16 23:58

No.1、2でご回答いただいたずんだしらたまさんの「返済表」は「元利均等方式」(返済元金+利息=支払金額が毎月均等で返済していく方法)、No.3、4でご回答いただいたsorun2さんの「返済表」は、「元金均等方式」(返済元金が均等で支払額は減少していく方法)のものです。

従って、sorun2さんの方式だと第1回の支払額は67,839円の元金返済分と利息12,500円を加えた、80,339円となり、その後、月毎に支払額は徐々に減少していきます。
ご質問者は「月額均等払」と言っているだけで、「元金均等払い」か「元利均等払い」か指定していませんので、この方法でもOKなのかも知れません。
しかし、「月額均等払い」というと、支払額が徐々に減少してい方法ではなく、ずんだしらたまさんのご回答のように、ローンの返済と同様に毎月支払い額が一定の方法(「元利均等払い」)のイメージではないかと思ったので、そちらを考えてみました。

さて、ローンの計算とはそもそもどのように行うのかというと、当たり前ではありますが、契約に基づいて計算されます。
以下のURLは広島銀行のローン契約書の内容です。
https://www.hirogin.co.jp/service/agreement/pdf/ …
上記契約書の第6条に「元利金の返済方法」という条項があり、
①元利均等返済方式となること
②利息は、各返済日に後払いすること
③毎月返済の利息は、元本残高（付利単位100円）×利率÷12で計算すること
と記載されています。
つまり、元金100円未満に対しては金利はつかないわけです。
これを基に月間利息を計算する式は
=INT(INT(残元金/100)*100*0.03/12)
となります。
さらに元利均等返済ですが、EXCELには元利金等返済の毎回返済額を求めるPMT関数がありますので、これを使います。
=INT(PMT(年利/12,返済回数-1,-(当初元金-据置額),0))+据置額*年利/12
今回のケースでは
=INT(PMT(0.03/12,60-1,-(5000000-1000000),0))+1000000*0.03/12
ここでは、円未満の端数を切捨てています。
No.1、2のずんだしらたまさんの回答では円未満を切上げていますが、上記の契約書には円未満の切上げ、切捨てについての記述がありません。
契約書にないなら法律に従えばよいと思うのですが、日本の法律には貸付利息の円未満切捨て、切り上げの定めがありません。
金融機関では、万一ローンの支払いが滞ったら、裁判で元利金の支払いを請求すると言う可能性もあるわけですが、法律に定めがないので、このとき切上げ計算で請求したら裁判で根拠を示せないことになります。このため、一般に金融機関では保守的に判断し、消費者に有利な円未満切捨てで対応していると思います。このため、それに従っておきました。
最終回には据置額と金利と最終回調整金(理論上の元利金等返済額と円未満切捨ての関係で誤差が生じるための調整額)を支払うものとします。
添付画像をご覧ください。
計算結果では毎月の支払金額は
1回～59回が75,504円、60回目が1,002,471円になります。

No.4 回答者： sorun2 回答日時： 2020/11/15 13:00

No3の訂正です。

60回残額は　0円です。

この回答へのお礼

ありがとうございます
拝見します
大変お手間となりました

お礼日時：2020/11/15 13:02

No.3 回答者： sorun2 回答日時： 2020/11/15 12:44

元本=500万円　年利=3%=0.03　月利=0.03÷12=0.0025

月額均等払額の計算
60回目利息=59回目残額×0.0025
60回支払い額=59回目残額+60回目利息=1,000,000円
　　　　　　 =59回目残額+59回目残額×0.0025=1,000,000
=59回目残額×(1+0.0025)=1,000,000
59回目残額=1,000,000÷(1+0.0025)=997,506
月額均等払額=(5,000,000-59回目残額)÷59
=(5,000,000-997,506)÷59=67,838.8=67,839円

No.2 回答者： ずんだしらたま 回答日時： 2020/11/15 02:18

画像が全く見えないですね…

失礼いたしました…

見にくいかもしれませんが、テキストで貼り直させていただきます。

回数 均等額 元本残高 利息
0 0 5000000 -
1 75543 4936957 12500
2 75543 4873757 12343
3 75543 4810399 12185
4 75543 4746882 12026
5 75543 4683207 11868
6 75543 4619373 11709
7 75543 4555379 11549
8 75543 4491225 11389
9 75543 4426911 11229
10 75543 4362436 11068
11 75543 4297800 10907
12 75543 4233002 10745
13 75543 4168042 10583
14 75543 4102920 10421
15 75543 4037635 10258
16 75543 3972187 10095
17 75543 3906575 9931
18 75543 3840799 9767
19 75543 3774858 9602
20 75543 3708753 9438
21 75543 3642482 9272
22 75543 3576046 9107
23 75543 3509444 8941
24 75543 3442675 8774
25 75543 3375739 8607
26 75543 3308636 8440
27 75543 3241365 8272
28 75543 3173926 8104
29 75543 3106318 7935
30 75543 3038541 7766
31 75543 2970595 7597
32 75543 2902479 7427
33 75543 2834193 7257
34 75543 2765736 7086
35 75543 2697108 6915
36 75543 2628308 6743
37 75543 2559336 6571
38 75543 2490192 6399
39 75543 2420875 6226
40 75543 2351385 6053
41 75543 2281721 5879
42 75543 2211883 5705
43 75543 2141870 5530
44 75543 2071682 5355
45 75543 2001319 5180
46 75543 1930780 5004
47 75543 1860064 4827
48 75543 1789172 4651
49 75543 1718102 4473
50 75543 1646855 4296
51 75543 1575430 4118
52 75543 1503826 3939
53 75543 1432043 3760
54 75543 1360081 3581
55 75543 1287939 3401
56 75543 1215616 3220
57 75543 1143113 3040
58 75543 1070428 2858
59 75543 997562 2677
60 1000056 0 2494

この回答へのお礼

ありがとうございます
詳しいところはこれから拝見します
お手間となりました
色々とお詳しいのですね

お礼日時：2020/11/15 11:59

No.1 回答者： ずんだしらたま 回答日時： 2020/11/15 02:08

お金関係の仕事をしている妻に頼まれて以前住宅ローンの返済関連で同じような表を作ったことがあるのですが、うろ覚えで作ってみました。

こんな感じで宜しかったでしょうか？
作った式からですと、月々75543円の返済が一番近くなるようです。60回目に1000056円返済します。

丸め計算など正確な計算方法までは分からないため、ずれがある場合は下の式を修正して再計算してみて下さい。

b3〜b61の式
=$E$2

c3の式
=C2+ROUNDUP(C2*0.03/12,0)-B3
以下はこのコピー。

d3の式　上の式の利息部分を抜き出しただけです。
=ROUNDUP(C2*0.03/12,0)