Question

Create a loan amortization schedule in Excell for a $275,000 mortgage that will be repaid over 20 years with monthlypayments.  The annual interest rate is 5.5 %.

What is your monthly payment?  $

What is the total dollar amount of payments made over the life of this loan? $__

What is the total dollar amount of interest paid over the life of this loan? $_

How many months will it take to pay off the loan if you pay an extra $100 per

month? ____

Answer 1

Ans 1) Monthly payment is given by PV of annuity formula

PV of annuity = paymnet * (1 - (1+r/12)^- 12*n)/(r/12)

275000 = payment * (1 - 1.0046^-240)/.0046

payment = $1891.69

total dollar amount of payments made over the life of this loan = 240 * $1891.69

= $454005.6

total dollar amount of interest paid over the life of this loan = total payment - principal

= $454005.6 - $275000

= $179005.6

Again we will use the PV of anuity formula

275000 = 1991.69 * (1 - (1.0046)^-n)/.0046

n = 219.1 Months

Loan Amortization schdule is given below but due to size issue it can't be be for 240 months.

rate	0.46%
nper	240
PV	275000
FV	0		PMT	$1,891.69
	Month	Opening Balance	Payment	Interest	Principal Payment	Closing Balance
	1	$        275,000.00	$ 1,891.69	$   1,260.42	$                 631.27	$     274,368.73
	2	$        274,368.73	$ 1,891.69	$   1,257.52	$                 634.17	$     273,734.56
	3	$        273,734.56	$ 1,891.69	$   1,254.62	$                 637.07	$     273,097.49
	4	$        273,097.49	$ 1,891.69	$   1,251.70	$                 639.99	$     272,457.49
	5	$        272,457.49	$ 1,891.69	$   1,248.76	$                 642.93	$     271,814.57
	6	$        271,814.57	$ 1,891.69	$   1,245.82	$                 645.87	$     271,168.69
	7	$        271,168.69	$ 1,891.69	$   1,242.86	$                 648.83	$     270,519.86
	8	$        270,519.86	$ 1,891.69	$   1,239.88	$                 651.81	$     269,868.05
	9	$        269,868.05	$ 1,891.69	$   1,236.90	$                 654.79	$     269,213.26
	10	$        269,213.26	$ 1,891.69	$   1,233.89	$                 657.80	$     268,555.46
	11	$        268,555.46	$ 1,891.69	$   1,230.88	$                 660.81	$     267,894.65
	12	$        267,894.65	$ 1,891.69	$   1,227.85	$                 663.84	$     267,230.81
	13	$        267,230.81	$ 1,891.69	$   1,224.81	$                 666.88	$     266,563.93
	14	$        266,563.93	$ 1,891.69	$   1,221.75	$                 669.94	$     265,893.99
	15	$        265,893.99	$ 1,891.69	$   1,218.68	$                 673.01	$     265,220.98
	16	$        265,220.98	$ 1,891.69	$   1,215.60	$                 676.09	$     264,544.89
	17	$        264,544.89	$ 1,891.69	$   1,212.50	$                 679.19	$     263,865.70
	18	$        263,865.70	$ 1,891.69	$   1,209.38	$                 682.31	$     263,183.39
	19	$        263,183.39	$ 1,891.69	$   1,206.26	$                 685.43	$     262,497.96
	20	$        262,497.96	$ 1,891.69	$   1,203.12	$                 688.57	$     261,809.38
	21	$        261,809.38	$ 1,891.69	$   1,199.96	$                 691.73	$     261,117.65
	22	$        261,117.65	$ 1,891.69	$   1,196.79	$                 694.90	$     260,422.75
	23	$        260,422.75	$ 1,891.69	$   1,193.60	$                 698.09	$     259,724.67
	24	$        259,724.67	$ 1,891.69	$   1,190.40	$                 701.29	$     259,023.38