ローン返済予定表からローン利息を逆算する方法

車両ローンの毎月支払額から元本とローン利息分に分ける計算をしたいです。利息の計算をしようとしましたがうまくできません。借入元本270万円、返済回数99回、分割払手数料　138,123円　実質年率　1.99％　です元利均等返済契約で毎月の返済額が28,920円(初回　28,923円)　ですがここから毎月の分割払い手数料を計算しようとしてもうまくできません。詳しい方ご教示ください。できればEXCEL関数を使いたいですが自分でやるとうまくできません。

No.2 回答者： Moryouyou 回答日時： 2024/02/25 18:06

計算が合いません。

分割手数料も合いません。

元利均等返済で、
28,920円×99ヶ月払い
とすると、
金利は1.4222%になります。

その前提で返済利息は以下のような
推移になります。
利息は合計で16.3万になります。
単純に各月の返済残高に利率1.4222%
をかけて利息を求め、そこから
返済額28,920を引くを繰り返します。

例
2,700,000+3,200ｰ28,920
=2,674,280(翌月残高)

月数　返済残高　　　利息
1　¥2,700,000　¥3,200
2　¥2,674,280　¥3,169
3　¥2,648,529　¥3,139
4　¥2,622,748　¥3,108
5　¥2,596,937　¥3,078
6　¥2,571,095　¥3,047
7　¥2,545,222　¥3,017
8　¥2,519,318　¥2,986
9　¥2,493,384　¥2,955
10　¥2,467,419　¥2,924
11　¥2,441,423　¥2,893
12　¥2,415,397　¥2,863
13　¥2,389,340　¥2,832
14　¥2,363,251　¥2,801
15　¥2,337,132　¥2,770
16　¥2,310,982　¥2,739
17　¥2,284,801　¥2,708
18　¥2,258,589　¥2,677
19　¥2,232,346　¥2,646
20　¥2,206,071　¥2,615
21　¥2,179,766　¥2,583
22　¥2,153,429　¥2,552
23　¥2,127,061　¥2,521
24　¥2,100,662　¥2,490
25　¥2,074,232　¥2,458
26　¥2,047,770　¥2,427
27　¥2,021,277　¥2,396
28　¥1,994,753　¥2,364
29　¥1,968,197　¥2,333
30　¥1,941,610　¥2,301
31　¥1,914,991　¥2,270
32　¥1,888,340　¥2,238
33　¥1,861,658　¥2,206
34　¥1,834,945　¥2,175
35　¥1,808,199　¥2,143
36　¥1,781,422　¥2,111
37　¥1,754,614　¥2,080
38　¥1,727,773　¥2,048
39　¥1,700,901　¥2,016
40　¥1,673,997　¥1,984
41　¥1,647,061　¥1,952
42　¥1,620,093　¥1,920
43　¥1,593,093　¥1,888
44　¥1,566,061　¥1,856
45　¥1,538,997　¥1,824
46　¥1,511,901　¥1,792
47　¥1,484,773　¥1,760
48　¥1,457,613　¥1,728
49　¥1,430,420　¥1,695
50　¥1,403,195　¥1,663
51　¥1,375,938　¥1,631
52　¥1,348,649　¥1,598
53　¥1,321,327　¥1,566
54　¥1,293,973　¥1,534
55　¥1,266,587　¥1,501
56　¥1,239,168　¥1,469
57　¥1,211,717　¥1,436
58　¥1,184,233　¥1,404
59　¥1,156,716　¥1,371
60　¥1,129,167　¥1,338
61　¥1,101,585　¥1,306
62　¥1,073,971　¥1,273
63　¥1,046,324　¥1,240
64　¥1,018,644　¥1,207
65　¥990,931　¥1,174
66　¥963,186　¥1,142
67　¥935,407　¥1,109
68　¥907,596　¥1,076
69　¥879,751　¥1,043
70　¥851,874　¥1,010
71　¥823,964　¥977
72　¥796,020　¥943
73　¥768,044　¥910
74　¥740,034　¥877
75　¥711,991　¥844
76　¥683,915　¥811
77　¥655,805　¥777
78　¥627,663　¥744
79　¥599,486　¥710
80　¥571,277　¥677
81　¥543,034　¥644
82　¥514,758　¥610
83　¥486,448　¥577
84　¥458,104　¥543
85　¥429,727　¥509
86　¥401,316　¥476
87　¥372,872　¥442
88　¥344,394　¥408
89　¥315,882　¥374
90　¥287,337　¥341
91　¥258,757　¥307
92　¥230,144　¥273
93　¥201,496　¥239
94　¥172,815　¥205
95　¥144,100　¥171
96　¥115,351　¥137
97　¥86,568　¥103
98　¥57,750　¥68
99　¥28,899　¥34

この回答へのお礼

丁寧な計算ありがとうございます。ﾛｰﾝ会社の支払利息は138,129円でそれが支払総額になっているのでおそらく利率が違うのでしょう。利息計算にも実質年率　という表現をしていたため残高×利率÷12で求められるわけではないと思います。
138,129円　になるような計算方式を再度自分で模索してみます。
ありがとうございました

お礼日時：2024/02/28 19:16

No.1 回答者： angkor_h 回答日時： 2024/02/25 16:33

先ずは、ローン返済について、excel計算をしてみてください。

要素は以下３点　
借入元本
借入年利(計算には月利を使う)
返済月額

分割払手数料は、
普通は年利に含んで名目自体が無いはずですが、
返済月額に定額上乗せであれば、返済月額を上げればよいです。

ローン返済の計算は、
元本(残額)に、その月利金額を加算して、返済月額を差し引き、
次月までの残金とする、
この繰り返しです。