Part 2 of 3 Posts on Interest Calculations
This post builds off of the content from Part 1. This post is a copy of the content of the Word rule document within the attached project. Download that project into OPA 12 to see it all working together.
Disclaimers: I don't work for or represent Oracle. Use the content in these posts at your own risk.
Compound Interest Calculations Like the "Internet Websites" Do It
Compound interest is the math headache that usually requires spreadsheet functions.
Prior to spreadsheets, there were logarithmic tables and stuff I used to know, but have forgotten. The basic premise is that interest accrued over a period of time is paid into an account daily, monthly, quarterly, semiannually, or yearly. Future interest is based on the new balances, so all your money works for you. Banks generally work in this fashion with the daily interests accumulating and being paid out at some future point in time. Withdrawals and deposits can occur, etc…
It currently takes lots of mainframe / Unix financials code that we can do pretty quickly here. We get the ability to visually watch the balance change! We can also try out new ways to make money with the test cases and spreadsheet analysis, but I digress. Since about 2007, bankers beat out politicians in my list of people to despise. Maybe if they had used OPA…
OPA can run that formula to see how much money we would have at the end. We can use this to check our future calculations. For fun, let's plug in the standard formulas and compute our future balance…
I am rounding to the nearest penny to match the results from the interest calculator. This is that infamous area where sneaky coders make lots of money by rounding down to the nearest penny for investment payments or up for collections and putting the fractional penny remainder in their own accounts.
Note: it is a LOT harder to get away with that type of mischief when your code is human-readable and can be reviewed by auditors. Score 1 for OPA.
[Rule 5.1] the compound interest maturity value compounding = Round(P * Xy(1 + r, n * t ),2)
Temporal Compound Interest Calculations (OPA)
Now, lets do it the "OPA way".
Like before, we want to watch the investment actually grow.
Like before, we get to add the balance on the beginning of each pay period except the first when we made our deposit. Each interest computation will rely on the previous balance. These 2 rules should give a loop warning. Ignore it.
[Rule 6.1] the compound interest balance over time
the period number = 1
the previous compound interest balance + the previous compound interest balance * r
[Rule 6.2] the previous compound interest balance
the period number = 2
ValueAt(WhenLast(the prior period end date, it is the day to be paid interest), the compound interest balance over time)
And what is our final balance? Simply look at the balance on the end date
[Rule 6.3] the compound interest maturity value compounding version 2 = ValueAt(the maturity date, the compound interest balance over time)
The balance today is also pretty easy… You can vary "today" to go backwards in time.
[Rule 6.4] today's compound interest balance = ValueAt(today, the compound interest balance over time)
That is it. Only 5 rules, and the last 2 are only to demonstrate getting the values at different points in time.
Interest Compound.zip 9.2 MB