Most financial professionals are familiar with the concept of the time value of money and the respective formulas to derive the future value, present value, rate, etc. While Excel, Google Sheets, or financial calculators offer convenient functions, not every one of us knows what equation is being solved and how it is solved.
I read the source code of numpy_financial and discovered that the equation being solved is
where is payment, is the number of compounding periods, is the future value, is the present value, is the rate of interest, and equals 0 when payments are made at the end of the time periods, and 1 when payments are made at the beginning. The sign of is the opposite of and .
Let’s move to the right, and note that the sum of the two terms on the left is the future value. The first term is the future value of the initial investment. The second term is the annuity formula. An annuity is a series of equal payments made at regular intervals. Therefore, the future value is equal to the initial investment compounded at the rate plus the future value of a series of regular equal payments.
Since the payment is fixed, let’s assume it is 1. Then the annuity formula is derived as followings:
If the payments are made at the beginning of each period, each annuity payment can compound for one extra period. Thus
When the equation is undefined. If we go back to the first step in the derivation above, if . Then the formula becomes .
Now, we have derived and understood the formula. We are ready to talk about how to solve the equations. Since there are 5 variables in the equation, it is expected to have 5 corresponding functions that solve one variable given the other four.
fv, pmt, pv are easy. We need to distinguish the case when the rate is 0 and solve the equation respectively.
nper is slightly more complicated which involves the use of logarithms since the number of compounding terms is in the power. The equation can be solved like the following:
The RATE formula is the most complicated. When using the RATE function in Excel, there is an argument called guess. Most of us will just leave it as default or even do not understand why there is such a thing. It looks impossible to write the equation with on one side. To find the root of this non-linear equation, we use Newton’s Method. It is a root-finding algorithm of a real-valued function. The process is repeated as
until a sufficiently precise, that is is less than some value, is reached.
We start with our initial . Then tangent line at intercepts the x-axis at which is a better approximation to the root. The slope of the tangent line is . Solving for . If the difference between and is smaller than the tolerance, the iteration stops.
That is it. We have the 5 formulas related to the time value of money and we know how to solve them.
There are some other financial formulas, for example, IRR, IPMT (interest portion of payment), and PPMT (the principal portion of payment). We can cover them in future articles.