Question

In: Finance

Loan Term: 5 Years Amortization Period: 5 years Payment Terms: Monthly at the end of the...

Loan Term: 5 Years

Amortization Period: 5 years

Payment Terms: Monthly at the end of the month

Initial Principal Amount: $12,000,000

Interest Rate: 5.75% annually

Develop an Amortization schedule for this particular loan. The schedule must show all payments associated with the loan. At the end of the fifth year of the loan, the loan balance should be zero.

Solutions

Expert Solution

Using excel formula to calculate amortization schedule:

Years Beginning Balance= 12000000 PMT =PMT(5.75%/12,60,-12000000) Interest part of PMT=5.75%/12*Beginning Principal Principal part of PMT= PMT-Interest Ending Balance= Beginning balance-Principal part of PMT
1 $                             12,000,000.00 230601.22 57500.00 173101.22 11826898.78
2 11826898.78 230601.22 56670.56 173930.66 11652968.12
3 11652968.12 230601.22 55837.14 174764.08 11478204.04
4 11478204.04 230601.22 54999.73 175601.49 11302602.55
5 11302602.55 230601.22 54158.30 176442.91 11126159.63
6 11126159.63 230601.22 53312.85 177288.37 10948871.26
7 10948871.26 230601.22 52463.34 178137.88 10770733.39
8 10770733.39 230601.22 51609.76 178991.45 10591741.93
9 10591741.93 230601.22 50752.10 179849.12 10411892.81
10 10411892.81 230601.22 49890.32 180710.90 10231181.91
11 10231181.91 230601.22 49024.41 181576.81 10049605.11
12 10049605.11 230601.22 48154.36 182446.86 9867158.25
13 9867158.25 230601.22 47280.13 183321.09 9683837.16
14 9683837.16 230601.22 46401.72 184199.50 9499637.66
15 9499637.66 230601.22 45519.10 185082.12 9314555.54
16 9314555.54 230601.22 44632.25 185968.97 9128586.57
17 9128586.57 230601.22 43741.14 186860.07 8941726.49
18 8941726.49 230601.22 42845.77 187755.45 8753971.05
19 8753971.05 230601.22 41946.11 188655.11 8565315.94
20 8565315.94 230601.22 41042.14 189559.08 8375756.86
21 8375756.86 230601.22 40133.83 190467.38 8185289.48
22 8185289.48 230601.22 39221.18 191380.04 7993909.44
23 7993909.44 230601.22 38304.15 192297.07 7801612.37
24 7801612.37 230601.22 37382.73 193218.49 7608393.88
25 7608393.88 230601.22 36456.89 194144.33 7414249.54
26 7414249.54 230601.22 35526.61 195074.61 7219174.94
27 7219174.94 230601.22 34591.88 196009.34 7023165.60
28 7023165.60 230601.22 33652.67 196948.55 6826217.05
29 6826217.05 230601.22 32708.96 197892.26 6628324.79
30 6628324.79 230601.22 31760.72 198840.50 6429484.29
31 6429484.29 230601.22 30807.95 199793.27 6229691.02
32 6229691.02 230601.22 29850.60 200750.62 6028940.40
33 6028940.40 230601.22 28888.67 201712.55 5827227.86
34 5827227.86 230601.22 27922.13 202679.09 5624548.77
35 5624548.77 230601.22 26950.96 203650.26 5420898.52
36 5420898.52 230601.22 25975.14 204626.08 5216272.44
37 5216272.44 230601.22 24994.64 205606.58 5010665.86
38 5010665.86 230601.22 24009.44 206591.78 4804074.08
39 4804074.08 230601.22 23019.52 207581.70 4596492.38
40 4596492.38 230601.22 22024.86 208576.36 4387916.02
41 4387916.02 230601.22 21025.43 209575.79 4178340.24
42 4178340.24 230601.22 20021.21 210580.00 3967760.23
43 3967760.23 230601.22 19012.18 211589.03 3756171.20
44 3756171.20 230601.22 17998.32 212602.90 3543568.30
45 3543568.30 230601.22 16979.60 213621.62 3329946.68
46 3329946.68 230601.22 15955.99 214645.22 3115301.45
47 3115301.45 230601.22 14927.49 215673.73 2899627.72
48 2899627.72 230601.22 13894.05 216707.17 2682920.55
49 2682920.55 230601.22 12855.66 217745.56 2465174.99
50 2465174.99 230601.22 11812.30 218788.92 2246386.07
51 2246386.07 230601.22 10763.93 219837.29 2026548.79
52 2026548.79 230601.22 9710.55 220890.67 1805658.12
53 1805658.12 230601.22 8652.11 221949.11 1583709.01
54 1583709.01 230601.22 7588.61 223012.61 1360696.40
55 1360696.40 230601.22 6520.00 224081.21 1136615.18
56 1136615.18 230601.22 5446.28 225154.94 911460.24
57 911460.24 230601.22 4367.41 226233.80 685226.44
58 685226.44 230601.22 3283.38 227317.84 457908.60
59 457908.60 230601.22 2194.15 228407.07 229501.52
60 229501.52 230601.22 1099.69 229501.52 0.00

jeff jeffy answered 1 year ago
Next > < Previous

Related Solutions

Loan Term: 5 Years Amortization Period: 5 years Payment Terms: Monthly at the end of the...
Loan Term: 5 Years Amortization Period: 5 years Payment Terms: Monthly at the end of the month Initial Principal Amount: $12,000,000 Interest Rate: 5.75% annually Develop an Amortization schedule for this particular loan. The schedule must show all payments associated with the loan. At the end of the fifth year of the loan, the loan balance should be zero.
The maturity of the balloon loan is 5 years, amortization period is 30 years, loan amount...
The maturity of the balloon loan is 5 years, amortization period is 30 years, loan amount is $85,000 with annual interest rate 4.5% and monthly payments. What is the monthly payment for this balloon mortgage? What is the balloon payment at the end of five years for this balloon mortgage?
The lender offers the following terms on the commercial loan of $1,500,000: the loan amortization period...
The lender offers the following terms on the commercial loan of $1,500,000: the loan amortization period is 30 years, interest rate is 7.5%, the payments are to be made monthly and the loan matures in 5 years. Prepare an annual debt service table, with beginning balance principal, interest, ending balance and total debt service rows for 5 years. Which line item is tax deductible? If the loan origination fees are 1.5% and due upon closing, what is the dollar value...
Goal-seek Loan Amortization Schedule Amount of the loan: $500,000 Length of the loan: 30 years Payment:...
Goal-seek Loan Amortization Schedule Amount of the loan: $500,000 Length of the loan: 30 years Payment: Equal annual payment Interest rate: Annual interest rate is 3.0% in year 1, and increases 0.1% in year 2, etc. In other words, the annual interest rates will be 3.0%, 3.1%, 3.2%,…. M1 Assignment - Goal-seek Loan Amortization Schedule Rubric Successfully created year-by-year table with Year, Beginning balance, PMT, interest, principal, ending balance 3 pts Successfully created a column to reflect floating interest rates...
loan amount - 1,200,000. terms(years) - 15 interest rate - 3.125% payment frequency - monthly Amortizing...
loan amount - 1,200,000. terms(years) - 15 interest rate - 3.125% payment frequency - monthly Amortizing Loan Assignment
calculate a loan amortization schedule for a $10,000 loan, 5% annual interest, one payment a year...
calculate a loan amortization schedule for a $10,000 loan, 5% annual interest, one payment a year for 10 years, starting on 1/1/2010. All calculations must be shown, i.e., do not use a “package” to complete this question.   Loan period starts from 1/1/2010. I mean the first payment is to be made on 1/1/2010.
What will be the monthly payment on a loan of $602, amortizedover 29 years ,...
What will be the monthly payment on a loan of $602, amortized over 29 years , at an annual interest rate of 9%
Problem 5-50 Amortization Schedule (LG9) Create the amortization schedule for a loan of $4,300, paid monthly...
Problem 5-50 Amortization Schedule (LG9) Create the amortization schedule for a loan of $4,300, paid monthly over two years using an 9 percent APR. (Round your answers to 2 decimal places.)       Month   Beginning Balance   Total Payment   Interest Paid   Principal Paid   Ending Balance 1                               2                               3                               4                               5                               6                               7  ...
Problem 5-49 Amortization Schedule (LG9) Create the amortization schedule for a loan of $14,000, paid monthly...
Problem 5-49 Amortization Schedule (LG9) Create the amortization schedule for a loan of $14,000, paid monthly over three years using a 9 percent APR. (Round your answers to 2 decimal places.)    Month Beginning Balance Total Payment Interest Paid Principal Paid Ending Balance 1                2                3                4                5                6                7...
Loan Amortization Schedule, $80,000,000 at 8% for 15 years Year Beginning Amount Payment Interest Payment of...
Loan Amortization Schedule, $80,000,000 at 8% for 15 years Year Beginning Amount Payment Interest Payment of Principal Ending Balance 1 $     80,000,000.00 $         1,933.28 $            533,333.33 $                (531,400.05) $             80,531,400.05 2 $     80,531,400.05 $         1,933.28 $            536,876.00 $                (534,942.72) $             81,066,342.77 3 $     81,066,342.77 $         1,933.28 $            540,442.29 $                (538,509.01) $             81,604,851.78 4 $     81,604,851.78 $         1,933.28 $            544,032.35 $                (542,099.07) $             82,146,950.84 179 $ 260,404,969.06 $         1,933.28 $        1,736,033.13 $           (1,734,099.85) $          262,139,068.91 180 $ 262,139,068.91 $         1,933.28 $       ...
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT