年間金利を入力して、金利変動について複数年にわたるスムーズな月次予測を実行しようとしています。
言い換えると、2022 年 (10%)、2023 年 (15%)、2024 年 (12%) の金利はありますが、2022 年 1 月から 2022 年 12 月まで 0.8% (=POWER((1+0.1),(1/12))-1) として、2023 年 1 月に 1.17% に変更するのではなく、月ごとに滑らかに予測したいと考えています。変化する値 (2022 年 1 月は 0.8%、2022 年 2 月は 0.85% など) を考慮して値を滑らかにしたいと思います。
これをスムーズに月次予測するのを手伝ってくれる数学の専門家はいますか?
ありがとう！
マイク
答え1
つまり、1 月の利息をある金額 (この場合は正しい金額) にし、2 月にはある金額だけ増加し、3 月にはその 2 倍になり、12 月には 11 倍になるまでこれを繰り返して、利息が毎月その追加額だけスムーズに増加し、それでも年間の正しい合計利息になるようにします。おそらく、1 月の利息は 4.50 ドルで、12 月には 15.50 ドルになるまで毎月 1 ドルずつ増加します。
もちろん、これは金利が月単位や日単位ではなく年単位で複利計算されることを前提としています。年単位でない場合は、もう少し複雑です。(ちなみに、これは「単利」と呼ばれ、すべての計算をコンピュータで実行できるため、ほとんどなくなりつつあります。)
とにかく、数学の問題として、これはどのように解けると思いますか? そうですね、1 月には「x」の金額があり、2 月には「x+$1」という具合になります。これを 1 年間で合計すると、12 か月分の x と 1+2+3+...+9+10+11 = 66 になります。つまり、12x + 66 で、年間の利息と同じになります。たとえば、$120 とします。代数計算すると、x = $4.50 になります。つまり、毎月 $4.50、$5.50、$6.50...$13.50、$14.50、$15.50 になります。
しかし、それは「完璧」ではありませんよね? つまり、4.50 ドルです。なぜですか? 毎月大きく増える 2.00 ドルではだめですか? 理想的には、開始時の利子は 0 ドルで、すべてが「増加」するはずですが、x に 0 ドルを投入するだけでは、1 月には利子がまったくつきません。つまり、1 月に架空の「増加」額があり、2 月はその 2 倍、12 月はその 12 倍になるとします。そうすれば、開始額が毎月増加する額と等しくなり、スムーズになります。それなら完全にスムーズです!
それで、もう一度計算をします。今度は 1+2+3+...+10+11+12 (12 の値で、上記の 11 ではありません)。結果は 78 です。つまり、分配する「バンプ」は 78 個あり、1 月に 1 個、2 月に 2 個、というように 12 月に 12 個になります。ああ、これで明らかになりましたね。
120 ドルの例では、120 ドル / 78 = 20 ドル / 13 = 1 回あたりの「バンプ」は約 1.54 ドルになります。
後は、その年の合計利息をその分割に代入するだけです。年間 10% の単利で 10,000 ドルの場合、1,000 ドルの利息を次のように分配します。1,000/78 = 約 12.82 ドル/月。つまり、2022 年は 1 月が 12.82 ドル、2 月が 25.64 ドル、3 月が 38.46 ドルというようになります。12.82 ドルから始まり、毎月 12.82 ドルずつ増加します。ほぼ「スムーズ」の定義です。4 セント余分に差し込む必要がありますが、3 か月ごとに 1 セントずつ差し込むのは簡単で、スムーズさにはあまり影響しません。
「78」は毎年同じなので、その年の元金と単利を合計し、それらを掛けて 78 で割ります。これは Excel なので、その計算にその月の「月番号」を掛けるだけです。
たとえば、列 A にその年の元金 (計算または手入力)、列 B に利率 (元金と同様、表示方法は自由)、列 C から N に月があり、各行が新しい年であるとします。各月の列の見出しを実際の日付 (1/1/"year" など) にして、"mmm" のように書式設定すると、"Jan"、"Feb" などと表示されるので、 を使用する=MONTH(C1)と 1 が返され、その列の乗数として使用できます。または、テキスト見出しを 10 通りの方法のいずれかで日付に変換して、MONTH()それに使用することもできます。どちらの方法でも、または別の方法でも、1 ～ 12 の乗数が得られます。したがって、各月のセルには次のようになります。
=((Principal * Interest Rate) / 78) * MONTH(Heading)
元金と利率は A1 と B1 にあり、「見出し」は C1 から N1 までなので、具体的には次のようになります。
=((A1 * B1) / 78) * Month(N1)
セル C2 に入力し、それを 12 か月すべてと使用する各行にコピーして貼り付けます。一度入力して、一度貼り付けるだけです。
機能があればSpill、数式を 1 つ記述するだけで、必要なすべてのセルにスピルされます (たとえば、3 年ある場合は、行 2、3、4 が使用されます)。
=((A2:A4 * B2:B4) / 78) * SEQUENCE(1,12)
を持っている場合、関数Spillも使用できSEQUENCE()、すべての月を使用していることがわかっているので、1 ～ 12 のシーケンスを生成することは、MONTH()見出しを使用した「巧妙な」方法と同じくらい効果的です。 と入力して Enter キーを押すと、36 個のセルがすべて入力されます。
余分なセントや少ないセントを捕捉するためにを追加しROUND()、最後のセル (12 月) で、計算した利息からその行の他の 11 セルの合計を実際に減算すると、利息がすべて加算されます。これ以上ごまかさないでください。それは物事を「正しい」ものに近づけるだけであり、セントまで「正しい」ことが目標ではありません。目標は滑らかさなので、各月の最終結果を丸めることは「正しい」方法よりも適切です。
長い答えは、実際よりもずっと悪く聞こえます。それは、上記の数式、または必要なすべてのセルに貼り付ける必要がある数式だけです。実際に使用する列と、その年の元金と利率の位置を調整すれば、完了です。