Question

In: Accounting

Solve using excel: A. Suppose you have taken out a $125,000 fully-amortizing fixed rate mortgage loan...

Solve using excel:

A. Suppose you have taken out a $125,000 fully-amortizing fixed rate mortgage loan that has a term of 15 years and an interest rate of 6%. After your first mortgage payment, how much of the original loan balance is remaining? 


B. Assume you have taken out a partially amortizing loan for $325,000 that has a term of 7 years, but amortizes over 30 years. Calculate the balloon payment at maturity (Year 7) if the interest rate on this loan is 4.5%. 


C. Let's assume that you have just taken out a mortgage loan for $200,000 with an origination fee of 2 points due upfront. The mortgage term is 30 years and the mortgage rate is fixed at 4%. What is the cost of the origination fee in dollar terms? 


Solutions

Expert Solution

A.

First we have to calculate yearly mortgage Payment for the Loan

The same can be done in excel using PMT formula which is PMT (rate, nper, PV). Where rate is interest rate. Nper is number of periods which is 15 and PV is 1,25,000

Pmt(6%,15,-1,25,000) = $12,870.35

The Repayment schedule is as follows

Repayment Schedule
Year OP Bal Interest EYI Clos Bal
1 $125,000 $7,500 $12,870.35 $119,629.65
2 $119,629.65 $7,178 $12,870.35 $113,937.09
3 $113,937.09 $6,836 $12,870.35 $107,902.97
4 $107,902.97 $6,474 $12,870.35 $101,506.80
5 $101,506.80 $6,090 $12,870.35 $94,726.86
6 $94,726.86 $5,684 $12,870.35 $87,540.13
7 $87,540.13 $5,252 $12,870.35 $79,922.19
8 $79,922.19 $4,795 $12,870.35 $71,847.18
9 $71,847.18 $4,311 $12,870.35 $63,287.66
10 $63,287.66 $3,797 $12,870.35 $54,214.58
11 $54,214.58 $3,253 $12,870.35 $44,597.11
12 $44,597.11 $2,676 $12,870.35 $34,402.59
13 $34,402.59 $2,064 $12,870.35 $23,596.40
14 $23,596.40 $1,416 $12,870.35 $12,141.84
15 $12,141.84 $729 $12,870.35 $0.00

As you can see after first mortgage payment $ 1,19,629 is the balance.

B. In similar way as given in question A Yearly payment is calculated

can be done in excel using PMT formula which is PMT (rate, nper, PV). Where rate is interest rate. Nper is number of periods which is 30 and PV is 3,25,000

Pmt(4.5%,30,-3,25,000) = $19,952

The repayment schedule is as follows

Repayment Schedule
Year opening balance Interest Installment Closing Balance
1 $325,000 $14,625 $19,952.25 $319,672.75
2 $319,672.75 $14,385 $19,952.25 $314,105.77
3 $314,105.77 $14,135 $19,952.25 $308,288.28
4 $308,288.28 $13,873 $19,952.25 $302,209.00
5 $302,209.00 $13,599 $19,952.25 $295,856.15
6 $295,856.15 $13,314 $19,952.25 $289,217.43
7 $289,217.43 $13,015 $19,952.25 $282,279.96
8 $282,279.96 $12,703 $19,952.25 $275,030.31
9 $275,030.31 $12,376 $19,952.25 $267,454.42
10 $267,454.42 $12,035 $19,952.25 $259,537.62
11 $259,537.62 $11,679 $19,952.25 $251,264.56
12 $251,264.56 $11,307 $19,952.25 $242,619.21
13 $242,619.21 $10,918 $19,952.25 $233,584.83
14 $233,584.83 $10,511 $19,952.25 $224,143.89
15 $224,143.89 $10,086 $19,952.25 $214,278.12
16 $214,278.12 $9,643 $19,952.25 $203,968.38
17 $203,968.38 $9,179 $19,952.25 $193,194.71
18 $193,194.71 $8,694 $19,952.25 $181,936.22
19 $181,936.22 $8,187 $19,952.25 $170,171.09
20 $170,171.09 $7,658 $19,952.25 $157,876.54
21 $157,876.54 $7,104 $19,952.25 $145,028.74
22 $145,028.74 $6,526 $19,952.25 $131,602.78
23 $131,602.78 $5,922 $19,952.25 $117,572.65
24 $117,572.65 $5,291 $19,952.25 $102,911.17
25 $102,911.17 $4,631 $19,952.25 $87,589.92
26 $87,589.92 $3,942 $19,952.25 $71,579.21
27 $71,579.21 $3,221 $19,952.25 $54,848.03
28 $54,848.03 $2,468 $19,952.25 $37,363.94
29 $37,363.94 $1,681 $19,952.25 $19,093.06
30 $19,093.06 $859 $19,952.25 ($0.00)

Since The Loan is Repayment at the end of term 7. The closing principal balance of 2,82,280 balloon payment is made to bank.

C. Here two points means two percent upfront fee is taken at the time of loan disbursal

same is calculated as $ 2,00,000 * 2% = $ 4,000


Related Solutions

Suppose you have taken out a $175,000 fully amortizing fixed rate mortgage loan that has a...
Suppose you have taken out a $175,000 fully amortizing fixed rate mortgage loan that has a term of 15 years and an interest rate of 5.25%. After your first mortgage payment, how much of the original loan balance is remaining? Can you teach me how to do it on excel, please? I am confused on how to make it monthly compounding.
Suppose you have taken out a $125,000 fully-amortizing fixed rate mortgage loan that has a term of 15 years and an interest rate of 6%.
Show work through excel formulas: please actually show how you do it   on excel exactly! 1. Suppose you have taken out a $125,000 fully-amortizing fixed rate mortgage loan that has a term of 15 years and an interest rate of 6%. After your first mortgage payment, how much of the original loan balance is remaining? 
 A. $1,054.82 B. $120,603.78 C. $124,570.18 D. $124,875.56 2. Assume you have taken out a partially amortizing loan for $325,000 that has a term of...
Solve using excel: A. You have taken out a $225,000, 3/1 ARM. The initial rate of...
Solve using excel: A. You have taken out a $225,000, 3/1 ARM. The initial rate of 5.8% (annual) is locked in for 3 years and is expected to increase to 6.5% at the end of the lock period. Calculate the initial payment on the loan. (Note: the term on this 3/1 ARM is 30 years) 
 B. Given the following information, calculate the Effective Borrowing Cost (EBC). Loan amount: $175,000, Term: 30 years, Interest rate: 7 %, Payment: $1,164.28, Discount points:...
SHOW CLEAR WORK ON EXCEL A fully amortizing mortgage loan is made for $100,000 at 6...
SHOW CLEAR WORK ON EXCEL A fully amortizing mortgage loan is made for $100,000 at 6 percent interest for 30 years. Determine payments for each of the periods a–d below if interest is accured: a. Monthly. b. Quarterly. c. Annually. d. Weekly.
A couple takes out a fully amortizing fixed rate thirty-year loan for $375,000. The couple makes...
A couple takes out a fully amortizing fixed rate thirty-year loan for $375,000. The couple makes monthly payments and the annual interest rate on the loan is 4.5%. The couple also pays 3 points in up- front fees to obtain the loan. (a) Calculate the loan’s APR [8 points] (b) Assuming a holding period of 12 years, calculate the loan’s EBC
A couple takes out a fully amortizing fixed rate thirty-year loan for $375,000. The couple makes...
A couple takes out a fully amortizing fixed rate thirty-year loan for $375,000. The couple makes monthly payments and the annual interest rate on the loan is 4.5%. The couple also pays 3 points in up- front fees to obtain the loan. (a) Calculate the loan’s APR [8 points] (b) Assuming a holding period of 12 years, calculate the loan’s EBC
A fully amortizing mortgage loan is made for $90,000 for 15 years. The interest rate is...
A fully amortizing mortgage loan is made for $90,000 for 15 years. The interest rate is 6 percent per year compounding monthly. Payments are to be made monthly. What is the principal payment in the first monthly payment?
You qualify for an $800,000 fully amortizing 30-year fixed rate mortgage with monthly payments. If the...
You qualify for an $800,000 fully amortizing 30-year fixed rate mortgage with monthly payments. If the annual interest rate is 3.63%, compounded monthly, what will the monthly mortgage payment be?
The principal component of the payment on a fully-amortizing fixed-rate mortgage is increasing at an increasing...
The principal component of the payment on a fully-amortizing fixed-rate mortgage is increasing at an increasing rate over the term of the loan. Group of answer choices True False
Mortgage Pricing A 30Y fixed rate mortgage is issued at 6% coupon rate. The loan fully...
Mortgage Pricing A 30Y fixed rate mortgage is issued at 6% coupon rate. The loan fully amortizes over 30 year period. Expected payoff time is 8 Years when initially issued. Assuming $1M in loan balance. a) Price the loan today at 5%, 6%, and 7% market yield, assuming loan termination term stays constant with interest rate (96 months at 5%; 96 months at 6%, and 96 months @ 7% b)calculate numerical duration and convexity at 6% market interest rate based...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT