Part 3 of 3 Posts on Interest Calculations
This post builds off of the content from Part 1 and Part 2. 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.
The project attached to this post is a complete project with all the rules from Parts 1 and 2.
Disclaimers: I don't work for or represent Oracle. Use the content in these posts at your own risk.
Making Payments on a Loan – Future Value Simple Interest
At this point, perhaps we can learn something car dealers have used to cheat customers. What would you expect the payment to be on a car loan for $30,000 at 2.5% interest over 5 years?
Many loan officers simply show you the final simple interest loan value on a 30,000 loan which would be $33750 over 5 years at 2.5% APR. The loan officer then divides that by the number of payments and gives you the payment amount.
Let's calculate that amount.
rL is the "period rate on the loan". Basically it is the annual rate / the number of times annually that we make payments (or more specifically that the bank adjusts the balance.)
[Rule 7.1] rL = a / n
And to help us out, here is the total number of loan payments (cL) we have to make.
[Rule 7.2] cL = n * t
So, the math should be simple, correct? What is the final balance for a simple interest loan and divide that by the number of payments to get how much we should pay...
[Rule 7.3] the future value simple interest payment = the simple interest maturity value / cL
So, let's watch the payoff over time… Looks good? We end with 0 dollars owed.
[Rule 7.4] the future value simple interest loan payoff = the simple interest balance over time – (the future value simple interest payment * (the period number – 1 ) )
However, the above calculation has a hidden catch the loan officer didn't tell you about when advertising the low 2.5% APR. It is known as using the "future value simple interest" to calculate the payment amount. It is deceptive because you should pay down the loan over time but with this method, you ONLY pay down at the end of the loan! The payments you made were not amortized.
Basically, the loan officer is ignoring all your payments in the interest calculation. From this perspective, a temporal representation doesn't show anything fancy -> just a $500 decrease in the loan amount every month until the 5 years is up.
Loan Payments Properly Amortized
What SHOULD happen is that the interest is only calculated on the balance of the loan every period! That changes the math somewhat.
We still use math to compute the true simple interest payment.
[Rule 8.1] the amortized interest payment = rL * ( ( Xy( 1 + rL, cL) * P ) / ( Xy( 1 + rL, cL ) - 1 ) )
Notice that payment is LESS than "the future value simple interest payment" above by approximately $30. This is because it is amortized over the length of the loan.
So, let's continue on and watch the proper payment schedule.
First, each period's additional interest is calculated based on the prior period's balance to include any payments you have made. To get there, we need to know the last date that the balance was updated.
[Rule 8.2] the amortized loan principle
the previous amortized loan principle
the period number > 1
[Rule 8.3] the previous amortized loan principle
ValueAt(WhenLast(the prior period end date, it is the day to be paid interest), the amortized loan principle)
the period number > 2
[Rule 8.4] the current period amortized loan interest
the previous amortized loan principle * rL
the period number > 1
[Rule 8.5] the accumulated amortized loan interest
ValueAt(the prior period end date, the accumulated amortized loan interest) + the current period amortized loan interest
the period number > 1
So, the total paid in a temporal attribute is
[Rule 8.6] the amortized loan value = P + the accumulated amortized loan interest
As before, we just go to the maturity date to discover stuff like the final amount we paid on the loan.
[Rule 8.7] the final amortized loan value = ValueAt(the maturity date, the amortized loan value)
And that is it. If you can understand the rules in this blog post and the last 2 blog posts, you should have a foundation for temporal attribute usage. Try studying all the blogs a few times. Feel free to ask questions in the comments.
Interest.zip 9.2 MB