[[20190611225854]] 『「開いた日のリースの残回数と残高を自動計算され』(Lammy) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『「開いた日のリースの残回数と残高を自動計算される」[miffy] について』(Lammy)

投稿
[[20101207173811]] 『開いた日のリースの残回数と残高を自動計算される』(miffy) 
について...

支払サイトが毎月ではなく、3か月に1回、3か月分をまとめて支払うリース会社もあるのですが、そういうケースでの支払済回数、残回数の求める関数はありますでしょうか。

< 使用 Excel:Office365、使用 OS:Windows10 >


 具体的なレイアウトとサンプルデータ(数例)をアップしてください。(正解値もお忘れなく)

(半平太) 2019/06/11(火) 23:46


リース料 リース開始日 リース終了日 初回支払日 最終支払日 リース支払額 支払サイト 支払日 支払回数 支払済回数 残回数 残高 支払総額

10,800 2018/10/1 2023/9/30 2019/01/31 2023/12/31 32,400 3カ月 月末 20 2 18 583,200 648,000

このような形です。
リース開始日とリース終了日の月数=支払回数(要するにリース料を毎月払う)はmiffyさんへの回答で分かったのですが、リース期間月数と支払回数が異なる場合(上の例は3カ月に一度まとめて払う)にどういった関数を作りこめばよいかご教示願います。

また、ある一時点で未払い残高を一年以内と一年超に分けて表示したいのでそちらの関数もご教示いただけるとありがたいです。

よろしくお願いします。
(Lammy) 2019/06/12(水) 00:33


 サンプルが1つしかないんですが・・

 (1) 「支払日が月末以外のケース」や「リース開始日が月初以外のケース」はありませんか?

 (2)支払日が休日に当たる場合、支払日はどっちにズラすんですか?
    (土日祝は考慮しないでいいんですか?)

(半平太) 2019/06/12(水) 09:35


(1)は今のところございません。
  開始日は1日、終了日は末日で今のところ統一しています。
(2)は、前営業日(金曜日)となります。

よろしくお願いします。

もう一つのケース

28,944 2018/7/1 2023/9/24 2019/1/31 2023/10/31 86,832 3ヶ月 月末 20(契約期間は63ヶ月ですが据置期間が3ヶ月あります。ここも関数で自動化したいです) 2 18 173,664 1,562,976

残高は2019年6月末現在。日付基準を任意で指定したいです。(例:2019/3 2019/9)
(Lammy) 2019/06/12(水) 11:22


質問が飲み込めてないので回答ではありませんが、情報整理のお手伝い。

 ___A____________B___________C___________D____________E____________F____________G________H_______I_________J________K_______L_________M_____
 リース料 リース開始日 リース終了日  初回支払日  最終支払日  リース支払額  支払サイト 支払日 支払回数 支払済回数 残回数   残高     支払総額 
   10,800    2018/10/1    2023/9/30  2019/01/31  2023/12/31        32,400       3カ月   月末       20          2    8    583,200    648,000
   28,944    2018/07/1    2023/9/24  2019/01/31  2023/10/31        86,832       3ヶ月   月末       20          2   18    173,664  1,562,976

(もこな2) 2019/06/12(水) 12:21


↑行番号入れ忘れました。 訂正します。

 ______A____________B___________C___________D____________E____________F____________G________H_______I_________J________K_______L_________M_____
  1 リース料 リース開始日 リース終了日  初回支払日  最終支払日  リース支払額  支払サイト 支払日 支払回数 支払済回数 残回数   残高     支払総額 
  2   10,800    2018/10/1    2023/9/30  2019/01/31  2023/12/31        32,400       3カ月   月末       20          2    8    583,200    648,000
  3   28,944    2018/07/1    2023/9/24  2019/01/31  2023/10/31        86,832       3ヶ月   月末       20          2   18    173,664  1,562,976

(もこな2) 2019/06/12(水) 12:31


もなこ2さん補足いただき大変ありがとうございます!

3行目のリース終了日は2023/9/30です。
ほかは表で合ってます。
支払済回数をどうやったら2回とできるのかの関数が知りたいです。
(要するに3ヶ月毎に1回、となるよう)

よろしくお願いします。
(Lammy) 2019/06/12(水) 12:38


 >リース料  リース開始日  リース終了日  初回支払日  最終支払日  リース支払額  支払サイト  支払日  支払回数  支払済回数  残回数  残高    支払総額 
 >  10,800  2018/10/1     2023/9/30     2019/1/31   2023/12/31        32,400  3カ月       月末          20           2      18  583,200  648,000 

 ご提示のサンプルは、本当に正しいですか?

 リース開始が、10/1 なら 初回支払日は 201/12/31 じゃないですか?(3ケ月目に3ケ月分ですから)

 仮に Lammyさんの 2019/1/31(4か月目)が初回日で正しいとして、
 以後、3ケ月毎ですから、4、7、10、1月が支払月にならないとおかしいですよね?

 なのに、何故12月が最終支払日になるんですか?

(半平太) 2019/06/12(水) 12:52


失礼しました。
サンプルは、

28,944 2018/07/1 2023/9/24 2019/01/31 2023/10/31 86,832 3ヶ月 月末 20 2 18 173,664 1,562,976

のみとさせてください。

よろしくお願いします。
(Lammy) 2019/06/12(水) 21:39


   行 ___A___ ____B____ ____C____ ____D____ _____E_____ ___F___ _G_ __H__ _I_ _J_ _K_ __ L __ ____M____
    2 28,944  2018/7/1  2023/9/30 2019/1/31 2023/10/31  86,832    3 月末   20   2  18 173,664 1,562,976
                               ↑
                ですね?

  1. 日付データは、土日祝に関係なく、とりあえず月初と月末を形式的に入れるんですか?
    それとも「前営業日(金曜日)」の絡みで月末じゃなかったりすることもあるんでしょうか?

  2.M列(支払総額)が1,562,976になる理由を教えてください。

  >(契約期間は63ヶ月ですが据置期間が3ヶ月あります。
  3.据え置き期間3ヶ月というのは、初めの3ヶ月はタダにするという意味ですか?

  >残高は2019年6月末現在。日付基準を任意で指定したいです。(例:2019/3 2019/9)
  4.その基準日を何処のセルに入れる積りですか?

  >また、ある一時点で未払い残高を一年以内と一年超に分けて表示したい
  5.レイアウトとサンプルデータを提示してください。(正解値もお忘れなく)

(半平太) 2019/06/12(水) 22:51


1.はい。形式的に月末日を入力し、土日の場合は前営業日(金曜日)となります。
2.ごめんなさい。86,832*20=1,736,640 でした。
3.はい。実質そういう意味になります。
4.こちらについては、1行目の任意セル(例:X1)に入力し、支払済セル(J2)のTODAY関数を$X$1に置き換えたら出来ました。違ってたらご指摘ください。
5.以下の通りです。

______A____________B___________C___________D____________E____________F____________G________H_______I_________J________K_______L_________M_____
リース料 リース開始日 リース終了日 初回支払日 最終支払日 リース支払額 支払サイト 支払日 支払回数 支払済回数 残回数 残高 支払総額
28,944 2018/7/1 2023/9/30 2019/01/31 2023/10/31 86,832 3ヶ月  月末 20 2 18 1,562,976 1,736,640

以上、よろしくお願いします。
(Lammy) 2019/06/12(水) 23:35


 >1.はい。形式的に月末日を入力し、土日の場合は前営業日(金曜日)となります。 

 1.こちらの質問の意図が不明瞭だったと思いますが、
   「開始日は1日、終了日は末日で統一」されているとのことなので、

   1年の契約なら、例えば始期 1/1 終了日12/31 と必ず入力されているのか、それとも
   土日祝を避けて、例えば始期 1/4 終了日12/28 と入力されることもあるのでしょうか?

  と言う疑問なんですけど、それによって作る数式が少し変わるので。

 2.なお、X1セルの基準日が、当月最終営業日なら、暦上の月末日でなくても
   月末に達したものとして、支払い済みの計算を行います。それでいいですね?

 >5.以下の通りです。
 3.どこに「一年以内と一年超の数値」や「ある時点」があるんですか?

(半平太) 2019/06/13(木) 07:47


1.入力は全て曜日は加味せず、
  1年の契約なら、例えば始期 1/1 終了日12/31 と必ず入力されています。
2.おそらく大丈夫ですが、
  1.同様、実際の支払日は考慮せず末日付で入力します。(決算日は曜日関係ないので)
  今月で言うと、2019/6/30

3.失礼しました。
  時点は「2019/6/30」一年以内は347,328 一年超は1,215,648 です。

よろしくお願いします。
(Lammy) 2019/06/13(木) 09:29


 >2.おそらく大丈夫ですが、 

 いや、土日祝関係なく暦上の月末を入れるなら、
 その方が楽なのでそうしてください。

 1年以内・超は後述するとして、とりあえず、それ以外の部分

 (1) F2セル =A2*G2
 (2) I2セル =(DATEDIF(D2+1,E2+1,"m")+G2)/G2
 (3) J2セル =IF($X$1<D2,0,(MIN(I2,INT((DATEDIF(D2+1,$X$1+1,"m")+G2)/G2))))
 (4) K2セル =I2-J2
 (5) L2セル =F2*K2
 (6) M2セル =F2*I2

 <結果図>
 行 _____A_____ ______B______ ______C______ _____D_____ _____E_____ ______F______ ___G___ ___H___ ____I____ _____J_____ ___K___ ____L____ ____M____ _ ____X____
  1 リース料/月 リース開始日  リース終了日  初回支払日  最終支払日  リース支払額  サイト  支払日  支払回数  支払済回数  残回数  残高      支払総額    2019/6/30
  2      28,944 2018/7/1      2023/9/30     2019/1/31   2023/10/31        86,832       3  月末          20           2      18  1,562,976 1,736,640            
  3      28,944 2018/7/1      2019/5/31     2018/7/31   2019/5/31         28,944       1  月末          11          11       0          0   318,384                                            
                                           ↑
                                          単に月数を入れてください。

 さて、1年以内・超についてですが、
 これは、リースの支払が発生してからカウントするのでしょうか?

 例  基準日1   基準日2    初回支払い
    2018/1/31    2018/12/31  2019/1/31

 上記例で、基準日1の時、まだ一回も払っていないですが、
      一年内に初回支払日が入ってきますので、1年内として1回分計上するんでしょうか?

      基準日2の時、まだ一回も払っていないですが、
      一年内には、初回(1月)、2回(4月)、3回(7月)、4回(10月)の支払日が到来します。
      その4回分を1年内として計上するんでしょうか?

(半平太) 2019/06/13(木) 19:24


 1年以内・超については、リースの支払が発生してからカウントします。
基準日を初回支払日と同じ2019/1/31にすると、支払済回数が0になってしまいました。
1が正しいと思うのですが、どうすればよろしいのでしょうか?

また、支払回数I2セル =(DATEDIF(D2+1,E2+1,"m")+G2)/G2を入力し、

 初回支払日2018/5/31最終支払日2023/4/30サイト3のケースだと、支払回数が20.666667

となってしまいました。

月数カウントは月末日が28日、30日、31日、4年に一回29日とあり、どんな末日から末日でも一月とカウントしたいのですがなかなか難しいですね。

ご教示のほどよろしくお願いいたします。

(Lammy) 2019/06/13(木) 23:23


 >基準日を初回支払日と同じ2019/1/31にすると、支払済回数が0になってしまいました。

 あれ? こちらでは1になりますけど。下図ご参照。各データがそちらと同じになっていますか?

 <結果図>
  行 _____A_____ ______B______ ______C______ _____D_____ _____E_____ ______F______ ___G___ ___H___ ____I____ _____J_____ ___K___ ____L____ ____M_____ ____X____
   1 リース料/月 リース開始日  リース終了日  初回支払日  最終支払日  リース支払額  サイト  支払日  支払回数  支払済回数  残回数  残高      支払総額   2019/1/31
   2      28,944 2018/7/1      2023/9/30     2019/1/31   2023/10/31        86,832       3  月末          20           1      19  1,649,808 1,736,640           

 >また、支払回数I2セル =(DATEDIF(D2+1,E2+1,"m")+G2)/G2を入力し、 
 >初回支払日2018/5/31最終支払日2023/4/30サイト3のケースだと、支払回数が20.666667 
 >となってしまいました。 

 その最終支払日はあり得ないんじゃないですか?

 5月から支払いを開始したら、以後、8、11、2、5月が支払い月(3ヶ月毎)ですから、
 4月が最終支払日になるハズないと思いますが、なぜ4月になるんですか?

 >月数カウントは月末日が28日、30日、31日、4年に一回29日とあり、
 >どんな末日から末日でも一月とカウントしたいのですがなかなか難しいですね。

 そう作式してありますけど。。

(半平太) 2019/06/13(木) 23:48


すみません。
式が違ってたみたいです。失礼しました。

そうですよね。
おそらく最終支払日のパンチミスかと思われますので明日会社で確認します。

多岐にわたりご教示いただき大変感謝しております。
ありがとうございました。

また疑問点が生じたらよろしくお願いいたします。

(Lammy) 2019/06/14(金) 00:15


 あれ? まだ 1年以内・超が解決してないですよ。

 >1年以内・超については、リースの支払が発生してからカウントします。

 それだと、以下になります。

 (1) N2セル =IF(X1<D2,0,MIN(K2,12/G2)*F2)
 (2) O2セル =IF(X1<D2,0,L2-N2)

 <結果図>
  行 _____D_____ _____E_____ ______F______ ___G___ ___H___ ____I____ _____J_____ ___K___ ____L____ ____M____ ____N____ ____O____ _ ____X____
   1 初回支払日  最終支払日  リース支払額  サイト  支払日  支払回数  支払済回数  残回数  残高      支払総額  一年以内  一年超      2019/6/30
   2 2019/1/31   2023/10/31        86,832       3  月末          20           2      18  1,562,976 1,736,640  347,328  1,215,648            
   3 2018/7/31   2019/5/31         28,944       1  月末          11          11       0          0   318,384        0          0            

(半平太) 2019/06/14(金) 09:12


そうでしたそうでした(^^;)

未支払残高を、基準日から一年ごとに出したいんですけど可能でしょうか?
今回で言うと、2019/6〜2020/5分、2020/6〜2021/5分、2021/6〜2022/5分・・・

よろしくお願いします。
(Lammy) 2019/06/14(金) 09:22


 1.N1セルから右に連番で、1.2.3.4・・ と入れるものとします。

 2.N2セル =IF($X$1<$D2,0,MIN($F2*12/$G2,MAX(0,$L2-$F2*12/$G2*(N$1-1))))
   右にコピー

 <結果図>
  行 ___N___ ___O___ ___P___ ___Q___ ___R___ _S_ _T_ _U_ _V_ _W_ ____X____
   1    1       2       3       4       5    6   7   8   9     2019/6/30
   2 347,328 347,328 347,328 347,328 173,664  0   0   0   0               

(半平太) 2019/06/14(金) 10:53


ありがとうございます。
しかし、行タイトル(1行目)に1年未満 1年〜2年 2年〜3年・・・としたいので

N2セルに =IF(DATEDIF($X$1,E2,"M")<=12,L2,(F2/G2)*12)) とし、
O2セルに =IF($L2-N2>=N2,N2,$L2-N2)
P2セル以降は、累計を足し込む式にしたらできました。

ちょっとイレギュラーな案件で、期間81ヶ月の8回払い、初回が667,764(3ヶ月分)2回目から7回目が2,671,056(12ヶ月分)8回目最終支払額が1,335,528(6ヶ月分)支払総額18,029,628というのがあります。

 月リース料  開始日 終了日 初回支払日 最終支払日
  222,588  2019/1/4 2025/10/3 2019/4/30  2026/8/31

ここまでイレギュラーだと関数対応は難しいですよね。

ちなみに、最終支払日を2025/10/31としたら支払総額と残高は合いました。

(Lammy) 2019/06/14(金) 16:52


 >ここまでイレギュラーだと関数対応は難しいですよね。

 ・・と言われましても、どこまでイレギュラーなのかこちらでは分かり兼ねます。

 沢山あるなら、パータンを分析して、
 それに見合ったデータを入れて、計算させることになるでしょうし、
 稀なら、個別対応になるんじゃないですか?

(半平太) 2019/06/14(金) 17:39


説明不足ですみません。
先ほどの例は100件に1件程度なので1%の出現率レベルです。
手入力等で個別対応します。
(Lammy) 2019/06/15(土) 00:25

お世話になります。

行 _____D_____ _____E_____ ______F______ ___G___ ___H___ ____I____ _____J_____ ___K___ ____L____ ____M____ ____N____ ____O____ _ ____X____

   1 初回支払日  最終支払日  リース支払額  サイト  支払日  支払回数  支払済回数  残回数  残高      支払総額  一年以内  一年超      2019/5/31
   2 2019/1/31   2023/10/31        86,832       3  月末          20           2      18  1,562,976 1,736,640  347,328  1,215,648            
   3 2018/7/31   2019/5/31         28,944       1  月末          11          11       0          0   318,384        0          0

支払が発生する行の支払額だけ表示させることは可能でしょうか?
上記の例だと2019/5/31現在(X列)なので3行目のみ(28,944)を表示させる。
2行目は初回支払日2019/1/31の3ヶ月サイトなので2019/4/30、2019/7/31となり、2019/5/31は飛ばされる。
よろしくお願いします。

(Lammy) 2019/06/17(月) 17:47


 >2行目は・・飛ばされる。 

 F2セルの86,832が目に見えなくなればいいと言うことですね?(=実体値は変えない)

 そうなら条件付き書式でやることになると思いますが、
 まさか、実体値を空白文字にしたい、と言う趣旨ではないですね?

(半平太) 2019/06/17(月) 23:20


おっしゃる通りです。
新たに列を挿入して、2019/5/31に引き落とされた額を表示したいのです。
ただし、合計額も求めたいので86,832がカウントされてはまずいです。
よろしくお願いします。

(Lammy) 2019/06/17(月) 23:43


 新たに挿入した列での話だったですか・・

 どこに挿入するのか分かりませんが、元のF列とX列の位置が変わらないとしたら、

 2行目 =IF(OR(INDEX(MOD(MONTH($D2)+INT(ROW(INDIRECT("A12:A23"))/$G2)*$G2-1,12)+1=MONTH($X$1),0)),F2,"")

(半平太) 2019/06/18(火) 08:12


ありがとうございます!
できました。

関数は一つ一つ後ほど復習したいと思います。

取り急ぎ、INDIRECT("A12:A23")はどういう意味なんですかね?

他はなんとなく分かるんですが、A12:A23 のセル指定というのがどうもピンとこなくて・・・。
(Lammy) 2019/06/18(火) 10:03


すみません。
初回支払日が基準日より先日のものも入ってしまいます。

例)初回支払日2019/12/31 基準日2019/5/31 支払サイト1ヶ月

ご教示のほど、よろしくお願いします。

(Lammy) 2019/06/18(火) 10:24


 >INDIRECT("A12:A23")はどういう意味なんですかね?
 >他はなんとなく分かるんですが

 INDIRECTを使ったのは、後で行削除されたりして行番号が変化するのを防ぐためです。
 行の削除・挿入が無ければ単に Row(A$12:A$23) で済む話です。

  それで{12;13;14;15;16;17;18;19;20;21;22;23}の配列が得られます。

  本当は{0;1;2;3;4;5;6;7;8;9;10;11}     の配列を得たいのですが、
  それだと、ROW(A1:A12)-1 としなければならず、「-1」がワン工程余分に入ってしまう。

  どの道、後で12で割った余りを出すので、一律12多くても害にならない、と言う見通しがあってのことです。

 >初回支払日が基準日より先日のものも入ってしまいます。 
 >           2行目 =IF(OR(INDEX(MOD(MONTH($D2)+INT(ROW(INDIRECT("A12:A23"))/$G2)*$G2-1,12)+1=MONTH($X$1),0)),F2,"")
                       ↓
 =IF(MEDIAN($X$1,D2,E2)=$X$1,IF(OR(INDEX(MOD(MONTH($D2)+INT(ROW(INDIRECT("A12:A23"))/$G2)*$G2-1,12)+1=MONTH($X$1),0)),F2,""),"")

(半平太) 2019/06/18(火) 11:31


毎度ありがとうございます!

非常に助かり感謝申し上げます。

関数は、なかなか奥が深く難しいですね。

いろいろ勉強していきたいと思いました。
(Lammy) 2019/06/18(火) 12:57


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.