Calculations with Python Code or Excel Spreadsheets

neal

To code or not to code¶

When faced with a technical challenge or problem solving, conceptual understanding is an important first step, and I almost always start with hand calculations. Once I feel that I have a grasp of the concept, then I turn to a digital approach which often involves using excel or python and running some numbers, and re-running them, and fiddling until I really understand. I always feel like, especially in engineering, that using the right tool for the right job can make a big difference in the results. Microsoft Excel or Libre Calc spreadsheet programs are a great way to plug some numbers in and perform simple calculations with nearly no learning curve to get an answer. But as the complexity and quantity of the data and compuation increases, spreadsheets start to not make sense. That's where I feel a code based calculation method is important tool in everyones analysis toolbox. Classicaly, good old pencil and paper was the only way to model and work out problems. But, with the availability of the personal computer, software has allowed us to do more work with less effort. I find it very fascinating to see how people solve problems. This post will explore that idea and hopefully give you an idea to go beyond excel the next time you need to run some numbers.

There are many ways to solve engineering problems. The main tools that I have found critical are:

hand calculations
- no learning curve
- low cost
- slow, tedious and can be error prone (me and algebra :( )
- forces you to understand
spreadsheets
- medium learning curve
- great at simple data storage
- great for simple calcualtions
- non-linear / confusing thought process
- not great at large scale date storage
- not repeatable
code
- linear thought process (usually)
- most powerful for calculations
- not great at simple data storage
- great for large scale data storage and processing
- powerful version control (git)
- very repeatable

These are a few methods for solving computational problems. Each one has a time and place, but recognizing when you are trying to put a square peg in a round hole can help keep you sane.

An Example - python and Excel to understand loans¶

OK, so we discussed some different approaches to problem solving. Lets look at an example of using an spreadsheet and python code to understand and determine the cost of an loan. Some terms are listed below if you aren't familiar with a compound interest loan.

apr - annual percentage rate
r - monthly percentge rate, apr/12
per - the peroid for which you want to find an interest
nper - number of payment
pv - present value, the loan amount pmt - payment for a loan
payments due at the end of the period

Let's throw some numbers at this. Here I will define some variables that define our loan parameters

In [1]:

############# define input variables #############
n = 24   # term in months
apr = 0.0250   # annual percentage rate
r = apr/12     # monthly percentage rate
baseloan = 30000   # principal amount of loan
downpayment = 5000 
PV = baseloan - downpayment  # present value of the loan

Hand Calculation¶

If we are to hand calculate our loan payments, we can use the following equation (found in Engineering Economy, Blank et al, pg 58)

$$ payment = PV \bigg[ \frac{r(1+r)^n}{(1+r)^n-1} \bigg] $$

In [2]:

# monthly payments assuming compounding interest
payment = -PV*((r*(1+r)**n)/((1+r)**n-1))
payment

Out[2]:

-1069.0098055421547

Easy enough, but what if you want to make a payment table, it would be very tedious by hand. Fortuantley, we have a computer. Since I understand the equation being used, I have no issues with automating this a little more. We can make an spreadsheet table. which is nice visually, didn't take very much time, and allows me to change some parameters.

Update - I found out that Excel has a great Loan Amortization spreadsheet built in, and it even includes early payoff, which is not that easy to calculate. I have included it in the spreadsheet below or you can make a new one by doing the following.

right-click on a new sheet -> Insert -> Spreadsheet Solutions -> Loan Amortization

Spreadsheet¶

But, if we want to change, say the term length, well then we have to change the layout of the table and mess with it, so the spreadsheet isn't as robust as we had hoped. How about a code solution? Here are the steps to get some python code going to do the same exact calcuation, and being very robust, and can be used for many different finanical applications.

Code¶

In [3]:

############# import python libraries #############
from numpy import *
from pandas import DataFrame, date_range, set_option, options
options.display.float_format = '${:10,.2f}'.format

In [4]:

############# calculate output #############
per =              arange(1,n+1)  # months
payment =          pmt(r, n, PV)  # monthly payment
interestpayment =  ipmt(r, per, n, PV)  # monthly interest
principalpayment = ppmt(r, per, n, PV)  # monthly principal
balance =          PV + per*payment - sum(interestpayment) # monthly balance
dates =            date_range('20160101', periods=n, freq='M')

############# create a table with the output #############
# create a dataframe for simple data management and output
df = DataFrame({'date':dates,
                'month':per,
                'balance':balance,
                'payment':ones(len(per))*payment,
                'interest':interestpayment,
                'principal':principalpayment})
# reorder the columns to match the spreadsheet
df = df[['date','month','balance','payment','interest','principal']]

############# print output #############
print(' monthly payment     = %f' % payment)
print(' total interest cost = %f\n' % sum(interestpayment))   
print(df)

 monthly payment     = -1069.009806
 total interest cost = -656.235333

         date  month     balance     payment    interest   principal
0  2016-01-31      1 $ 24,587.23 $ -1,069.01 $    -52.08 $ -1,016.93
1  2016-02-29      2 $ 23,518.22 $ -1,069.01 $    -49.96 $ -1,019.05
2  2016-03-31      3 $ 22,449.21 $ -1,069.01 $    -47.84 $ -1,021.17
3  2016-04-30      4 $ 21,380.20 $ -1,069.01 $    -45.71 $ -1,023.30
4  2016-05-31      5 $ 20,311.19 $ -1,069.01 $    -43.58 $ -1,025.43
5  2016-06-30      6 $ 19,242.18 $ -1,069.01 $    -41.45 $ -1,027.56
6  2016-07-31      7 $ 18,173.17 $ -1,069.01 $    -39.31 $ -1,029.70
7  2016-08-31      8 $ 17,104.16 $ -1,069.01 $    -37.16 $ -1,031.85
8  2016-09-30      9 $ 16,035.15 $ -1,069.01 $    -35.01 $ -1,034.00
9  2016-10-31     10 $ 14,966.14 $ -1,069.01 $    -32.86 $ -1,036.15
10 2016-11-30     11 $ 13,897.13 $ -1,069.01 $    -30.70 $ -1,038.31
11 2016-12-31     12 $ 12,828.12 $ -1,069.01 $    -28.53 $ -1,040.48
12 2017-01-31     13 $ 11,759.11 $ -1,069.01 $    -26.37 $ -1,042.64
13 2017-02-28     14 $ 10,690.10 $ -1,069.01 $    -24.19 $ -1,044.82
14 2017-03-31     15 $  9,621.09 $ -1,069.01 $    -22.02 $ -1,046.99
15 2017-04-30     16 $  8,552.08 $ -1,069.01 $    -19.84 $ -1,049.17
16 2017-05-31     17 $  7,483.07 $ -1,069.01 $    -17.65 $ -1,051.36
17 2017-06-30     18 $  6,414.06 $ -1,069.01 $    -15.46 $ -1,053.55
18 2017-07-31     19 $  5,345.05 $ -1,069.01 $    -13.27 $ -1,055.74
19 2017-08-31     20 $  4,276.04 $ -1,069.01 $    -11.07 $ -1,057.94
20 2017-09-30     21 $  3,207.03 $ -1,069.01 $     -8.86 $ -1,060.15
21 2017-10-31     22 $  2,138.02 $ -1,069.01 $     -6.65 $ -1,062.36
22 2017-11-30     23 $  1,069.01 $ -1,069.01 $     -4.44 $ -1,064.57
23 2017-12-31     24 $      0.00 $ -1,069.01 $     -2.22 $ -1,066.79

Assuming the user has a basic understanding of how to write a computer program, code is(should be) very straightforward to read. Spreadsheets, like the one shown above can be nebulus and requires a fair bit of digging to see what calcualtions are being made. If you need any help with the functions, use the help function to show the inputs

help(pmt)
help(ipmt)
help(ppmt)

Try playing around with the code and see what happens when the terms of the loan are changed. The code is more robust to changes, where the spreadsheet has to be reworked

Summary¶

A simple demo compared spreadsheets versus code with an example of a common calcualtion problem, loans! When pursuing future projects, think about what tools would be the best for the given problem, as well as you, and possibly your team.

Stay curious!