This is my first post in this community, already tried to find my answer, and didn't succeed :(.
I'm working a way to understand the formula behind a loan that allows the client to extra pay the same monthly amount twice a year without increasing the interest, an option that works with flexible paying in some banks and common in places like Perú.
An example (taken from a real amortization schedule):
+----+------------+-------+----------+--------------+-----------+------------+----------------------------------+
| # | 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 | |
+----+------------+-------+----------+--------------+-----------+------------+----------------------------------+
Note: in Perú they calculate the monthly payment including life insurance charge, I subtracted the concepts in order to simplify (but the result is a non-fixed monthly payment).
So, my problem is: I need to calculate a fixed monthly payment allowing two extra payments a year and those ones without applying interests.
For example, a 30 year, 12% year rate, and USD 1,000 loan, using constant-amortization mortgage:
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 ~
I'm looking for a financial math strategy that allows me to double pay that 10 bucks two extra times a year (paying the same interest in that months).
If I single pay, it works like a charm:
+--------------------------------------------------------------+
| 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 |
+-----+---------+---------+----------+--------------+----------+
But double paying obviously produces a negative balance... I need it to be the exact thing: reach zero in the last one.
+-----------------------------------------------------------------+
| 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 |
+-----+---------+-----------+----------+--------------+-----------+
That in google sheets, here.
Any thoughts? I will really appreciate the help!
Thanks in advance