Question

In: Accounting

Question 1: You are required to produce an amortisation table for a home loan and a...

Question 1: You are required to produce an amortisation table for a home loan and a diagram demonstrating the link between loan repayments and principal outstanding. Please see slide 31 from Topic 2 (or p146 from text) for an example of the layout of the table. The home loan is for $200,000 and is to be amortised over a time period of 30 years requiring annual payments. All calculations should be executed in excel. From your table produce a diagram that demonstrates the relationship between the outstanding principal and the number of years into the loan.

The interest rate to be used is 12% plus the last digit of your student number. Assume that interest rates do not change over the life of the laon.

Solutions

Expert Solution

Answered using goal seek function in excel

Principal $ 200,000.00
Period 30 year
interest rate 12%
Year Interest Annual payments Balance
1 $    24,000.00 $             24,828.73 $      199,171.27
2 $    23,900.55 $             24,828.73 $      198,243.09
3 $    23,789.17 $             24,828.73 $      197,203.53
4 $    23,664.42 $             24,828.73 $      196,039.22
5 $    23,524.71 $             24,828.73 $      194,735.20
6 $    23,368.22 $             24,828.73 $      193,274.69
7 $    23,192.96 $             24,828.73 $      191,638.92
8 $    22,996.67 $             24,828.73 $      189,806.86
9 $    22,776.82 $             24,828.73 $      187,754.95
10 $    22,530.59 $             24,828.73 $      185,456.81
11 $    22,254.82 $             24,828.73 $      182,882.90
12 $    21,945.95 $             24,828.73 $      180,000.11
13 $    21,600.01 $             24,828.73 $      176,771.39
14 $    21,212.57 $             24,828.73 $      173,155.23
15 $    20,778.63 $             24,828.73 $      169,105.13
16 $    20,292.62 $             24,828.73 $      164,569.01
17 $    19,748.28 $             24,828.73 $      159,488.56
18 $    19,138.63 $             24,828.73 $      153,798.45
19 $    18,455.81 $             24,828.73 $      147,425.54
20 $    17,691.06 $             24,828.73 $      140,287.87
21 $    16,834.54 $             24,828.73 $      132,293.68
22 $    15,875.24 $             24,828.73 $      123,340.19
23 $    14,800.82 $             24,828.73 $      113,312.29
24 $    13,597.47 $             24,828.73 $      102,081.03
25 $    12,249.72 $             24,828.73 $        89,502.02
26 $    10,740.24 $             24,828.73 $        75,413.53
27 $      9,049.62 $             24,828.73 $        59,634.42
28 $      7,156.13 $             24,828.73 $        41,961.82
29 $      5,035.42 $             24,828.73 $        22,168.51
30 $      2,660.22 $             24,828.73 $                   0.00

Related Solutions

Question 1: You are required to produce an amortisation table for a home loan and a...
Question 1: You are required to produce an amortisation table for a home loan and a diagram demonstrating the link between loan repayments and principal outstanding. The home loan is for $200,000 and is to be amortised over a time period of 30 years requiring annual payments. All calculations should be executed in excel. From your table produce a diagram that demonstrates the relationship between the outstanding principal and the number of years into the loan. The interest rate to...
You are required to produce a table showing bond values and the impact of changes in...
You are required to produce a table showing bond values and the impact of changes in interest rate over the life of a bond and a diagram demonstrating the link between the changes in values (due to changes in interest rate) and time to maturity. The bond has a face value of $1,000, pays a coupon rate of 7% p.a paid annually and it is issued with 10 years to maturity. All calculations should be executed in excel. Your table...
Question 1 – Case Study (30%) You are required to produce a written assignment of maximum...
Question 1 – Case Study (30%) You are required to produce a written assignment of maximum 500 words for each section. Each section is 15 marks. (a) The management of your company has requested you to propose two CAD system for the new design project that was recently awarded to the company. Your proposal should be a comparative study and include: - i) CAD software name and developer ii) Hardware and system requirements iii) CAD/CAM features and capabilities iv) Price...
You are required to produce a table showing bond valuesand the impact of changes in interest...
You are required to produce a table showing bond valuesand the impact of changes in interest rate over the lifeof a bond and a diagram demonstrating the link between the changes in values(due to changes in interest rate) and time to maturity. The bond has a face value of $1,000, pays a coupon rate of 7% p.a paid annually and it is issued with 10 years to maturity. All calculations should be executed in excel.Your table should show the following:The...
The following table shows the number of weeks of labor required to produce 1 unit of...
The following table shows the number of weeks of labor required to produce 1 unit of each good in each country: Country Beef Petroleum Denmark 6 weeks 5 weeks Western Sahara 20 weeks 40 weeks Which country has the absolute advantage in beef? Which in petroleum? Which country has the comparative advantage in beef? Which in petroleum? Suppose Denmark has 600 weeks of labor available. Construct a production possibilities frontier for Denmark. Construct the Danish consumption possibilities frontier if the...
The following table shows the number of weeks of labor required to produce 1 unit of...
The following table shows the number of weeks of labor required to produce 1 unit of each good in each country Country Beef Petroleum Denmark 6 weeks 5 weeks Western Sahara 20 weeks 40 weeks Which country has the absolute advantage in beef? Which in petroleum? Which country has the comparative advantage in beef? Which in petroleum? Suppose Denmark has 600 weeks of labor available. Construct a production possibilities frontier for Denmark. Construct the Danish consumption possibilities frontier if the...
Table 1 given below shows the number of labor hours required to produce 1 bushel of...
Table 1 given below shows the number of labor hours required to produce 1 bushel of wheat and 1 ton of iron in Russia and North Korea. Table 1 Labor hours to make: In North Korea In Russia 1 bushel of wheat 4 1 1 ton of iron 10 5 You make several Ricardian assumptions: these are the only two commodities, the productivity of labor is constant for each product no matter how much is produced, and competition prevails in...
1. You apply for a home equity loan. The lender gets an appraisal on your home,...
1. You apply for a home equity loan. The lender gets an appraisal on your home, showing a value of $183,500. You have a first mortgage with a current balance of $87,435. Based on a 75% LTV ratio, what is the maximum line of credit you can get? 2. Assume you buy 100 shares of stock at a price of $63.75 per share and incur brokerage fees of $200. You own the stock for 5 years and receive dividends of...
You have a home loan of $150,000. The interest rate is 5.5% and the loan is...
You have a home loan of $150,000. The interest rate is 5.5% and the loan is for 30 years, with monthly payments. If you make a ONE TIME extra principle payment of $22,000 in period number 18, how much do you SAVE in total interest paid of the life of the loan? A. $22,000 B. $48,814 C. $35,712 D. $61,492
u are required to produce a table showing bond valuesand the impact of changes in interest...
u are required to produce a table showing bond valuesand the impact of changes in interest rate over the lifeof a bond and a diagram demonstrating the link between the changes in values(due to changes in interest rate) and time to maturity. The bond has a face value of $1,000, pays a coupon rate of 7% p.a paid annually and it is issued with 10 years to maturity. All calculations should be executed in excel.Your table should show the following:The...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT