topic Re: Additional loan amortization table question in SAS Programming

Additional loan amortization table question

greg6363 — Mon, 21 Oct 2019 17:13:35 GMT

I have created a manual program to create a loan amortization table since my SAS license does not have the financial functions. Now my question is how to integrate both loan term and payment frequency into the program. Accounts pay weekly, bi-weekly semi-monthly or monthly (12, 24, 26 or 52 total payments) on either a six month, nine month or twelve month loan term.

data amort_form_main;
FundAmount=XXXX;
APR=X.XXXXXX;
N=XX;/*PmtFreq*/
Balance=FundAmount;
APR_Rate=APR/N;
NPER=ceil(N*(9/12));/*LoanTerm*/
DO i=1 to NPER;
    PMT= (APR_RATE + APR_RATE/(((1+APR_RATE)**NPER) -1))*FundAmount;
    BALANCE= BALANCE*(1+APR_RATE) - PMT;
    INTEREST=BALANCE*APR_RATE;
    PRINCIPAL=PMT-INTEREST;
    OUTPUT;
END;
Format PMT BALANCE INTEREST PRINCIPAL DOllAR21.2;
RUN;

I wanted to see if I am taking the loan term and the payment frequency into account correctly within this program in order to make sure the loan payment is broken down correctly between the principal and the interest components.. Any feedback would be greatly appreciated. Thanks.

Re: Additional loan amortization table question

ballardw — Mon, 21 Oct 2019 17:25:30 GMT

I understand that you may not have access to Proc Loan but none of the Finance functions? What version of SAS are you running?

Are you sure that you do not have access to the MORT function?

Try running this code and show us the log result.

data have;
   payment=mort(50000, . , .10/12, 30*12);
run;

If data have has a value of Payment in the 438.79 range then you have the financial function you may need.

The above figures the monthly payment for a loan of $50000 at 10% compounded monthly for 30 years.

Re: Additional loan amortization table question

greg6363 — Mon, 21 Oct 2019 18:02:26 GMT

Well, the mort function worked for me when I ran your sample code. Strange.

OK, now what about for irregular loan terms and payment frequencies.

I have a loan with a 9 month term that has a weekly payment frequency.

When I write the code (.75 for 3/4ths of a year to capture the 9 month term and the 39 weekly payments

for the payment frequency) as such:

data loan_xxx;

mort payment=mort(900, . , 4.9969736/12, .75*39);

run;

I get a payment amount that is much higher than the coupon. Let me know if I'm taking the non-traditional

units for loan term and payment frequency into account properly. Thanks.

Re: Additional loan amortization table question

ballardw — Mon, 21 Oct 2019 21:07:24 GMT

@greg6363 wrote:

Well, the mort function worked for me when I ran your sample code. Strange.

OK, now what about for irregular loan terms and payment frequencies.

I have a loan with a 9 month term that has a weekly payment frequency.

When I write the code (.75 for 3/4ths of a year to capture the 9 month term and the 39 weekly payments

for the payment frequency) as such:

data loan_xxx;

mort payment=mort(900, . , 4.9969736/12, .75*39);

run;

I get a payment amount that is much higher than the coupon. Let me know if I'm taking the non-traditional

units for loan term and payment frequency into account properly. Thanks.

I'm not a financial wizard but the /12 in the example is to turn an annual interest rate into a monthly. So I would suspect that you need to use additional calculation to get weekly (note: NOT 4 weeks per months), I think / 52.

Interest rate of 4% should be entered as 0.04. Notice the example I provided for 10% uses .10. And to get a weekly compounded interest rate you would divide by 52 from an annual rate.

The last parameter should be an integer number of payment periods. So 39.

Does this look more appropriate for a result?

data loan_xxx;
   mortpayment=mort(900, . ,0.049969736/52, 39);
run;

If you have some known rates and payments to check with then experiment.