SAS - Calculating the present value of future interest and amortization payments of loans

Question

I would like to calculate the present value (PV) of future interest and amortization payments on loans (flat amortization and annuity) in a dataset. The discount rate should be the inflation e.g. 2 per cent. I have seen the finance function in SAS, but it requires each cashflow which are difficult due to the nature of the dataset.

E.g. if you borrow 100,000 for 100 month (flat amortization) you will repay (amortization) 1,000 each month. But due to inflation (e.g. 2 per cent) the 100 month repayment is not the same as the 1 month in real terms. The same goes for the interest cost.

In the end I would like to have columns after my dataset that show the PV of all future interest and amortization payments.

If possible I would like to have the solution in a data step.

Example of my dataset

data have;
    call streaminit(12345);
    do i = 1 to 5;
        loanAmount = abs(rand("normal", 300E3, 200E3));
        interestRateNominalAnnual = abs(rand('normal',0.05,0.05));
        maturityMonth = abs(rand("normal", 120, 24));
        output;
    end;
    format loanAmount comma10. interestRateNominalAnnual 5.2 maturityMonth 3.0;
run;

Want

loanAmount	interestRateNominalAnnual	maturityMonth
352,847	0.10	140
189,445	0.13	90
271,693	0.10	122
545,052	0.04	127
70,121	0.03	109

Are you asking for the monthly payment split into interest and principle needed to pay off 70,121 in 109 months at a an interest rate of 3%? Or the sum of the 109 interest payments? If the later then what is the meaning of the last column? Also is 3% the monthly (per payment) interest rate or the annual interest rate (so the monthly rate would be 0.25%). — Tom, Nov 01 '21 at 16:19
The interest rate is annual. So the monthly interest rate would be 0.03/12 for the 70,121 loan. What I would like to calculate is the PV of all future payments (interest and amortization). Not the invoice for the loans. Due to inflation the the interest and amortization payments for month 100 is different than the value at 109 month. So, the PV of amortization should be calculated as well. — aef, Nov 01 '21 at 16:22

score 1 · Accepted Answer · answered Nov 08 '21 at 17:07

options cmplib=work.ORBA;
proc fcmp outlib=work.ORBA.pv; * Present value;
    subroutine pvLoan(  /* input */
                        loanAmount,
                        maturityMonth,
                        interestRateAnnual,
                        inflationAnnual,

                        /* output */
                        amortizationSumAnnuity,
                        interestSumAnnuity,
                        invoiceSumAnnuity,

                        amortizationSumFlat,
                        interestSumFlat,
                        invoiceSumFlat,

                        pvAmortizationSumAnnuity,
                        pvInterestSumAnnuity,
                        pvInvoiceSumAnnuity,

                        pvAmortizationSumFlat,
                        pvInterestSumFlat,
                        pvInvoiceSumFlat);

    outargs amortizationSumAnnuity,     interestSumAnnuity,     invoiceSumAnnuity,
            amortizationSumFlat,        interestSumFlat,        invoiceSumFlat, 
            pvAmortizationSumAnnuity,   pvInterestSumAnnuity,   pvInvoiceSumAnnuity,
            pvAmortizationSumFlat,      pvInterestSumFlat,      pvInvoiceSumFlat;

        if missing(loanAmount) or missing(maturityMonth) or missing(interestRateAnnual) then 
            do;
                pvAmortizationSumAnnuity    = .;
                pvInterestSumAnnuity        = .;
                pvInvoiceSumAnnuity         = .;
                pvAmortizationSumFlat       = .;
                pvInterestSumFlat           = .;
                pvInvoiceSumFlat            = .;
            end;
        else
            do;
                inflationMonth  = sum(exp((log(sum(1,inflationAnnual)))/12),-1);

                do maturityPeriod = 1 to maturityMonth;
*                   ------------------------------------------------------------------------------------------;
*                               ANNUITY;
*                   ------------------------------------------------------------------------------------------;
                    fv                      = 0; * Specifies the future value after the last payment is made;
                    paymentDueDate          = 0; * Specifies whether the payments occur at the beginning or end of a period. 0 represents the end-of-period payments;

*                   NOMINAL VALUE;
*                   If the interest rate is zero you only amortize. Equal to flat amortization;
                    if interestRateAnnual = 0 then 
                        do;
                            amortizationAnnuity         = loanAmount / maturityMonth;
                            interestPaymentAnnuity      = 0;
                            invoiceAnnuity              = sum(amortizationAnnuity, interestPaymentAnnuity);
                        end;
                        else
                            do;
                                amortizationAnnuity     = abs(finance('ppmt', interestRateAnnual/12, maturityPeriod, maturityMonth, loanAmount, paymentDueDate));
                                invoiceAnnuity          = abs(finance('pmt', interestRateAnnual/12, maturityMonth, loanAmount, fv, paymentDueDate));
                                interestPaymentAnnuity  = abs(sum(invoiceAnnuity, - amortizationAnnuity));
                            end;

*                   Cumulative nominal flat amortization;
                    amortizationSumAnnuity      = sum(amortizationSumAnnuity, amortizationAnnuity);
                    interestSumAnnuity          = sum(interestSumAnnuity, interestPaymentAnnuity);
                    invoiceSumAnnuity           = sum(amortizationSumAnnuity, interestSumAnnuity);

*                   PRESENT VALUE;
*                   Present value of the interest and amortization for a annuity;
                    pvAmortizationAnnuity       = amortizationAnnuity    / ((1+inflationMonth)**maturityPeriod);
                    pvInterestAnnuity           = interestPaymentAnnuity / ((1+inflationMonth)**maturityPeriod);
                    pvInvoiceAnnuity            = sum(pvAmortizationAnnuity, pvInterestAnnuity);

*                   Cumulative present value annuity;
                    pvAmortizationSumAnnuity    = sum(pvAmortizationSumAnnuity, pvAmortizationAnnuity);
                    pvInterestSumAnnuity        = sum(pvInterestSumAnnuity, pvInterestAnnuity);
                    pvInvoiceSumAnnuity         = sum(pvAmortizationSumAnnuity, pvInterestSumAnnuity);

*                   ------------------------------------------------------------------------------------------;
*                               FLAT AMORTIZATION;
*                   ------------------------------------------------------------------------------------------;
*                   NOMINAL VALUE;
*                   Payment in period n;
                    amortizationFlat        = loanAmount / maturityMonth;
                    interestPaymentFlat     = (sum(loanAmount, -amortizationFlat*(maturityPeriod-1)) * interestRateAnnual/12);
                    invoiceFlat             = sum(amortizationFlat, interestPaymentFlat);

*                   Cumulative nominal flat amortization;
                    amortizationSumFlat     = sum(amortizationSumFlat, amortizationFlat);
                    interestSumFlat         = sum(interestSumFlat, interestPaymentFlat);
                    invoiceSumFlat          = sum(amortizationSumFlat, interestSumFlat);

*                   PRESENT VALUE;
*                   Present value of the interest and amortization for flat amortization;
                    pvAmortizationFlat      = amortizationFlat    / ((1+inflationMonth)**maturityPeriod);
                    pvInterestFlat          = interestPaymentFlat / ((1+inflationMonth)**maturityPeriod);
                    pvInvoiceFlat           = sum(pvAmortizationFlat, pvInterestFlat);

*                   Cumulative present value flat amortization;
                    pvAmortizationSumFlat   = sum(pvAmortizationSumFlat, pvAmortizationFlat);
                    pvInterestSumFlat       = sum(pvInterestSumFlat, pvInterestFlat);
                    pvInvoiceSumFlat        = sum(pvAmortizationSumFlat, pvInterestSumFlat);
                end;
            end;
    endsub;
run;

data have;
    call streaminit(12345);
    do i = 1 to 5;
        loanAmount          = abs(floor(rand("normal", 300E3, 200E3)));
        maturityMonth       = abs(floor(rand("normal", 120, 24)));
        interestRateAnnual  = abs(rand('normal',0.05,0.05));
        output;
    end;
    format loanAmount maturityMonth comma10. interestRateAnnual percent10.2;
    drop i;
run;

data want;
set have;
*   Call the subroutine and performe the calculations;
    call pvLoan(    /* input */
                    loanAmount,
                    maturityMonth,
                    interestRateAnnual,
                    0.02,
                    /* output */
                    amortizationSumAnnuity,
                    interestSumAnnuity,
                    invoiceSumAnnuity,

                    amortizationSumFlat,
                    interestSumFlat,
                    invoiceSumFlat,

                    pvAmortizationSumAnnuity,
                    pvInterestSumAnnuity,
                    pvInvoiceSumAnnuity,

                    pvAmortizationSumFlat,
                    pvInterestSumFlat,
                    pvInvoiceSumFlat);
    format _numeric_ comma20. interestRateAnnual percent5.2;
run;

Please don't post only code as an answer, but also provide an explanation what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes. — Tyler2P, Nov 08 '21 at 17:18
Thanks for the tip. I will return with an explanation shortly. — aef, Nov 09 '21 at 09:23

SAS - Calculating the present value of future interest and amortization payments of loans

Example of my dataset

Want

1 Answers1