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