エクセルでクレジットカードのような分割払い表を作りたい

エクセルで、総額・支払いの始まる月・分割回数を入力すれば、支払い金額を月ごとに割り振ってくれるシートを作成したいです。

（手数料は考慮せず何回でもゼロとし、割り切れないときは初回の支払いに端数を入れ込む）

例えば、

・20万円の商品を3分割で購入
・10月から支払いスタート

という条件のときに、10月〜12月で分割して支払う金額を表示させたいのですが、月と金額と分割の関係がゴチャゴチャになり、うまくエクセルの表に落とし込めません……

どのように表現すればいいか、知恵をお貸しください。

No.4 ベストアンサー 回答者： goomania 回答日時： 2021/09/06 23:17

添付画像をご覧ください。

B列のB2、B3、B4に入力エリアを用意し、B2に「支払総額」、B3に「分割回数」、B4に「支払開始年月」を入力するとC列に「返済予定月」、D列に「返済予定金額」を表示するものです。
入力の注意点は、支払開始年月は西暦年月日を入力し、セルの書式設定で「年月」を表示するようにしている点です。
添付画像では、とりあえず分割回数120回(10年)まで、対応できるようにしています。

D2に

=$B$2-SUM($D$3:$D$121)

を記述しています。

また、C2に

=IF(ROW(A1)>$B$3,"",EOMONTH($B$4,ROW(A1)-1))

という数式を記述し、さらに、D3に財務関数のPMT関数を利率(手数料率)を0として利用することにして、

=IF(C3<>"",ROUND(PMT(0,$B$3,-$B$2,,),0),"")

という数式を記述し、それぞれ121行目まで下方向へコピーしています。

その上で、C2～D121まで、「条件付き書式設定」でセルの色と枠線を指定して、分割回数(添付画像①は12回、添付画像➁は3回)によって自動的に表の見かけのサイズが変わるようにしています。

この回答へのお礼

皆さんアドバイスありがとうございました！

他の方の意見も参考にしながら、無事に分割支払いの表を作成することができました！

お礼日時：2021/09/07 20:51

No.3 回答者： みく19 回答日時： 2021/09/04 23:38

例えばですが。

B10に式を入れて、後はオートフィルで入力できます。
商品が複数あるなら、列方向にコピペしてください。
年月しか表示していませんが、日付は1日にしてください。

No.2 回答者： トモクンアヤチャン 回答日時： 2021/09/04 23:23

添付参考にしてください。

D2=1 初回の回数＝1
E2=B2 初回の支払月
F2=B1-INT(B1/B3)*(B3-1) 　切り捨てた端数は初回に合計
D3=IF(ROW()-1>B$3,"",D2+1) 分割回数までカウントアップ
E3=IF(ROW()-1>B$3,"",IF(E2+1<13,E2+1,1)) 分割回数分の支払月
F3=IF(ISNUMBER(E3),INT(B$1/B$3),"") 　端数切捨て
D3:F3を下にコピーしてください（最大60回払いくらい？）

No.1 回答者： mygoonickname 回答日時： 2021/09/04 22:11

こんばんは。

実際、どの様なものが良いか不明ですが、テンプレートが無料でDLできる
　場所がありますので、それを使うか、参考にされては如何でしょうか？

https://template.k-solution.info/2006/01/04a4_1. …