金利を含めた合計借入額から金利分と元金額を別々に知るにはどうしたらいいですか？

650万円を年2.7%で借りた場合(12年間144回)、総支払額が7,616,948円でした。
この場合、元金6,5000,000円、 金利分1,116,948ということになります。

例えば総支払額が5,000,000円ちょうどだったとします。
その場合の元金分と金利分はいくらといくらになるでしょうか？
(金利は2.7%として)
算数が苦手でまったくわかりません・・・

できればエクセルでの数式がわかればお教えいただけると助かります。

質問者からの補足コメント

• yhr2さん、xs200さんありがとうございます。
  yhr2さんに教えていただいたことを、エクセルでやってみました。
  このようなことであっていますか？

  
    補足日時：2017/04/18 13:09

No.4 ベストアンサー 回答者： xs200 回答日時： 2017/04/18 13:56

質問内容が変化したような?

補足コメントでは間違いです。毎月払いですから2.7%を12で割らなければいけません。毎月の返済金額はどう計算しましたか?

毎月の返済額はPMT関数で計算します。
=ROUNDDOWN(PMT(2.7%/12,144,-6500000),0)
=52895
毎年の返済額は
634740円になります。

利息の計算はIPMT関数です。
=ROUNDDOWN(IPMT(2.7%/12,n,144,-6500000),0)
nは何回目の返済かです。
初回なら
=ROUNDDOWN(IPMT(2.7%/12,1,144,-6500000),0)
30回目なら
質問内容が変化したような?

補足コメントでは間違いです。毎月払いですから2.7%を12で割らなければいけません。毎月の返済金額はどう計算しましたか?

毎月の返済額はPMT関数で計算します。
=ROUNDDOWN(PMT(2.7%/12,144,-6500000),0)
=52895
毎年の返済額は
634740円になります。

利息の計算はIPMT関数です。
=ROUNDDOWN(IPMT(2.7%/12,n,144,-6500000),0)
nは何回目の返済かです。
1回目なら
=ROUNDDOWN(IPMT(2.7%/12,1,144,-6500000),0)
30回目なら
=ROUNDDOWN(IPMT(2.7%/12,30,144,-6500000),0)

A列に返済回数があるのでnはA列を参照すればいいです。
初回
=ROUNDDOWN(IPMT(2.7%/12,A2,144,-6500000),0)

この回答へのお礼

ありがとうございます。
よくわかりました。
これを使って計算の表をつくります。
助かりました。
※yhr2さんも、詳しくありがとうございましたむ。

お礼日時：2017/04/18 14:31

No.5 回答者： yhr2 回答日時： 2017/04/18 14:30

No.1&2 です。

お示しのエクセルでほぼよいと思います。年単位の少し荒い計算ですが。

実際には月単位で、「毎月返済」「ボーナス月は多めに返済」というような条件で、年間合計66万返済しているのだと思います。
より正確には「月単位の表」にすればよいのですが、行数は12倍に増えます。

もし正確な表が必要であれば
（１）「返済年」の行を「返済月」に変える
（２）金利を「年利2.7%」を「月利0.225% (=2.7%/12)」に変える
（３）「毎年の返済額」を「毎月の返済額」に変える（平常月とボーナス月で異なれば、そのとおりに）
に修正して、「月単位の表」にしてみてください。
考え方は「年単位」と同じですが、実際のローンに近い条件の精度の高い計算になります。

あとは、上記の最後の年（「月ごと」だと最後の月）は、残債がゼロになる金額に微調整してください。

その表（補足の表も同じ）で、「残債の差額」が「元金の返済額」で、実際の返済額から「元金の返済額」を引いたものが「利息の返済額」になります。それらを積算して行けば、たとえば「返済総額が 500万円になったときに、元金でいくら分返済し終えたことになるのかが計算できます。#3さんのような「比例計算」にはならず、利息の比率の方が多いはずです。
最初のうちは「利息ばかり返している」ことになるからです。

No.3 回答者： xs200 回答日時： 2017/04/18 11:38

支払回数も同じだとすれば

7,616,948 : 5,000,000 = 6,500,000 : 元金
7,616,948 : 5,000,000 = 1,116,948 : 金利

元金
=6500000*5000000/7616948
=4,266,801

金利
=5000000*1116948/7616948
=733,199

総支払額
4,266,801+733,199=5,000,000 ; ぴったり

No.2 回答者： yhr2 回答日時： 2017/04/18 10:25

No.1です。

考え方として、

（１）その年の利息は、その年に支払う。
（２）利息を払った残りで、現金を返済する。

ということです。だから、最初のうちは利息ばっかり返して、元金がなかなか減りません。

（その年の返済額） - （その年の利息 = 前年の残債×利率）＝（元金の返済額）

です。

この式を使って

（前年の残債）-（元金の返済額）
＝（前年の残債）- [（その年の返済額）-（その年の利息 = 前年の残債×利率）]
＝ [（前年の残債）×（ 1 + 利率）] -（その年の返済額）

を計算しているのが No.1 のエクセルの式です。

No.1 回答者： yhr2 回答日時： 2017/04/18 10:15

エクセルで、

（１）1行目に元金と利率（%金利/100、この場合には 0.027）、毎年の返済額
（２）2行目に「=（1行目の「残債」＝「元金」）×（ 1 + 利率）-（毎年の返済額）」　　←これが「1年後の残債」
（３）3行目に「=（2行目の「残債」）×（ 1 + 利率）-（毎年の返済額）」　　←これが「2年後の残債」
（４）4行目に「=（3行目の「残債」）×（ 1 + 利率）-（毎年の返済額）」　　←これが「3年後の残債」
　　････
（N）N行目に「=（(N-1)行目の「残債」）×（ 1 + 利率）-（毎年の返済額）」　　←これが「(N-1)年後の残債」
　　････
（残債がゼロになるまで続ける）

という表を作れば、毎年の「借金の残高」が表にできます。(2行目の式を作れば、あとは全行「コピペ」で作れます）

毎年、単純なことを繰り返しているだけですから。

毎年の「借金の減少額」は((N-1)年後の残債) - （N年後の残債）ですが、「毎年の返済額」よりかなり少ないです。差額は「元金」を返しているのではなく、「利息」分を返しているからです。これと「毎年の返済額」の差が「利息分の返済額」ということです。
必要なら、これを計算する列を上記表の横に作って、これを毎年分計算して積算すれば、途中段階でもそこまでの返済額の内訳（「元金」分と「利息」分）の集計ができます。

文章では分かりづらいので、とにかくエクセルでやってみてください。