In: Accounting
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?
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