Question

1.                 You have just purchased a new house and taken a mortgage for $100,000. The interest rate is 12% compounded monthly and you will make payments for 25 years.

a)     Find the size of the monthly payment.

b)     The bank has a policy of rounding the payments up to the next cent. Find the new monthly payment and compute a new n.

c)     What was the balance of the loan after three periods?

d)     How much of your third payment was Principal?         Interest?

e)     How much did you pay in the first year?

f)      How much of that was principal?

g)     How much of that was interest?

h)     What was the balance of the loan at the end of the first year?

i)       How much interest did you pay in the third year?

j)       Assuming the mortgage stays at the same rate until maturity find the size of your last payment.

2.                 You purchase a new 4-wheel drive vehicle for $35,000. The dealer lets you put $5,000 down and finance the rest of the purchase over 5 years with an interest rate of 9% compounded monthly.

a)     Find the size of your monthly payments. Round up to the next cent.

b)     How much of your sixth payment was interest? How much was principal.

c)     After 3 years how much have you paid the dealer? How much of that was interest? How much do you owe?

d)     How much interest do you pay in the second year?

e)     Find the size of your final payment.

3.                 Banks will apply the 30% rule, no more than 30% of your gross income maybe used for your mortgage payment. Your gross income is $3,333.33. You are looking for a 5 year term with a 25 year amortization. If banks are charging j₂ = 6%:

a)     Find the maximum size of the payment, the maximum mortgage. ($1000,$156,297)

b)     How much would you owe at the end of the five year term?($140,412)

c)     After five years the interest rate increases to j2 = 8%, find the size of the new payment (assume the same amortization period)($1163.12)

d)     Five years later the interest rate stays the same but you can afford to pay an extra $12,000 on your mortgage. Assuming you will amortize the balance over the remaining 15 years, what is your payment?($1049.34)

                                        i.                       Instead of reducing your payment you would like to keep the payment the same as in part c and reduce the number payments you have left to make. How much time will you save? (30 Payments or about 2.5 years)

4.                 Your gross income is $5,000 per month. How large a mortgage can you afford if the rate is J₂ = 8% and you are looking for a 3 year term and a 20 year amortization.

a)  Find the size of your maximum mortgage.($181,081)

b)  After 3 years you renew your mortgage at the same rate but are able to make a $10,000 payment.

                                     i.                          Find the size of the new monthly payment. Assume you will amortize the remaining balance over 17 years. ($1,410.95)

                                    ii.                          Instead of reducing your payment you would like to pay your mortgage off faster and keep your payment the same. How much time will you save assuming the interest rate stays the same?(23 full and one partial payment - save two years)

Answer 1

a)
Principal	$        100,000.00
Rate = 12%/12	1.00%
nper = 25 x 12	300
Monthly payment (PMT) (PV(1%,300,-100000)	$1,053.22
b)
Principal	$        100,000.00
Rate = 12%/12	1.00%
PMT	1054
N = Nper (1%,1054,-1000000)	298.6	months
c)
Balance after 3rd period	$99,838.73
d)
Principal	$54.29
Interest	$               998.93
e)
Total Payment in first year	$          12,638.69
f) Principal	$675.02
g) Interest	$          11,963.67
h) Loan Balance	$          99,324.98
i) Interest in the third year	$          11,781.60
j) Last Payment	$1,053.22

Interest = 1% x previous Ending Bal Months Payments Principal 12 a) 13 Principal 14 Rate = 12%/12 15 nper = 25 x 12 16 Monthly payment (PMT) (PV(1%,300,-100000) 100000 =12%/12 =25.12 =PMT(B14,815,-B13) 17 18 b) 19 Principal 20 Rate = 12%/12 21 PMT 22 N = Nper (1%,1054,-1000000) 100000 =12%/12 1054 =NPER(B20,121,-B19) months | 24 c) 25 Balance after 3rd period 26 =J16 28 Principal 29 Interest =116 =H16 =$B$16 =$B$16 =$B$16 =$B$16 =$B$16 =$B$16 =$B$16 =$B$16 =$B$16 =$B$16 =$B$16 =$B$16 =$B$16 =$B$16 =$B$16 =$B$16 =$B$16 =$B$16 =$B$16 =$B$16 =$B$16 =$B$16 =$B$16 =$B$16 =$B$16 =$B$16 =$B$16 =$B$16 =$B$16 =$B$16 =$B$16 =$B$16 =$B$16 =$B$16 =$B$16 =$B$16 e) Total Payment in first year =J131% =J141% =J151% =J16.1% =J171% =J18 1% =J191% =J20*1% =J21.1% =J22.1% =J23*1% =J24*1% =J25*1% =J26.1% =J27*1% =J28*1% =J29.1% =J30*1% = 31.1% = 32.1% =133*1% = 134 1% = 35.1% = 36.1% = 137*1% =J381% =39*1% =J40*1% =J4101% =J42.1% =143*1% =J441% =J45*1% =146*1% = 347*1% =J481% Ending Bal. 100000 =J13-114 =J14-115 =J15-116 =J16-117 =J17-118 =J18-119 = J19-120 =J20-121 =J21-122 =J22-123 =J23-124 =J24-125 =J25-126 =J26-127 =J27-128 =J28-129 =J29-130 =J30-131 = J31-132 =J32-133 =J33-134 =134-135 = 35-136 =J36-137 = J37-138 =J38-139 =J39-140 =J40-141 =141-142 =J42-143 =143-144 =J44-145 =J45-146 = 146-147 =J47-148 =J48-149 =G14-H14 =G15-H15 =G16-H16 =G17-H17 =G18-H18 =G19-H19 =G20-H20 =G21-H21 =G22-H22 =G23-H23 =G24-H24 =G25-H25 =G26-H26 =G27-H27 =G28-H28 =G29-H29 =G30-H30 =G31-H31 =G32-H32 =G33-H33 =G34-H34 =G35-H35 =G36-H36 =G37-H37 =G38-H38 =G39-H39 =G40-H40 =G41-441 =G42-H42 =G43-H43 =G44-H44 =G45-H45 =G46-H46 =G47-H47 =G48-H48 =G49-H49 =SUM(G14:25) 34 f) Principal 35 g) Interest 36 h) Loan Balance 37 i) Interest in the third year 38 ) Last Payment =SUM(114:125) =SUM(H14:H25) =J25 =SUM(H38:H49) =B16