Universal debt schedule calculator

This notebook produces bond or loan payment and amortization schedules for absolutely any payment shape imaginable. The only restriction is that interest rates have to be fixed.

In [1]:
import numpy as np

# collect inputs

bnow = float(input("Enter the initial size of the loan: "))
balloon= float(input("Enter the size of the final balloon payment (0 if none): "))
periods = int(input("Enter the term (=number of years, say): "))
nper= int(input("Enter the number of payments per period: "))
rper = float(input("Enter the interest rate per period, in percent: "))
nregims = int(input("Enter the number of distinct growth regimes: "))

rnow=rper/nper # interest rate per payment period
term=periods*nper # total number of payments

checksum=False
while not checksum: 
    shape = np.array([input("Enter the growth rate in percent and length in number of payments (5 10, e.g.) for regime  " + str(i+1) + ": ").split()
                           for i in range(nregims)]).astype(np.float)
    
    checksum= (np.sum(shape,axis=0)[1]==term) # this makes sure that the regimes add up in length to the term
    if not checksum:
        print(" ")
        print("Error: inconsistent inputs")
        print("The length of your growth regimes does not add up to the number of payments.")
        print("Let's try this again.")
        print(" ")
Enter the initial size of the loan: 100000000
Enter the size of the final balloon payment (0 if none): 20000000
Enter the term (=number of years, say): 5
Enter the number of payments per period: 12
Enter the interest rate per period, in percent: 10
Enter the number of distinct growth regimes: 3
Enter the growth rate in percent and length in number of payments (5 10, e.g.) for regime  1: 5 20
Enter the growth rate in percent and length in number of payments (5 10, e.g.) for regime  2: 0 20
Enter the growth rate in percent and length in number of payments (5 10, e.g.) for regime  3: -5 20

Now we show the generic shape of payment to make sure the input was properly understood.

In [2]:
# now we display the shape of payments to make sure this matches what the user had in mind

paymentshape=np.ones(term)
regime=0
switch=shape[0][1]

for i in range(1,term):
    paymentshape[i]=paymentshape[i-1]*(1+shape[regime][0]/100)
    if (i==switch-1 and regime<nregims-1):
        regime=regime+1
        switch=switch+shape[regime][1]
    
import matplotlib.pyplot as plt #graphing module with matlab-like properties
%matplotlib inline 

plt.plot(range(1,term+1),paymentshape)

plt.title("Shape of payments")
Out[2]:
Text(0.5, 1.0, 'Shape of payments')

Now let's compute payments.

In [3]:
import numpy_financial as npf
import pandas as pd

F=npf.npv(rnow/100,np.concatenate(([0],paymentshape),axis=None)) # this the bond factor
# warning: npv does not discount first payment in numpy-financial unlike excel
firstpayment=(bnow-balloon/(1+rnow/100)**term)/F # this uses the "fundamental equation of bond design"
actualpayments=firstpayment*paymentshape

ib=np.zeros(term) # start of period balance
fb=np.zeros(term) # end of period balance
intpayment=np.zeros(term) # interest path

for t in range(term):
    if t==0:
        ib[t]=bnow
    else:
        ib[t]=fb[t-1]
    
    intpayment[t]=ib[t]*rnow/100
    fb[t]=ib[t]+intpayment[t]-actualpayments[t]
    
pd.options.display.float_format = '{:,.2f}'.format # this is the financial format we like

bondtable=pd.DataFrame({'Month':range(1,term+1),'Initial Balance': ib,'Payment':actualpayments,
                'Interest':intpayment,'Final Balance':fb}) # bond payment schedule

bondtable=bondtable.set_index('Month') 


# let's show the first and last few lines of the bond table

sliced_bond = bondtable.head(20)
sliced_bond = sliced_bond.append(bondtable.tail(20)) 



print("Debt payment table:")
print(sliced_bond)
Debt payment table:
       Initial Balance      Payment   Interest  Final Balance
Month                                                        
1       100,000,000.00   978,388.91 833,333.33  99,854,944.42
2        99,854,944.42 1,027,308.35 832,124.54  99,659,760.61
3        99,659,760.61 1,078,673.77 830,498.01  99,411,584.84
4        99,411,584.84 1,132,607.46 828,429.87  99,107,407.25
5        99,107,407.25 1,189,237.83 825,895.06  98,744,064.48
6        98,744,064.48 1,248,699.73 822,867.20  98,318,231.96
7        98,318,231.96 1,311,134.71 819,318.60  97,826,415.84
8        97,826,415.84 1,376,691.45 815,220.13  97,264,944.53
9        97,264,944.53 1,445,526.02 810,541.20  96,629,959.71
10       96,629,959.71 1,517,802.32 805,249.66  95,917,407.06
11       95,917,407.06 1,593,692.44 799,311.73  95,123,026.35
12       95,123,026.35 1,673,377.06 792,691.89  94,242,341.17
13       94,242,341.17 1,757,045.91 785,352.84  93,270,648.10
14       93,270,648.10 1,844,898.21 777,255.40  92,203,005.30
15       92,203,005.30 1,937,143.12 768,358.38  91,034,220.56
16       91,034,220.56 2,034,000.27 758,618.50  89,758,838.79
17       89,758,838.79 2,135,700.29 747,990.32  88,371,128.82
18       88,371,128.82 2,242,485.30 736,426.07  86,865,069.60
19       86,865,069.60 2,354,609.57 723,875.58  85,234,335.61
20       85,234,335.61 2,472,340.05 710,286.13  83,472,281.69
41       44,978,584.01 2,348,723.04 374,821.53  43,004,682.50
42       43,004,682.50 2,231,286.89 358,372.35  41,131,767.96
43       41,131,767.96 2,119,722.55 342,764.73  39,354,810.15
44       39,354,810.15 2,013,736.42 327,956.75  37,669,030.48
45       37,669,030.48 1,913,049.60 313,908.59  36,069,889.47
46       36,069,889.47 1,817,397.12 300,582.41  34,553,074.77
47       34,553,074.77 1,726,527.26 287,942.29  33,114,489.80
48       33,114,489.80 1,640,200.90 275,954.08  31,750,242.98
49       31,750,242.98 1,558,190.85 264,585.36  30,456,637.48
50       30,456,637.48 1,480,281.31 253,805.31  29,230,161.48
51       29,230,161.48 1,406,267.25 243,584.68  28,067,478.92
52       28,067,478.92 1,335,953.88 233,895.66  26,965,420.69
53       26,965,420.69 1,269,156.19 224,711.84  25,920,976.34
54       25,920,976.34 1,205,698.38 216,008.14  24,931,286.10
55       24,931,286.10 1,145,413.46 207,760.72  23,993,633.35
56       23,993,633.35 1,088,142.79 199,946.94  23,105,437.51
57       23,105,437.51 1,033,735.65 192,545.31  22,264,247.17
58       22,264,247.17   982,048.87 185,535.39  21,467,733.70
59       21,467,733.70   932,946.42 178,897.78  20,713,685.06
60       20,713,685.06   886,299.10 172,614.04  20,000,000.00

We probably did this right because the final balance equals the balloon, as it should.