Question

Build a short-term loan model, allowing terms out to 5 years. Take the term, annual interest rate, and principal balance as inputs. Either calculate the required payment or use a payment input cell. Use your model to answer the following questions:

- with a term of 5 years and a rate of 5%, what should payments be for a $10,000 loan

- how many months will it take to pay off the loan if payments are $1,000

- if all payments but the last payment are $1,000, how much should be paid off in the last month to leave $0 due?

Add a discount factor to your model. Use another input for the appropriate opportunity cost of capital.

- what is the PV of the payments if the discount rate is 5%

- what is the loan's NPV to the customer if the discount rate is 4%? 6%?

Answer 1

i) Loan amount: 10000

Interest rate: 5%

Number of years: 5

Equal monthly installament (EMI) :

EMI = (P X R/12) X [(1+R/12) ^N] / {[(1+R/12) ^N]-1}

P is the original loan amount or principal, R is the rate of interest that is applicable per annum and N is the number of monthly installments/ loan tenure.

EMI = (10000 X 0.05/12) X [ (1+0.05/12)^60] / {[(1+0.05/12)^60]-1}

= 41.667 X 1.2834 / 0.2834

= 188.71

Per annum the payments will be = 188.71*12 = 2264.5

For 5 years the payments will be = 2264.5 * 5 = 11,322.52

Interest for the 5 years = 11,322.52-10,000 = 1,322.52

II) If the payments are $1000, it will be 11 months to clear the loan of $10000 alongwith interest @ 5 %. The workings will be as follows:-

Interest = Loan outstanding * Rate of Interest /12

Months	EMI	Interest	Principal	Outstanding
1	1000	41.67	958.33	9,041.67
2	1000	37.67	962.33	8,079.34
3	1000	33.66	966.34	7,113.00
4	1000	29.64	970.36	6,142.64
5	1000	25.59	974.41	5,168.24
6	1000	21.53	978.47	4,189.77
7	1000	17.46	982.54	3,207.23
8	1000	13.36	986.64	2,220.59
9	1000	9.25	990.75	1,229.84
10	1000	5.12	994.88	234.97
11	235.95	0.98	234.97	0.00

III ) In the last installement, the amount of $ 235.95 to be paid to leave $ 0 due.

IV) PV of the payments if the discount rate is 5%

PV Factor formulae = 1/(1+R)^n

R = Discount rate n= no.of years

Year	payment outflow	PV Factor @ 5%	Present Value
1	2264.50	0.95238	2156.67
2	2264.50	0.90703	2053.97
3	2264.50	0.86384	1956.16
4	2264.50	0.82270	1863.01
5	2264.50	0.78353	1774.30
	Total	9804.12

V) Loan's NPV to the customer if the discount rate is 4%.

Outflows are shown in "- "value

Year	Cash flows	PV Factor @ 5%	Present Value
0	10,000.00	1	10,000.00
1	-2,264.50	0.9615	-2,177.41
2	-2,264.50	0.9246	-2,093.66
3	-2,264.50	0.8890	-2,013.14
4	-2,264.50	0.8548	-1,935.71
5	-2,264.50	0.8219	-1,861.26
	NPV	-81.17

Loan's NPV to the customer if the discount rate is 6%.

Year	Cash flow	PV Factor @ 5%	Present Value
0	10000	1	10000
1	-2264.50	0.9434	-2136.32
2	-2264.50	0.8900	-2015.40
3	-2264.50	0.8396	-1901.32
4	-2264.50	0.7921	-1793.70
5	-2264.50	0.7473	-1692.17
	NPV	461.09