DO LOOP: HOW to create EMI calculator

[Purushottam]

Dear All,

Kindly provide the Solution for the following.

I am trying since Moring but not able to Make It.

data EMI_Form;
P=100000;
R=0.12;
do N=1 to 12;
EMI=(P*R*(1+R)**N)/(1+R)**(N-1);
output;
end;
run;

but I am Getting this output.

	P	R	N	EMI

1

100000

0.12

1

13440

2

100000

0.12

2

13440

3

100000

0.12

3

13440

4

100000

0.12

4

13440

5

100000

0.12

5

13440

6

100000

0.12

6

13440

7

100000

0.12

7

13440

8

100000

0.12

8

13440

9

100000

0.12

9

13440

10

100000

0.12

10

13440

11

100000

0.12

11

13440

12

100000

0.12

12

13440

Kindly Suggest me how to make the formula.

thanks a lot

[RW9]

What is is supposed to do (provide test data in the form of a datastep, and required output plus explanation)?  I don't know what an EMI calculator is.  Perhaps it should total the value across records?  If so then use a retain:

data emi_form;

  retain emi;

  p=1000;

  ...;

run;

[Purushottam]

Dear Sir,

EMI stands for the Easy Monthly Installment against the Loan which one take from any bank or any one else.

suppose MR A took loan of $ 10000 and the interest Rate is 10% on that. The period choosen is 2 year. then Mr A has to money return to the Money lendor on small donation.

Now Kindly Help me Please.

Thanks.

[Reeza]

Why aren't you using a financial function? Please post what you expect the value/output to be.

[Haikuo]

Not sure what you are after, but your formula ends up like:

P*R*(1+R)

after being simplifed,  N is irrelevant. Suggest you check your formula. 

[KachiM]

You missed to read the formula correctly. Use this and find whether this works for you.

data have; 
   P=100000; 
   R=0.12;
   do N=1 to 12;   
      EMI=(P*R*(1+R)**N)/((1+R)**N-1); 
      output; 
   end; 
run;

[Purushottam]

data have; 
   P=100000; 
   R=0.12;
   do N=1 to 12;   
      EMI=(P*R*(1+R)**N)/((1+R)**N-1); 
      output; 
   end; 
run;
Dear 

this is not working. it is not giving the desired output.

[Reeza · Accepted solution]

Here's a workup for a full payment plan, includes the payment amount, interest portion, principal portion, balance at each time period. Assumes payment at end of period.

data emi_form;
P=100000;
R=0.12;
N=12;
Years=1;
Balance=P;
ERate=R/N;
NPER=N*YEARS;

DO i=1 to NPER;

    PMT= (ERATE + ERATE/(((1+ERATE)**NPER) -1))*P;
    BALANCE= BALANCe*(1+ERATE) - PMT;
    INTEREST=BALANCE*ERATE;
    PRINCIPAL=PMT-INTEREST;

    OUTPUT;

END;

Format PMT BALANCE INTEREST PRINCIPAL DOllAR21.2;
RUN;

[Purushottam]

Thank You very much Reeza.

I have no word to say.

after completing my small concepts I will go to learn the Functions. 

[MikeZdeb]

Hi, the output here matches what you get on EMI Calculator ...

http://ncalculators.com/mortgage/emi-calculator.htm

(close to but not the same as Reeza's ... maybe you really want to write all the formulas on your own but to me that's akin to writing a data step to compute means and standard deviations rather than using PROC MEANS or SAS functions)

data ci (keep=period p prin int);
retain initial 100000 rate 0.12;
p = mort(initial, . , rate/12 , 12);
do period=1 to 12;
  prin = ppmt(0.12/12, period, 12, 100000);
  int = p - prin;
  output;
end;
label p='payment' prin='principle' int='interest';
run;

period       payment     principle      interest

   1          $8,885        $7,885        $1,000
   2          $8,885        $7,964          $921
   3          $8,885        $8,043          $842
   4          $8,885        $8,124          $761
   5          $8,885        $8,205          $680
   6          $8,885        $8,287          $598
   7          $8,885        $8,370          $515
   8          $8,885        $8,454          $431
   9          $8,885        $8,538          $347
  10          $8,885        $8,624          $261
  11          $8,885        $8,710          $175
  12          $8,885        $8,797           $88

[Purushottam]

This is working, it seems like the yesterday's solutions.

thanks a lot

[MikeZdeb]

Hi. Yes, it is similar but uses a new function ... PPMT.  Take a look at ...

FINANCE Function
Computes financial calculations such as depreciation, maturation, accrued interest, net present value, periodic savings, and internal rates of return.

https://support.sas.com/documentation/cdl/en/lefunctionsref/67960/HTML/default/viewer.htm#p1cnn1jwdm...

If you have some $$$ to spend on a book and want to learn about SAS functions, I would buy Ron Cody's book ...

SAS® Functions by Example, Second Edition

http://www.sas.com/store/prodBK_62857_en.html

Even if you do not buy the book, all the SAS code and data used can be downloaded (FREE) ...

http://support.sas.com/downloads/package.htm?pid=1680

and you can read an excerpt of the book (FREE) ...

http://www.sas.com/storefront/aux/en/spfunctionxexample/62857_excerpt.pdf

Ron also has number of function-related papers (FREE) that you can find via Google seraches, for example ...

An Introduction to SAS® Character Functions

http://www2.sas.com/proceedings/sugi31/247-31.pdf

[Reeza]

@MikeZdeb, I agree in general, but the OP didn't seem to want to use them. Sometimes it's also good to understand the math behind functions as well. I'm sure I took a class where they made us derive these...strangely enough it was the class that convinced me to switch to Statistics.

[MikeZdeb]

HI.  Re " ...  good to understand the math behind functions ... " , your code did produce different answers with the same set of parameters (monthly payment, interest, etc.).  Was that really the correct math for EMI?  Just curious.

[Reeza]

I don't actually get the same numbers when I run your code Mike.

I did have to change it to the finance PPMT function though...

data ci (keep=period p prin int);
retain initial 100000 rate 0.12;
p = mort(initial, . , rate/12 , 12);
do period=1 to 12;
  prin = finance('ppmt', 0.12/12, period, 12, 100000);
  int = p - prin;
  output;
end;
label p='payment' prin='principle' int='interest';
run;

proc print data=ci;
run;

Obs p period prin int
1 8884.88 1 -7884.88 16769.76
2 8884.88 2 -7963.73 16848.61
3 8884.88 3 -8043.36 16928.24
4 8884.88 4 -8123.80 17008.68
5 8884.88 5 -8205.04 17089.92
6 8884.88 6 -8287.09 17171.97
7 8884.88 7 -8369.96 17254.84
8 8884.88 8 -8453.66 17338.54
9 8884.88 9 -8538.19 17423.07
10 8884.88 10 -8623.58 17508.45
11 8884.88 11 -8709.81 17594.69
12 8884.88 12 -8796.91 17681.79