抵押
如何計算貸款固定支付（如 PMT）但每年兩次額外支付雙倍支付（無利息）
這是我在這個社區的第一篇文章，已經嘗試找到我的答案，但沒有成功:(。
我正在研究一種方法來理解貸款背後的公式，該公式允許客戶每年兩次額外支付相同的月度金額而不會增加利息，這種選擇適用於某些銀行的靈活支付，在秘魯等地很常見。
一個例子（取自真實的攤銷時間表）：
+----+------------+-------+----------+--------------+-----------+------------+----------------------------------+ | # | Date | Month | Payment | Amortization | Interests | Balance | Comment | +----+------------+-------+----------+--------------+-----------+------------+----------------------------------+ | 1 | 2015-04-30 | Apr | 2,699.00 | -332.10 | 3,031.10 | 439,425.00 | First payment, usually different | +----+------------+-------+----------+--------------+-----------+------------+----------------------------------+ | 2 | 2015-05-31 | May | 2,715.34 | 39.90 | 2,675.44 | 439,757.10 | | +----+------------+-------+----------+--------------+-----------+------------+----------------------------------+ | 3 | 2015-06-30 | Jun | 2,711.25 | -53.40 | 2,764.65 | 439,717.20 | | +----+------------+-------+----------+--------------+-----------+------------+----------------------------------+ | 4 | 2015-07-31 | Jul | 5,614.66 | 2,939.14 | 2,675.52 | 439,770.60 | Double payment | +----+------------+-------+----------+--------------+-----------+------------+----------------------------------+ | 5 | 2015-08-31 | Aug | 2,708.01 | -127.38 | 2,835.39 | 436,831.46 | | +----+------------+-------+----------+--------------+-----------+------------+----------------------------------+ | 6 | 2015-09-30 | Sep | 2,716.12 | 57.70 | 2,658.42 | 436,958.84 | | +----+------------+-------+----------+--------------+-----------+------------+----------------------------------+ | 7 | 2015-10-31 | Oct | 2,716.14 | 58.08 | 2,658.06 | 436,901.14 | | +----+------------+-------+----------+--------------+-----------+------------+----------------------------------+ | 8 | 2015-11-30 | Nov | 2,712.08 | -34.50 | 2,746.58 | 436,843.06 | | +----+------------+-------+----------+--------------+-----------+------------+----------------------------------+ | 9 | 2015-12-31 | Dec | 5,615.47 | 2,957.55 | 2,657.92 | 436,877.56 | Double payment | +----+------------+-------+----------+--------------+-----------+------------+----------------------------------+ | 10 | 2016-01-31 | Jan | 2,712.92 | -15.28 | 2,728.20 | 433,920.01 | | +----+------------+-------+----------+--------------+-----------+------------+----------------------------------+ | 11 | 2016-02-29 | Feb | 2,716.97 | 76.95 | 2,640.02 | 433,935.29 | | +----+------------+-------+----------+--------------+-----------+------------+----------------------------------+ | 12 | 2016-03-31 | Mar | 2,716.99 | 77.44 | 2,639.55 | 433,858.34 | | +----+------------+-------+----------+--------------+-----------+------------+----------------------------------+ | 13 | 2016-04-30 | Apr | 2,712.96 | -14.37 | 2,727.33 | 433,780.90 | | +----+------------+-------+----------+--------------+-----------+------------+----------------------------------+ | 14 | 2016-05-31 | May | 2,717.01 | 77.84 | 2,639.17 | 433,793.27 | | +----+------------+-------+----------+--------------+-----------+------------+----------------------------------+ | 15 | 2016-06-30 | Jun | 2,712.98 | -13.95 | 2,726.93 | 433,717.43 | | +----+------------+-------+----------+--------------+-----------+------------+----------------------------------+ | 16 | 2016-07-31 | Jul | 5,616.36 | 2,977.58 | 2,638.78 | 433,732.48 | Double payment | +----+------------+-------+----------+--------------+-----------+------------+----------------------------------+ | 17 | 2016-08-31 | Aug | 2,713.84 | 5.55 | 2,708.29 | 430,753.80 | | +----+------------+-------+----------+--------------+-----------+------------+----------------------------------+注意：在秘魯，他們計算包括人壽保險費在內的每月付款，為了簡化，我減去了這些概念（但結果是不固定的每月付款）。
所以，我的問題是：我需要計算一個固定的每月付款，允許每年兩次額外付款，而那些不計利息的付款。
例如，30 年、12% 年利率和 1,000 美元的貸款，使用固定攤銷抵押貸款：
P = A / ((( 1 + i ) ^ n - 1 )/( i ( 1 + i ) ^ n )) A = loan amount = 1,000 i = monthly rate = (( 1 + 12% ) ^ ( 1 / 12 ) - 1 ) = 0.00948879293 n = periods = 30 * 12 = 360 --- So, my payment (P) will be: USD 9.83 ~我正在尋找一種金融數學策略，它允許我每年兩次額外支付這 10 美元（在那幾個月支付相同的利息）。
如果我單筆付款，它就像一個魅力：
+--------------------------------------------------------------+ | Single Payment | +-----+---------+---------+----------+--------------+----------+ | # | # Month | Payment | Interest | Amortization | Balance | +-----+---------+---------+----------+--------------+----------+ | 0 | | | | | 1,000.00 | +-----+---------+---------+----------+--------------+----------+ | 1 | 1 | 9.83 | 9.50 | 0.33 | 999.67 | +-----+---------+---------+----------+--------------+----------+ | 2 | 2 | 9.83 | 9.50 | 0.33 | 999.34 | +-----+---------+---------+----------+--------------+----------+ | 3 | 3 | 9.83 | 9.49 | 0.33 | 999.01 | +-----+---------+---------+----------+--------------+----------+ | 4 | 4 | 9.83 | 9.49 | 0.34 | 998.67 | +-----+---------+---------+----------+--------------+----------+ | 5 | 5 | 9.83 | 9.49 | 0.34 | 998.34 | +-----+---------+---------+----------+--------------+----------+ | 6 | 6 | 9.83 | 9.48 | 0.34 | 997.99 | +-----+---------+---------+----------+--------------+----------+ | 7 | 7 | 9.83 | 9.48 | 0.35 | 997.65 | +-----+---------+---------+----------+--------------+----------+ | 8 | 8 | 9.83 | 9.48 | 0.35 | 997.30 | +-----+---------+---------+----------+--------------+----------+ | 9 | 9 | 9.83 | 9.47 | 0.35 | 996.95 | +-----+---------+---------+----------+--------------+----------+ | 10 | 10 | 9.83 | 9.47 | 0.36 | 996.59 | +-----+---------+---------+----------+--------------+----------+ | 11 | 11 | 9.83 | 9.47 | 0.36 | 996.23 | +-----+---------+---------+----------+--------------+----------+ | 12 | 12 | 9.83 | 9.46 | 0.36 | 995.87 | +-----+---------+---------+----------+--------------+----------+ | ... | | | | | | +-----+---------+---------+----------+--------------+----------+ | 355 | 7 | 9.83 | 0.54 | 9.29 | 47.72 | +-----+---------+---------+----------+--------------+----------+ | 356 | 8 | 9.83 | 0.45 | 9.37 | 38.35 | +-----+---------+---------+----------+--------------+----------+ | 357 | 9 | 9.83 | 0.36 | 9.46 | 28.88 | +-----+---------+---------+----------+--------------+----------+ | 358 | 10 | 9.83 | 0.27 | 9.55 | 19.33 | +-----+---------+---------+----------+--------------+----------+ | 359 | 11 | 9.83 | 0.18 | 9.64 | 9.69 | +-----+---------+---------+----------+--------------+----------+ | 360 | 12 | 9.78 | 0.09 | 9.69 | 0.00 | +-----+---------+---------+----------+--------------+----------+但是雙重支付顯然會產生負餘額……我需要它是確切的東西：在最後一個達到零。
+-----------------------------------------------------------------+ | Double paying (july and december) | +-----+---------+-----------+----------+--------------+-----------+ | # | # Month | Payment | Interest | Amortization | Balance | +-----+---------+-----------+----------+--------------+-----------+ | 0 | | | | | 1,000.00 | +-----+---------+-----------+----------+--------------+-----------+ | 1 | 1 | 9.83 | 9.50 | 0.33 | 999.67 | +-----+---------+-----------+----------+--------------+-----------+ | 2 | 2 | 9.83 | 9.50 | 0.33 | 999.34 | +-----+---------+-----------+----------+--------------+-----------+ | 3 | 3 | 9.83 | 9.49 | 0.33 | 999.01 | +-----+---------+-----------+----------+--------------+-----------+ | 4 | 4 | 9.83 | 9.49 | 0.34 | 998.67 | +-----+---------+-----------+----------+--------------+-----------+ | 5 | 5 | 9.83 | 9.49 | 0.34 | 998.34 | +-----+---------+-----------+----------+--------------+-----------+ | 6 | 6 | 9.83 | 9.48 | 0.34 | 997.99 | +-----+---------+-----------+----------+--------------+-----------+ | 7 | 7 | 19.65 | 9.48 | 10.17 | 987.82 | +-----+---------+-----------+----------+--------------+-----------+ | 8 | 8 | 9.83 | 9.38 | 0.44 | 987.38 | +-----+---------+-----------+----------+--------------+-----------+ | 9 | 9 | 9.83 | 9.38 | 0.45 | 986.93 | +-----+---------+-----------+----------+--------------+-----------+ | 10 | 10 | 9.83 | 9.38 | 0.45 | 986.48 | +-----+---------+-----------+----------+--------------+-----------+ | 11 | 11 | 9.83 | 9.37 | 0.46 | 986.03 | +-----+---------+-----------+----------+--------------+-----------+ | 12 | 12 | 19.65 | 9.37 | 10.29 | 975.74 | +-----+---------+-----------+----------+--------------+-----------+ | ... | | | | | | +-----+---------+-----------+----------+--------------+-----------+ | 355 | 7 | 19.65 | -43.01 | 62.66 | -4,589.83 | +-----+---------+-----------+----------+--------------+-----------+ | 356 | 8 | 9.83 | -43.60 | 53.43 | -4,643.26 | +-----+---------+-----------+----------+--------------+-----------+ | 357 | 9 | 9.83 | -44.11 | 53.94 | -4,697.20 | +-----+---------+-----------+----------+--------------+-----------+ | 358 | 10 | 9.83 | -44.62 | 54.45 | -4,751.65 | +-----+---------+-----------+----------+--------------+-----------+ | 359 | 11 | 9.83 | -45.14 | 54.97 | -4,806.61 | +-----+---------+-----------+----------+--------------+-----------+ | 360 | 12 | -4,852.27 | -45.66 | -4,806.61 | 0.00 | +-----+---------+-----------+----------+--------------+-----------+在Google表中，here。
有什麼想法嗎？我將非常感謝您的幫助！
提前致謝
我設法計算出讓余額為零的單筆付款:)
訣竅是：計算每月比率，在每個雙倍支付月份將該比率翻倍，將所有比率相加，然後將本金除以該比率。
它需要兩次迭代，但工作得很好！仍在尋找一種非迭代的方式……
每月比率：
1/(1+monthly rate)^period，例如。最後一個應該是1/(1+0.00948879293)^360= 0.03337792393。鑑於最後一個是雙重支付的：0.03337792393*2= 0.06675584786.我已經更新了同一個Google表（這裡）中的公式，並將線上性解決方案中工作。
像這樣：
+──────+──────────+──────────────+────────+──────────+───────────+───────────────+──────────+ | # | # Month | Double pay? | Ratio | Payment | Interest | Amortization | Balance | +──────+──────────+──────────────+────────+──────────+───────────+───────────────+──────────+ | 0 | | | | | | | 1,000.00 | | 1 | 1 | FALSE | 0.99 | 8.46 | 9.50 | -1.04 | 1,001.04 | | 2 | 2 | FALSE | 0.98 | 8.46 | 9.51 | -1.05 | 1,002.10 | | 3 | 3 | FALSE | 0.97 | 8.46 | 9.52 | -1.06 | 1,003.16 | | 4 | 4 | FALSE | 0.96 | 8.46 | 9.53 | -1.07 | 1,004.23 | | 5 | 5 | FALSE | 0.95 | 8.46 | 9.54 | -1.08 | 1,005.32 | | 6 | 6 | FALSE | 0.94 | 8.46 | 9.55 | -1.09 | 1,006.41 | | 7 | 7 | TRUE | 1.87 | 16.91 | 9.56 | 7.35 | 999.06 | | 8 | 8 | FALSE | 0.93 | 8.46 | 9.49 | -1.03 | 1,000.09 | | 9 | 9 | FALSE | 0.92 | 8.46 | 9.50 | -1.04 | 1,001.13 | | 10 | 10 | FALSE | 0.91 | 8.46 | 9.51 | -1.05 | 1,002.19 | | 11 | 11 | FALSE | 0.90 | 8.46 | 9.52 | -1.06 | 1,003.25 | | 12 | 12 | TRUE | 1.79 | 16.91 | 9.53 | 7.38 | 995.87 | | ... | | | | | | | | | 355 | 7 | TRUE | 0.07 | 16.91 | 0.62 | 16.29 | 49.28 | | 356 | 8 | FALSE | 0.03 | 8.46 | 0.47 | 7.99 | 41.29 | | 357 | 9 | FALSE | 0.03 | 8.46 | 0.39 | 8.06 | 33.23 | | 358 | 10 | FALSE | 0.03 | 8.46 | 0.32 | 8.14 | 25.09 | | 359 | 11 | FALSE | 0.03 | 8.46 | 0.24 | 8.22 | 16.87 | | 360 | 12 | TRUE | 0.07 | 17.03 | 0.16 | 16.87 | 0.00 | +──────+──────────+──────────────+────────+──────────+───────────+───────────────+──────────+