Question

The compounding frequency on a loan is once every year. If you borrow $35,974.44 at an annual interest rate of 3.75%, how much must you pay every year so that you pay back the loan in 17 years? How do I do the steps in Excel? using PEMDAS

Please show steps

IF YOU GOT 2900. HOW DID YOU GET THAT ?

Answer 1

We are given the following information:

r	3.75%
n	17
frequency	1(annual payments)
PV	$       35,974.44

We need to solve the following equation to arrive at the required payment or PMT:

So the annual payment is 2900

We can use the excel formula =pmt(0.0375,17,35974.4,0) to get the same answer. The answer will be negative because if we take a loan the payment of the same will be an outflow

The amortization schedule is as follows:

Year	Opening Balance	PMT	Interest	Principal repayment	Closing Balance
1	$            35,974.44	$         2,900.00	$         1,349.04	$                     1,550.96	$         34,423.48
2	$            34,423.48	$         2,900.00	$         1,290.88	$                     1,609.12	$         32,814.36
3	$            32,814.36	$         2,900.00	$         1,230.54	$                     1,669.46	$         31,144.90
4	$            31,144.90	$         2,900.00	$         1,167.93	$                     1,732.07	$         29,412.83
5	$            29,412.83	$         2,900.00	$         1,102.98	$                     1,797.02	$         27,615.81
6	$            27,615.81	$         2,900.00	$         1,035.59	$                     1,864.41	$         25,751.41
7	$            25,751.41	$         2,900.00	$             965.68	$                     1,934.32	$         23,817.08
8	$            23,817.08	$         2,900.00	$             893.14	$                     2,006.86	$         21,810.23
9	$            21,810.23	$         2,900.00	$             817.88	$                     2,082.12	$         19,728.11
10	$            19,728.11	$         2,900.00	$             739.80	$                     2,160.20	$         17,567.91
11	$            17,567.91	$         2,900.00	$             658.80	$                     2,241.20	$         15,326.71
12	$            15,326.71	$         2,900.00	$             574.75	$                     2,325.25	$         13,001.46
13	$            13,001.46	$         2,900.00	$             487.55	$                     2,412.45	$         10,589.01
14	$            10,589.01	$         2,900.00	$             397.09	$                     2,502.91	$           8,086.10
15	$              8,086.10	$         2,900.00	$             303.23	$                     2,596.77	$           5,489.33
16	$              5,489.33	$         2,900.00	$             205.85	$                     2,694.15	$           2,795.18
17	$              2,795.18	$         2,900.00	$             104.82	$                     2,795.18	$                    0.00
		$       49,300.00	$       13,325.56	$                   35,974.44

Opening balance = previous year's closing balance
Closing balance = Opening balance+Loan-Principal repayment
PMT is calculated as per the above formula
Interest = 0.0375 /12 x opening balance
Principal repayment = PMT - Interest
PMT = principal + interest so the graphical representation of proportion of both in each payment is as follows: