Question

Amortization X. Determine the monthly payment to amortize a $250,000 debt at 3% for 25 years. Discuss quantitatively how the term would change if an extra payment is made each year? P=D⋅(r/m)/(1-(1+r/m)^(-mt) ) P=? D=$250,000 R=3%=.03 M=12 T=25 P=D⋅(r/m)/(1-(1+r/m)^(-mt) ) P=250,000⋅(.03/12)/(1-(1+.03/12)^(-(12)(25)) ) P=200,000⋅.0025/(1-(1.0025)^(-(300)) ) P=200,000⋅.0025/(1-.47281) P=200,000⋅.0025/.52719 P=200,000(.00474212) P=$948.42

How do I explain an extra payment a year? Please show all work.

Answer 1

Solution 1: Calculation of Monthly payment to amortize a$250,000 debt at 3% for 25 years:

Following are the steps to be followed on Microsoft Excel:

Step 1: Click on "FORMULAS" tab at the top of Microsoft Excel
Step 2: Select the option "Financial"
Step 3: Under "Financial" select the option "PMT"
Step 4: Insert Rate = 0.03/12 Nper =25*12 PV = -250000 Type = 0

PMT = $1185.53

Solution 2: Reduction in the term if extra payment is made in a year can be explained as follows:

From the below first year repayment schedule, we see that total installment paid is $14,226.34 and there is a Principal repayment of $6,819.60. Hence, the closing balance of the loan at the end of first year is $243,180.40.

Period	Opening Balance	Monthly Instalment	Principal Repayment	Interest Repayment	Closing Balance
1	250000	1,185.53	$560.53	$625.00	$249,439.47
2	$249,439.47	1,185.53	$561.93	$623.60	$248,877.54
3	$248,877.54	1,185.53	$563.33	$622.19	$248,314.21
4	$248,314.21	1,185.53	$564.74	$620.79	$247,749.46
5	$247,749.46	1,185.53	$566.15	$619.37	$247,183.31
6	$247,183.31	1,185.53	$567.57	$617.96	$246,615.74
7	$246,615.74	1,185.53	$568.99	$616.54	$246,046.75
8	$246,046.75	1,185.53	$570.41	$615.12	$245,476.34
9	$245,476.34	1,185.53	$571.84	$613.69	$244,904.50
10	$244,904.50	1,185.53	$573.27	$612.26	$244,331.24
11	$244,331.24	1,185.53	$574.70	$610.83	$243,756.54
12	$243,756.54	1,185.53	$576.14	$609.39	$243,180.40
Total Repayment		$14,226.34	$6,819.60	$7,406.74

Suppose there is an extra payment of $5000 at the end of first year, let us calculate the new term.

Following are the steps to be followed on Microsoft Excel:

Step 1: Click on "FORMULAS" tab at the top of Microsoft Excel
Step 2: Select the option "Financial"
Step 3: Under "Financial" select the option "NPER"
Step 4: Insert Rate = 0.03/12 PMT = -1185.53 PV = 243180.40-5000 Type = 0

NPER = 279.42

Hence, we see that as there is an extra payment of $5000 at the end of first year, the term has been reduced from 288 months to 279.42 months.