Reference no: EM131071483
Excel Spreadsheet Assignment
Each student will create a loan amortization table and using Excel based on the following information:
|
|
Loan 1
|
Loan 2
|
Loan 3
|
Loan 4
|
Loan 5
|
|
Mortgage
|
45,000
|
180,000
|
270,000
|
450,000
|
540,000
|
|
interest (6%)
|
0.005
|
0.005
|
0.005
|
0.005
|
0.005
|
|
total payments (30 years)
|
360
|
360
|
360
|
360
|
360
|
|
monthly payment
|
($269.80)
|
($1,079.19)
|
($1,618.79)
|
($2,697.98)
|
($3,237.57)
|
Amortization Schedule for a Loan Payable
Most loans are not repaid in one large payment but paid with smaller installment payments during the agreed upon term of the loan. The payments may be on a monthly, quarterly or semiannual basis but in all cases the payment represents interest and a principal. The payments continue until the loan is completely repaid.
Generally we compute the monthly payment for a mortgage using the following formula:
A = {I x P x (1 + i)n} / {(1 + i)n - 1}
A - the payment amount
i - is the interest rate
P - is the principal or loan amount
n - is the number of periods
The payment is set to make the present value of the payments equal to the amount of the loan at the end of the period. By convention, the quoted mortgage rate is annualized with monthly compounding.
Notes:
- Since we are looking for monthly payment the interest rate needs to be stated monthly (6%/12 = .5%)
- The monthly payment includes interest and principal.
The computation is also available in Excel using the following formula:
@PMT(rate, nper, pv)
Rate - Interest rate (making sure it is stated for the payment ie; monthly, annually or semi-annually)
Nper - number of periods
Pv - present value or the current loan value
Once we know the payment for the life of the loan, we can now create our amortization schedule which will break down the payment between the interest and principal.
|
Amortization schedule for:
|
|
|
|
|
monthly
|
|
|
|
|
Month
|
payment
|
principal
|
interest
|
loan balance
|
|
|
|
6%
|
45,000
|
|
1
|
269.80
|
44.80
|
225.00
|
44,955.20
|
|
2
|
269.80
|
45.02
|
224.78
|
44,910.18
|
|
3
|
269.80
|
45.25
|
224.55
|
44,864.93
|
|
4
|
269.80
|
45.48
|
224.32
|
44,819.45
|
|
5
|
269.80
|
45.70
|
224.10
|
44,773.75
|
|
6
|
269.80
|
45.93
|
223.87
|
44,727.82
|
|
7
|
269.80
|
46.16
|
223.64
|
44,681.66
|
|
8
|
269.80
|
46.39
|
223.41
|
44,635.26
|
|
9
|
269.80
|
46.62
|
223.18
|
44,588.64
|
|
--
|
|
|
|
|
|
--
|
|
|
|
|
|
356
|
269.80
|
263.17
|
6.63
|
1,063.61
|
|
357
|
269.80
|
264.48
|
5.32
|
799.13
|
|
358
|
269.80
|
265.80
|
4.00
|
533.33
|
|
359
|
269.80
|
267.13
|
2.67
|
266.20
|
|
360
|
267.53
|
266.20
|
1.33
|
0.00
|
|
Totals
|
$97,125.73
|
$45,000.00
|
$52,125.73
|
|
Based on the loan amortization schedule we will be able to prepare journal entries for each monthly payment made by the company.
Payment 1
Loan Payable $44.80
Interest Expense $225.00
Cash $269.80
-to record monthly payment on loan
Payment 359
Loan Payable $267.13
Interest Expense $2.67
Cash $269.80
-to record monthly payment on loan
|
Weapons of mass destruction were not found in iraq,
: Even though it is reported that weapons of mass destruction were not found in Iraq, do you agree or disagree with the statement: The Bush administration is justified in toppling Saddam Hussein, considering the threat that he posed to world no longer ..
|
|
Digital signal conditioning lab
: For the comparator below, complete the design so that the threshold voltage is 1.25V which means that if the input is below 1.25V, the output will be HIGH and if its greater, it will be low.
|
|
Traditional project management environment
: What would a project manager focus on when doing risk management in an agile environment that they would not likely focus on in a traditional project management environment?
|
|
What is the capitalized cost of the marina
: An investor is considering the construction of a new marina on the Detroit river at a cost of $68m. M&O costs will average $80,000 per year for the first five years, and rise to $100,000 per year thereafter. A major overhaul costing $12m will be requ..
|
|
Prepare journal entries for monthly payment made by company
: Based on the loan amortization schedule we will be able to prepare journal entries for each monthly payment made by the company.
|
|
Present and the future of religion
: Among the religions we discussed this week, identify one religion that you find the most interesting. Explain your response. Explain at least three ways that social trends affect the formation of new religions and dictate new religious movements.
|
|
Regards to unlimited rights-limited and restricted rights
: Per the text, both the government and industry have specific and valid interests in data, yet the government’s needs may well exceed those of private commercial contractors. From a contractor’s perspective, analyze the government’s data rights with r..
|
|
Number of equivalent units of production
: If Department H had 500 units, 60% completed, in process at the beginning of the period, 6,000 units were completed during the period, and 600 units were 30% completed at the end of the period, what was the number of equivalent units of production..
|
|
What information will be required by the clinician
: In order to diagnose attention deficit/hyperactivity disorder (ADHD), what information will be required by the clinician
|