Part 1 of 3 Posts on Interest Calculations

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.

___________________________________________________________________________________________

 

Intro

The purpose of this ruleset is to demonstrate financial calculations (specifically interest calculations) in OPA both with and without temporal attributes.  The use of temporal attributes will demonstrate more power and simplicity in computing and assessing monetary change over time.  This simplicity is needed for helping to handle complex logic in other rulesets. 

 

This ruleset does not make use of entities.

 

Our objective is to use OPA to show basic investments and loan payoffs over time as shown here.


The Basic Setup for Investment and Loans

Let's start with an OPA legend.  Usually we see legends only in Excel, but they can be a nice tool in Word, especially when a lot of math is involved.

Use a non-booolean attribute in a rule

 

t is the term in years (e.g. "5")

P is the original principle (e.g. "30000")

APR is the annual percentage rate (e.g. "2.5")

 

n is the number of times a year the interest accumulates

a is the annual rate of return

r is the period rate

i is the period simple interest

rL is the loan period rate

cL is the total number of loan payments

 

Always provide an ability to override the current date.

[Rule 1.1] today = the current date

 

We can calculate the maturity date (aka due date) by adding the term of the loan to the issue date.

[Rule 1.2] the maturity date = the date t years after the issue date

 

And, we need to know the frequency of interest deposits made each year

[Rule 1.3] the number of times a year the interest accumulates

1

the payment frequency = "Yearly"

2

the payment frequency = "Semiannually"

4

the payment frequency = "Quarterly"

12

the payment frequency = "Monthly"

365

the payment frequency = "Daily"

uncertain

otherwise

 

Providing a Temporal Timeline

It helps to visualize each accumulation "period." We want a temporal timeline to reference in our calculations and watch in Temporal Visualization.

We also need a temporal attribute per the "OPA Access Timeline Pattern".

The easiest way to understand what is happending with each of these rules is to view the attributes in Temporal Visualization in the debugger.  Test cases have been provided.

[Rule 2.1] the calendar date = AddDays(the issue date, TemporalDaysSince(the issue date, AddDays(the maturity date,2)))

 

Using the above the calendar date (temporal), we can quickly put a "number" to each period of the loan. 

[Rule 2.2] the period difference

YearDifference(the issue date, the calendar date)

the payment frequency = "Yearly"

Trunc((MonthDifference(the issue date, the calendar date) / 6), 0)

the payment frequency = "Semiannually"

Trunc((MonthDifference(the issue date, the calendar date) / 3), 0) 

the payment frequency = "Quarterly"

MonthDifference(the issue date, the calendar date)

the payment frequency = "Monthly"

DayDifference(the issue date, the calendar date)

the payment frequency = "Daily"

Uncertain

otherwise

 

The first period starts at the issue date, not a month later. As such, we need to adjust the period number.

[Rule 2.3] the period number = the period difference + 1

 

The final period we will show at the last payment on the maturity date.

[Rule 2.4] the period start date

AddYears(the issue date, the period difference)

the payment frequency = "Yearly"

AddMonths(the issue date, the period difference * 6)

the payment frequency = "Semiannually"

AddMonths(the issue date, the period difference * 3)

the payment frequency = "Quarterly"

AddMonths(the issue date, the period difference)

the payment frequency = "Monthly"

the calendar date

the payment frequency = "Daily"

Uncertain

otherwise

 

[Rule 2.5] the period end date

the date 1 day before AddYears(the period start date,1)

the payment frequency = "Yearly"

the date 1 day before AddMonths(the period start date,6)

the payment frequency = "Semiannually"

the date 1 day before AddMonths(the period start date,3)

the payment frequency = "Quarterly"

the date 1 day before AddMonths(the period start date,1)

the payment frequency = "Monthly"

the period start date

the payment frequency = "Daily"

Uncertain

otherwise

 

 

[Rule 2.6] the prior period end date

ValueAt(the date 1 day before the maturity date, the period end date)

the calendar date = the maturity date

ValueAt(the date 1 day before the period start date, the period end date)

otherwise

 

 

Simple Interest Calculations Like the "Internet Websites" Do It

There are, of course, websites to help you compute simple interest, such as:

http://www.webmath.com/simpinterest.html

https://www.calculatorsoup.com/calculators/financial/simple-interest-plus-principal-calculator.php

The above is not endorsed, simply provided as "checks" on this example policy model and to demonstrate we can provide more via OPA!

The interest charge is usually stated as a rate percent of the principal per year or annual percentage rate.  We have to divide by 100 to convert from percent to decimal. I do that here.  (As a note for you financial types, I recognize that I am somewhat misusing APR which may also include fees, but most people get the idea.)

 

("a" is the annual rate of return by converting from percent to decimal)

[Rule 3.1] a = APR / 100

 

("r" is the period rate of return by dividing the annual rate by the number of periods each year.  12 in the case of months...)

[Rule 3.2] r = a / n

 

Note that in simple interest, after a period of time, you are given a specified amount of money based on a rate for the initial investment.  The interest does NOT get reinvested so the math is simple...  Simple Interest = original principle x the rate of return

[Rule 3.3] i = P * r

 

On the maturity date, you get back the original principle and the interest.

Math geeks have a formula they like to use.  Basically this formula says that at the end of a loan, you get the principle + the principle * the annual percentage rate * the number of years.  Let us be kind and use their formulas.  It all works and we could have expanded the English out if we wanted to say something like:

the simple interest maturity value = the initial principle + ( the initial principle * the annual rate of return * the term in years)

Check this out in the debugger:

[Rule 3.4] the simple interest maturity value = P * (1 + (a * t))

 

 

Temporal Simple Interest Calculations (OPA)

But we want to "watch" our investment grow! We want to see the balance each time its updated.

We get paid for each period of the investment immediately after the period ends.  We will see the new balance at the start of the next period and the final balance at the maturity date.

 

Let's give ourselves a "Pay Day" at the start of every period except the first one.

[Rule 4.1] it is the day to be paid interest if

the calendar date = the date 1 day after the prior period end date

 

To REALLY see the growth, we use temporal dates within a temporal function.  In this case, we use the period end dates.

This formula varies from above by calculating the balance at each period.

[Rule 4.2] the simple interest balance over time = P + IntervalDailySumIf(the issue date, the period end date, i, it is the day to be paid interest)

 

An interesting fact that threw me for a few moments is that the year 2012 is a leap year.  We get paid 366 times if we set the accumulation frequency to daily.  That gives us a tiny bit of extra money.  I have not fully researched this in banking to see the proper way to handle leap year.

Now that we have appropriate temporal interest calculations, we can simply find the balance at any point in time.

[Rule 4.3] the simple interest maturity value version 2 = ValueAt(the maturity date, the simple interest balance over time)

[Rule 4.4] today's simple interest balance = ValueAt(today, the simple interest balance over time)

 

That wasn't really too hard.  That is not even two dozen rules.  Simply examine the variables in Temporal Visualization and make sure you follow what happened.  The next post will introduce Compound Interest.