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.
Discuss This Question: 1 Reply