Emulating the Math of Spreadsheet Financial Formulas and Functions in Python
About This Article
This is a potentially extensible project. It started because, to the author, it appears Python is lacking in even the most basic functions of financial calculation, as well as in areas which the author may not be knowledgeably prepared to address. It may (or may not) expand in multiple directions. The original idea was to create a module to hold certain modestly complex formulas, but that is – for now – too ambitious so we will settle for vetted code examples checked against Google Sheets and Excel for accuracy.
This is not a tutorial on the math of money and time, though it cannot help but have some elements of such a tutorial. There are lots of very good resouces on the web concerning the time value of money. There are also some really pathetic ones obviously written by folks whose backgrounds did NOT prepare them to write about the subject. You can easily do further research on your own. The author of this article has an MBA in finance, for what that is worth.
Part of the reason that a module may be too ambitious can be seen in the excellent coaching facilities of the big spreadsheets that help a user understand and apply certain formulas and functions on the fly, while maintaining the option of “…for more help” that can go into pages of explaination and examples. That is not always structurally adaptable to a Python module. It’s easy enough for most folk to understand a formula like:
but some programmers may be a bit more challenged by (and this is really easy compared to some stuff):
If you want to use the formulas and code discussed here you should take seriously that, in this area, ignorance can be very expensive. This documentation may help you decide where to continue your research.
The Right Formula for Success
Microsoft can’t keep straight what they want to call them; are they Formulas or are they Functions? The most recent Excel tab heading is “Formulas” but the command group is “Function Library”. Google Sheets is consistent in calling them functions, but does not give the user a clue as to the actual math. Excel does so, but sparingly.
In any case, whether its in VisiCalc, Lotus 1-2-3, Excel or Google Sheets, there is nothing new about the math – the math has been around since Adam was a small boy. Much of what the spreads have to offer is already available in Python. Some is built-in. See The Basic Five Formulas below. A lot is available in the standard-always-available modules listed in the Global Module Index, for example: math, cmath, calendar, datetime, decimal, fractions, random, and statistics. A vast amount (over a million files) is available for your perusal in “The Cheese Shop” – Python packages stored and indexed by the Python Software Foundation at https://pypi.org/.
It came as a surprise that there seems to be a bit of a “hole” in python in financial functions. So that is the primary focus of this article. The first question in creating this article and its related functions was: Who is the audience? One audience is folks to whom the future value formula above is a perfectly clear explanation. Secondarily are those folks who are both smart enough and interested enough to spend considerable time learning about the formulas and the math in order to understand and utilize the concepts correctly. This article is intended to give that group a fighting chance.
There is a LARGE third group – folks who will either need basic, effortless functions, or those who will decide the math required is not their cup-of-tea, so we won’t worry about that group.
We are hoping to create functions that can roughly parallel the financial formulas (to start) in Excel and Google Sheets. Function/formula names will mimic those that Microsoft and Google use despite the fact that these may violate the sacred Python tradition (laid out in PEP 8 by Hizoner Himself) of starting functions with a small letter. Doing this makes it vastly easier for the programmer to have a huge backup reference readily available in the spreadsheets and easily comparable for testing.
There are a few more issues to discuss in preparation for diving into financial formulas, but before we get to those we should take a look at the Basic 5 functions that both Excel and Google Sheets address under the heading ∑ – how odd that they both pick this symbol and this exact group. This is the place that both Excel and Google Sheets get started. It gives us a quick taste of what we are trying to accomplish and of different paths that lead to the same result.
THE Basic Five FORMULAS
Just to get our feet wet: in BOTH Excel and Google Sheets there are five identical basic or featured formulas – and there is no need to create a new function for them in Python because all we need is built in to the core language. ( ** We assume than a spreadsheet “range” of cells is equivalent to a Python “list” of numbers. In our example: consecutive cells B3:F3 containing: 50, 21.5, 67, 10, 3 would be like a list called nums where nums = [50, 21.5, 67, 10, 3])
Function/Formula Spreadsheet Result Python – using built-in functions
Sum =SUM(B3:F3) 151.5 = sum(nums)
Average =AVERAGE(B3:F3) 30.3 = sum(nums)/len(nums)
Count of numbers =COUNT(B3:F3) 5 = len(nums)
Max =MAX(B3:F3) 67 = max(nums)
Min =MIN(B3:F3) 3 = min(nums)
Five Issues with Time, Money and Computers
There are five bothersome issues, when dealing with financial functions in computers, of which the user and programmer must be aware.
First, there is an annoying lack of consistency in the terminology used in “the field” for various components of formulas in finance. For example, “Present Value”, “Pv”, “P”, “pv”, “Principal”, “PVAdue”, “c1”, and “cf1” can all mean and stand for the same thing and various sources will use different combinations of the multi-letter abbreviations in presenting formulas. This is true of a number of the essential components in financial functions – “t”, “n”, and “nper” perhaps being the next most confusing. Sometimes this stems from the plausible but inaccurate assumption that “annual” is some kind on natural or mathematically inherent period. With a little communication and clarification we can overcome those matters.
Which leads us to the second issue: “the math” does not know anything about time and does not care whether a “period” is 1 year or 1 nanosecond. It is just a process. It is up to the user or programmer to adjust numbers for the formulas to be accurate in the context of the time periods desired and to understand what assumptions are being made by the formulas being employed. In “the field”, sometimes, but not always, “interest” mean simple annual interest though just about as often it means effective interest after compounding. Sometimes, but not always, the formula bears the responsibility of converting an annualized interest into the amount to be compounded at each period. About half the time the formula expects the user to do that. Sometimes – particularly with hand held calculators – a investment or a payment must be input as a negative number and occasionally functions are written assuming percentages are entered as integers.
For example, assume we want “9% interest” on $100 compounded monthly. Either the formula assumes an annual scenario and accepts 9, divides 9 by 100 to get .09 – a percent – then divides .09 by 12 to get .0075 – the interest used per compound period – OR the user may have to input the interest as .0075 to start with. To make this more interesting, very frequently formulas require that “annual interest” be input as a decimal fraction (.09), then the formula takes it from there.
In this article and in our module functions:
- All inputs are positive numbers
- Formulas reflect pure math – user must calculate nper and rper
- Percentages are entered as decimals, i.e., 7 3/4% is entered as .0775
$ pv – the apparent or calculated value now of an asset, including money-in-hand (aka Principal) and one or more sums to be received in the future or a series of cash flows considered with respect to a specific rate of return or discount rate
$ fv – the value at some given future moment of a current asset with a know present value or series of cash flows of know amount(s) at know intervals, with respect to a specific interest or discount rate
$ pmt – a payment, an amount of money given or received
% rper – the interest rate or portion of R applied to a compounding period – usually a decimal fraction of a percentage (used instead of the frequently used “r” for clarity and consistency with nper)
$ intamt – the amount of money earned as interest per period; “int” instead of “i” for clarity
# nper – the number of compounding periods of an investment – usually an integer but it can include a decimal component
? period – a term expressing and defining a commonly accepted but not necessarily precise time frame including: “hourly”, “continuous”, “daily”, “weekly”, “monthly”, “quarterly”, “semiannually”, and “annually”
$ I – the amount of money earned or to be earned as simple interest
$ P – Principal, cash flow 1, cash invested,
% R – the simple interest rate expressed as a traditional annual percentage (i.e, 8% per year)
# T – the length of a financial event measured in years
# type – a designation of whether a payment is received at the start or end of a period: 0 for start (default) and 1 for the end of the period; historically 0 is called “in advance” and 1 is referred to as “in arrears”
Third and more difficult is the issue of inconsistency’s in the treatment of time. These have been virtually ignored, literally for centuries, because until very recently we did not have the tools or technology to effectively deal with them. They are still generally ignored because their financial relevance is frequently either too difficult, too cumbersome, or too small to warrant their mathematical inclusion and/or explanation. These time issues will require user or programmer decisions, or at least acquiescence to defaults. Many of these issues show up in the real world primarily in short term or very large transactions. A few examples:
How long is a year? Historically, major institutions have used 360, 364, 365 and even (rarely) 366 days. How about a month?; calendar count, 30, or 31? And a quarter – you would think 25% of a year, but commonly in short term loans it depends on the quarter and the days in it, translated into a four or five decimal place fraction. The second quarter, April to June, for example has (30+30+31=) 91 days which is (91/365=) 24.9315% of the year. Should an allowance for leap day be included? You get the gist.
Fourth, is the issue of continuous compounding which mathematically can be solved with integral calculus but, since the difference between hourly and continuous compounding is way down the decimal chain of the final penny, common sense usually makes the extra overhead and complexity not worth anybody’s effort. Common sense may occasionally raise its ugly head in a few other contexts, for example, for “type 1” payments (in arrears), formulas commonly move the payment to the first day of the next period instead of an impossibly more complex calculation using the actual last day of the period. We agree with and adopt this expedient.
So when, and if, we address formulas with commonly accepted time periods we will assume the following. (This will be any case where we are not using actual starting and ending date calculations which are made possible by some really sophisticated software such as Python’s datetime module.)
annual -> 365 days
semiannual -> 50% of annual
quarterly -> 25% of annual
monthly -> annual/12
weekly -> annual/7 (= 52.143)
daily -> 1, i.e., 365/365
hourly -> annual * 24 (= 8,760)
continuous -> we substitute hourly
Lastly, come to grips with the fact that even today there are tiny rounding “errors” or differences between various calculators, computers and software. This is unavoidable since computers track numbers in base 2 and have to convert them to base 10 for most humans to understand. These differences are usually small enough to be virtually irrelevant but they do exist all the time. As an example, we are aware of a fund at Fidelity which regularly reports a 1 cent difference from the way Quicken displays it. Any way you look at it, current computers and software are vastly more accurate than the tables used by mathematicians for the last 400 years. For a good short summary see “14. Floating Point Arithmetic: Issues and Limitations” at https://docs.python.org/2/tutorial/floatingpoint.html
The first day of Finance 101
On day 1 students are usually introduced to the granddaddy of all financial formulas: intamt = pv * rper * nper. It is ALWAYS represented (or misrepresented?) as I=P*R*T. (This, I am convinced, is a concerted effort to confuse and weed out those who should not be trying to major in Finance anyway.) Verbally this quick and glibly spoken “I is equal to PRT” *1 is explained as “Interest is equal to the Principal times the rate times the time.” This is spoken in a hushed and reverent impartation of wisdom after which students are expected to swear “Dr. Jones said it, I believe it, and that settles it.” This is the simplest of simple interest – no compounding, time is in years or part of years. (*1 There is an old joke among finance majors that ends with the punch line “Oh yea, well I is better than PRT.” – its very similar to the one stating “you dufus, everybody know cake are square and pie are round.”)
In truth, this formula really is an important cornerstone introduction. In the simplest possible terms and glossing over the options, it reveals that money has value over time. A value different from what you perceive as it sits in your hand, or is promised to you by granddaddy’s trust on your 21st birthday. The truth of interest is that if you have $1000 in your hand (your pv – or principal) and you can let someone else use it for 1 year (nper=1, time, T) after which you will get back your principal plus an additional 5%, your money will have “earned” an additional (5%x1000=) $50.00 which we shall call “interest” (I or intamt if nper=1 and period is assumed to be “annual”) . Never mind that if you do this you may well be thrown out of the temple, the important thing is you are learning to do the math! On with the show.
Simple Interest to be Earned
Formula: I = P*R*T
Spreadsheet name: IPMT
Variables used: P – Principal (cash out), R – annual interest rate, T – number of years
Python function:
def IPMT(P,R,T):
# cash out, annual rate, number of years - no compounding
return(P*R*T) # the simple interest earned
Lets get this hoary old-timer one out of the way. It is here only to honor tradition – kind of like “Hello World!” in teaching any computer language ever. Nobody will ever use it practically as a function but it might be helpful in introducing the template.
One important note, the formula I = P*R*T implies three other formulas:
P = I/(R*T), T = I/(P * R), and R = I/(P * T)
…and this is generally true of, if not pointed out in, all the functions that follow.
Present Value
Formula(s):
OR
Variables used: fv, rper, nper
Python function(S):
def PV(fv, rper, nper):
return(fv*((1+rper)**(-1*nper)))
def PV2(fv, rper, nper):
return(fv/(1+rper)**nper)
The programmer has the responsibility of deciding how the user inputs the number of intervals (in years, months, hours, etc.) and how that input is converted into the required number of periods – “nper”. This is also true for “rper”. If the programmer’s client wants to use a 360 day year compounded hourly at 10% annual interest, it is the programmers responsibility to calculate (360*24=) 8,640 instead of the 8,760 this author would assume, then use that to derive (.10/8640=) .000011574 to use as “rper”. The two formulas above are mathematically identical and of course yield the same answer – both are found commonly.
Annuities
A series of equal payments made at equal intervals constitutue an “annuity”. Annuities could be god-awful long formulas which, due to being “geometric progressions”, can be massively reduced to fairly simple formulas. We can and frequently need to calculate both the present and future values of annuities for a million different every day purposes. A small complication – whether a payment is made or collected at the first of a period or at the end of that period means there are two distinct formulas for each situation. Note that “period” and “interval” mean the same thing and hopefully this time line explains the start and end issue.
:start of 1st period (1) :start of 3rd period (1)
|_______________1_______________2_______________3_______________4_______________5
……………………..:end of 1st period (0) :end of 3rd period (0)
Present Value of a Series of Equal Payments
Formula: Type 0 – payment at end of the interval
Formula: Type 1 – payment at the start of the interval
Spreadsheet name: PV
Variables used: pmt, rper, nper, type
Start and end calculations require two different formulas; in order to avoid user confusion they will be handled seamlessly in one function differentiated by the user’s input of “type” as 0 (payment at end of interval) or 1 (payment at start of interval). If type is null, end of period is assumed – otherwise there is an error.
Python function:
def PV(pmt, nper, rper, type):
if type == "":
type = 0
while type is 0:
return pmt*((1-(1+rper)**(-1*nper))/rper)
while type is 1:
return(pmt*(1+((1-(1+rper)**(-1*(nper-1)))/rper)))
print("type error")
sys.exit()
Future Value of a Series of Equal Payments
Formula: Type 0 – payment at end of the interval – an “ordinary annuity”
Formula: Type 1 – payment at the beginning of a period – an “annuity due”
Spreadsheet name: FV
Variables used: pmt, rper, nper , type
Note that if we already have a value for an ordinary annuity we can calculate the annuity due easily with:
fv = ordinary annuity value * (1 + rper)
Demo Python Function:
Hey, what’s with the use of the word ‘Demo’ you ask. The original idea was to return a rounded float, but during testing we discovered that ’round’ does not always behave the way you expect. In one of our test examples the round function refused to deliver more than one (1) decimal place no matter how it was constructed. So the following function returns BOTH a properly formatted string and a pure float. It’s programmer’s choice. Probably the best long term solution is to just generate the pure float and use the new f-string format to assure perfect output – which is critical in financial documents. (There is a wikipython toolbox, TB3, that is 2 dense pages just on formatting.)
That said, here is the demo future value function handling type 0 and type 1:
def FV(pay, nper, rper, ftype):
if ftype == "":
ftype = 0
if ftype == 0:
rtn = pay*((((1+rper)**nper)-1)/(rper))
elif ftype == 1:
rtn = (pay*(1+rper)) * ( (((1+rper)**nper)-1)/rper)
else:
rtn = 0
print ("FV input of ftype error")
return "$ {:<,.2f}".format(rtn), rtn
A Related Diversion
A kind of a companion piece has been posted with a Python function that produces an amortization table with all the most common defaults and assumptions. It is called “An Easy Amortization Schedule Function” and is the article after this one. Check it out. I compared it’s results to one sent me by one of America’s largest mortgage companies and the bottom line was identical to the penny, though occasionally a month went plus or minus one cent.
OK, that’s a start, I am going to leave it to you, the user of www.wikipython.com, as to whether or not this line is worth pursuing. As always, comments are deeply appreciated.
John
