Question

I would like help with using the excel PMT function to answer this question. I know how to find monthly/total payments but I do not know how to find "amount paid towards principle after 29 months with 6.7% nominal interest and 360standard month payment schedule"

I would like to know the excel steps thank you

Answer 1

The PMT function gives you monthly payments and this consists of both interest as well as principal.

To determine the amount that has been paid towards principle after 29 months with 6.7% nominal interest and 360 standard month payment schedule you will have to make an amortization table.

Suppose that your loan amount is $20,000. Thus monthly payments = PMT (6.7%/12, 360, 20000) = $129.06. This includes both principal as well as interest. You will have to segregate the two using a loan amortization table as shown below:

Month	Loan balance at the start of the month	Monthly payment	Interest	Principal	Loan balance at the end of the month
1	20,000.00	129.06	111.67	17.39	19,982.61
2	19,982.61	129.06	111.57	17.49	19,965.13
3	19,965.13	129.06	111.47	17.58	19,947.54
4	19,947.54	129.06	111.37	17.68	19,929.86
5	19,929.86	129.06	111.28	17.78	19,912.08
6	19,912.08	129.06	111.18	17.88	19,894.20
7	19,894.20	129.06	111.08	17.98	19,876.22
8	19,876.22	129.06	110.98	18.08	19,858.14
9	19,858.14	129.06	110.87	18.18	19,839.96
10	19,839.96	129.06	110.77	18.28	19,821.68
11	19,821.68	129.06	110.67	18.38	19,803.29
12	19,803.29	129.06	110.57	18.49	19,784.80
13	19,784.80	129.06	110.47	18.59	19,766.21
14	19,766.21	129.06	110.36	18.69	19,747.52
15	19,747.52	129.06	110.26	18.80	19,728.72
16	19,728.72	129.06	110.15	18.90	19,709.82
17	19,709.82	129.06	110.05	19.01	19,690.81
18	19,690.81	129.06	109.94	19.12	19,671.69
19	19,671.69	129.06	109.83	19.22	19,652.47
20	19,652.47	129.06	109.73	19.33	19,633.14
21	19,633.14	129.06	109.62	19.44	19,613.70
22	19,613.70	129.06	109.51	19.55	19,594.16
23	19,594.16	129.06	109.40	19.65	19,574.50
24	19,574.50	129.06	109.29	19.76	19,554.74
25	19,554.74	129.06	109.18	19.87	19,534.86
26	19,534.86	129.06	109.07	19.99	19,514.88
27	19,514.88	129.06	108.96	20.10	19,494.78
28	19,494.78	129.06	108.85	20.21	19,474.57
29	19,474.57	129.06	108.73	20.32	19,454.25
30	19,454.25	129.06	108.62	20.44	19,433.81

Thus the principal amount in month 29 is $20.32 and in month 30 is $20.44

The formulas used can be seen in the image below:

XI - q quant 2020 21 - Microsoft Excel х File Home Insert Page Layout Formulas Data Review View OP X Σ AutoSum και Calibri 11 Α Α΄ Wrap Text General HILA ... Fill % Cut E Copy Paste Format Painter Clipboard BIU А Merge & Center % +.0 .00 .00 0 Insert Delete Format Conditional Format Cell Formatting as Table Styles Styles 2 Clear Sort & Find & Filter - Select Editing Font Alignment Number Cells АЗ fic Month v A B C D E F G H --- 3 Month 4 1 Principal =C4-D4 =C5-D5 =C6-D6 Loan balance at the end of the month =B4-E4 =B5-65 =B6-E6 5 =A4+1 6 =A5+1 7 =A6+1 =B7-E7 =C7-D7 =C8-08 8 =A7+1 =B8-E8 9 =A8+1 =C9-09 10 =A9+1 =C10-D10 =C11-D11 =B9-E9 =B10-E10 =B11-E11 11 =A10+1 12 =A11+1 =C12-D12 =B12-E12 13 =A12+1 Loan balance at the start of the month Monthly payment Interest 20000 =-A1 =6.7%/12*B4 =F4 =C4 =6.7%/12*85 =F5 =C5 =6.7%/12*B6 =F6 =C6 =6.7%/12*B7 =F7 =C7 =6.7%/12*B8 =F8 =C8 =6.7%/12*B9 =F9 =C9 =6.7%/12*B10 =F10 =C10 =6.7%/12*B11 =F11 =C11 =6.7%/12*B12 =F12 =C12 =6.7%/12*B13 =F13 =C13 =6.7%/12*B14 =F14 =C14 =6.7%/12*B15 =F15 =C15 |=6.7%/12*B16 =F16 =C16 =6.7%/12*B17 =F17 =C17 =6.7%/12*B18 =F18 1=C18 =6.7%/12*B19 =F19 =C19 =6.7%/12*B20 =F20 =C20 =6.7%/12*B21 =F21 1=C21 =6.7%/12*822 =F22 =C22 =6.7%/12*B23 =F23 =C23 |=6.7%/12*B24 May June July Aug Sheet1 Sheet2 =C13-D13 =B13-E13 14 =A13+1 =C14-D14 =B14-E14 =C15-D15 =C16-D16 15 =A14+1 16 =A15+1 17 =A16+1 18 =A17+1 19 =A18+1 20 =A19+1 =B15-E15 =B16-E16 =B17-E17 =B18-E18 =C17-D17 =C18-D18 =C19-019 21 =A20+1 22 =A21+1 =C20-D20 =C21-D21 =C22-D22 =C23-D23 =B19-E19 =B20-E20 =B21-E21 =B22-E22 =B23-E23 =B24-E24 23 =A22+1 =C24-D24 24 =A23+1 K April Ready El 100% + e x W EN 12:18 30/07/2020