0

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

Improve this question
15
  • The easiest way to figure this out is with a computer program. Amortization formulas are designed to accept the same payment every period... They don't really work with irregular payments because you have to recalculate the remaining amortization in the month that the irregular payment occurs.
    – Robert Harvey
    Apr 30, 2020 at 20:16
  • For your spreadsheet, all you should have to do is change the payment in the appropriate cells (as you are doing now), and then put a special formula in the last cell that computes the close-out payment.
    – Robert Harvey
    Apr 30, 2020 at 20:18
  • @RobertHarvey, thanks a lot, but I'm precisely trying to make my own code to figure this out... so the first suggestion is out of table for me. About the second one, my sheet is already doing that: the last payment assumes the role to fix any round() lack of accuracy.
    – benjaroa
    Apr 30, 2020 at 20:48
  • Well, I don't quite understand then. The last payment should be engineered so that it always results in a balance of zero.
    – Robert Harvey
    Apr 30, 2020 at 20:57
  • And it is: docs.google.com/spreadsheets/d/…. The final balance (payment #360) is zero in both tables. The one with equal-payments work fine, but the other one end paying a lot more for the loan (that's what the also payment is a negative number)
    – benjaroa
    Apr 30, 2020 at 21:01

1 Answer 1

Reset to default
1

I managed to calculate the single payment that let to zero balance :)

The trick was: calculate a monthly ratio, double that ratio in each double-paying months, sum all that ratios and divide the principal amount into that.

It takes two iterations, but work just fine! Still looking for a non-iterative way...

The monthly ratio: 1/(1+monthly rate)^period, eg. the last one should be 1/(1+0.00948879293)^360= 0.03337792393. Given the last one is a double-paying one: 0.03337792393*2= 0.06675584786.

I already updated the formulas in the same google sheet (here) and will be working in a linear solution.

Like this:

+──────+──────────+──────────────+────────+──────────+───────────+───────────────+──────────+
| #    | # 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     |
+──────+──────────+──────────────+────────+──────────+───────────+───────────────+──────────+
Improve this answer
2
  • Can't get much better than that.
    – Robert Harvey
    May 1, 2020 at 1:58
  • Now I'm digging in a way to calculate a present value factor for uniform and non-uniform series... who knows, it may work :D
    – benjaroa
    May 1, 2020 at 3:02

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .