売掛金の年齢表を作るとき、経過日数を請求日から数えると遅延が多く出る。支払サイトの中でまだ期日が来ていないものまで、31日以上の欄に入るからだ。
未入金120件で両方を計算した。請求日から31日以上たっているのは70件。実際に支払期日を過ぎているのは40件だった。1.75倍に膨らむ。
以下の計算は、2026年9月21日にMicrosoft 365のExcel（バージョン16）で行った。120件の売掛は式を確かめるために作った架空のものだ。会計上の処理は扱っていない。
請求日から数えると、期日前が遅延に入る
月末締め翌々月末払いの取引先を考える。5月10日に請求した分の支払期日は7月31日だ。
6月末の時点で、請求日からは50日たっている。31日から60日の欄に入る。ところが期日はまだ1か月先で、遅れてはいない。
支払サイトが取引先ごとに違うので、請求日から数えると、サイトの長い取引先ばかりが遅延に見える。実際には約束どおりに払っている相手だ。
年齢表の目的が「回収が遅れている先を見つけること」なら、数える起点は支払期日になる。
支払期日を式で出す
期日は、締め日と支払サイトから計算できる。取引先ごとにこの3つを持っておく。
締め日: =IF(締め日=0,EOMONTH(請求日,0),
IF(DAY(請求日)<=締め日,DATE(YEAR(請求日),MONTH(請求日),締め日),
DATE(YEAR(請求日),MONTH(請求日)+1,締め日)))
支払期日: =IF(支払日=0,EOMONTH(締め日,月差),
DATE(YEAR(EDATE(締め日,月差)),MONTH(EDATE(締め日,月差)),支払日))月末締めなら締め日に0、月末払いなら支払日に0を入れる。20日締め翌月10日払いなら、締め日20、月差1、支払日10になる。
この式は支払予定表と同じものだ。払う側と受け取る側で、同じ計算を逆から使う。払う側の作り方は支払予定表の記事に書いた。
経過日数と区分はここから出る。
期日からの日数: =今日-支払期日
区分: =IF(日数<=0,"期日前",IF(日数<=30,"1-30",
IF(日数<=60,"31-60",IF(日数<=90,"61-90","91日以上"))))期日前をマイナスの日数として持つのが要点になる。0で切ると、期日当日が遅延側に入る。
120件で計算した結果
未入金120件のうち、期日前が80件、期日を過ぎているのが40件だった。
金額では、売掛の総額93,208,000円に対して遅延が28,366,000円。30.4%にあたる。91日以上のものが12,182,000円ある。
件数では3分の1だが、金額では3割。件数と金額でだいたい同じ割合になった。ここがずれる場合、金額の大きい取引が遅れているか、小さい取引ばかりが溜まっているかのどちらかになる。
91日以上の12,182,000円は、このまま放っておくと回収が難しくなる帯だ。ここだけは件数で見ずに、1件ずつ相手と話す。
件数で追うと、相手を間違える
取引先ごとに集計すると、順番が入れ替わる。
件数がいちばん多いのは中央印刷の11件だ。ところが金額では594万円で2位になる。金額の1位は東和電機の630万円で、件数は8件しかない。
件数順で電話をかけると、中央印刷から始めることになる。金額順なら東和電機だ。回収の効果が大きいのは後者になる。
上位2社で遅延金額の43.2%を占めていた。8社のうち1社は遅延ゼロだ。全部の取引先に同じ督促を送るより、2社に絞って話したほうが動く。
上位2社の金額: =LARGE($遅延金額の列,1)+LARGE($遅延金額の列,2)
占める割合: =ROUND(上位2社/合計*100,1)平均で何日遅れているかを出す
区分だけでは、遅れ方の傾向が見えない。平均日数を2つ出すと分かる。
単純平均: =ROUND(AVERAGEIFS($日数,$日数,">0"),1)
金額で重み付け: =ROUND(SUMPRODUCT(($日数>0)*$日数*$金額)/SUMPRODUCT(($日数>0)*$金額),1)120件で計算したら、単純平均が85.5日、金額で重みを付けると89.3日だった。3.8日の差がある。
金額で重みを付けたほうが長いということは、金額の大きい売掛ほど長く遅れているという意味になる。逆なら、小口が溜まっているだけで、金額の大きいものは順調に回収できている。
この2つの差が大きいほど、金額順に追う意味がある。差がほとんどないなら、遅れ方に偏りがないので、区分の古いものから順に片づけてよい。
1件あたりの平均金額も出してみた。遅延しているものが709,150円、期日前のものが810,525円。遅れている売掛のほうが1件あたりは小さい。件数の割に金額が積み上がっていない、という読み方になる。
いちばん長い遅延は164日で、金額は880,000円だった。この1件は年齢表の外で扱う。
区分ごとに何をするか決めておく
年齢表を作っただけでは回収は進まない。区分ごとの動きを先に決めておく。
期日前は何もしない。ここに手を出すと、相手に不信感を持たれる。
1-30日は事務の範囲で確認する。入金の行き違い、請求書の未着、振込の締切のずれ。ほとんどがこれで解決する。連絡の記録は残す。
31-60日は営業に渡す。事務の確認で解決しなかったということは、相手の側に事情がある。取引の継続そのものに関わるので、担当者が話す。
61-90日は、次の取引を止めるかどうかを決める段階になる。出荷を続けると、回収できない金額が増える。
91日以上は、1件ずつ経営に上げる。ここは表で処理する話ではない。
=IF(区分="期日前","",IF(区分="1-30","事務で確認",
IF(区分="31-60","営業に渡す",IF(区分="61-90","取引を止めるか判断","経営に上げる"))))この列を足しておけば、表を開いたときに次の動きが決まる。誰がやるかまで書いておくと、渡し忘れが減る。
年齢表の使い方
配布している年齢表は3枚でできている。入力するのは黄色の列だけだ。
「取引先」に取引先名、締め日、支払の月差、支払日を入れる。この4つが条件になる。
「売掛」に取引先、請求日、金額の3つを入れる。締め日と支払サイトはVLOOKUPで取引先シートから引くので、入力しない。支払期日、経過日数、区分が右側に出る。期日を過ぎた行は赤くなる。
「設定」に今日の日付を入れる。月末に締めて見るなら、その日を入れる。過去の時点の年齢表も、この日付を変えれば出せる。
右側に区分ごとの件数と金額の集計がある。遅延の金額と割合も出る。
入金済みの行は消すか、別のシートに移す。残っている行が未入金という形にしておけば、合計がそのまま売掛残高になる。
踏んだ失敗
請求日から数えた年齢表を営業に渡していた。支払サイトの長い取引先が毎月上位に並び、そのたびに確認してもらっていた。実際には期日前で、確認は無駄だった。営業からの信用も落ちた。
期日を手で入れていたこともある。取引先が増えるたびに間違いが混ざった。締め日と支払サイトを取引先シートに持たせて、売掛の行では引くだけにした。条件が変わったときに直すのも1か所で済む。
件数順に督促していた年もある。小さい金額の未入金が多い取引先ばかりに時間を使い、金額の大きい先が後回しになった。順番を金額にしてから、回収までの日数が短くなった。
入金済みの行を残したまま集計していたこともある。売掛残高が実際より多く出て、資金繰りの見通しが狂った。消し込んだ行はその日のうちに移す。消込の突き合わせは経費精算のチェックの記事に書いた式がそのまま使える。
確認していないこと
一部入金があった場合の扱いは入れていない。残高の列を足して、そこがゼロになったら消す形にする必要がある。
相殺や値引きで金額が変わった場合も扱っていない。金額の列を直接書き換えると、元の請求額が分からなくなる。調整の行を別に足すほうが追える。
貸倒れの判断と会計上の処理も扱っていない。91日以上の帯に何が入っているかを見るところまでで、その先は税理士に確認してほしい。
支払期日が土日祝に当たったときの扱いも入れていない。取引先によって前営業日か翌営業日かが変わる。1日か2日の差なので区分は動かないことが多いが、期日当日で判定したい場合は調整が要る。月次の作業に組み込む形は月次の締めの記事と同じで、第何営業日に見るかを決めておく。
