Questions : My friend has a request to create a sheet in which he wants to find the interest and principle component in his equal monthly installment on all the payment schedule.
My Solution :
Excel has a build in function PPMT() to find principle part of the EMI paid by him. The PMT() Function will be used for find the EMI Value of the loan part.
The steps to create the sheet is;
A |
B |
C |
D |
E |
F |
G |
H |
5 |
|
|
|
|
|
|
|
6 |
Loan Amount |
(5,00,000) |
|
|
|
|
|
7 |
Term |
18 |
Months |
|
|
|
|
8 |
Rate |
14% |
|
|
|
|
|
9 |
|
|
|
|
|
|
|
10 |
PMT |
Rs.30,958 |
=PMT(C8/12,C7,C6,0) |
|
|
|
11 |
|
|
|
=PPMT($C$8/12,$C13,$C$7,$C$6) |
|
12 |
|
Payment |
Date |
Principal |
Interest |
Balance |
|
13 |
|
1 |
1-Dec-10 |
25,124 |
5,833 |
25,124 |
|
14 |
|
2 |
1-Jan-11 |
25,417 |
5,540 |
50,542 |
|
15 |
|
3 |
1-Feb-11 |
25,714 |
5,244 |
76,256 |
|
16 |
|
4 |
1-Mar-11 |
26,014 |
4,944 |
1,02,269 |
|
17 |
|
5 |
1-Apr-11 |
26,317 |
4,640 |
1,28,587 |
|
18 |
|
6 |
1-May-11 |
26,624 |
4,333 |
1,55,211 |
|
19 |
|
7 |
1-Jun-11 |
26,935 |
4,023 |
1,82,146 |
|
20 |
|
8 |
1-Jul-11 |
27,249 |
3,708 |
2,09,396 |
|
21 |
|
9 |
1-Aug-11 |
27,567 |
3,390 |
2,36,963 |
|
22 |
|
10 |
1-Sep-11 |
27,889 |
3,069 |
2,64,852 |
|
23 |
|
11 |
1-Oct-11 |
28,214 |
2,743 |
2,93,066 |
|
24 |
|
12 |
1-Nov-11 |
28,543 |
2,414 |
3,21,609 |
|
25 |
|
13 |
1-Dec-11 |
28,876 |
2,081 |
3,50,486 |
|
26 |
|
14 |
1-Jan-12 |
29,213 |
1,744 |
3,79,699 |
|
27 |
|
15 |
1-Feb-12 |
29,554 |
1,404 |
4,09,253 |
|
28 |
|
16 |
1-Mar-12 |
29,899 |
1,059 |
4,39,152 |
|
29 |
|
17 |
1-Apr-12 |
30,248 |
710 |
4,69,399 |
|
30 |
|
18 |
1-May-12 |
30,601 |
357 |
5,00,000 |
|
Step 1: Enter the Loan amount in negative in a cell
Step 2 : Enter the Payment terms and rate of interest to be paid
Step 3: Use the PMT function to find the equal monthly installment "
Step 4: Create a table to find the every month principle with function. "=PPMT($C$8/12,$C13,$C$7,$C$6)"
Step 5: The interest component can be calculated by the formula "=$C$10-E13"
Step 6 : The remaining outstanding value of the loan value will be estimated by the formula =$C$2+E13
Step 7 : Copy and paste the value above in a sheet and paste it in a excel sheet to understand the functions.
Please post your comments to make it useful for our friends.