Question

A loan of $100,000 is made today. The borrower will make equal repayments of $3800.75 per month with the first payment being exactly one month from today. The interest being charged on this loan is constant (but unknown). For the following two scenarios, calculate the interest rate being charged on this loan, expressed as a nominal annual rate in percentage: (a) The loan is fully repaid exactly after 31 monthly repayments, i.e., the loan outstanding immediately after 31 repayments is exactly 0.

A loan of $100,000 is made today. The borrower will make equal repayments of $3800.75 per month with the first payment being exactly one month from today. The interest being charged on this loan is constant (but unknown). For the following two scenarios, calculate the interest rate being charged on this loan, expressed as a nominal annual rate in percentage: (b) The term of the loan is unknown but it is known that the loan outstanding 2 years later equals to $26281.16.

Answer 1

The answers are:

Scenario 1	12.70%
Scenario 2	13.20%

Calculations:

Scenario 1: Here monthly payment (pmt) = 3800.75, nper = 31 and PV = 100,000

In excel use the "rate" formula. The syntax will be: Rate(31, -3800.75, 100000). This will give a result of 1.05834%. Since the rate is in monthly terms the annual % = 1.05834% *12 = 12.70%

Thus the nominal annual rate = 12.70%

Scenario 2: Here monthly payment (pmt) = 3800.75, PV = 100,000 and nper is unknown. The loan balance after 2 years i.e. 24 months = 26281.16. To find out let us take the annual nominal interest rate be "r"%. Let us make a loan amortization table. Once the table is set let is use "solver" function in excel and set the cell for loan outstanding at the end of 2 years to 26281.16 by changing the cell for "r". The excel's solver function gives us a rate of 13.2%.

Thus nominal annual rate is 13.20%

Loan amortization table:

Month	Loan due at start of month	Payment	Interest	Principal paid	Loan due at end of month
1	100,000.00	3,800.75	1,100.00	2,700.75	97,299.25
2	97,299.25	3,800.75	1,070.29	2,730.46	94,568.79
3	94,568.79	3,800.75	1,040.26	2,760.49	91,808.30
4	91,808.30	3,800.75	1,009.89	2,790.86	89,017.44
5	89,017.44	3,800.75	979.19	2,821.56	86,195.88
6	86,195.88	3,800.75	948.15	2,852.60	83,343.28
7	83,343.28	3,800.75	916.78	2,883.97	80,459.31
8	80,459.31	3,800.75	885.05	2,915.70	77,543.61
9	77,543.61	3,800.75	852.98	2,947.77	74,595.84
10	74,595.84	3,800.75	820.55	2,980.20	71,615.65
11	71,615.65	3,800.75	787.77	3,012.98	68,602.67
12	68,602.67	3,800.75	754.63	3,046.12	65,556.55
13	65,556.55	3,800.75	721.12	3,079.63	62,476.92
14	62,476.92	3,800.75	687.25	3,113.50	59,363.42
15	59,363.42	3,800.75	653.00	3,147.75	56,215.66
16	56,215.66	3,800.75	618.37	3,182.38	53,033.29
17	53,033.29	3,800.75	583.37	3,217.38	49,815.90
18	49,815.90	3,800.75	547.97	3,252.78	46,563.13
19	46,563.13	3,800.75	512.19	3,288.56	43,274.57
20	43,274.57	3,800.75	476.02	3,324.73	39,949.84
21	39,949.84	3,800.75	439.45	3,361.30	36,588.54
22	36,588.54	3,800.75	402.47	3,398.28	33,190.26
23	33,190.26	3,800.75	365.09	3,435.66	29,754.61
24	29,754.61	3,800.75	327.30	3,473.45	26,281.16