Financial Calculations in RPG
Does anyone have sample code of the following calculations (Excel Equivalent in brackets):
1. Net Present Value (NPV)
2. Payment (PMT)
3. Principal Payment (PPMT)
Any form of RPG to run on an iSeries would be great.
Software/Hardware used:
Software/Hardware used:
ASKED:
September 22, 2005 10:13 AM
UPDATED:
September 24, 2005 10:23 PM
Answer Wiki:
Unfortunately the reason the Excel functions exist is that there is no other simple formula for these calculations. Programming these calculations requires is an iterative process. You find a reasonable starting point and make minor repetitive increments until you get to your answer. I don't know RPG, so the following code is generic. I only addressed the details for payments.
To Calculate Payments
"Define the following work fields as numeric (2 decimals)
obal (original balance a.k.a. principal, ex: 1000)
ebal (ending balance after a payment is applied)
rate (annual interest rate, ex: .10 = 10%)
ratem (monthly rate = rate / 12)
term (number of months, ex: 36 = 3 years)
pmt (payment)
pmt_int (portion of payment that is interest)
pmt_prin (portion of payment that is principal)
pmt_last (last payment)
"Set any initial values. Could be from user input.
obal = 1000
rate = .10
term = 36
"Calculate monthly interest rate
ratem = rate / 12
"Calculate a low starting point for payments.
"Assume equal payments with no interest
pmt = obal / term
"Modify payment until it produces a good result
"(i.e. balance at end of term is <= 0)
ebal = obal
Do while ebal > 0
"Loop through number of terms to calculate ending balance
Do term times
pmt_int = ebal * ratem "calculate interest
pmt_prin = pmt - pmt_int "calculate principal
ebal = ebal - pmt_prin "calculate ending bal
Enddo
"If ending balance is too big, try a greater payment
if ebal > 0
ebal = obal
pmt = pmt + .01
endif
Enddo
"At this point, pmt is your correct amount
"The last payment must be modified to handle rounding
pmt_last = pmt + ebal
For NPV the starting balance must be modified until you find your answer. Calculate your initial starting balance assuming a rate of 0 (i.e. simply add up all of your payments and income.) Then loop to calculate your remaining balance at the end of your term using the real interest rate. If you have too much left, reduce your starting balance by 1 penny and recalculate everything until the ending balance is < 0.
I've never played with PPMT. I guess you'd use the payment logic, but during the inner loop you'd need to increment your payment each term by your rate.
Hope this helps. Maybe someone else has a better answer. Good luck.
To see all answers submitted to the Answer Wiki: View Answer History.
Let me see if I can add something to this discussion.
In the “olden days,” people did lookups of cost of money factors from pre-computed tables, and those tables were based on interest rate and number of periods. For example, one could look up the present worth of a future amount factor for an interest rate of 10% per year and twenty period in such a table and get the value .148644. They then multiplied the selected factor times the specific monetary amount in question ? for example, multiplying that factor times a future amount of $2,000 yield a present worth of $297.29.
You are in effect bringing back the “olden days” by creating your own executables, and mimicking those steps will get you to where you want to go without losing precision.
For a given transaction or case, first you must access (or compute) the appropriate values for “interest” (cost of money). You also need to compute the number of periods, and the two need to be expressed in the same units of time. For example, if you expect a payment in September, 2025 and you deal in annual interest rates, your software or some built-in calendaring function would need to compute the number of years (20). If your rates are in months or days or whatever, you would of course compute time in those units. Note that sealing in small units of time, like days, makes it essential that you preserve precision.
Once you have i and n, you plug them into the appropriate formula to produce the appropriate factor. For example, as shown below, an interest rate of 10% per annum and a payment that won’t be received for 20 years will result in a present worth factor of .148643628. As long as you can do floating point arithmetic and you retain enough precision for your business purpose, the formulas should present little problem. This factor in effect is the present worth of 1 unit of money at 10% over 20 years.
Once you have computed the factor, you multiply the factor times the number of dollars in the particular transaction ? e.g., in this case multiply .148643626 times $2,000 to produce the present worth amount of $297.29. You can blend the two steps together (as I presume Microsoft does inside Excel), but particularly for debugging and auditing you want to be sure you do not inadvertently lose precision, so computing (and perhaps logging) the factor separately isolates the opportunity for error and also enables you to cross-check against some “olden days” book that includes cost of money tables. Those same books (e.g., Engineering Economy by Gerald W. Smith) include the formulas themselves.
Below I have pasted in pieces of an Excel spreadsheet that does the computation exactly as described above and without using built-in Excel cost of money formulas. I named the input cells “i” and “n,” and the column labeled “Formula” is the Excel equation that I wrote to compute the factor (I left out the Excel = to force this display; on my spreadsheet the operative formula with the Excel = is within the column headed “formula” ).
As I think is apparent, writing code in almost any programming language to compute cost of money factors is pretty trivial. Most of the work will be in serving up and manipulating the input data and saving and publishing the outputs.
I do not know if you are doing this because of Sarbanes-Oxley or other compliance issues, but there are many other situations where people are being forced to give up the “black box” solutions (and very good ones) built into MS Office and elsewhere, taking those people back to “olden days.” If you are a high compliance line of work (e.g., consumer lending), make sure your selection of formulas and exact implementation match regulatory requirements because truncation in intermediate results could get up into compliance trouble – (e.g., the car or mortgage payment is overstated by a penny), .
Fulton Wilcox Colts Neck Solutions LCC
present worth of a future sum (10% interest, 20 years)
Formula factor amount Equals
1/((1+i)^n) 0.148643628 $2,000.00 $297.29
annuity from a present sum (10% interest, 20 years)
Formula factor amount equals
(i*((1+i)^n))/(((1+i)^n)-1) 0.117459625 $10,000.00 $1,175