ややこしい計算

とてもややこしい計算というのがあります。
たとえば、こんな計算の必要に遭遇しました。建物の改修工事費用の積算です。

建物の改修工事費用の計算のファクター

    一回あたりの工事費用    「工事金額」
    工事を行う周期             「工事周期」
    工事費用の借入金          「地方債充当」
    返済の据え置き期間       「据置期間」
    完済年数                      「償還年数」

対象が公営住宅だったので、借入金としては地方債を起債することにしているため、そういう名称になっています。

改修工事には、外壁改修、屋上防水改修、給水管改修、ガス管改修、排水管改修の５種類があり、それぞれごとにそのファクターが異なります。
建物の耐用年数（建物構造から別途計算しています）のうちに、それぞれ何回工事を行うのかも種類によって違ってきます。したがって、全体で何回工事を行うのかもわかりません。
どの種類の改修工事も多額の費用がかかるので、それを自己資金だけではなく、借入（ここでは起債）で賄います。起債の償還は一定の据置期間の後、予め決められた償還年数にかかわらずその建物の耐用年数内に完済することが必要です。
それから最終回の工事から耐用年数までの残された年数が極端に短い場合は、最終回の改修を行わずに簡単な修繕で済ませること、なども織り込まなくてはなりません。

こういったことを、エクセルのワークシート関数だけで計算することは、可能でしょうが、とても複雑な計算式になって、可読性が著しく悪くなり、プログラムのメンテナンスに大混乱をきたしそうです。

さて、どうしたものか。
そこで用意したのは、図のようなワークシートです。

該当工事の一回ごとに、（計算欄）として準備した中の４０行に、その回の工事年次から計算した償還開始年と償還年数をマクロで記入し、それにもとづいてワークシート関数で「工事金額」「起債額」「期首残高」「元金返済」「利息」、それをあわせた「返済額」と「期末残高」を計算します。その結果を、最下部に用意した３行にそれぞれ挿入して、各年の経費を計算する、というのが大まかなスキームです。
図の ①②③ を必要なだけ繰り返します。

（計算欄）は、実際には見る必要がないので、通常は全体の行の高さを０にして隠しています。

これだけでは、チェックのしようがないので、一回ずつステップ実行できるような機能をつけました。それが、赤丸で示した「開閉ボタン」と「STEP」ボタンです。

開閉ボタンをクリックすると、（計算欄）が見えるようにしてあります。その中の STEP ボタンをクリックすると、外壁改修の一回目の結果が（計算欄）の中に表示されます。外壁改修を１５年周期としていると、一回目は１５年目になり、そこで工事が発生し、起債が行われます。それから据置期間は利息のみ支払い、その後元金の返済が行われます。画面は１５年目のところにスクロールします。
NEXT ボタンで次に進みます。CONT ボタンで最後まで実行します。

各回の計算結果は、計算終了とともに、下の３行のそれぞれの欄の直後に挿入されていきます。

ワークシート上での計算とマクロとは当然同期していませんので、計算終了を確認してからコピーして挿入する必要があります。計算終了の確認は、
　　　　Application.CalculationState = xlDone
で行えます。

実は、挿入ではなくて、たとえば「工事金額」と「起債額」の行の間には２０数行の見えない行を 行の高さ＝０ で隠しているのですが、そこに記載されるごとに AutoFit で行を開いているのです。

さて、どうしたものかと思案したあげくの計算方法でした。これは、技法の紹介というよりも、こんなやりかたでやった、という記録のつもりです。