Question

A) How do you solve for loan amounts and fixed payments for fully-amortized loans?

B) How do you construct a loan amortization schedule?

C) How do the various components of the loan amortization schedule change as the term of the loan progresses?

EXTRA: EXCEL:How do you use Excel functions to compute the FVA, PVA and PMT?

Answer 1

Ans A) we can solve it using excel funtion PMT (rate, nper, pv, fv)

where rate is interest rate

nper is number of period

PV is present value

FV is future value

eg:

rate	0.42%
nper	240
PV	284000
FV	0		PMT	$1,874.27

Ans b) We can also construct the loan amortization schedule in excel which is given as below for 24 month

Month	Opening Balance	Payment	Interest	Principal Payment	Closing Balance
1	$        284,000.00	$ 1,874.27	$ 1,183.33	$                 690.94	$     283,309.06
2	$        283,309.06	$ 1,874.27	$ 1,180.45	$                 693.82	$     282,615.24
3	$        282,615.24	$ 1,874.27	$ 1,177.56	$                 696.71	$     281,918.53
4	$        281,918.53	$ 1,874.27	$ 1,174.66	$                 699.61	$     281,218.91
5	$        281,218.91	$ 1,874.27	$ 1,171.75	$                 702.53	$     280,516.39
6	$        280,516.39	$ 1,874.27	$ 1,168.82	$                 705.46	$     279,810.93
7	$        279,810.93	$ 1,874.27	$ 1,165.88	$                 708.40	$     279,102.53
8	$        279,102.53	$ 1,874.27	$ 1,162.93	$                 711.35	$     278,391.19
9	$        278,391.19	$ 1,874.27	$ 1,159.96	$                 714.31	$     277,676.88
10	$        277,676.88	$ 1,874.27	$ 1,156.99	$                 717.29	$     276,959.59
11	$        276,959.59	$ 1,874.27	$ 1,154.00	$                 720.28	$     276,239.31
12	$        276,239.31	$ 1,874.27	$ 1,151.00	$                 723.28	$     275,516.04
13	$        275,516.04	$ 1,874.27	$ 1,147.98	$                 726.29	$     274,789.74
14	$        274,789.74	$ 1,874.27	$ 1,144.96	$                 729.32	$     274,060.43
15	$        274,060.43	$ 1,874.27	$ 1,141.92	$                 732.36	$     273,328.07
16	$        273,328.07	$ 1,874.27	$ 1,138.87	$                 735.41	$     272,592.66
17	$        272,592.66	$ 1,874.27	$ 1,135.80	$                 738.47	$     271,854.19
18	$        271,854.19	$ 1,874.27	$ 1,132.73	$                 741.55	$     271,112.64
19	$        271,112.64	$ 1,874.27	$ 1,129.64	$                 744.64	$     270,368.01
20	$        270,368.01	$ 1,874.27	$ 1,126.53	$                 747.74	$     269,620.27
21	$        269,620.27	$ 1,874.27	$ 1,123.42	$                 750.86	$     268,869.41
22	$        268,869.41	$ 1,874.27	$ 1,120.29	$                 753.99	$     268,115.42
23	$        268,115.42	$ 1,874.27	$ 1,117.15	$                 757.13	$     267,358.30
24	$        267,358.30	$ 1,874.27	$ 1,113.99	$                 760.28	$     266,598.02

Here we start with the opening balance which is same as Present value for period 1.

then we have fixed payment

then we have interest payment which is equal to opening balance * interest rate

then we have principal payment which is equal to payment - intereest payment

then finally we have ending balance which is beginning balance - principal payment.

Ans C) Over the term of the loan progress principal payment increases and interest payment decreases. Which lead to rapid reduction in loan ending balance as loan progresses.